CODEWITHSUNDEEP
javasqlselect
CWortz
CWortz

Reputation: 13

Java Select Statement Results Issue

Given the following Select Statement

String cmpquery = "SELECT cwfour.ord_number, cwfour.ord_status,  
orderheader.ord_status 
FROM live.dbo.cwfour cwfour, live.dbo.orderheader orderheader
WHERE ((cwfour.ord_number = orderheader.ord_hdrnumber AND NOT 
cwfour.ord_status='CAN')) or ((cwfour.ord_number = orderheader.ord_hdrnumber 
AND NOT cwfour.ord_status='CMP'))"

The resultset shows all orders in the file, but I am asking for only the ones that are not CAN or CMP, canceled or completed. What am I missing in my where clause?

Upvotes: 0

Views: 38

Answers (2)

Mureinik
Mureinik

Reputation: 311143

You are querying rows where the status isn't 'CAN' or isn't 'CMP', which will return all the rows, since 'CAN' ins't 'CMP' and vise versa. Instead, you should query the rows aren't either. E.g.:

SELECT cwfour.ord_number, cwfour.ord_status, orderheader.ord_status
FROM   live.dbo.cwfour cwfour, live.dbo.orderheader orderheader
WHERE  cwfour.ord_number = orderheader.ord_hdrnumber AND
       cwfour.ord_status NOT IN ('CAN', 'CMP')

It's also worth noting that implicit joins (listing more than one table in the from clause) have been considered deprecated from quite some time now. Instead, it's recommended to use explicit joins:

SELECT cwfour.ord_number, cwfour.ord_status, orderheader.ord_status
FROM   live.dbo.cwfour cwfour
JOIN   live.dbo.orderheader orderheader ON 
       cwfour.ord_number = orderheader.ord_hdrnumber
WHERE  cwfour.ord_status NOT IN ('CAN', 'CMP')

Upvotes: 1

Adam
Adam

Reputation: 5599

Logic!

Your condition is:

(cwfour.ord_number = orderheader.ord_hdrnumber AND NOT cwfour.ord_status='CAN')
OR
(cwfour.ord_number = orderheader.ord_hdrnumber AND NOT cwfour.ord_status='CMP')

A AND !B
OR
A AND !C

Which is

A & (!B | !C)

Simply: A & (almost anything) ;)

Upvotes: 0

Related Questions