Hi All,
Please help me out in executing the following db2 querry in unix

db2 "select AP_RQ_ACQ_INST_ID || ',' || txn_classifier || ',' || AP_RS_RESP_CD || ',' || count(*) || ',' || decimal(SUM(AP_RQ_TXN_AMT) 10,5) from TXN_RECORD where
CREATE_TS > '2010-11-22 11:00:00.008645'
and CREATE_TS < '2010-11-22 23:00:00.008645'
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD with ur" > vin11.csv


Error:


SQL0440N No authorized routine named "||" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884


Please let me know what needs to be done

Regards,
Duddu

"select AP_RQ_ACQ_INST_ID || ',' || txn_classifier || ',' || AP_RS_RESP_CD || ',' || B.CNT || ',' || B.SUM1 from TXN_RECORD A
JOIN (SELECT COUNT(*) AS CNT,decimal(SUM(AP_RQ_TXN_AMT) 10,5) SUM1,AP_RQ_ACQ_INST_ID FROM TXN_RECORD
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD) B on B.AP_RQ_ACQ_INST_ID = A.AP_RQ_ACQ_INST_ID

where
CREATE_TS > '2010-11-22 11:00:00.008645'
and CREATE_TS < '2010-11-22 23:00:00.008645'
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD
with ur

Thanks Rajesh for helping me out

Tried to execute its giving me an error like:

SQL0104N An unexpected token "10" was found following "l(SUM(AP_RQ_TXN_AMT)".
Expected tokens may include: "<space>". SQLSTATE=42601


Please help me out Rajesh..

My Bad,try this:
select AP_RQ_ACQ_INST_ID || ',' || txn_classifier || ',' || AP_RS_RESP_CD || ',' || B.CNT || ',' || B.SUM1 from TXN_RECORD A
JOIN (SELECT COUNT(*) AS CNT,decimal(SUM(AP_RQ_TXN_AMT) ,10,5) SUM1,AP_RQ_ACQ_INST_ID FROM TXN_RECORD
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD) B on B.AP_RQ_ACQ_INST_ID = A.AP_RQ_ACQ_INST_ID

where
CREATE_TS > '2010-11-22 11:00:00.008645'
and CREATE_TS < '2010-11-22 23:00:00.008645'
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD
with ur

thanks Rajesh,

SQL0203N A reference to column "B.AP_RQ_ACQ_INST_ID" is ambiguous.
SQLSTATE=42702

After modifying..
please help me out Rajesh


Regards,
Duddu

Try this:
select A.AP_RQ_ACQ_INST_ID || ',' || txn_classifier || ',' || AP_RS_RESP_CD || ',' || TO_CHAR(B.CNT) || ',' || TO_CHAR(B.SUM1) from TXN_RECORD A
JOIN (SELECT COUNT(*) AS CNT,decimal(SUM(AP_RQ_TXN_AMT) ,10,5) SUM1,AP_RQ_ACQ_INST_ID FROM TXN_RECORD
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD) B on B.AP_RQ_ACQ_INST_ID = A.AP_RQ_ACQ_INST_ID

where
CREATE_TS > '2010-11-22 11:00:00.008645'
and CREATE_TS < '2010-11-22 23:00:00.008645'
group by A.AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD
with ur

SQL0440N No authorized routine named "TO_CHAR" of type "FUNCTION" having
compatible arguments was found. SQLSTATE=42884


Please help out Rajesh

"||" is an alias for the CONCAT function, which can only operate on character expressions. you will need to convert all non-character columns in your select list to character data types, using whatever means are available in your version of DB2. Obviously, TO_CHAR is not one of them, so I presume your DB2 version is lower than 9.7.

select A.AP_RQ_ACQ_INST_ID || ',' || txn_classifier || ',' || AP_RS_RESP_CD || ',' || CHAR(B.CNT) || ',' || CHAR(B.SUM1) from TXN_RECORD A
JOIN (SELECT COUNT(*) AS CNT,decimal(SUM(AP_RQ_TXN_AMT) ,10,5) SUM1,AP_RQ_ACQ_INST_ID FROM TXN_RECORD
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD) B on B.AP_RQ_ACQ_INST_ID = A.AP_RQ_ACQ_INST_ID

where
CREATE_TS > '2010-11-22 11:00:00.008645'
and CREATE_TS < '2010-11-22 23:00:00.008645'
group by A.AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD
with ur

Hi,
I converted everything into char now i was able to view the output in the file but some column names are missing

SQL:

db2 "select AP_RQ_ACQ_INST_ID || ',' || txn_classifier || ',' || AP_RS_RESP_CD || ',' || char(count(*)) || ',' || char( decimal(SUM(AP_RQ_TXN_AMT),10,5)) AS TOTAL_AMT from TXN_RECORD where
CREATE_TS > '2010-11-22 11:00:00.008645'
and CREATE_TS < '2010-11-22 23:00:00.008645'
group by AP_RQ_ACQ_INST_ID,txn_classifier,AP_RS_RESP_CD with ur" >>vin.csv;

output:


TOTAL_AMT
----------------------------------------------------------------------------------------------------------------------------------------------------
60300000002,ACTIVATION,00,539 ,16207.50000
60300000002,ACTIVATION,08,3 ,00200.00000
60300000002,ACTIVATION,74,5 ,00200.00000


i was not able to view the other column names except totalamt
and also i see 12 digits taken for count in the sql

Please help me out please

and also i see the following message at the end of the rows as


SQL0413N Overflow occurred during numeric data type conversion.
SQLSTATE=22003




please help me please

There are no "other columns": since you concatenate everything into one string, your query outputs a single column, to which you assign the alias "TOTAL_AMT".

Clearly, there's only one place in the query where you do numeric data type conversion: "decimal(SUM(AP_RQ_TXN_AMT),10,5)".

The column you are mentioning is a double column
so i was converting this column to decimal and char

please help me out what needs to be done for this


char(decimal(SUM(AP_RQ_TXN_AMT),10,5))

is this correct or not..

You convert the values to DECIMAL(10, 5), which means only values between -99999.99999 and 99999.99999 will fit this data type. If the SUM() exceeds those boundaries, you will get the error. Either you reduce the number of rows, or you increase the precision and/or scale for the DECIMAL data type you cast to.