how to specify condition, to show data where in the part of the select is nothing
Hi
i have one select where the output you can see here, i need to add criteriathat t_exdt = '1/1/1753' and also t_exdt1 = '1/1/1753' but when i put both of them i lost selected area. Is the way how to select it all?
here is the select:
SELECT
ttiitm001101.t_item,
ttiitm001101.t_dsca,
ttiitm001101.t_cuni,
ttiitm001101.t_unom,
ttibom010101.t_sitm,
ttibom010101.t_exdt,
ttibom010101.t_qana,
ttdinv001101_1.t_cwar,
ttdinv001101_1.t_stoc,
ttiitm001101_1.t_dsca as t_dsca1,
ttiitm001101_1.t_cuni as t_cuni1,
ttibom010101_1.t_sitm as t_sitm1,
ttibom010101_1.t_exdt as t_exdt1,
ttibom010101_1.t_qana as t_qana1,
ttiitm001101_2.t_dsca as t_dsca2,
ttiitm001101_2.t_cuni as t_cuni2,
ttdinv001101_2.t_cwar as t_cwar2,
ttdinv001101_2.t_stoc as t_stoc2
FROM
ttiitm001101
LEFT OUTER JOIN ttibom010101 ON ttiitm001101.t_item = ttibom010101.t_mitm
LEFT OUTER JOIN ttdinv001101 as ttdinv001101_1 ON ttibom010101.t_sitm = ttdinv001101_1.t_item
INNER JOIN ttiitm001101 as ttiitm001101_1 ON ttibom010101.t_sitm = ttiitm001101_1.t_item
LEFT OUTER JOIN ttibom010101 as ttibom010101_1 ON ttibom010101.t_sitm = ttibom010101_1.t_mitm
FULL JOIN ttiitm001101 as ttiitm001101_2 ON ttibom010101_1.t_sitm = ttiitm001101_2.t_item
FULL JOIN ttdinv001101 as ttdinv001101_2 ON ttibom010101_1.t_sitm = ttdinv001101_2.t_item
WHERE
ttiitm001101.t_item BETWEEN upper(@StartItem) AND upper(@EndItem)
AND ttibom010101.t_exdt = '1/1/1753'
ORDER BY
ttiitm001101.t_item ASC,
ttibom010101.t_sitm ASC,
ttibom010101_1.t_sitm ASC
September 30th, 2012 8:06am
Hi,
I am not getting you question clearly ?
Can you tel me,wat you need as ur output exactly .
Thanks
Esmari
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 8:10am
Perhaps you need to change
WHERE
ttiitm001101.t_item BETWEEN upper(@StartItem) AND upper(@EndItem)
AND ttibom010101.t_exdt = '1/1/1753'
to
AND
ttiitm001101.t_item BETWEEN upper(@StartItem) AND upper(@EndItem)
AND ttibom010101.t_exdt = '1/1/1753'Best Regards,Uri Dimant SQL Server MVP,
http://sqlblog.com/blogs/uri_dimant/
MS SQL optimization: MS SQL Development and Optimization
MS SQL Blog:
Large scale of database and data cleansing
MS SQL Consultants:
Improves MS SQL Database Performance
September 30th, 2012 8:18am
Hi Esmari,
I will try to explain it, I have select that shows some output, when you right click on the image and you click view image, you will see the output,
there are columns and i would like, that this select show me all t_sitm where t_exdt='1/1/1753' and also all t_sitm1 where t_exdt1='1/1/1753', but when i put both of the conditions to the report, the red marked area disappear, so now till t_cuni1 select
is as i would like, but t_exdt1 contains some rows where is other value as '1/1/1753'
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 8:46am
thanks for your reply
i tried, but didn't help
September 30th, 2012 8:47am
hi to everybody
thanks to Uri Dimant i found solution
now the report is working as it should
SELECT
ttiitm001101.t_item,
ttiitm001101.t_dsca,
ttiitm001101.t_cuni,
ttiitm001101.t_unom,
ttibom010101.t_sitm,
ttibom010101.t_exdt,
ttibom010101.t_qana,
ttdinv001101_1.t_cwar,
ttdinv001101_1.t_stoc,
ttiitm001101_1.t_dsca as t_dsca1,
ttiitm001101_1.t_cuni as t_cuni1,
ttibom010101_1.t_sitm as t_sitm1,
ttibom010101_1.t_exdt as t_exdt1,
ttibom010101_1.t_qana as t_qana1,
ttiitm001101_2.t_dsca as t_dsca2,
ttiitm001101_2.t_cuni as t_cuni2,
ttdinv001101_2.t_cwar as t_cwar2,
ttdinv001101_2.t_stoc as t_stoc2
FROM
ttiitm001101
LEFT OUTER JOIN ttibom010101 ON ttiitm001101.t_item = ttibom010101.t_mitm
LEFT OUTER JOIN ttdinv001101 as ttdinv001101_1 ON ttibom010101.t_sitm = ttdinv001101_1.t_item
INNER JOIN ttiitm001101 as ttiitm001101_1 ON ttibom010101.t_sitm = ttiitm001101_1.t_item
LEFT OUTER JOIN ttibom010101 as ttibom010101_1 ON ttibom010101.t_sitm = ttibom010101_1.t_mitm AND ttibom010101_1.t_exdt = '1/1/1753'
FULL JOIN ttiitm001101 as ttiitm001101_2 ON ttibom010101_1.t_sitm = ttiitm001101_2.t_item
FULL JOIN ttdinv001101 as ttdinv001101_2 ON ttibom010101_1.t_sitm = ttdinv001101_2.t_item
WHERE
ttiitm001101.t_item BETWEEN upper(@StartItem) AND upper(@EndItem)
AND ttibom010101.t_exdt = '1/1/1753'
ORDER BY
ttiitm001101.t_item ASC,
ttibom010101.t_sitm ASC,
ttibom010101_1.t_sitm ASC
Free Windows Admin Tool Kit Click here and download it now
September 30th, 2012 12:09pm