Results 1 to 3 of 3

Thread: Query help

  1. #1
    Join Date
    Jan 2011
    Posts
    1

    Unanswered: Query help

    select distinct a.comp_code,a.process_code,a.nexus_year,a.nexus_se q_no ,
    a.is_audit_mail_gen , (select param_val from nexus_process_config
    where param_name like 'STEP_ID') step_id
    from NEXUS_TR_DATA a, nexus_process_approval_mast b where
    a.is_audit_mail_gen is null OR a.is_audit_mail_gen = ' '
    and b.step_id IN (step_id)
    and a.process_code = b.process_code
    and a.comp_code = '5655'
    and a.process_code = 'ITR'
    DECODE (a.comp_code ,'5655','AND (sysdate between TO_DATE('01-JAN-11') AND TO_DATE('30-JAN-11'))',
    '5663', 'AND (sysdate between TO_DATE('01-FEB-11') AND TO_DATE('15-JAN-11'))')



    i am facing diff in the decode part. can sum1 tel wat m i doin wrong?

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Decode is not available in MySQL. Try using IF or CASE but in this case you need to write this using an OR: Also TO_DATE is not a valid function in MySQL.

    Code:
    select distinct a.comp_code,a.process_code,a.nexus_year,a.nexus_se q_no ,
    a.is_audit_mail_gen , (select param_val from nexus_process_config
    where param_name like 'STEP_ID') step_id
    from NEXUS_TR_DATA a, nexus_process_approval_mast b where
    a.is_audit_mail_gen is null OR a.is_audit_mail_gen = ' '
    and b.step_id IN (step_id)
    and a.process_code = b.process_code
    and a.comp_code = '5655'
    and a.process_code = 'ITR'
    and ((a.comp_code = '5655' AND system between str_to_date('01-JAN-11', '%d-%m-%y') AND str_to_date('30-JAN-11','%d-%m-%y'))
    OR (a.comp_code = '5663' AND sysdate between str_to_date('01-FEB-11', '%d-%m-%y') AND str_to_date('15-JAN-11', '%d-%m-%y')))
    Last edited by it-iss.com; 01-11-11 at 05:36.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ruchis View Post
    can sum1 tel wat m i doin wrong?
    you're typing with your thumbs again

    this type of "language" has no place in query writing -- you're likely to make errors in your SQL syntax that way
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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