Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    Ireland
    Posts
    1

    Unanswered: error on Outer join using DB2 SQL

    hi all,
    I'm a new user and I've been struggling with an outer join for a over a day now. I'm more familiar with Oracle SQL+ syntax, and despite researching DB2 SQL, I'm still getting errors.

    Can anyone explain the following error to me? Really sorry if it turns out to be a stupid typo, but I've been looking at this for hours, and I've tried many variations.

    It's a five table join, where three tables are inner joined (ITEM, CUSTOMER and ITEM_ACTIVITY), and then there's an outer join to two other tables which are also inner joined (CUST_CONVER, CUST_CONVER_DTL). After numerous failed attmepts to get the outer join to work, I tried to do it as a join of two-sub selects. I get the following error:

    SQL0199N The use of the reserved word "FROM" following "" is not valid.
    Expected tokens may include: "+ ) - ". SQLSTATE=42601

    I don't see why I should be getting this error. IS my approach completely wrong? I can write this as a five table join in Oracle no problem, but when I tried in DB2 I got various errors or didn't retrieve the right number of rows, etc. Someone suggested I try it like this, but I'm still stuck and I've spent far too long on this already.

    Any help would be extremely appreciated.

    the SQL:

    SELECT TEMP1.*, TEMP2.*
    FROM (select
    a.BUSINESS_UNIT,
    a.CUST_ID,
    a.ITEM,
    a.ENTRY_AMT,
    c.ACCOUNTING_DT,
    a.ENTRY_CURRENCY,
    a.ENTRY_USE_ID
    FROM
    PS_ITEM C,
    PS_CUSTOMER b,
    PS_ITEM_ACTIVITY a
    WHERE a.ITEM_SEQ_NUM = 1
    AND a.ENTRY_TYPE in ( 'CACHQ','CHGDL','OA')
    AND a.BUSINESS_UNIT = c.BUSINESS_UNIT
    AND a.ITEM = c.ITEM
    AND a.CUST_ID = c.CUST_ID
    AND A.ITEM_LINE = C.ITEM_LINE
    AND a.CUST_ID = b.CUST_ID
    AND B.CUST_ID = C.CUST_ID
    AND b.CUSTOMER_TYPE = '2'
    AND c.ITEM_STATUS <> 'C') AS TEMP1
    LEFT OUTER JOIN
    (SELECT
    e.SETID,
    E.CUST_ID,
    E.CONVER_DTTM_INIT,
    E.CONVER_DT,
    E.CONVER_SEQ_NUM,
    E.CONVER_DTTM,
    E.KEYWORD1,
    E.KEYWORD2,
    E.KEYWORD3,
    E.OPRID,
    F.CONVR_QUALIFIER_CD,
    F.CONVR_QUAL_FIELD,
    F.BUSINESS_UNIT,
    F.CONVR_QUAL_FIELD3,
    F.ITEM_LINE
    FROM PS_CUST_CONVER E, PS_CUST_CONVER_DTL F
    WHERE E.SETID = F.SETID
    AND E.CUST_ID = F.CUST_ID
    AND E.CONVER_DTTM_INIT = F.CONVER_DTTM_INIT
    AND E.CONVER_DT = F.CONVER_DT
    AND E.CONVER_SEQ_NUM = F.CONVER_SEQ_NUM
    AND F.CONVR_QUALIFIER_CD = 'I'
    AND E.CONVER_DTTM_INIT = (SELECT MAX(G.CONVER_DTTM_INIT)
    FROM PS_CUST_CONVER G
    WHERE G.SETID = E.SETID
    AND G.CUST_ID = E.CUST_ID
    )
    ) AS TEMP2
    ON
    (TEMP1.CUST_ID =TEMP2.CUST_ID
    AND TEMP1.ITEM = TEMP2.CONVR_QUAL_FIELD)

  2. #2
    Join Date
    Jul 2002
    Location
    ATL
    Posts
    170

    Re: error on Outer join using DB2 SQL

    You are having a syntax error in your query.
    You cannot specify select st in FROM clause.

    Originally posted by kfinna
    hi all,
    I'm a new user and I've been struggling with an outer join for a over a day now. I'm more familiar with Oracle SQL+ syntax, and despite researching DB2 SQL, I'm still getting errors.

    Can anyone explain the following error to me? Really sorry if it turns out to be a stupid typo, but I've been looking at this for hours, and I've tried many variations.

    It's a five table join, where three tables are inner joined (ITEM, CUSTOMER and ITEM_ACTIVITY), and then there's an outer join to two other tables which are also inner joined (CUST_CONVER, CUST_CONVER_DTL). After numerous failed attmepts to get the outer join to work, I tried to do it as a join of two-sub selects. I get the following error:

    SQL0199N The use of the reserved word "FROM" following "" is not valid.
    Expected tokens may include: "+ ) - ". SQLSTATE=42601

    I don't see why I should be getting this error. IS my approach completely wrong? I can write this as a five table join in Oracle no problem, but when I tried in DB2 I got various errors or didn't retrieve the right number of rows, etc. Someone suggested I try it like this, but I'm still stuck and I've spent far too long on this already.

    Any help would be extremely appreciated.

    the SQL:

    SELECT TEMP1.*, TEMP2.*
    FROM (select
    a.BUSINESS_UNIT,
    a.CUST_ID,
    a.ITEM,
    a.ENTRY_AMT,
    c.ACCOUNTING_DT,
    a.ENTRY_CURRENCY,
    a.ENTRY_USE_ID
    FROM
    PS_ITEM C,
    PS_CUSTOMER b,
    PS_ITEM_ACTIVITY a
    WHERE a.ITEM_SEQ_NUM = 1
    AND a.ENTRY_TYPE in ( 'CACHQ','CHGDL','OA')
    AND a.BUSINESS_UNIT = c.BUSINESS_UNIT
    AND a.ITEM = c.ITEM
    AND a.CUST_ID = c.CUST_ID
    AND A.ITEM_LINE = C.ITEM_LINE
    AND a.CUST_ID = b.CUST_ID
    AND B.CUST_ID = C.CUST_ID
    AND b.CUSTOMER_TYPE = '2'
    AND c.ITEM_STATUS <> 'C') AS TEMP1
    LEFT OUTER JOIN
    (SELECT
    e.SETID,
    E.CUST_ID,
    E.CONVER_DTTM_INIT,
    E.CONVER_DT,
    E.CONVER_SEQ_NUM,
    E.CONVER_DTTM,
    E.KEYWORD1,
    E.KEYWORD2,
    E.KEYWORD3,
    E.OPRID,
    F.CONVR_QUALIFIER_CD,
    F.CONVR_QUAL_FIELD,
    F.BUSINESS_UNIT,
    F.CONVR_QUAL_FIELD3,
    F.ITEM_LINE
    FROM PS_CUST_CONVER E, PS_CUST_CONVER_DTL F
    WHERE E.SETID = F.SETID
    AND E.CUST_ID = F.CUST_ID
    AND E.CONVER_DTTM_INIT = F.CONVER_DTTM_INIT
    AND E.CONVER_DT = F.CONVER_DT
    AND E.CONVER_SEQ_NUM = F.CONVER_SEQ_NUM
    AND F.CONVR_QUALIFIER_CD = 'I'
    AND E.CONVER_DTTM_INIT = (SELECT MAX(G.CONVER_DTTM_INIT)
    FROM PS_CUST_CONVER G
    WHERE G.SETID = E.SETID
    AND G.CUST_ID = E.CUST_ID
    )
    ) AS TEMP2
    ON
    (TEMP1.CUST_ID =TEMP2.CUST_ID
    AND TEMP1.ITEM = TEMP2.CONVR_QUAL_FIELD)

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: error on Outer join using DB2 SQL

    I think you shoud do the following :

    select * from
    (
    select a,b from tab1 t1
    left outer join
    select c,d from tab2 t2
    on t1.a=t2.a
    )
    as temp


    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •