CODEWITHSUNDEEP
unixjoindb2export
Ankur Anand
Ankur Anand

Reputation: 11

how to write db2 query with join and export clause in unix

I am getting errors in this query with SQLSTATE=42703 SQL0206N

db2 export to prc_sec_01.txt of del 
SELECT PRICE.PRC_SECURITY_ID,
 \(decimal\(PRICE.PRC_PRICE\)\),
 PRICE.PRC_CURRENCY,PRICE.PRC_CURRENCY
FROM RISKAPP.PRICE as PRICE
 inner join RISKAPP.SECURITY as SECURITY
 on PRICE.PRC_SECURITY_ID = SECURITY.SEC_SECURITY_ID
 and SECURITY.SEC_TYPE = "E"
 AND SECURITY.SEC_MATURITY_DATE >='20141118'
 AND PRICE.PRC_EXTRACT_PERIOD ='PBNS'
 AND PRICE.PRC_LAST_UPDATE_TMS >= '2014-10-01'

Upvotes: 0

Views: 250

Answers (1)

AngocA
AngocA

Reputation: 7693

First, you need to test the query works as desired. Please try to run just the select. If there are many rows, you can limit the result with fetch first x rows only

db2 "
SELECT PRICE.PRC_SECURITY_ID,
 \(decimal\(PRICE.PRC_PRICE\)\),
 PRICE.PRC_CURRENCY,PRICE.PRC_CURRENCY
FROM RISKAPP.PRICE as PRICE
 inner join RISKAPP.SECURITY as SECURITY
 on PRICE.PRC_SECURITY_ID = SECURITY.SEC_SECURITY_ID
 and SECURITY.SEC_TYPE = "E"
 AND SECURITY.SEC_MATURITY_DATE >='20141118'
 AND PRICE.PRC_EXTRACT_PERIOD ='PBNS'
 AND PRICE.PRC_LAST_UPDATE_TMS >= '2014-10-01'
 fetch first 10 rows only"

If the previous query works well, you just need to add the export statement at the beginning.

db2 export to prc_sec_01.txt of del 
SELECT PRICE.PRC_SECURITY_ID,
...

As recommended by @mustaccio, it is a good practice to surround everything into quotes, instead of escape characters:

db2 "export to prc_sec_01.txt of del 
SELECT PRICE.PRC_SECURITY_ID,
 decimal(PRICE.PRC_PRICE),
 PRICE.PRC_CURRENCY,PRICE.PRC_CURRENCY
FROM RISKAPP.PRICE as PRICE
 inner join RISKAPP.SECURITY as SECURITY
 on PRICE.PRC_SECURITY_ID = SECURITY.SEC_SECURITY_ID
 and SECURITY.SEC_TYPE = "E"
 AND SECURITY.SEC_MATURITY_DATE >='20141118'
 AND PRICE.PRC_EXTRACT_PERIOD ='PBNS'
 AND PRICE.PRC_LAST_UPDATE_TMS >= '2014-10-01'"

Upvotes: 2

Related Questions