Thread: Complex query

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    7
    Rep Power
    0

    Complex query


    I have three tables called O_S,O_T,O_P

    In O_T i have a field called I_ID . For I_ID column i have to write a query by using below conditions.


    Code:
    O_S.H_NUM = O_T.H_NUM
    AND O_T.C_NUM IS NOT NULL
    AND O_T.I_DATE <= SYSDATE
    AND O_T.EXP_DT IS NULL
    AND (
    (O_S.P_S_CD='CA' AND O_T.I_DATE >= sysdate - 4018) 
    OR
    (O_S.P_S_CD!='CA' AND O_T.I_DATE >= sysdate - 3652)
    ) then
    take O_T.I_ID
    
    
    Join O_P.H_NUM = O_T.H_NUM and 
    get O_T.I_ID then
    take the same I_ID record from O_T_R if found
    else take the records from O_T If not found
    Please help me
  2. #2
  3. Lord of the Dance
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Oct 2003
    Posts
    4,131
    Rep Power
    2011
    Can you specify the database you are using?

    Maybe you are looking for the CASE WHEN syntax?
    E.g.: https://msdn.microsoft.com/en-us/library/ms181765.aspx
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    7
    Rep Power
    0
    Hi thanks for your reply. I am using oracle database. and i need the above query without using CASE statement. Kindly help me
  6. #4
  7. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    867
    Rep Power
    391

    Cool


    If you already have the pseudo-code, what is preventing you for coding the actual query?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,803
    Rep Power
    352
    Originally Posted by natrajdreams
    i need the above query without using CASE statement.
    Why?
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

IMN logo majestic logo threadwatch logo seochat tools logo