asked on

Where and in how many places have I messed up this Query?

I'm stuck, I have very little experience in SQL, and it has already been interesting trying to find little differences in how to write a query for MS SQL 2012 when I was barely familiar with MS SQL 2008. I have the flowing query (apologies on how choppy it is, as it has evolved while I wrestled with each new error)

UPDATE 
 [SUBSCRIBER].[dbo].[Subscriber Data]
 SET
  [AcctType] = UPPER(AcctType)


UPDATE S
SET S.[AcctType] = CASE
	WHEN S.[AcctLineCode] = 'XA' 
			THEN 'ANSWER'
	WHEN S.[AcctLineCode] IN ('XX', 'XD', 'XIT', 'XT', 'YE', 'ZR', 'ZZ', 'XS8', 'XS5', 'XR9', 'XR8', 'XR4', 'XR1', 'XF' , 'X5', 'X19' , 'X13' )  
			THEN 'INFO'
	WHEN S.[AcctLineCode] IN ('DA1', 'DC1', 'DC5', 'DR1', 'DR3', 'DR4', 'DR7', 'DR9', 'DS1', 'DS5', 'DS8', 'D11', 'DV7', 'DV9' )  
			THEN 'DISCONNECTED'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/D)'
    WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (1/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (R3/D)'
	WHEN his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (1/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (S3/W)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (R3/W)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/W)'
    WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (S3/W)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (R3/W)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (1/W)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (S3/W)' 
	WHEN [OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (R3/W)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/W)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (S3/D)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (R3/D)'
	WHEN [AlarmCode_Count] >= 3 
		AND S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (1/D)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (S3/M)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (R3/M)'
	WHEN [AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/M)'
    WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (S3/M)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (R3/M)'
	WHEN his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (1/M)'
	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (S3/M)' 
	WHEN hisb.[OC_Count] >= 10 
		AND his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (R3/M)'
	WHEN his.[AlarmCode_Count] >= 1 
		AND S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/M)'
    WHEN S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (1/-)'
    WHEN S.[AcctType] LIKE 'COM%'  
			THEN 'COMMERCIAL (1/-)'
    WHEN S.[AcctType] LIKE 'RE%'  
			THEN 'RESIDENTIAL (T/-)'
	WHEN S.[AcctType] LIKE 'MED%'  
			THEN 'MEDICAL (T/-)'
    ELSE '?' END
FROM [SUBSCRIBER].[dbo].[Subscriber Data] S
INNER JOIN (
    SELECT [AccountID], COUNT(*) AS [AlarmCode_Count]
    FROM [History0119].[dbo].[SignalHistory] his
    WHERE [AlarmCode] IN ('991', 'TSF', 'TST')
    GROUP BY AccountID
) AS his ON his.[AccountID] = S.[AccountID]
INNER JOIN (
    SELECT [AccountID], COUNT(*) AS [OC_Count]
    FROM [History0119].[dbo].[SignalHistory] his
	LEFT JOIN [History1218].[dbo].[SignalLog] Sl ON his.[signalID] = Sl.[signalID]
    WHERE his.[AlarmCode] IN ('ACL', 'AOP', 'CL-',
 'CLA', 'CLC', 'CLE', 'CLL', 'CLO', 'CLS', 'CLP',
 'COC', 'DEC', 'DIS', 'FCE', 'KCL', 'KOP', 'NYC',
 'NYO', 'OCU', 'OP-', 'OPA', 'OPD', 'OPE', 'OPL',
 'OPN', 'OPP', 'OPS', 'RCL', 'RCO', 'ROP', 'TS1',
 'TS2', 'XCT') 
		AND Sl.[fAction] NOT IN ('* Account is in TEST Mode
', '* Zone/Code is in TEST Mode
'                                   
            )
    GROUP BY AccountID
) AS hisb ON his.[AccountID] = S.[AccountID]

INNER JOIN (
    SELECT [AccountID], COUNT(*) AS [Ts_Count]
    FROM [SUBSCRIBER].[dbo].[Time Schedules] TS
    GROUP BY AccountID
) AS ts ON ts.[AccountID] = S.[AccountID]

