Results 1 to 14 of 14
  1. #1
    Join Date
    Nov 2010
    Posts
    7

    Unanswered: Please help me out in executing the following db2 querry getting a comma delimited

    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

  2. #2
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by duddu9 View Post
    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
    Last edited by Rajesh1203; 11-29-10 at 18:47.

  3. #3
    Join Date
    Nov 2010
    Posts
    7
    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..

  4. #4
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by duddu9 View Post
    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

  5. #5
    Join Date
    Nov 2010
    Posts
    7
    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

  6. #6
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by duddu9 View Post
    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

  7. #7
    Join Date
    Nov 2010
    Posts
    7
    SQL0440N No authorized routine named "TO_CHAR" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884


    Please help out Rajesh

  8. #8
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    "||" 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.

  9. #9
    Join Date
    Aug 2010
    Posts
    40
    Quote Originally Posted by duddu9 View Post
    SQL0440N No authorized routine named "TO_CHAR" of type "FUNCTION" having
    compatible arguments was found. SQLSTATE=42884


    Please help out Rajesh
    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

  10. #10
    Join Date
    Nov 2010
    Posts
    7

    Please help me

    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

  11. #11
    Join Date
    Nov 2010
    Posts
    7
    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

  12. #12
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by duddu9 View Post
    i was not able to view the other column names except totalamt
    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".

    Quote Originally Posted by duddu9 View Post
    SQL0413N Overflow occurred during numeric data type conversion.
    SQLSTATE=22003
    Clearly, there's only one place in the query where you do numeric data type conversion: "decimal(SUM(AP_RQ_TXN_AMT),10,5)".

  13. #13
    Join Date
    Nov 2010
    Posts
    7
    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..

  14. #14
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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