I have one question in SQL and would like to take some help from you. I am using the below query to group by based on the test results and then can able to send these results to the recipients.
SELECT alertQueue.[Prod No] AS ProdNumber
, SUBSTRING(alertQueue.[Prod No], PATINDEX('%[^0]%', alertQueue.[Prod No] +'.'), LEN(alertQueue.[Prod No])) AS ProducerNumber
, alertQueue.[Tank No] AS TankNo
, alertQueue.[Sequence_Number] AS SeqNumber
, alertQueue.[Notification_Account] AS NotificationAccount
, alertQueue.[Login] AS [Login]
, alertQueue.[Manifest_Number] AS ManifestNumber
, prod.[Prod Div] AS Division
, prod.[Prod Name] AS Name
, alertQueue.[Notification_Type] AS NotificationType
, alertQueue.[Pickup_Date] AS PickupDate
, STUFF((SELECT CASE alertQueue.[Notification_Type]
WHEN 'Phone'
THEN ' <br/> ' + nestedAlertQueue.[Test_Name] + ':' + CAST(nestedAlertQueue.[Test_Value] AS varchar(MAX))
WHEN 'Email'
THEN ' <br/> ' + nestedAlertQueue.[Test_Name] + ' is ' + CAST(nestedAlertQueue.[Test_Value] AS varchar(MAX))
+ CASE nestedAlertQueue.[Test_Name]
WHEN 'BF' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
WHEN 'LPC' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
WHEN 'MUN' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
WHEN 'PIC' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
WHEN 'PRO' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
WHEN 'SCC' THEN ' ( ' + alertQueue.Threshold + ' ' + alertQueue.Threshold_Value + ' )'
ELSE ''
END
ELSE ''
END
FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS nestedAlertQueue
INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS nestedProducer
ON nestedProducer.[Prod No KEY] = nestedAlertQueue.[Prod No]
WHERE nestedAlertQueue.[Prod No] = alertQueue.[Prod No]
AND nestedAlertQueue.[Tank No] = alertQueue.[Tank No]
AND nestedAlertQueue.[Sequence_Number] = alertQueue.[Sequence_Number]
AND nestedAlertQueue.[Notification_Account] = alertQueue.[Notification_Account]
AND nestedAlertQueue.[Login] = alertQueue.[Login]
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS TestDetails
FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS alertQueue
INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS prod
ON prod.[Prod No KEY] = alertQueue.[Prod No]
WHERE alertQueue.[Notification_Type] = 'Email'
GROUP BY alertQueue.[Prod No]
, alertQueue.[Tank No]
, alertQueue.[Sequence_Number]
, alertQueue.[Notification_Account]
, alertQueue.[Login]
, alertQueue.[Manifest_Number]
, prod.[Prod Div]
, prod.[Prod Name]
, alertQueue.[Notification_Type]
, alertQueue.[Pickup_Date]
ORDER BY alertQueue.[Prod No]
, alertQueue.[Notification_Account]
, alertQueue.[Login] ASC
But when I run this query I am getting an error message saying
> Msg 8120, Level 16, State 1, Line 18
Column 'LIBECIRTP.API.Test_Alert_Queue.Threshold' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
After that, I have added the missing columns to both SELECT and GROUP BY which is working fine but returning more records than needed.
So, if the alertQueue.[Notification_Type] = 'Phone' there shouldn't be alertQueue.[Threshold] and alertQueue.[Threshold_Value] and if alertQueue.[Notification_Type] = 'Email' there should be alertQueue.[Threshold] and alertQueue.[Threshold_Value] in both SELECT and GROUP BY.
Can any one of you please help me with this. I really appreciate any help.
The 2nd item in the select list is not included in the GROUP BY clause. Shouldn't it be? The tables which are nested inside the STUFF function join to the outer tables... maybe there's some issue there. I'd guess that's what's causing the error.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
January 27, 2020 at 11:58 pm
Thanks, it worked. I really appreciate your help. Made below changes.
Instead of alertQueue used nestedAlertQueue and it resolved my issue.
SELECT alertQueue.[Prod No] AS ProdNumber
, SUBSTRING(alertQueue.[Prod No], PATINDEX('%[^0]%', alertQueue.[Prod No] +'.'), LEN(alertQueue.[Prod No])) AS ProducerNumber
, alertQueue.[Tank No] AS TankNo
, alertQueue.[Sequence_Number] AS SeqNumber
, alertQueue.[Notification_Account] AS NotificationAccount
, alertQueue.[Login] AS [Login]
, alertQueue.[Manifest_Number] AS ManifestNumber
, prod.[Prod Div] AS Division
, prod.[Prod Name] AS Name
, alertQueue.[Notification_Type] AS NotificationType
, alertQueue.[Pickup_Date] AS PickupDate
, STUFF((SELECT CASE alertQueue.[Notification_Type]
WHEN 'Phone'
THEN CAST(CONCAT(' <br/> ', nestedAlertQueue.[Test_Name], ': ', nestedAlertQueue.[Test_Value]) AS varchar(MAX))
WHEN 'Email'
THEN CAST(CONCAT(' <br/> '
, nestedAlertQueue.[Test_Name]
, ' is '
, nestedAlertQueue.[Test_Value]
, CASE nestedAlertQueue.[Test_Name]
WHEN 'BF' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'LPC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'MUN' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'PIC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'PRO' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'SCC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'COLI' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'FFA' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'FRZP' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'INH' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'ROPY' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'SED' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'SPC' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'TEMP' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
ELSE ''
END) AS VARCHAR(MAX))
ELSE ''
END
FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS nestedAlertQueue
INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS nestedProducer
ON nestedProducer.[Prod No KEY] = nestedAlertQueue.[Prod No]
WHERE nestedAlertQueue.[Prod No] = alertQueue.[Prod No]
AND nestedAlertQueue.[Tank No] = alertQueue.[Tank No]
AND nestedAlertQueue.[Sequence_Number] = alertQueue.[Sequence_Number]
AND nestedAlertQueue.[Notification_Account] = alertQueue.[Notification_Account]
AND nestedAlertQueue.[Login] = alertQueue.[Login]
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS TestDetails
FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS alertQueue
INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS prod
ON prod.[Prod No KEY] = alertQueue.[Prod No]
GROUP BY alertQueue.[Prod No]
, alertQueue.[Tank No]
, alertQueue.[Sequence_Number]
, alertQueue.[Notification_Account]
, alertQueue.[Login]
, alertQueue.[Manifest_Number]
, prod.[Prod Div]
, prod.[Prod Name]
, alertQueue.[Notification_Type]
, alertQueue.[Pickup_Date]
ORDER BY alertQueue.[Prod No]
, alertQueue.[Notification_Account]
, alertQueue.[Login] ASC
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply