simple db2 query question

newbietodb2 · #1 (**permalink**) 08-02-11, 16:18

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 #"

tonkuma · #2 (**permalink**) 08-02-11, 21:33

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.

tonkuma · #3 (**permalink**) 08-03-11, 00:27

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

Internet Services

Web Development

Digital Marketing

Consumer Tech