Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2005
    Posts
    2

    Unanswered: sql statement problem -newbie

    Good Day everyone!

    i am new here and a newbie in sql world.i am hoping someone can help me with my problem. i have an sql statement which was given to me by my co-programmer but i dont understand it, due to limited time i decided to ask someone who has more knowledge in this matter. anyway, there goes the sql string:

    -begin sql string

    SELECT
    SUPLR_PART_NO, PS, PART_DESCRIPTION,
    MAX(DECODE (SDATE, TO_NUMBER(TO_CHAR(SYSDATE, 'YYYYMM')), CNT, NULL )) AS MONTH1,
    MAX(DECODE (SDATE, TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,1),'YYYYMM')) , CNT, NULL ) ) AS MONTH2,
    MAX(DECODE (SDATE, TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,2),'YYYYMM')) , CNT, NULL ) ) AS MONTH3
    FROM (
    SELECT
    A.SUPLR_PART_NO, A.PS, B.PART_DESCRIPTION, TO_NUMBER(SUBSTR(B.STARTUP, 1, 6)) AS SDATE, COUNT(A.TQN1) CNT
    FROM TBL_NEEDED4_J A LEFT JOIN TBL_SUPLR_ORDER B
    ON A.SUPLR_PART_NO = B.PART_NUMBER AND A.PS = B.PS
    WHERE A.SUPLR_PART_NO LIKE '4%'
    AND TO_NUMBER(SUBSTR(B.STARTUP, 1, 6)) <= TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMM'))
    AND TO_NUMBER(SUBSTR(B.TERMINATION, 1, 6)) >= TO_NUMBER(TO_CHAR(ADD_MONTHS(SYSDATE,2), 'YYYYMM'))
    GROUP BY
    A.SUPLR_PART_NO, A.PS, B.PART_DESCRIPTION, TO_NUMBER(SUBSTR(B.STARTUP, 1, 6))
    )
    GROUP BY SUPLR_PART_NO, PS, PART_DESCRIPTION

    -end sql string

    the sql string above has no problem but it doesn't show a value for month1, month2 and month3.(note: the tables that has been access have values). i hope someone can help me resolve this problem. i am trying to figure it out but i only have a limited knowledge in sql. any suggestions are really appreciated...

    cheers,
    think

  2. #2
    Join Date
    Dec 2003
    Posts
    1,074
    DECODE(<find this>, <in this>,<show this if you find it>, <show this if you don't>)

    Code:
    SQL>   select decode(200510,to_number(to_char(sysdate,'YYYYMM')),'found it','didnt find it')
      2* from dual
    SQL> /
    
    DECODE(200510
    -------------
    found it
    
    SQL>   select decode(200511,to_number(to_char(sysdate,'YYYYMM')),'found it','didnt find it')
      2* from dual
    SQL> /
    
    DECODE(200511
    -------------
    didnt find it
    -Chuck

  3. #3
    Join Date
    Oct 2005
    Posts
    2
    to chuck:

    tnx man, i really appreciate ur reply and i found it useful information. i did make my sql string works now. tnx to u...


    cheers,
    think

Posting Permissions

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