Recursive Common Table Expression

Antonis Galaios

Recursive Common Table Expression
Dear listers hi ,



Is it possible to use a recursive common table expression with DSNTIAUL
like the example below ?



//SYSTSIN DD *

DSN SYSTEM(XXXX) RETRY(0) TEST(0)

RUN PROGRAM(DSNTIAUL) PARM('SQL') PLAN(TIBPLAN) -

LIB('DSN810.XXXX.RUNLIB.LOAD')

END

/*

//SYSIN DD *

WITH APPL(AP_A,AP_B) AS

(

SELECT A,B FROM TABLEA JOIN TABLEB

ON TABLEA.A = TABLEB.A

WHERE SOME-CRITERIA

)

SELECT A,B FROM TABLEC JOIN APPL

ON AP_A = TABLEC.A




P Think before you print.

Disclaimer:
This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete the copy from your system.
EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, solicitation, offer or agreement or any information contained in this communication.
EFG Eurobank Ergasias S.A. cannot accept any responsibility for the accuracy or completeness of this message as it has been transmitted over a public network. If you suspect that the message may have been intercepted or amended, please call the sender.


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Zdenek Chmel

Re: Recursive Common Table Expression
(in response to Antonis Galaios)
Hi Antonis,

I didn't test your example, but generally DSNTIAUL can work with a recursive CTE.

Cheers
Zdenek

> ------------ Pùvodní zpráva ------------
> Od: Galeos Antonis <[login to unmask email]>
> Pøedmìt: [DB2-L] Recursive Common Table Expression
> Datum: 25.1.2010 11:16:16
> ----------------------------------------
> Dear listers hi ,
>
>
>
> Is it possible to use a recursive common table expression with DSNTIAUL
> like the example below ?
>
>
>
> //SYSTSIN DD *
>
> DSN SYSTEM(XXXX) RETRY(0) TEST(0)
>
> RUN PROGRAM(DSNTIAUL) PARM('SQL') PLAN(TIBPLAN) -
>
> LIB('DSN810.XXXX.RUNLIB.LOAD')
>
> END
>
> /*
>
> //SYSIN DD *
>
> WITH APPL(AP_A,AP_B) AS
>
> (
>
> SELECT A,B FROM TABLEA JOIN TABLEB
>
> ON TABLEA.A = TABLEB.A
>
> WHERE SOME-CRITERIA
>
> )
>
> SELECT A,B FROM TABLEC JOIN APPL
>
> ON AP_A = TABLEC.A
>
>
>
>
> P Think before you print.
>
> Disclaimer:
> This e-mail is confidential. If you are not the intended recipient, you should
> not copy it, re-transmit it, use it or disclose its contents, but should return
> it to the sender immediately and delete the copy from your system.
> EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion,
> recommendation, conclusion, solicitation, offer or agreement or any information
> contained in this communication.
> EFG Eurobank Ergasias S.A. cannot accept any responsibility for the accuracy or
> completeness of this message as it has been transmitted over a public network.
> If you suspect that the message may have been intercepted or amended, please
> call the sender.
>
>
> _____________________________________________________________________
>
> * IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
> _____________________________________________________________________
>
> http://www.idug.org/events/index.html is your DB2 Events calendar! RUG
> meetings,
> Webcasts, Conferences- what is going on next?
> RUG leaders- get your events on the calendar today!
> _____________________________________________________________________
>
> If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the
> home of IDUG's DB2-L
>
>
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Walter Jani&#223;en

AW: Recursive Common Table Expression
(in response to Zdenek Chmel)
Hi

The problem can be, that you forgot the semicolon at the end of the statement.

Mit freundlichen Grüßen
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Laufzeitarchitektur
Victoriaplatz 2
40477 Düsseldorf
mailto:[login to unmask email]

Vorsitzender des Aufsichtsrates: Jürgen Vetter
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Ina Kirchhof, Dr. Christian Nymphius, Dr. Michael Regauer, Wolfgang Schön.
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf, HRB 37996



________________________________
Von: IDUG DB2-L [mailto:[login to unmask email] Im Auftrag von Galeos Antonis
Gesendet: Montag, 25. Januar 2010 10:42
An: [login to unmask email]
Betreff: [DB2-L] Recursive Common Table Expression

Dear listers hi ,

Is it possible to use a recursive common table expression with DSNTIAUL like the example below ?

//SYSTSIN DD *
DSN SYSTEM(XXXX) RETRY(0) TEST(0)
RUN PROGRAM(DSNTIAUL) PARM('SQL') PLAN(TIBPLAN) -
LIB('DSN810.XXXX.RUNLIB.LOAD')
END
/*
//SYSIN DD *
WITH APPL(AP_A,AP_B) AS
(
SELECT A,B FROM TABLEA JOIN TABLEB
ON TABLEA.A = TABLEB.A
WHERE SOME-CRITERIA
)
SELECT A,B FROM TABLEC JOIN APPL
ON AP_A = TABLEC.A


P Think before you print.



Disclaimer:

This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete the copy from your system.

EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, solicitation, offer or agreement or any information contained in this communication.

EFG Eurobank Ergasias S.A. cannot accept any responsibility for the accuracy or completeness of this message as it has been transmitted over a public network. If you suspect that the message may have been intercepted or amended, please call the sender.



________________________________

[ http://www.idug.org/images/M_images/idug%20na3.jpg ] < http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here. < http://www.idug.org/register >

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Steve Wallace

Re: Recursive Common Table Expression
(in response to Walter Janißen)
Antonis,
I see you've had a couple of replies saying it should work. I have to
say I've not been able to get DSNTIAUL to work with a CTE, I had to go
back to a nested table expression.

Regards,
Steve Wallace
Mainframe DBA
Legal & General Assurance Society
Work: 01737 374308 (7100 4308)



________________________________

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Galeos
Antonis
Sent: 25 January 2010 09:42
To: [login to unmask email]
Subject: [DB2-L] Recursive Common Table Expression



Dear listers hi ,



Is it possible to use a recursive common table expression with DSNTIAUL
like the example below ?



//SYSTSIN DD *

DSN SYSTEM(XXXX) RETRY(0) TEST(0)

RUN PROGRAM(DSNTIAUL) PARM('SQL') PLAN(TIBPLAN) -

LIB('DSN810.XXXX.RUNLIB.LOAD')

END

/*

//SYSIN DD *

WITH APPL(AP_A,AP_B) AS

(

SELECT A,B FROM TABLEA JOIN TABLEB

ON TABLEA.A = TABLEB.A

WHERE SOME-CRITERIA

)

SELECT A,B FROM TABLEC JOIN APPL

ON AP_A = TABLEC.A



P Think before you print.



Disclaimer:

This e-mail is confidential. If you are not the intended recipient, you
should not copy it, re-transmit it, use it or disclose its contents, but
should return it to the sender immediately and delete the copy from your
system.

EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any
opinion, recommendation, conclusion, solicitation, offer or agreement or
any information contained in this communication.

EFG Eurobank Ergasias S.A. cannot accept any responsibility for the
accuracy or completeness of this message as it has been transmitted over
a public network. If you suspect that the message may have been
intercepted or amended, please call the sender.




________________________________

IDUG - The Worldwide DB2 User Community!
< http://www.idug.org/db2-north-america-conference/index.html >

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you
are not already an IDUG member, please register here.
< http://www.idug.org/register >


**********************************************************************
This email (and any attachments) may contain privileged and/or confidential information. If you are not the intended recipient please do not disclose, copy, distribute, disseminate or take any action in reliance on it. If you have received this message in error please reply and tell us and then delete it. Should you wish to communicate with us by email we cannot guarantee the security of any data outside our own computer systems. For the protection of Legal & General's systems and staff, incoming emails will be automatically scanned. Any information contained in this message may be subject to applicable terms and conditions and must not be construed as giving investment advice within or outside the United Kingdom.

Legal & General Group plc is registered in England under company number 1417162 and is a holding company.

The registered office for all companies in the Legal & General group is One Coleman Street London EC2R 5AA.

The following subsidiary companies of Legal & General Group Plc are authorised and regulated by the Financial Services Authority: Legal & General Partnership Services Limited, Legal & General Insurance Limited, Legal & General Assurance Society Limited, Legal & General (Unit Trust Managers) Limited and Legal & General (Portfolio Management Services) Limited.

Legal & General International (Ireland) is incorporated in Ireland under company number 440141 with its registered office at Beaux Lane House, Lower Mercer Street, Dublin 2, Ireland and is authorised by the Financial Regulator in Ireland.

Full details can be found at http://www.legalandgeneralgroup.com

**********************************************************************


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

James Campbell

Re: Recursive Common Table Expression
(in response to Steve Wallace)
What is the actual error message you receive? Or what is it that makes you think it is not
working?

James Campbell

On 26 Jan 2010 at 9:24, Wallace, Stephen wrote:

>
> Antonis,
> I see you've had a couple of replies saying it should work.I have to say I've not been able toget
> DSNTIAUL to work with a CTE,I had to go back to a nested table expression.
>
> Regards,
> Steve Wallace
> Mainframe DBA
> Legal & General Assurance Society
> Work: 01737 374308 (7100 4308)
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Galeos Antonis
> Sent: 25 January 2010 09:42
> To: [login to unmask email]
> Subject: [DB2-L] Recursive Common Table Expression
>
>
> Dear listers hi ,
>
> Is it possible to use a recursive common table expression with DSNTIAULlike the example
> below ?
>
> //SYSTSIN DD *
> DSN SYSTEM(XXXX) RETRY(0) TEST(0)
> RUN PROGRAM(DSNTIAUL) PARM('SQL') PLAN(TIBPLAN) -
> LIB('DSN810.XXXX.RUNLIB.LOAD')
> END
> /*
> //SYSIN DD *
> WITH APPL(AP_A,AP_B) AS
> (
> SELECT A,B FROM TABLEA JOIN TABLEB
> ON TABLEA.A = TABLEB.A
> WHERE SOME-CRITERIA
> )
> SELECT A,B FROM TABLEC JOIN APPL
> ON AP_A = TABLEC.A
>
>
> P Think before you print.
>
> Disclaimer:
> This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its
> contents, but should return it to the sender immediately and delete the copy from your system.
> EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, solicitation,
> offer or agreement or any information contained in this communication.
> EFG Eurobank Ergasias S.A. cannot accept any responsibility for the accuracy or completeness of this message as it
> has been transmitted over a public network. If you suspect that the message may have been intercepted or amended,
> please call the sender.
>
>
>
> IDUG - The Worldwide DB2 User Community!
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an
> IDUG member, please register here.
> **********************************************************************
> This email (and any attachments) may contain privileged and/or confidential information. If you are
> not the intended recipient please do not disclose, copy, distribute, disseminate or take any action
> in reliance on it. If you have received this message in error please reply and tell us and then
> delete it. Should you wish to communicate with us by email we cannot guarantee the security of
> any data outside our own computer systems. For the protection of Legal & General's systems and
> staff, incoming emails will be automatically scanned. Any information contained in this message
> may be subject to applicable terms and conditions and must not be construed as giving
> investment advice within or outside the United Kingdom.
>
> Legal & General Group plc is registered in England under company number 1417162 and is a
> holding company.
>
> The registered office for all companies in the Legal & General group is One Coleman Street
> London EC2R 5AA.
>
> The following subsidiary companies of Legal & General Group Plc are authorised and regulated
> by the Financial Services Authority: Legal & General Partnership Services Limited, Legal &
> General Insurance Limited, Legal & General Assurance Society Limited, Legal & General (Unit
> Trust Managers) Limited and Legal & General (Portfolio Management Services) Limited.
>
> Legal & General International (Ireland) is incorporated in Ireland under company number 440141
> with its registered office at Beaux Lane House, Lower Mercer Street, Dublin 2, Ireland and is
> authorised by the Financial Regulator in Ireland.
>
> Full details can be found at http://www.legalandgeneralgroup.com
> **********************************************************************
>
>
> IDUG - The Worldwide DB2 User Community!
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an
> IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Antonis Galaios

Re: Recursive Common Table Expression
(in response to James Campbell)
Thank you all for your answers . Finally I've tested and it works fine
and with good performance results

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of James
Campbell
Sent: Tuesday, January 26, 2010 12:33 PM
To: [login to unmask email]
Subject: Re: [DB2-L] Recursive Common Table Expression

What is the actual error message you receive? Or what is it that makes
you think it is not
working?

James Campbell

On 26 Jan 2010 at 9:24, Wallace, Stephen wrote:

>
> Antonis,
> I see you've had a couple of replies saying it should work.I have to
say I've not been able toget
> DSNTIAUL to work with a CTE,I had to go back to a nested table
expression.
>
> Regards,
> Steve Wallace
> Mainframe DBA
> Legal & General Assurance Society
> Work: 01737 374308 (7100 4308)
>
>
>
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Galeos
Antonis
> Sent: 25 January 2010 09:42
> To: [login to unmask email]
> Subject: [DB2-L] Recursive Common Table Expression
>
>
> Dear listers hi ,
>
> Is it possible to use a recursive common table expression with
DSNTIAULlike the example
> below ?
>
> //SYSTSIN DD *
> DSN SYSTEM(XXXX) RETRY(0) TEST(0)
> RUN PROGRAM(DSNTIAUL) PARM('SQL') PLAN(TIBPLAN) -
> LIB('DSN810.XXXX.RUNLIB.LOAD')
> END
> /*
> //SYSIN DD *
> WITH APPL(AP_A,AP_B) AS
> (
> SELECT A,B FROM TABLEA JOIN TABLEB
> ON TABLEA.A = TABLEB.A
> WHERE SOME-CRITERIA
> )
> SELECT A,B FROM TABLEC JOIN APPL
> ON AP_A = TABLEC.A
>
>
> P Think before you print.
>
> Disclaimer:
> This e-mail is confidential. If you are not the intended recipient,
you should not copy it, re-transmit it, use it or disclose its
> contents, but should return it to the sender immediately and delete
the copy from your system.
> EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any
opinion, recommendation, conclusion, solicitation,
> offer or agreement or any information contained in this communication.
> EFG Eurobank Ergasias S.A. cannot accept any responsibility for the
accuracy or completeness of this message as it
> has been transmitted over a public network. If you suspect that the
message may have been intercepted or amended,
> please call the sender.
>
>
>
> IDUG - The Worldwide DB2 User Community!
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
you are not already an
> IDUG member, please register here.
> **********************************************************************
> This email (and any attachments) may contain privileged and/or
confidential information. If you are
> not the intended recipient please do not disclose, copy, distribute,
disseminate or take any action
> in reliance on it. If you have received this message in error please
reply and tell us and then
> delete it. Should you wish to communicate with us by email we cannot
guarantee the security of
> any data outside our own computer systems. For the protection of Legal
& General's systems and
> staff, incoming emails will be automatically scanned. Any information
contained in this message
> may be subject to applicable terms and conditions and must not be
construed as giving
> investment advice within or outside the United Kingdom.
>
> Legal & General Group plc is registered in England under company
number 1417162 and is a
> holding company.
>
> The registered office for all companies in the Legal & General group
is One Coleman Street
> London EC2R 5AA.
>
> The following subsidiary companies of Legal & General Group Plc are
authorised and regulated
> by the Financial Services Authority: Legal & General Partnership
Services Limited, Legal &
> General Insurance Limited, Legal & General Assurance Society Limited,
Legal & General (Unit
> Trust Managers) Limited and Legal & General (Portfolio Management
Services) Limited.
>
> Legal & General International (Ireland) is incorporated in Ireland
under company number 440141
> with its registered office at Beaux Lane House, Lower Mercer Street,
Dublin 2, Ireland and is
> authorised by the Financial Regulator in Ireland.
>
> Full details can be found at http://www.legalandgeneralgroup.com
> **********************************************************************
>
>
> IDUG - The Worldwide DB2 User Community!
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If
you are not already an
> IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free
technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L