July 12, 2013 at 3:16 am
Hi all, I just want to know that in my below code is it possible to combine first two exists condition in a single exists and last two in a single exists so that I can put AND condition between them. As my script can give me wrong output as if any condition is true it will give me result as True only.
If @firstvoucher value is true and @lastvoucher value is wrong then it will give me output as True only which is wrong...
IF EXISTS
(
SELECT gv.VoucherNo
FROM GV_Voucher As gv
INNER JOIN GV_ReceivedOffice ro ON ro.VoucherNo = gv.VoucherNo
INNER JOIN GV_VoucherStatus As gvs
ON gv.VoucherStatusId = gvs.VoucherStatusId
WHERE gvs.VoucherStatus = 'Active at HO'
AND gv.VoucherNo = @FirstVoucher
)
OR EXISTS
(
SELECT gv.VoucherBookletNo
FROM GV_StoreAllocation As gv
JOIN GV_Voucher v on v.VoucherNo = gv.VoucherBookletNo
JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo
INNER JOIN GV_VoucherStatus As gvs ON v.VoucherStatusId = gvs.VoucherStatusId
WHERE stn.AWBNo IS NULL
AND gvs.VoucherStatus = 'Dispatched' and v.VoucherNo = @FirstVoucher
)
AND EXISTS
(
SELECT gv.VoucherNo
FROM GV_Voucher As gv
INNER JOIN GV_VoucherStatus As gvs
ON gv.VoucherStatusId = gvs.VoucherStatusId
INNER JOIN GV_ReceivedOffice ro ON ro.VoucherNo = gv.VoucherNo
WHERE gvs.VoucherStatus = 'Active at HO'
AND gv.VoucherNo = @Lastvoucher
)
OR EXISTS
(
SELECT gv.VoucherBookletNo
FROM GV_StoreAllocation As gv
JOIN GV_Voucher v on v.VoucherNo = gv.VoucherBookletNo
JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo
INNER JOIN GV_VoucherStatus As gvs ON v.VoucherStatusId = gvs.VoucherStatusId
WHERE stn.AWBNo IS NULL
AND gvs.VoucherStatus = 'Dispatched' AND v.VoucherNo = @Lastvoucher
)
BEGIN
PRINT 'Correct voucher nos'
END
ELSE
BEGIN
RAISERROR('User Define: VOucher Already Sold',16,1)
END
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2013 at 3:28 am
Rearrange the first two queries like so:
IF EXISTS
(
SELECT gv.VoucherNo
FROM GV_Voucher As gv
INNER JOIN GV_VoucherStatus gvs
ON gv.VoucherStatusId = gvs.VoucherStatusId
AND gvs.VoucherStatus = 'Active at HO'
INNER JOIN GV_ReceivedOffice ro ON ro.VoucherNo = gv.VoucherNo
WHERE gv.VoucherNo = @FirstVoucher
)
OR EXISTS
(
SELECT gv.VoucherBookletNo
FROM GV_Voucher v
INNER JOIN GV_VoucherStatus As gvs
ON v.VoucherStatusId = gvs.VoucherStatusId
AND gvs.VoucherStatus = 'Dispatched'
INNER JOIN GV_StoreAllocation gv on v.VoucherNo = gv.VoucherBookletNo
INNER JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo AND stn.AWBNo IS NULL
WHERE v.VoucherNo = @FirstVoucher
)
Can you see where I'm going with this?
Incidentally, try to keep to the same aliases for your tables, it makes coding - and troubleshooting - much easier. Don't use 'gv' for GV_StoreAllocation when 'sa' distinguishes it better from the other tables.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 3:32 am
You can replace the first two queries with one query:
SELECT gv.VoucherNo
FROM GV_Voucher As gv
INNER JOIN GV_VoucherStatus gvs
ON gv.VoucherStatusId = gvs.VoucherStatusId
AND gvs.VoucherStatus = 'Active at HO'
WHERE gv.VoucherNo = @FirstVoucher
AND (
EXISTS (SELECT 1 FROM GV_ReceivedOffice ro WHERE ro.VoucherNo = gv.VoucherNo)
OR
EXISTS (SELECT 1
FROM GV_StoreAllocation gv
INNER JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo AND stn.AWBNo IS NULL
WHERE v.VoucherNo = gv.VoucherBookletNo)
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 3:33 am
Ok I will change my code in that manner...
But can you please suggest me how can I accomplished my required query
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2013 at 3:41 am
Here are queries 3 and 4 condensed in the same way;
SELECT gv.VoucherBookletNo
FROM GV_Voucher v
INNER JOIN GV_VoucherStatus gvs
ON v.VoucherStatusId = gvs.VoucherStatusId
WHERE v.VoucherNo = @Lastvoucher
AND (
(gvs.VoucherStatus = 'Dispatched' AND EXISTS (
SELECT 1
FROM GV_StoreAllocation gv
inner JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo AND stn.AWBNo IS NULL
WHERE v.VoucherNo = gv.VoucherBookletNo))
OR
(gvs.VoucherStatus = 'Active at HO' AND EXISTS (
SELECT 1
FROM GV_ReceivedOffice ro
WHERE ro.VoucherNo = gv.VoucherNo))
)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 3:47 am
Chris, I am not clear what you did....
Will this query be work in this manner..
If a voucher is 'Active at Ho' or its status is 'Dispatched' it is correct But in your query you have not mentioned condition for 'Dispatched'
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2013 at 3:51 am
kapil_kk (7/12/2013)
Chris, I am not clear what you did....Will this query be work in this manner..
If a voucher is 'Active at Ho' or its status is 'Dispatched' it is correct But in your query you have not mentioned condition for 'Dispatched'
Apologies, fixed here:
-- queries 1 & 2
SELECT gv.VoucherNo
FROM GV_Voucher As gv
INNER JOIN GV_VoucherStatus gvs
ON gv.VoucherStatusId = gvs.VoucherStatusId
WHERE gv.VoucherNo = @FirstVoucher
AND (
(gvs.VoucherStatus = 'Active at HO' AND EXISTS (
SELECT 1
FROM GV_ReceivedOffice ro
WHERE ro.VoucherNo = gv.VoucherNo))
OR
(gvs.VoucherStatus = 'Dispatched' AND EXISTS (
SELECT 1
FROM GV_StoreAllocation gv
INNER JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo AND stn.AWBNo IS NULL
WHERE v.VoucherNo = gv.VoucherBookletNo))
)
-- queries 3 & 4
SELECT gv.VoucherBookletNo
FROM GV_Voucher v
INNER JOIN GV_VoucherStatus gvs
ON v.VoucherStatusId = gvs.VoucherStatusId
WHERE v.VoucherNo = @Lastvoucher
AND (
(gvs.VoucherStatus = 'Dispatched' AND EXISTS (
SELECT 1
FROM GV_StoreAllocation gv
inner JOIN GV_STNDetails stn ON stn.STNNO = gv.STNNo AND stn.AWBNo IS NULL
WHERE v.VoucherNo = gv.VoucherBookletNo))
OR
(gvs.VoucherStatus = 'Active at HO' AND EXISTS (
SELECT 1
FROM GV_ReceivedOffice ro
WHERE ro.VoucherNo = gv.VoucherNo))
)
Check carefully against your originals.
Is there any difference between queries 1&2 and queries 3&4? I can't see any.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 4:13 am
Thanks Chris :-),
In past also you make me learn Tally table n many things, its my pleasure to learn new things from you always.. :-P:-D
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
July 12, 2013 at 4:16 am
kapil_kk (7/12/2013)
Thanks Chris :-),In past also you make me learn Tally table n many things, its my pleasure to learn new things from you always.. :-P:-D
You're welcome! I learn something new on here almost every day.
Check your aliases. Remember the point about retaining your aliases? There are mistakes in both queries because the alias for table GV_Voucher varies.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
July 12, 2013 at 4:28 am
ChrisM@Work (7/12/2013)
kapil_kk (7/12/2013)
Thanks Chris :-),In past also you make me learn Tally table n many things, its my pleasure to learn new things from you always.. :-P:-D
You're welcome! I learn something new on here almost every day.
Check your aliases. Remember the point about retaining your aliases? There are mistakes in both queries because the alias for table GV_Voucher varies.
Yes I have changed the aliases...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply