WHEN / CASE statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

WHEN / CASE statement

Author

Topic

kidaduo
Starting Member

45 Posts

Posted - 2007-07-05 : 18:30:24

Folks!

Getting error msg from the attached stored procedures! Error msg-'Invalid column name 'TRUE'

can you help/tell what I'm doing wrong

Thk

Josephine

CREATE PROCEDURE dbo.SP_EXPERIAN_QUEUE
(
@FRAUDANALYST NVARCHAR(50),
@QUEUE NVARCHAR(24),
@STATUS NVARCHAR(24)
)
AS

SELECT
E.EXPERIANID,
E.ALERT_RECEIVED_DATE,
E.ACCT,
E.SRC_INDV_ID,
E.INQUIRY_GOVT_NUMBER,
E.CUST_NM,
E.INQUIRY_STATE,
E.INQUIRY_DATE,
E.ALERT_IDENTIFIER,
E.[OVERRIDE] ,
E.BEST_SSN,
E.PRECISEIDSCORE
FROM
TBLEXPERIAN E
WHERE
TRUE = CASE
WHEN @FRAUDANALYST = 'ALL' THEN TRUE
WHEN EXPERIANANALYSTASSIGNED = @FRAUDANALYST THEN TRUE
ELSE FALSE END
AND
TRUE = CASE
WHEN @QUEUE = 'ALL' THEN TRUE
WHEN @QUEUE = 'LOWSCORE' AND
(
E.PRECISEIDSCORE < '500' OR
(
E.PRECISEIDSCORE < '600' AND
(
E.FraudShieldIndicator4 = 'Y' OR
E.FraudShieldIndicator5 = 'Y' OR
E.FraudShieldIndicator6 = 'Y' OR
E.FraudShieldIndicator13 = 'Y' OR
E.FraudShieldIndicator14 = 'Y' OR
E.FraudShieldIndicator25 = 'Y' OR
E.FraudShieldIndicator26 = 'Y' OR
E.FraudShieldIndicator27 = 'Y' OR
E.FraudShieldIndicator16 = 'Y' OR
E.FraudShieldIndicator10 = 'Y'
)
)
) THEN TRUE
WHEN @QUEUE = 'HIGH' THEN TRUE
ELSE FALSE END
AND TRUE = CASE
WHEN @STATUS = 'ALL' THEN TRUE
WHEN @STATUS = 'PENDING' and
(
E.EXPERIAN_DECISION_DT IS NOT NULL AND
E.EXPERIAN_DECISION IN ('YELLOW',
'E-MAIL SENT',
'ESCALATED FOR DECISION',
'MANAGEMENT REVIEW',
'QUALITY REVIEW',
'7 DAY LETTER')
) THEN TRUE
WHEN @STATUS = 'UNWORKED' AND
E.EXPERIAN_DECISION_DT is null THEN TRUE
ELSE FALSE END

Josephine

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-05 : 18:40:06

It would be 'TRUE' (in quotes). What are you trying to do?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

kidaduo
Starting Member

45 Posts

Posted - 2007-07-05 : 18:51:05

Dinakar. I tried 'TRUE' before, it never work. Thank u for help!

I dont know how to explain, but- I'm trying to make the statement below true--
'TRUE' = CASE
WHEN @FRAUDANALYST = 'ALL' THEN TRUE
WHEN EXPERIANANALYSTASSIGNED = @FRAUDANALYST THEN TRUE
ELSE FALSE END
AND
'TRUE' = CASE
WHEN @QUEUE = 'ALL' THEN TRUE
WHEN @QUEUE = 'LOWSCORE' AND
(
E.PRECISEIDSCORE < '500' OR
(
E.PRECISEIDSCORE < '600' AND
(
E.FraudShieldIndicator4 = 'Y' OR
E.FraudShieldIndicator5 = 'Y' OR
E.FraudShieldIndicator6 = 'Y' OR
E.FraudShieldIndicator13 = 'Y' OR
E.FraudShieldIndicator14 = 'Y' OR
E.FraudShieldIndicator25 = 'Y' OR
E.FraudShieldIndicator26 = 'Y' OR
E.FraudShieldIndicator27 = 'Y' OR
E.FraudShieldIndicator16 = 'Y' OR
E.FraudShieldIndicator10 = 'Y'
)
)
) THEN TRUE
WHEN @QUEUE = 'HIGH' THEN TRUE
ELSE FALSE END
---------------------------------------------------------------

quote:
Originally posted by dinakar

It would be 'TRUE' (in quotes). What are you trying to do?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Josephine

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-05 : 18:55:18

I think what you are looking for is something like this:


Where 
(CASE
		 	WHEN @FRAUDANALYST = 'ALL' THEN 1
			WHEN EXPERIANANALYSTASSIGNED = @FRAUDANALYST THEN 1
			ELSE 0 END ) = 1
