Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2007
    Posts
    2

    Unanswered: sql and case statement

    Hello,

    114078 1 QUAL
    114078 2 GRAPH
    114078 3 PROOF
    114078 4 MAIL
    114078 5 COMP
    114078 6 CHANGE
    114078 7 GRAPH
    114078 8 COMP

    the above data thats what i have in my cascadeadstatus table

    select table1.bookcode,table1.adnumber,table1.seq,table1. status,cascadeadstatus.adnumber,cascadeadstatus.se q,cascadeadstatus.status
    from(SELECT contractrowid,c.BookCode,entrydate,cas.AdNumber,Se q,Status,c.BookYear,DateSet
    FROM book, bookyear, contract c, cascadeadstatus cas, ContractLineItem cli
    WHERE book.BookRowID = bookyear.BookRef
    and cas.AdNumber = cli.AdId
    AND cli.ContractRef = c.ContractRowID
    and bookyear.BookRef = c.BookRef
    AND bookyear.BookYear = c.BookYear
    and book.bookcode in ("CC")
    and publicationdate between current_date and DATEADD(mm,12,current_date)
    and status="COMP") table1, cascadeadstatus
    where cascadeadstatus.adnumber="114078" and
    cascadeadstatus.adnumber = table1.adnumber
    and cascadeadstatus.seq = CASE
    when cascadeadstatus.seq = table1.seq-1 and cascadeadstatus.status = "GRAPH" then table1.seq-1
    when cascadeadstatus.seq = table1.seq-2 and cascadeadstatus.status = "GRAPH" then table1.seq-2
    when cascadeadstatus.seq = table1.seq-3 and cascadeadstatus.status = "GRAPH" then table1.seq-3
    when cascadeadstatus.seq = table1.seq-4 and cascadeadstatus.status = "GRAPH" then table1.seq-4
    when cascadeadstatus.seq = table1.seq-5 and cascadeadstatus.status = "GRAPH" then
    table1.seq-5
    when cascadeadstatus.seq = table1.seq-6 and cascadeadstatus.status = "GRAPH" then
    table1.seq-6
    when cascadeadstatus.seq = table1.seq-7 and cascadeadstatus.status = "GRAPH" then
    table1.seq-7
    when cascadeadstatus.seq = table1.seq-8 and cascadeadstatus.status = "GRAPH" then
    table1.seq-8
    when cascadeadstatus.seq = table1.seq-9 and cascadeadstatus.status = "GRAPH" then
    table1.seq-9
    when cascadeadstatus.seq = table1.seq-10 and cascadeadstatus.status = "GRAPH" then
    table1.seq-10
    end

    what i have in table1 is:

    114078 5 COMP
    114078 8 COMP

    I want to get the following by joining the 2 tables: table1 and cascadeadstatus:

    114078 5 COMP 2 GRAPH
    114078 8 COMP 7 GRAPH


    but i am getting:

    114078 5 COMP 2 GRAPH
    114078 8 COMP 2 GRAPH
    114078 8 COMP 7 GRAPH

    please help

    Thanks

  2. #2
    Join Date
    Nov 2002
    Posts
    272
    That looks complicated.
    In human language (more or less), are you looking for the max(cascadeadstatus.seq) with cascadeadstatus.status = 'GRAPH' and cascadeadstatus.seq <= table1.seq ?

  3. #3
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Argh! Stupid session died with all the work i prepared!
    Last edited by aschk; 04-13-07 at 09:14.

  4. #4
    Join Date
    Apr 2007
    Posts
    2
    IVON,
    Yes it is a complicated query. yesterday finally after 2-3 days I was able to get what I want. and Thank you so much for trying to help me.

  5. #5
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    Is it possible for you to post your solution so that we can all analyse it and learn

Posting Permissions

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