DB2 SQL - problem - IBM AS400

This is a discussion on DB2 SQL - problem - IBM AS400 ; Someone can tell me why with DB2 the table T2 is not reachable in the subselect where i've put the caracter : $. Note $ included in the real query. Thanks a lot and have a great day. SELECT * ...

+ Reply to Thread
Results 1 to 4 of 4

Thread: DB2 SQL - problem

  1. DB2 SQL - problem

    Someone can tell me why with DB2 the table T2 is not reachable in the
    subselect where i've put the caracter : $. Note $ included in the
    real query.

    Thanks a lot and have a great day.

    SELECT *
    from calchist T1
    Join CALCHINEU T2
    Using (XVGRP,XVPSUB,XVBFTY,XVPRLC,XVBFSC,XVPRSC)
    WHERE
    ((T1.xvedcy*100000)+(T1.xvedyr*10000)+(T1.xvedmt*1 00)+xveddy)

    <

    (select min(((T3.xvedcy*100000)+(T3.xvedyr*10000)+
    (T3.xvedmt*100)+T3.xveddy))
    from
    ( select T3.xvedcy,t3.xvedyr,t3.xvedmt,t3.xveddy
    from calchist t3
    WHERE t3.xvgrp= $t2.XVGRP and T3.xvpsub=T2.XVPSUB and
    T3.xvbfty=T2.xvbfty AND T3.XVPRLC=T2.XVPRLC and
    T3.xvbfsc=T2.xvbfsc and T3.XVPRSC=T2.XVPRSC
    ORDER BY xvedcy desc,xvedyr desc, xvedmt desc, xveddy desc
    fetch first 9 rows only) as
    temptable)

  2. Re: DB2 SQL - problem

    >*Note $ included in the
    > real query.


    Why do you have a $ to start with ?

  3. Re: DB2 SQL - problem

    On Jul 16, 3:30*pm, Karlitos wrote:
    > Someone can tell me why with DB2 the table T2 is not reachable in the
    > subselect where i've put the caracter : $. *Note $ included in the
    > real query.
    >
    > Thanks a lot and have a great day.
    >
    > SELECT *
    > from calchist T1
    > * * *Join CALCHINEU T2
    > * * * * *Using (XVGRP,XVPSUB,XVBFTY,XVPRLC,XVBFSC,XVPRSC)
    > WHERE
    > ((T1.xvedcy*100000)+(T1.xvedyr*10000)+(T1.xvedmt*1 00)+xveddy)
    >
    > <
    >
    > (select min(((T3.xvedcy*100000)+(T3.xvedyr*10000)+
    > * * * * * * *(T3.xvedmt*100)+T3.xveddy))
    > from
    > ( select T3.xvedcy,t3.xvedyr,t3.xvedmt,t3.xveddy
    > * * * *from calchist t3
    > WHERE t3.xvgrp= $t2.XVGRP and T3.xvpsub=T2.XVPSUB and
    > * * * T3.xvbfty=T2.xvbfty AND T3.XVPRLC=T2.XVPRLC and
    > * * * T3.xvbfsc=T2.xvbfsc and T3.XVPRSC=T2.XVPRSC
    > ORDER BY xvedcy desc,xvedyr desc, xvedmt desc, xveddy desc
    > * *fetch first 9 rows only) as
    > temptable)


    Try moving the T2 outside the first 9 part like this..

    ..>
    (select min(((T3.xvedcy*100000)+(T3.xvedyr*10000)+
    (T3.xvedmt*100)+T3.xveddy))
    from
    ( select T3.xvedcy,t3.xvedyr,t3.xvedmt,t3.xveddy ,t3.xvgrp (more flds)
    from calchist t3
    ORDER BY xvedcy desc,xvedyr desc, xvedmt desc, xveddy desc
    fetch first 9 rows only) as
    temptable WHERE t3.xvgrp= t2.XVGRP (more flds) )


    Jonathan

  4. Re: DB2 SQL - problem


    "Karlitos" skrev i en meddelelse
    news:7d799e28-2f1a-4bad-a7d2-d17284f34171@e53g2000hsa.googlegroups.com...
    > Someone can tell me why with DB2 the table T2 is not reachable in the
    > subselect where i've put the caracter : $. Note $ included in the
    > real query.
    >
    > Thanks a lot and have a great day.



    I'm not in the least sure this will help you, but shouldn't the outer
    subselect reference temptable, in stead of T3?

    SELECT *
    from calchist T1
    Join CALCHINEU T2
    Using (XVGRP,XVPSUB,XVBFTY,XVPRLC,XVBFSC,XVPRSC)
    WHERE
    ((T1.xvedcy*100000)+(T1.xvedyr*10000)+(T1.xvedmt*1 00)+xveddy)

    <

    (select min(((temptable.xvedcy*100000)+(temptable.xvedyr*1 0000)+
    (temptable.xvedmt*100)+temptable.xveddy))
    from
    ( select T3.xvedcy,t3.xvedyr,t3.xvedmt,t3.xveddy
    from calchist t3
    WHERE t3.xvgrp= $t2.XVGRP and T3.xvpsub=T2.XVPSUB and
    T3.xvbfty=T2.xvbfty AND T3.XVPRLC=T2.XVPRLC and
    T3.xvbfsc=T2.xvbfsc and T3.XVPRSC=T2.XVPRSC
    ORDER BY xvedcy desc,xvedyr desc, xvedmt desc, xveddy desc
    fetch first 9 rows only) as
    temptable)



+ Reply to Thread