Help with SQL query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with SQL query

Author

Topic

jbon
Starting Member

20 Posts

Posted - 2009-09-24 : 10:11:37

Hi,
I have a SQL query problem that I need help to solve.

I have following SQL query that gives me a correct result (one row as result for LIH.INVOIC_NO = '438692295')

SELECT LIH.INVOIC_NO AS Fakturanr, LILD.EXEC_DTM AS Transportdag, LILR.REF_NO AS Fraktsedel, LIL.ID,
MAX(case when LILL.LOCATION_QUALIFIER = '5' then LILL.LOCATION end) AS Frånort,
MAX(case when LILL.LOCATION_QUALIFIER = '8' then LILL.LOCATION end) AS Tillort,
MAX(case when LILM.MEASUREMENT_DIMENSION = 'AAD' then LILM.MEASUREMENT_VALUE end) AS "Vikt (kg)",
MAX(case when LILM.MEASUREMENT_DIMENSION = 'VOL' then LILM.MEASUREMENT_VALUE end) AS "Volym (m3)",
MAX(case when LILM.MEASUREMENT_DIMENSION = 'LMT' then LILM.MEASUREMENT_VALUE end) AS "FLM (m)",
LILQ.QUANTITY AS "Fraktvikt (kg)", LILMOA.MON_AMOUNT AS Fraktpris
FROM LOC_INVOIC_HEADER LIH
JOIN LOC_INVOIC_LIN LIL ON LIH.INVOIC_NO = LIL.INVOIC_NO AND LIH.ICHG_SENDER = LIL.ICHG_SENDER
JOIN LOC_INVOIC_LIN_MEA LILM ON LIL.ID = LILM.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_DTM LILD ON LIL.ID = LILD.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_RFF LILR ON LIL.ID = LILR.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_LOC LILL ON LIL.ID = LILL.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_MOA LILMOA ON LIL.ID = LILMOA.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_QTY LILQ ON LIL.ID = LILQ.LOC_INVOIC_LIN_ID
WHERE LIH.INVOIC_NO = '438692295' AND LILR.REF_QUALIFIER = 'AAS'
AND LILMOA.MON_AMOUNT_TYPE_QUALIFIER = '203' AND LILQ.QUANTITY_QUALIFIER = '100'
GROUP BY LIH.INVOIC_NO, LILD.EXEC_DTM, LILR.REF_NO, LILQ.QUANTITY, LILMOA.MON_AMOUNT, LIL.ID
ORDER BY LIH.INVOIC_NO

But when I want to insert another value for LIH.INVOIC_NO (OR condition) I get a not correct result, see query below. I want that both LIH.INVOIC_NO (WHERE LIH.INVOIC_NO = '438692295' OR LIH.INVOIC_NO = '442491395' ) should take the other conditions in mind.

The result of below will be that I receive several rows for WHERE LIH.INVOIC_NO = '438692295'.

SELECT LIH.INVOIC_NO AS Fakturanr, LILD.EXEC_DTM AS Transportdag, LILR.REF_NO AS Fraktsedel, LIL.ID,
MAX(case when LILL.LOCATION_QUALIFIER = '5' then LILL.LOCATION end) AS Frånort,
MAX(case when LILL.LOCATION_QUALIFIER = '8' then LILL.LOCATION end) AS Tillort,
MAX(case when LILM.MEASUREMENT_DIMENSION = 'AAD' then LILM.MEASUREMENT_VALUE end) AS "Vikt (kg)",
MAX(case when LILM.MEASUREMENT_DIMENSION = 'VOL' then LILM.MEASUREMENT_VALUE end) AS "Volym (m3)",
MAX(case when LILM.MEASUREMENT_DIMENSION = 'LMT' then LILM.MEASUREMENT_VALUE end) AS "FLM (m)",
LILQ.QUANTITY AS "Fraktvikt (kg)", LILMOA.MON_AMOUNT AS Fraktpris
FROM LOC_INVOIC_HEADER LIH
JOIN LOC_INVOIC_LIN LIL ON LIH.INVOIC_NO = LIL.INVOIC_NO AND LIH.ICHG_SENDER = LIL.ICHG_SENDER
JOIN LOC_INVOIC_LIN_MEA LILM ON LIL.ID = LILM.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_DTM LILD ON LIL.ID = LILD.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_RFF LILR ON LIL.ID = LILR.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_LOC LILL ON LIL.ID = LILL.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_MOA LILMOA ON LIL.ID = LILMOA.LOC_INVOIC_LIN_ID
JOIN LOC_INVOIC_LIN_QTY LILQ ON LIL.ID = LILQ.LOC_INVOIC_LIN_ID
WHERE LIH.INVOIC_NO = '438692295' OR LIH.INVOIC_NO = '442491395' AND LILR.REF_QUALIFIER = 'AAS'
AND LILMOA.MON_AMOUNT_TYPE_QUALIFIER = '203' AND LILQ.QUANTITY_QUALIFIER = '100'
GROUP BY LIH.INVOIC_NO, LILD.EXEC_DTM, LILR.REF_NO, LILQ.QUANTITY, LILMOA.MON_AMOUNT, LIL.ID
ORDER BY LIH.INVOIC_NO

Can anyone tell me why and give my suggestions for how to solve my problem?

Many thanks in advance!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-09-24 : 10:13:52

WHERE (LIH.INVOIC_NO = '438692295' OR LIH.INVOIC_NO = '442491395') AND ...

No, you're never too old to Yak'n'Roll if you're too young to die.