SQL Query Help

Question

SQL Query Help

The Casual Bot

Grasshopper

Points: 22
More actions
January 22, 2010 at 2:14 am

#140730

Hi Everyone
im trying to get some info for a db
what i need is is stock that has "SUSPENSE" In one of its ten price lines analisis fields so i can get this ok but it returns around 9,000 records so i need to cut this down to stock sold with in the last 12 months so i used an inner join to the order details table and tried to see i i can only return stock iteam the have been sold with in the last 12 months but its returning date from back in 03
SELECT STK_STOCK_2.STKCODE2 AS [Stock Code], STK_STOCK_2.STK_SANALYSIS1 AS [Price Line 1], STK_STOCK_2.STK_SANALYSIS2 AS [Price Line 2],
STK_STOCK_2.STK_SANALYSIS3 AS [Price Line 3], STK_STOCK_2.STK_SANALYSIS4 AS [Price Line 4],
STK_STOCK_2.STK_SANALYSIS5 AS [Price Line 5], STK_STOCK_2.STK_SANALYSIS6 AS [Price Line 6],
STK_STOCK_2.STK_SANALYSIS7 AS [Price Line 7], STK_STOCK_2.STK_SANALYSIS8 AS [Price Line 8],
STK_STOCK_2.STK_SANALYSIS9 AS [Price Line 9], STK_STOCK_2.STK_SANALYSIS10 AS [Price Line 10], ORD_DETAIL.OD_DATE
FROM STK_STOCK_2 INNER JOIN
ORD_DETAIL ON STK_STOCK_2.STKCODE2 = ORD_DETAIL.OD_STOCK_CODE
WHERE (STK_STOCK_2.STK_BUY_UNIT10 > 1) OR
(STK_STOCK_2.STK_SANALYSIS1 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS2 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS3 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS5 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS4 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS6 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS7 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS8 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS9 = 'SUSPENSE') OR
(STK_STOCK_2.STK_SANALYSIS10 = 'SUSPENSE') AND (ORD_DETAIL.OD_DATE >= '2009-01-22')
Any idea where im going wrong

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Try grouping the OR conditions in parenthesis:

SELECT STK_STOCK_2.STKCODE2 AS [Stock Code], STK_STOCK_2.STK_SANALYSIS1 AS [Price Line 1], STK_STOCK_2.STK_SANALYSIS2 AS [Price Line 2],

STK_STOCK_2.STK_SANALYSIS3 AS [Price Line 3], STK_STOCK_2.STK_SANALYSIS4 AS [Price Line 4],

STK_STOCK_2.STK_SANALYSIS5 AS [Price Line 5], STK_STOCK_2.STK_SANALYSIS6 AS [Price Line 6],

STK_STOCK_2.STK_SANALYSIS7 AS [Price Line 7], STK_STOCK_2.STK_SANALYSIS8 AS [Price Line 8],

STK_STOCK_2.STK_SANALYSIS9 AS [Price Line 9], STK_STOCK_2.STK_SANALYSIS10 AS [Price Line 10], ORD_DETAIL.OD_DATE

FROM STK_STOCK_2 INNER JOIN

ORD_DETAIL ON STK_STOCK_2.STKCODE2 = ORD_DETAIL.OD_STOCK_CODE

WHERE (

(STK_STOCK_2.STK_BUY_UNIT10 > 1) OR

(STK_STOCK_2.STK_SANALYSIS1 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS2 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS3 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS5 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS4 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS6 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS7 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS8 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS9 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS10 = 'SUSPENSE')

)

AND (ORD_DETAIL.OD_DATE >= '2009-01-22')

-- Gianluca Sartori

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 2

You can also replace the multiple 'OR's with an 'IN'

SELECT STK_STOCK_2.STKCODE2 AS [Stock Code], STK_STOCK_2.STK_SANALYSIS1 AS [Price Line 1], STK_STOCK_2.STK_SANALYSIS2 AS [Price Line 2],

STK_STOCK_2.STK_SANALYSIS3 AS [Price Line 3], STK_STOCK_2.STK_SANALYSIS4 AS [Price Line 4],

STK_STOCK_2.STK_SANALYSIS5 AS [Price Line 5], STK_STOCK_2.STK_SANALYSIS6 AS [Price Line 6],

STK_STOCK_2.STK_SANALYSIS7 AS [Price Line 7], STK_STOCK_2.STK_SANALYSIS8 AS [Price Line 8],

STK_STOCK_2.STK_SANALYSIS9 AS [Price Line 9], STK_STOCK_2.STK_SANALYSIS10 AS [Price Line 10], ORD_DETAIL.OD_DATE

FROM STK_STOCK_2 INNER JOIN

ORD_DETAIL ON STK_STOCK_2.STKCODE2 = ORD_DETAIL.OD_STOCK_CODE