Open in new window

I know it is long, I'm sure there is a way to make it smaller, however currently I am perplexed as to why my result is.

(18618 rows affected)

(0 rows affected)

So my simple question is where and in how many places have I messed up as the column is now all upper case, but that is it.
TS1.png
SUB1.png
SUB2.png
THIS0119SH.png
THIS0119SL.png

D B

At very first, brief glance, try to combine your WHEN statements in the CASE expression. For example:

	WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE 'UL/C%'  
			THEN 'UL/COMMERCIAL (S3/D)' 
...
    WHEN ((S.[ClosingTolOpenAlert] = 'TRUE') OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND S.[AcctType] LIKE '%COM%'  
			THEN 'COMMERCIAL (S3/D)'

Open in new window

Both of these resolve to the value of 'COMMERCIAL (S3/D)' and the only difference is one looks for AcctType LIKE 'UL/C%' and the other, '%COM%'. Put them together as one. Also, compare ClosingTolOpenAlert to 0 (false) or 1 (true) as it is defined as BIT, which will be either 0 or 1 (or NULL):

    WHEN ((S.[ClosingTolOpenAlert] = 1) OR (S.[OETolerance] > 0) OR (S.[OLTolerance] > 0) OR (S.[CETolerance] > 0) OR (S.[CETolerance] > 0)) 
		AND ts.[Ts_Count] > 0 
		AND his.[AlarmCode_Count] >= 17 
		AND (S.[AcctType] LIKE '%COM%'  OR S.[AcctType] LIKE '%COM%')  
			THEN 'COMMERCIAL (S3/D)'

Open in new window

D B

Also, bear in mind, with your INNER JOINs, there MUST be data in the JOINed table to expose any AccountID. If there is no data in [SignalHistory] or [Time Schedules] the account will not be part of the UPDATE.
Unless your database collation is set to CS (case sensitive) the case doesn't matter.
Try replacing:
UPDATE S
SET S.[AcctType] = CASE
with SELECT * to view the data that would be returned from the query.

D B

Also, given the complexity of the CASE expression, bear in mind that SQL Server will, under most conditions short-circuit a CASE expression. That means, once it has found a WHEN condition that is met, it will exit the CASE. So try to put the most common conditions at the top to exit the CASE ASAP.
In this code section:

	LEFT JOIN [History1218].[dbo].[SignalLog] Sl ON his.[signalID] = Sl.[signalID]
    WHERE his.[AlarmCode] IN ('ACL', 'AOP', 'CL-',
 'CLA', 'CLC', 'CLE', 'CLL', 'CLO', 'CLS', 'CLP',
 'COC', 'DEC', 'DIS', 'FCE', 'KCL', 'KOP', 'NYC',
 'NYO', 'OCU', 'OP-', 'OPA', 'OPD', 'OPE', 'OPL',
 'OPN', 'OPP', 'OPS', 'RCL', 'RCO', 'ROP', 'TS1',
 'TS2', 'XCT') 
		AND Sl.[fAction] NOT IN ('* Account is in TEST Mode
', '* Zone/Code is in TEST Mode
'                                   
            )
    GROUP BY AccountID
) AS hisb ON his.[AccountID] = S.[AccountID]

Open in new window

You have the closing apostrophes for * Account is in TEST Mode and * Zone/Code is in TEST Mode on a new line. This will be interpreted by SQL Server literally, which means the fAction must be stored in the table as '* Account is in TEST Mode<CR><LF>' (Carriage Return + Line Feed). Unless this is the actual stored value, move the closing apostrophes to the end of the string on the same line.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

csePixelated

ASKER

Scott, that did the trick, thank-you!
It would seem to ignore everything in the "case when" parts, not just the ones that are part of the Inner join, this is interesting as i thought it would affect each row until it got stuck by the one es containing variables that needed to be left outer joins.
Doug, I really appreciate the help,
I will look into the CASE expression info you gave, I am running that separately now.
although you may have overlooked 'UL/COMMERCIAL (S3/D)' and 'COMMERCIAL (S3/D)' are not quite the same the UL abbreviation is an important distinction for that field.