AND 	
	(CASE 
			WHEN @QUEUE = 'ALL' THEN 1
			WHEN @QUEUE = 'LOWSCORE' AND 
				(
					E.PRECISEIDSCORE < '500' OR
					(
						E.PRECISEIDSCORE < '600' AND
						(
							E.FraudShieldIndicator4  = 'Y' OR
							E.FraudShieldIndicator5  = 'Y' OR
							E.FraudShieldIndicator6  = 'Y' OR
							E.FraudShieldIndicator13 = 'Y' OR
							E.FraudShieldIndicator14 = 'Y' OR
							E.FraudShieldIndicator25 = 'Y' OR
							E.FraudShieldIndicator26 = 'Y' OR
							E.FraudShieldIndicator27 = 'Y' OR
							E.FraudShieldIndicator16 = 'Y' OR
							E.FraudShieldIndicator10 = 'Y'
						)
					) 
				) THEN 1
			WHEN @QUEUE = 'HIGH' THEN 1
			ELSE 0 END) = 1

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 18:55:30

[code]CREATE PROCEDURE dbo.SP_EXPERIAN_QUEUE
(
@FRAUDANALYST NVARCHAR(50),
@QUEUE NVARCHAR(24),
@STATUS NVARCHAR(24)
)
AS

SET NOCOUNT ON

SELECT EXPERIANID,
ALERT_RECEIVED_DATE,
ACCT,
SRC_INDV_ID,
INQUIRY_GOVT_NUMBER,
CUST_NM,
INQUIRY_STATE,
INQUIRY_DATE,
ALERT_IDENTIFIER,
[OVERRIDE],
BEST_SSN,
PRECISEIDSCORE
FROM TBLEXPERIAN
WHERE @FRAUDANALYST IN ('ALL', EXPERIANANALYSTASSIGNED)
AND 1 = CASE
WHEN @QUEUE IN ('ALL', 'HIGH') THEN 1
WHEN @QUEUE = 'LOWSCORE' AND (PRECISEIDSCORE < '500' OR PRECISEIDSCORE < '600' AND 'Y' IN (FraudShieldIndicator4, FraudShieldIndicator5, FraudShieldIndicator6, FraudShieldIndicator13, FraudShieldIndicator14, FraudShieldIndicator25, FraudShieldIndicator26, FraudShieldIndicator27, FraudShieldIndicator16, FraudShieldIndicator10) THEN 1
ELSE 0
END
AND 1 = CASE
WHEN @STATUS = 'ALL' THEN 1
WHEN @STATUS = 'PENDING' AND EXPERIAN_DECISION_DT IS NOT NULL AND EXPERIAN_DECISION IN ('YELLOW', 'E-MAIL SENT', 'ESCALATED FOR DECISION', 'MANAGEMENT REVIEW', 'QUALITY REVIEW', '7 DAY LETTER') THEN 1
WHEN @STATUS = 'UNWORKED' AND EXPERIAN_DECISION_DT IS NULL THEN 1
ELSE 0
END[/code]

Peter Larsson
Helsingborg, Sweden

kidaduo
Starting Member

45 Posts

Posted - 2007-07-05 : 18:57:37

Thank you Dinakar. It work fine.

quote:

Originally posted by dinakar

I think what you are looking for is something like this:


Where 
(CASE
		 	WHEN @FRAUDANALYST = 'ALL' THEN 1
			WHEN EXPERIANANALYSTASSIGNED = @FRAUDANALYST THEN 1
			ELSE 0 END ) = 1
AND 	
	(CASE 
			WHEN @QUEUE = 'ALL' THEN 1
			WHEN @QUEUE = 'LOWSCORE' AND 
				(
					E.PRECISEIDSCORE < '500' OR
					(
						E.PRECISEIDSCORE < '600' AND
						(
							E.FraudShieldIndicator4  = 'Y' OR
							E.FraudShieldIndicator5  = 'Y' OR
							E.FraudShieldIndicator6  = 'Y' OR
							E.FraudShieldIndicator13 = 'Y' OR
							E.FraudShieldIndicator14 = 'Y' OR
							E.FraudShieldIndicator25 = 'Y' OR
							E.FraudShieldIndicator26 = 'Y' OR
							E.FraudShieldIndicator27 = 'Y' OR
							E.FraudShieldIndicator16 = 'Y' OR
							E.FraudShieldIndicator10 = 'Y'
						)
					) 
				) THEN 1
			WHEN @QUEUE = 'HIGH' THEN 1
			ELSE 0 END) = 1

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Josephine

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 19:06:17

12 seconds...

Peter Larsson
Helsingborg, Sweden

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-05 : 19:12:01

quote:
Originally posted by Peso

12 seconds...

Peter Larsson
Helsingborg, Sweden

Were you spending that time trying to format the code ?

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-05 : 19:14:45

That's it. I made some changes in the code too...

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources