Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2003
    Posts
    10

    Unanswered: Db2 Stoered procedure

    Hi

    I am new to the Db2 Stored procedure so would like to get some help on it

    I am writing an Outbound script to output data from my Db2 Contact table in a flat file. The Field Date value c1 is stored as 400.00

    I would like to get an output value Field c2 as 00000040000 in my flat file. This field has to be of 11 characters always prefixed by zeros and therefore if the value of field c1 is 20.00 then the value of field c2 would be
    00000002000 in my output file

    Can some one write an Db2 SQL Statement for me...

    Any help would be appreciated

    Thanks

    Gautam S

  2. #2
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Db2 Stoered procedure

    Here is a start:

    SELECT CONCAT(REPEAT('0',11-LENGTH(CHAR(c1))),CHAR(c1)) AS c2 FROM xxx WHERE .....

    Originally posted by gsinha
    Hi

    I am new to the Db2 Stored procedure so would like to get some help on it

    I am writing an Outbound script to output data from my Db2 Contact table in a flat file. The Field Date value c1 is stored as 400.00

    I would like to get an output value Field c2 as 00000040000 in my flat file. This field has to be of 11 characters always prefixed by zeros and therefore if the value of field c1 is 20.00 then the value of field c2 would be
    00000002000 in my output file

    Can some one write an Db2 SQL Statement for me...

    Any help would be appreciated

    Thanks

    Gautam S

  3. #3
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Db2 Stoered procedure

    Further enhancement to prior post

    SELECT CONCAT(REPEAT('0',11-LENGTH(CAST(c1 as INTEGER))),CHAR(CAST(c1 as integer))) FROM xxx WHERE ....

    Originally posted by dmmac
    Here is a start:

    SELECT CONCAT(REPEAT('0',11-LENGTH(CHAR(c1))),CHAR(c1)) AS c2 FROM xxx WHERE .....

  4. #4
    Join Date
    May 2002
    Location
    General Deheza, Cba, Arg.
    Posts
    276

    Re: Db2 Stoered procedure

    HI,


    With the EXPORT function do you create a flat file.

    For example:

    EXPORT TO "C:\Cotizacion.txt" OF DEL MODIFIED BY COLDEL; MESSAGES "C:\Cotizacion.txt" SELECT * FROM DB2DWAD1.A0_COTIZACION_DIA;

  5. #5
    Join Date
    Oct 2003
    Posts
    10

    Re: Db2 Stoered procedure

    Hi

    when i am running the query for an input value of 400.00

    i am getting an output value of 0000000400

    The lengh should be of 11 characters and not 10 . So when you change 11 to 12 i am getting the the value of 11 characters but the value that i intend to get is

    00000040000 . The 2 zeros after the decimals are not being captured...

    Any Inputs ?????????????????

    regards

    Gautam S


    Originally posted by dmmac
    Further enhancement to prior post

    SELECT CONCAT(REPEAT('0',11-LENGTH(CAST(c1 as INTEGER))),CHAR(CAST(c1 as integer))) FROM xxx WHERE ....

  6. #6
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Db2 Stoered procedure

    Then you must multiple c1 by 100. How does that output look?

    SELECT CONCAT(REPEAT('0',11-LENGTH(CAST((c1*100) as INTEGER))),CHAR(CAST((c1*100) as integer))) FROM xxx WHERE ....

    Originally posted by gsinha
    Hi

    when i am running the query for an input value of 400.00

    i am getting an output value of 0000000400

    The lengh should be of 11 characters and not 10 . So when you change 11 to 12 i am getting the the value of 11 characters but the value that i intend to get is

    00000040000 . The 2 zeros after the decimals are not being captured...

    Any Inputs ?????????????????

    regards

    Gautam S

  7. #7
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Db2 Stoered procedure

    Amendment to my below post (ignore last post). With a little bit more refinement this is what you want:

    SELECT
    CONCAT(REPEAT('0',11-LENGTH(CAST(CAST((c1*100) as INTEGER) as char(5)))),CAST(CAST((c1*100) as integer) as char(5)))
    FROM xxx WHERE .....


    Originally posted by dmmac
    Then you must multiple c1 by 100. How does that output look?

    SELECT CONCAT(REPEAT('0',11-LENGTH(CAST((c1*100) as INTEGER))),CHAR(CAST((c1*100) as integer))) FROM xxx WHERE ....

  8. #8
    Join Date
    Oct 2003
    Posts
    10

    Re: Db2 Stoered procedure

    Hi

    There are still some issues with this syntax.

    When the input value is 90.00

    The output value is 0000009000 which is of 10 characters and not 11 which I want. There should be an 0 in addition to the 6 zeros that we have. The final output value should be 00000009000 (Total Length of 11)

    When the input is 900.00
    The output value is a Perfect which is 00000090000 (Total 11 Characters)


    When the input value is 9000.00

    The output value is 00000090000 which is wrong as 9000.00 will have to be represented as 900000 and there will be 5 zeros in front of it meaning 00000900000 (Total length of 11)


    Any Views


    Thanks

    Gautam S






    Originally posted by dmmac
    Amendment to my below post (ignore last post). With a little bit more refinement this is what you want:

    SELECT
    CONCAT(REPEAT('0',11-LENGTH(CAST(CAST((c1*100) as INTEGER) as char(5)))),CAST(CAST((c1*100) as integer) as char(5)))
    FROM xxx WHERE .....

  9. #9
    Join Date
    Aug 2003
    Location
    Massachusetts, USA
    Posts
    106

    Re: Db2 Stoered procedure

    I just tweaked the statement adding an additional DB2 function and changing the CHAR() length:

    CONCAT(REPEAT('0',11-LENGTH(RTRIM(CAST(CAST((c1*100) as INTEGER) as char(11))))),RTRIM(CAST(CAST((c1*100) as integer) as char(11))))

    I suggest going to the IBM web site and looking through the SQL reference manuals

    http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main


    Originally posted by gsinha
    Hi

    There are still some issues with this syntax.

    When the input value is 90.00

    The output value is 0000009000 which is of 10 characters and not 11 which I want. There should be an 0 in addition to the 6 zeros that we have. The final output value should be 00000009000 (Total Length of 11)

    When the input is 900.00
    The output value is a Perfect which is 00000090000 (Total 11 Characters)


    When the input value is 9000.00

    The output value is 00000090000 which is wrong as 9000.00 will have to be represented as 900000 and there will be 5 zeros in front of it meaning 00000900000 (Total length of 11)


    Any Views


    Thanks

    Gautam S

  10. #10
    Join Date
    Nov 2003
    Location
    Netherlands
    Posts
    96

    Re: Db2 Stoered procedure

    Another solution could be:

    char(right(digits(decimal(c1,11,2)),11),11)



    Originally posted by dmmac
    I just tweaked the statement adding an additional DB2 function and changing the CHAR() length:

    CONCAT(REPEAT('0',11-LENGTH(RTRIM(CAST(CAST((c1*100) as INTEGER) as char(11))))),RTRIM(CAST(CAST((c1*100) as integer) as char(11))))

    I suggest going to the IBM web site and looking through the SQL reference manuals

    http://www-3.ibm.com/cgi-bin/db2www/...bs.d2w/en_main

Posting Permissions

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