Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

sql 2000 query

Options
  • 22-02-2011 5:15pm
    #1
    Faolchu
    Registered Users Posts: 1,740 ✭✭✭


    folks
    trying to figure out a sql query and its doing my nut in.

    basically i want all entries on TABLE3 that have the all the values in the fields listed and have values "Code" or "B Code. here's what have: 
    SELECT
TABLE1.ACCOUNTNO, 
TABLE2.CCY, 
TABLE1.SECID, 
TABLE3.SECALTID, 
TABLE1.TDYPOOLQTY,
convert(money,TABLE1.TDYPOOLQTY) * convert(money,TABLE2.DENOM) 

FROM TABLE1,TABLE2,TABLE3 

WHERE 
TABLE2.SECID = TABLE1.SECID 
AND TABLE2.SECID = TABLE3.SECID
AND TABLE1.ACCOUNTNO='123456'
AND TABLE2.MDATE>=GETDATE()
AND TABLE1.TDYPOOLQTY <> 0 
AND TABLE1.BR = '01'
AND TABLE3.SECIDTYPE = 'CODE' 

or 
Where TABLE3.SECIDTYPE = 'B CODE'
AND TABLE2.SECID = TABLE1.SECID 
AND TABLE2.SECID = TABLE3.SECID
AND TABLE1.ACCOUNTNO='123456'
AND TABLE2.MDATE>=GETDATE()
AND TABLE1.TDYPOOLQTY <> 0 
AND TABLE1.BR = '01'

    am i approaching this right? basically i need all entries on TABLE3 that have a value of "Code" or "B Code" with the corresponding values in TABLE1 and TABLE2


Comments

  • Options
    #2
    wavehopper1
    Registered Users Posts: 356 ✭✭wavehopper1


    If the only difference between the two parts of your query is that TABLE3.SECIDTYP is 'CODE' or 'B CODE', you can use the IN syntax to simplify the Where clause.

    Like this:

    <snip>
    WHERE
    TABLE2.SECID = TABLE1.SECID
    AND TABLE2.SECID = TABLE3.SECID
    AND TABLE1.ACCOUNTNO='123456'
    AND TABLE2.MDATE>=GETDATE()
    AND TABLE1.TDYPOOLQTY <> 0
    AND TABLE1.BR = '01'
    AND TABLE3.SECIDTYPE IN ('CODE', 'B CODE')


  • Options
    #3
    Faolchu
    Registered Users Posts: 1,740 ✭✭✭Faolchu


    nice one tried that and it kind of worked. When i say that i mean it worked as expected its just the way our system is structured it appears there are duplicates.

    basically for every SECIDTYPE 'CODE' transaction theres a corresponding SECIDTYPE 'B CODE' transaction on TABLE3. so someone makes the entry on the table its given a SECIDTYPE of 'CODE' when a different person approves this transaction the approval is a seperate entry on the same table with a SECIDTYPE of 'B CODE'

    I'm thinking that the booking and the approval will need to be on seperate tables or something having link between them using a primary key based on a Transaction number or something like that.

    thanks for the help though it highlighted a possible need for us to approach the system differently,


Advertisement