Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.
Hi all, please see this major site announcement: https://www.boards.ie/discussion/2058427594/boards-ie-2026

sql 2000 query

  • 22-02-2011 04:15PM
    #1
    Faolchu
    Registered Users, Registered Users 2 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

  • #2
    wavehopper1
    Registered Users, Registered Users 2 Posts: 361 ✭✭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')


  • #3
    Faolchu
    Registered Users, Registered Users 2 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