-
Sep 8th, 2020, 07:01 AM
#1
Find value that is not included in a group of rows.
Hello.
I have
WebPayTN_lngPayTransNumber, WebPayLog_strStep
1 BankResponseRaw
1 NOTBankResponseRaw
1 EXCEL
1 TU
and
WebPayTN_lngPayTransNumber, WebPayLog_strStep
2 NOTBankResponseRaw
2 EXCEL
2 TU
I want to find the WebPayTN_lngPayTransNumber that do not have a WebPayLog_strStep named BankResponseRaw
Of course if i do "and WebPayLog_strStep <> ' BankResponseRaw' " I will also bring the WebPayTN_lngPayTransNumber "1" just without the BankResponseRaw line.
But I wouldn't bring that row with just the missing 1 -- BankResponseRaw though I only need to bring the id 2 row.
I'm trying to do that with CTE but I'm stuck. I would expect to get some NULLS so I can eliminate them but it will not work.
Code:
With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
AS
-- Define the CTE query.
(
select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog
where WebPayLog_strStep ='BankResponseRaw'
--group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
)
select L.WebPayTN_lngPayTransNumber,CTE.WebPayTN_lngPayTransNumber, CTE.WebPayLog_strStep, L.WebPayLog_strStep
from CTE Left join tblWebPaymentModuleLog L on CTE.WebPayTN_lngPayTransNumber= L.WebPayTN_lngPayTransNumber
where CTE.WebPayTN_lngPayTransNumber= L.WebPayTN_lngPayTransNumber AND CTE.WebPayLog_strStep <> L.WebPayLog_strStep
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 8th, 2020, 07:39 AM
#2
Re: Find value that is not included in a group of rows.
Maybe something like this?
SELECT
WebPayLog_strStep
FROM dbo.test1
EXCEPT
SELECT
WebPayLog_strStep
FROM dbo.test2
SELECT
WebPayLog_strStep
FROM dbo.test2
EXCEPT
SELECT
WebPayLog_strStep
FROM dbo.test1
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Sep 8th, 2020, 08:31 AM
#3
Re: Find value that is not included in a group of rows.
That will give me a empty table
with both:
Code:
SELECT
WebPayLog_strStep
FROM tblWebPaymentModuleLog
EXCEPT
SELECT
WebPayLog_strStep
FROM tblWebPaymentModuleLog
or
Code:
With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
AS
-- Define the CTE query.
(
select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog
where WebPayLog_strStep ='BankResponseRaw'
--group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
)
SELECT
WebPayLog_strStep
FROM CTE
EXCEPT
SELECT
WebPayLog_strStep
FROM tblWebPaymentModuleLog
the key is the "BankResponseRaw"
The select should give me the number "2" rows because they do not contain the "BankResponseRaw"
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 8th, 2020, 08:35 AM
#4
Re: Find value that is not included in a group of rows.
Code:
WebPayTN_lngPayTransNumber WebPayLog_strStep
100000001 StoreTransDetails
100000001 GetPurchaseInfo
100000001 RedirectionInfo
100000001 GetPurchaseInfo
100000001 RedirectionInfo
100000001 GetPurchaseInfo
100000001 RedirectionInfo
100000002 StoreTransDetails
100000001 GetPurchaseInfo
100000001 RedirectionInfo
100000003 StoreTransDetails
100000003 GetPurchaseInfo
100000003 RedirectionInfo
100000003 GetPurchaseInfo
100000003 RedirectionInfo
100000004 StoreTransDetails
100000004 GetPurchaseInfo
100000004 RedirectionInfo
NULL BankResponseRaw
100000004 BankResponseRaw
100000004 GetPurchaseResult
NULL GetPaymentResult
100000005 StoreTransDetails
100000005 GetPurchaseInfo
100000005 RedirectionInfo
this is a real sample of the select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
-
Sep 18th, 2020, 11:38 AM
#5
Re: Find value that is not included in a group of rows.
there is just one table involved, right?
somthing like that?
Code:
select WebPayTN_lngPayTransNumber,max(case when WebPayLog_strStep = 'BankResponseRaw' THEN 1 ELSE 0 END) AS HasBankResponseRaw
from tbl
group by WebPayTN_lngPayTransNumber
having HasBankResponseRaw = 0
?
-
Sep 18th, 2020, 12:09 PM
#6
Re: Find value that is not included in a group of rows.
Originally Posted by sapator
That will give me a empty table
with both:
Code:
SELECT
WebPayLog_strStep
FROM tblWebPaymentModuleLog
EXCEPT
SELECT
WebPayLog_strStep
FROM tblWebPaymentModuleLog
or
Code:
With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
AS
-- Define the CTE query.
(
select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog
where WebPayLog_strStep ='BankResponseRaw'
--group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
)
SELECT
WebPayLog_strStep
FROM CTE
EXCEPT
SELECT
WebPayLog_strStep
FROM tblWebPaymentModuleLog
the key is the "BankResponseRaw"
The select should give me the number "2" rows because they do not contain the "BankResponseRaw"
I think you were close with that last one... but here's why it didn't work:
your CTE is sourced by the table for all records with 'BankResponseRaw' ... So that includes row 1.
You're then selecting frmo that CTE (which has one row) and then EXCEPTING everything in the table... since the table has 1 in it... it gets excluded from your query results....
You had it upside down:
Code:
With CTE (WebPayTN_lngPayTransNumber,WebPayLog_strStep)
AS
-- Define the CTE query.
(
select WebPayTN_lngPayTransNumber,WebPayLog_strStep from tblWebPaymentModuleLog
where WebPayLog_strStep ='BankResponseRaw'
--group by WebPayTN_lngPayTransNumber,WebPayLog_strStep
)
SELECT
WebPayTN_lngPayTransNumber
FROM tblWebPaymentModuleLog
EXCEPT
SELECT
WebPayTN_lngPayTransNumber
FROM CTE
And you want the WebPayTN_lngPayTransNumber not the WebPayLog_strStep ....
You'll probably want the DISTINCT results from that too...
try flipping your query like I did and select the other fields, see if that works.
-tg
-
Sep 22nd, 2020, 09:52 AM
#7
Re: Find value that is not included in a group of rows.
Hi.
Thanks I will have to remake the tables when I get the chance and try this, as we are constantly on and off from work due to the covid BS.
So I just got to the office today from September 8th that I posted and I hear there would be a lockdown again in Athens, so I might leave again to EVIA Island that the is no lockdown for now, I appreciate the answers, will post back if I get the chance to recreate the issue.
ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|