WHERE (

(STK_STOCK_2.STK_BUY_UNIT10 > 1) OR

'SUSPENSE' IN (STK_STOCK_2.STK_SANALYSIS1,

STK_STOCK_2.STK_SANALYSIS2,

STK_STOCK_2.STK_SANALYSIS3,

STK_STOCK_2.STK_SANALYSIS4,

STK_STOCK_2.STK_SANALYSIS5,

STK_STOCK_2.STK_SANALYSIS6,

STK_STOCK_2.STK_SANALYSIS7,

STK_STOCK_2.STK_SANALYSIS8,

STK_STOCK_2.STK_SANALYSIS9,

STK_STOCK_2.STK_SANALYSIS10)

)

AND (ORD_DETAIL.OD_DATE >= '2009-01-22')

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

The Casual Bot Grasshopper Points: 22 More actions · Answer 3

This kinda works

it returns multiple lines for the smae stock iteam but i only need on line per iteam

iv just tried SELECT DISTINCT but this again select all the data

the query now stands at

SELECT DISTINCT STK_STOCK_2.STKCODE2 AS [Stock Code], STK_STOCK_2.STK_SANALYSIS1 AS [Price Line 1], STK_STOCK_2.STK_SANALYSIS2 AS [Price Line 2],

STK_STOCK_2.STK_SANALYSIS3 AS [Price Line 3], STK_STOCK_2.STK_SANALYSIS4 AS [Price Line 4],

STK_STOCK_2.STK_SANALYSIS5 AS [Price Line 5], STK_STOCK_2.STK_SANALYSIS6 AS [Price Line 6],

STK_STOCK_2.STK_SANALYSIS7 AS [Price Line 7], STK_STOCK_2.STK_SANALYSIS8 AS [Price Line 8],

STK_STOCK_2.STK_SANALYSIS9 AS [Price Line 9], STK_STOCK_2.STK_SANALYSIS10 AS [Price Line 10], ORD_DETAIL.OD_DATE

FROM STK_STOCK_2 INNER JOIN

ORD_DETAIL ON STK_STOCK_2.STKCODE2 = ORD_DETAIL.OD_STOCK_CODE

WHERE (

(STK_STOCK_2.STK_BUY_UNIT10 > 1) OR

(STK_STOCK_2.STK_SANALYSIS1 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS2 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS3 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS5 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS4 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS6 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS7 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS8 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS9 = 'SUSPENSE') OR

(STK_STOCK_2.STK_SANALYSIS10 = 'SUSPENSE')

)

AND (ORD_DETAIL.OD_DATE >= '2009-01-22')

The Casual Bot Grasshopper Points: 22 More actions · Answer 4

also i just tried to filter the duplicates out in excel but im returning 1,200,000 rows so excel cant handle it 🙁

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

If you are joining the orders just to filter your data, you could use WHERE EXISTS:

SELECT STK_STOCK_2.STKCODE2 AS [Stock Code],

STK_STOCK_2.STK_SANALYSIS1 AS [Price Line 1],

STK_STOCK_2.STK_SANALYSIS2 AS [Price Line 2],

STK_STOCK_2.STK_SANALYSIS3 AS [Price Line 3],

STK_STOCK_2.STK_SANALYSIS4 AS [Price Line 4],

STK_STOCK_2.STK_SANALYSIS5 AS [Price Line 5],

STK_STOCK_2.STK_SANALYSIS6 AS [Price Line 6],

STK_STOCK_2.STK_SANALYSIS7 AS [Price Line 7],

STK_STOCK_2.STK_SANALYSIS8 AS [Price Line 8],

STK_STOCK_2.STK_SANALYSIS9 AS [Price Line 9],

STK_STOCK_2.STK_SANALYSIS10 AS [Price Line 10]

FROM STK_STOCK_2

WHERE EXISTS ( SELECT 1

FROM ORD_DETAIL

WHERE STK_STOCK_2.STKCODE2 = ORD_DETAIL.OD_STOCK_CODE

AND ( ORD_DETAIL.OD_DATE >= '2009-01-22' ) )

AND ( ( STK_STOCK_2.STK_BUY_UNIT10 > 1 )

OR ( STK_STOCK_2.STK_SANALYSIS1 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS2 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS3 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS5 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS4 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS6 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS7 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS8 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS9 = 'SUSPENSE' )

OR ( STK_STOCK_2.STK_SANALYSIS10 = 'SUSPENSE' )

)

Please note that using multiple ORs or a single IN as Mark suggested is just about syntax, not performance, because the algebrizer translates the IN expression back to multiple ORs.

-- Gianluca Sartori

The Casual Bot Grasshopper Points: 22 More actions · Answer 6

it worked !!!!!!

Thanks you guys

:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D:-D