Results 1 to 4 of 4

Thread: sql question

  1. #1
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18

    Unanswered: sql question

    The environmentis udb 8.1 fixpack 4 on aix 5.2

    One of the app personal gave me this sql

    u060esp1:/tmp>cat 1.sql
    select distinct rtrim(char(reg_msu_qy))
    concat ' for ' concat char(reg_rtl_prc_am) as test
    from esp.adv_grp_prm_prc where reg_rtl_prc_am=2.990;

    u060esp1:/tmp>db2 -tf 1.sql

    TEST
    ---------------------
    1 for 00002.990
    2 for 00002.990

    2 record(s) selected.

    The app personal asked me this, how to change ths sql to get rid of
    the leading zeros means she wants to see the output like this.
    TEST
    ---------------------
    1 for 2.990
    2 for 2.990

    Please note one point here, the value 2.990 is shown only for sample, in the table there may be several values like 13.990 or 123.449 etc.,

    Actually I suggested like this,
    select distinct rtrim(char(reg_msu_qy)) concat ' for '
    concat substr(char(reg_rtl_prc_am),5,6) as test
    from esp.adv_grp_prm_prc
    where reg_rtl_prc_am=2.990;
    But it works only for values like 2.990 or 1.990 or 4.990 but my answer is not generic one to ger rid of leading zeros.

    If somebody share there knowledge to get rid of leading zeros in udb,it will be grate.

    Thanks
    Jagadish
    jagadish dara

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

    Re: sql question

    To further your example
    .... ' for ' concat RTRIM(char(INTEGER(SUBSTR(char(reg_rtl_prc_am),1,P OSSTR(char(reg_rtl_prc_am),'.') - 1)))) concat SUBSTR(char(reg_rtl_prc_am),POSSTR(char(reg_rtl_pr c_am),'.')) )

    Originally posted by jdara1
    The environmentis udb 8.1 fixpack 4 on aix 5.2

    One of the app personal gave me this sql

    u060esp1:/tmp>cat 1.sql
    select distinct rtrim(char(reg_msu_qy))
    concat ' for ' concat char(reg_rtl_prc_am) as test
    from esp.adv_grp_prm_prc where reg_rtl_prc_am=2.990;

    u060esp1:/tmp>db2 -tf 1.sql

    TEST
    ---------------------
    1 for 00002.990
    2 for 00002.990

    2 record(s) selected.

    The app personal asked me this, how to change ths sql to get rid of
    the leading zeros means she wants to see the output like this.
    TEST
    ---------------------
    1 for 2.990
    2 for 2.990

    Please note one point here, the value 2.990 is shown only for sample, in the table there may be several values like 13.990 or 123.449 etc.,

    Actually I suggested like this,
    select distinct rtrim(char(reg_msu_qy)) concat ' for '
    concat substr(char(reg_rtl_prc_am),5,6) as test
    from esp.adv_grp_prm_prc
    where reg_rtl_prc_am=2.990;
    But it works only for values like 2.990 or 1.990 or 4.990 but my answer is not generic one to ger rid of leading zeros.

    If somebody share there knowledge to get rid of leading zeros in udb,it will be grate.

    Thanks
    Jagadish

  3. #3
    Join Date
    Dec 2002
    Location
    cincinnati
    Posts
    18

    works well

    Really it is grate, Your sql is producing what I want, I am just pasting what I ran in our aix box.

    cat 20.sql

    select distinct rtrim(char(reg_msu_qy)) concat ' for '
    concat RTRIM(char(INTEGER(SUBSTR(char(reg_rtl_prc_am),1,P OSSTR(char(reg_
    rtl_prc_am),'.') - 1)))) concat SUBSTR(char(reg_rtl_prc_am),POSSTR(char(reg_rtl_
    prc_am),'.'))

    from esp.adv_grp_prm_prc ;

    1 for 100.000
    1 for 11.000
    1 for 11.090
    1 for 11.112
    1 for 11.270

    If you donot mind, can you explain the logic little bit. If you are busy just leave it, I will read the documentation about the functions whatever you used in the udb manuals.

    Thanks
    Jagadish
    jagadish dara

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

    Re: works well

    The addition of POSSTR (or the equivalent LOCATE) to find the position of the decimal. So with the first SUBSTR, it locates everything to the left of decimal. You have to subtract 1 from the result of POSSTR as it specifies the positon of the decimal. So the 2nd SUBSTR takes everything to the right of the decimal.

    Originally posted by jdara1
    Really it is grate, Your sql is producing what I want, I am just pasting what I ran in our aix box.

    cat 20.sql

    select distinct rtrim(char(reg_msu_qy)) concat ' for '
    concat RTRIM(char(INTEGER(SUBSTR(char(reg_rtl_prc_am),1,P OSSTR(char(reg_
    rtl_prc_am),'.') - 1)))) concat SUBSTR(char(reg_rtl_prc_am),POSSTR(char(reg_rtl_
    prc_am),'.'))

    from esp.adv_grp_prm_prc ;

    1 for 100.000
    1 for 11.000
    1 for 11.090
    1 for 11.112
    1 for 11.270

    If you donot mind, can you explain the logic little bit. If you are busy just leave it, I will read the documentation about the functions whatever you used in the udb manuals.

    Thanks
    Jagadish

Posting Permissions

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