Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2011
    Posts
    1

    Unanswered: simple db2 query question

    Hi - I am a complete newcomer to db2 (but have been an mssql dba for several years) when I run the below I get the error message 'unable to extract query -ibm serie access odbc driver token & was not valid. valid tokens (+ etc ' I would appreciate any help.
    Thanks, John


    SELECT &lib.F4801.WAMCU AS "Company #", Company.ABALPH AS "Company", &lib.F4801.WADOCO AS "Workorder #", &lib.F4801.WAPARS AS "Parent WO #", &lib.F4801.WAVR01 AS "Ref", &lib.F4801.WADL01 AS "Description", DATE(DIGITS( DECIMAL( &lib.F0911.GLDGJ + 1900000,7,0)) )
    AS "Date", &lib.F4801.WASRST AS "Status", &lib.F4801.WAAMTO /100 AS "Budgeted", &lib.F0911.GLPO AS "PO Number", &lib.F0911.GLEXA AS "Explanation - Name Alpha", &lib.F0911.GLAA /100 AS "Amount", Originator.ABALPH AS "Originator", &lib.F0101.ABALPH AS "Manager"
    FROM (((((&lib.F0911 INNER JOIN &lib.F4801 ON (&lib.F0911.GLSBL = &lib.F4801.WADOCO)) LEFT JOIN &lib.F0101 Originator ON (&lib.F4801.WAAN8 = Originator.ABAN8)) LEFT JOIN &lib.F0101 ON (&lib.F4801.WAANSA = &lib.F0101.ABAN8)) INNER JOIN &lib.F0101 Company ON (TRIM(&lib.F4801.WAMCU) = TRIM(Company.ABAN8) )) )
    WHERE (( &lib.F0911.GLOBJ ='2003') AND ( &lib.F0911.GLFY &SPARM02) AND ( &lib.F4801.WADOCO <11111111) AND ( &lib.F4801.WAPARS <>'11111111') AND ( &lib.F0911.GLSBLT ='W') AND ( &lib.F4801.WASRST &SPARM01) AND ( &lib.F0911.GLDCT <>'33') AND ( &lib.F4801.WATYPS &SPARM03) AND ( &lib.F0911.GLLT ='AA') )
    ORDER BY "Parent WO #", "Workorder #"

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can't use "&lib." in an SQL statement.
    Also, I saw "&SPARM01", "&SPARM02" and "&SPARM03" which are not valid.

    Replace them with valid characters before excution of your query.

    Another isuue:
    Use of &SPARM0x are not valid, like...
    ( &lib.F0911.GLFY &SPARM02 )
    Add a operator like "=", "<>", so on.
    Last edited by tonkuma; 08-02-11 at 22:37.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that you are using too many useless parenthese.

    Here is an example of rewriting which removed useless parentheses, added correlation names and chnged sequence of joins.
    Code:
    SELECT F48.WAMCU        AS "Company #"
         , Cmp.ABALPH       AS "Company"
         , F48.WADOCO       AS "Workorder #"
         , F48.WAPARS       AS "Parent WO #"
         , F48.WAVR01       AS "Ref"
         , F48.WADL01       AS "Description"
         , DATE(
              DIGITS(
                 DECIMAL(
                    F09.GLDGJ + 1900000
                  , 7 , 0
                 )
              )
           )                AS "Date"
         , F48.WASRST       AS "Status"
         , F48.WAAMTO /100  AS "Budgeted"
         , F09.GLPO         AS "PO Number"
         , F09.GLEXA        AS "Explanation - Name Alpha"
         , F09.GLAA /100    AS "Amount"
         , Org.ABALPH       AS "Originator"
         , Mgr.ABALPH       AS "Manager"
     FROM  &lib.F0911 F09
     INNER JOIN
           &lib.F4801 F48
       ON  F48.WADOCO = F09.GLSBL
     INNER JOIN
           &lib.F0101 Cmp
       ON  TRIM(Cmp.ABAN8) = TRIM(F48.WAMCU)
    /* ON  Cmp.ABAN8  = F48.WAMCU */
     LEFT  JOIN
           &lib.F0101 Org
       ON  Org.ABAN8  = F48.WAAN8
     LEFT  JOIN
           &lib.F0101 Mgr
       ON  Mgr.ABAN8  = F48.WAANSA
     WHERE F09.GLOBJ  =  '2003'
       AND F09.GLFY   &SPARM02
       AND F09.GLSBLT =  'W'
       AND F09.GLDCT  <> '33'
       AND F09.GLLT   =  'AA'
       AND F48.WADOCO <  11111111
       AND F48.WAPARS <> '11111111'
       AND F48.WASRST &SPARM01
       AND F48.WATYPS &SPARM03
     ORDER BY
           "Parent WO #"
         , "Workorder #" 
    ;
    If Cmp.ABAN8 and F48.WAMCU were left justified(i.e. no left blanks),
    ON TRIM(Cmp.ABAN8) = TRIM(F48.WAMCU)
    can be replaced by
    ON Cmp.ABAN8 = F48.WAMCU

Posting Permissions

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