IF inside of CASE

Can anyone help me with condition inside CASE. Below is my code and I get
syntax error at IF line. Thanks.
SELECT TOP 100 PERCENT
dbo.WorkerDeductions.EmployeeNumber,
dbo.WorkerDeductions.DedCode,
dbo.WorkerDeductions.DedAmt,
dbo.WorkerDeductions.DeductionBalance,
dbo.WorkerDeductions.DedPercent,
dbo.PayInfoNHS.EarnGross,
dbo.PayInfoNHS.CheckID,
DedCalc = CASE
WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
BEGIN
dbo.WorkerDeductions.DedAmt
END
ELSE dbo.WorkerDeductions.DeductionBalance
WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
ELSE dbo.WorkerDeductions.DedAmt
END
FROM dbo.WorkerDeductions INNER JOIN
dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
dbo.PayInfoNHS.EmployeeNumber INNER JOIN
dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
dbo.DeductionCodeLookup.DedCode
WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
102))
ORDER BY dbo.WorkerDeductions.EmployeeNumberYou can't use IF in a query; IF is for flow, not statement-level control.
Try a nested CASE:
CASE
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
WHEN ... THEN
CASE WHEN ... THEN
ELSE ...
END
ELSE
..
END
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"David C" <dlchase@.lifetimeinc.com> wrote in message
news:ODfTYAyLFHA.2384@.tk2msftngp13.phx.gbl...
> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>|||David C,
You can nest a CASE expression inside another, but you can not use IF inside
a CASE.
AMB
"David C" wrote:

> Can anyone help me with condition inside CASE. Below is my code and I get
> syntax error at IF line. Thanks.
> SELECT TOP 100 PERCENT
> dbo.WorkerDeductions.EmployeeNumber,
> dbo.WorkerDeductions.DedCode,
> dbo.WorkerDeductions.DedAmt,
> dbo.WorkerDeductions.DeductionBalance,
> dbo.WorkerDeductions.DedPercent,
> dbo.PayInfoNHS.EarnGross,
> dbo.PayInfoNHS.CheckID,
> DedCalc = CASE
> WHEN dbo.WorkerDeductions.DeductionBalance > 0 THEN
> IF dbo.WorkerDeductions.DeductionBalance > dbo.WorkerDeductions.DedAmt
> BEGIN
> dbo.WorkerDeductions.DedAmt
> END
> ELSE dbo.WorkerDeductions.DeductionBalance
> WHEN dbo.WorkerDeductions.DedPercent > 0 THEN
> ROUND(dbo.WorkerDeductions.DedPercent * dbo.PayInfoNHS.EarnGross, 2)
> ELSE dbo.WorkerDeductions.DedAmt
> END
> FROM dbo.WorkerDeductions INNER JOIN
> dbo.PayInfoNHS ON dbo.WorkerDeductions.EmployeeNumber =
> dbo.PayInfoNHS.EmployeeNumber INNER JOIN
> dbo.DeductionCodeLookup ON dbo.WorkerDeductions.DedCode =
> dbo.DeductionCodeLookup.DedCode
> WHERE (dbo.PayInfoNHS.CheckDate = CONVERT(DATETIME, '2005-03-18 00:00:00',
> 102))
> ORDER BY dbo.WorkerDeductions.EmployeeNumber
>
>|||That worked. Thank you.
David
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!

if exists condition..

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

regardsfireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?

Whichever you fancy. I would probably write the second nine times out
of ten, but that it would only be because db_id() would not come in
my mind. And a nice characteristic of the first, is that it works on
SQL 2000 as well.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I would probably write the second
[...]

thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?|||fireball (fireball@.onet.kropka.eu) writes:

Quote:

Originally Posted by

Quote:

Originally Posted by

>I would probably write the second

[...]
>
thank you.
so you would say, first solution is not less professional at all then the
second one.. - right?

Yes, both are perfectly OK, and which you pick is a matter of taste or
the flux of the moment.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||The first one; its more portable between versions of SQL Server, the second
only works in SQL 2005 and upwards.

--
Tony Rogerson
SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson - technical commentary from a SQL
Server Consultant
http://sqlserverfaq.com - free video tutorials

"fireball" <fireball@.onet.kropka.euwrote in message
news:embrdl$n8d$1@.atlantis.news.tpi.pl...

Quote:

Originally Posted by

which is more proper:
if DB_ID(@.db) IS NOT NULL
or
IF EXISTS (SELECT name FROM sys.databases WHERE name = @.db)
?
>
>
>
>
regards
>
>

if else with WHERE

Hi,
I'm trying to create a store proc and use a WHERE clause only if a certain
condition is met. When I use the WHERE after an if condition, the sql
enterprise tool tells me
it's invalid. "Invalid syntax near WHERE clause"
Here is my proc:
CREATE PROCEDURE [dbo].GetContractorsList
@.characterFilter char(1)
AS
SELECT
[ContractorCode],
[ContractorName],
FROM
[dbo].[Contractors]
if @.characterFilter !='*'
begin
WHERE ContractorName LIKE @.characterFilter + '%'
end
Any ideas on how to do this?
Thanks,
OpaOpa wrote:
> Hi,
> I'm trying to create a store proc and use a WHERE clause only if a
> certain condition is met. When I use the WHERE after an if
> condition, the sql enterprise tool tells me
> it's invalid. "Invalid syntax near WHERE clause"
>
http://www.sommarskog.se/dyn-search.html
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"|||Thanks for your reply Bob.
The article you posted is quite lengthy and refers to Dynamic SQL.
Can I do this without it?
"Bob Barrows [MVP]" wrote:

> Opa wrote:
> http://www.sommarskog.se/dyn-search.html
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>
>|||Yes, one of the methods described in the article should suit your purpose:
WHERE (@.characterFilter != '*' OR ContractorName LIKE @.characterFilter +
'%')
I suggest you read the entire article. It is quite informative.
Opa wrote:
> Thanks for your reply Bob.
> The article you posted is quite lengthy and refers to Dynamic SQL.
> Can I do this without it?
>
> "Bob Barrows [MVP]" wrote:
>
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

If Else Statement in a Update Trigger

I am trying to build an update trigger to check the condition if checkbox is true then add 1 year, else add 3 years. The code works before i added the if checkbox = true.

Original Working code:

IF NOT UPDATE (EDITED)

UPDATE drvisit

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Code I am trying to use with an IF Statement:

IF NOT UPDATE (EDITED)

UPDATE drvisit

IF Checkbox = True

SET nextvisit = dateadd (yy, 1, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Else

Set nextvisit = dateadd (yy, 3, lastaudio)

FROM Apt

WHERE rec# IN (SELECT rec# FROM inserted)

Use a case statement instead of "IF"
This example assumes "checkbox" is a bit field...

IF NOT UPDATE (EDITED)

UPDATE drvisit
SET nextvisit = case when checkbox = 1 then dateadd (yy, 1, lastaudio) else dateadd (yy, 3, lastaudio) end
FROM Apt
WHERE rec# IN (SELECT rec# FROM inserted)

|||Sweet thanks.. If i did want to do and if statement how would I? On a few occasions I tried it and could never get it to work.|||

Glad to be of help.

"IF" is really only for controlling the flow of execution of your SQL script. It can't be used within a single SQL statement (like UPDATE) to conditionally apply a value.

If Else If condition failing

This is only the second stored procedure that I've written, and I'm
having some issues with a conditional statement that I can't figure
out. The statement has a conditional If statement with two Else If's
that checks for a passed parameter's value (an integer I pass when
executing the SP via ASP). Here is basically what I have:
-- @.SET STATUS has 3 valid values:
-- 1 - Don't set status
-- 2 - Set inactive
-- 3 - Set active
CREATE PROCEDURE dbo.sp_SomeProcedure
@.SET_STATUS int
AS
SET NOCOUNT ON
IF @.SET_STATUS = 1
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE
BEGIN
INSERT STATEMENT
END
ELSE IF @.SET_STATUS = 2
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
ELSE IF @.SET_STATUS = 3
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
GO
When @.SET_STATUS is set to either 1 or 2, the sequel statements run
fine and the corresponding row gets updated or inserted accordingly.
If however the @.SET_STATUS is passed as 3, the procedure executes fine,
but the update statement is not run.
I've manually plugged in the "exists" condition and the update
statement for this part of the procedure and they fire off correctly
when run in QA. I'm at a loss. I've pretty much determined that it is
failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
why since it passes the syntax check.
Am I missing something here? Thanks in advance!Here's how I recommend the structure:
IF @.SET_STATUS = 1
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 2
BEGIN
.. do stuff ...
END
IF @.SET_STATUS = 3
BEGIN
.. do stuff ...
END
There's no need for ELSE, and you should always wrap the result of an IF
statement in BEGIN/END.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||The ELSE IF @.SET_STATUS = 3 is the else of the IF EXISTS() under
@.SET_STATUS = 2
So it's never actually getting to the IF @.SET_STATUS = 3 statement.
Add BEGIN..END around the IF EXISTS() under each ELSE IF
e.g.
ELSE IF @.SET_STATUS = 2
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
ELSE IF @.SET_STATUS = 3
BEGIN
IF EXISTS (SELECT STATEMENT)
BEGIN
UPDATE STATEMENT
END
END
tsigler@.gmail.com wrote:
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>|||Aaron, you're a rock star! It must not have liked the second "else if"
and after updating like you suggested, everything works like a champ.
Thanks!!!|||Thanks Trey ;)|||Depending on what you're doing in the 3 updates, you may be able to combine
these into 1 statement.
> IF @.SET_STATUS = 1 AND NOT EXISTS (SELECT STATEMENT)
> BEGIN
> INSERT STATEMENT
> END
ELSE
> BEGIN
> UPDATE STATEMENT
> END
Explain what the 3 updates do in your procedure and maybe we can suggest a
better way.
In this part:
> ELSE IF @.SET_STATUS = 2 (or 3)
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
I don't think that you need to check IF EXISTS unless you are performing an
action if this is FALSE.
Your update statement should affect 0 rows if it doesn't exist.
<tsigler@.gmail.com> wrote in message
news:1135797481.201874.264140@.g47g2000cwa.googlegroups.com...
> This is only the second stored procedure that I've written, and I'm
> having some issues with a conditional statement that I can't figure
> out. The statement has a conditional If statement with two Else If's
> that checks for a passed parameter's value (an integer I pass when
> executing the SP via ASP). Here is basically what I have:
> -- @.SET STATUS has 3 valid values:
> -- 1 - Don't set status
> -- 2 - Set inactive
> -- 3 - Set active
> CREATE PROCEDURE dbo.sp_SomeProcedure
> @.SET_STATUS int
> AS
> SET NOCOUNT ON
> IF @.SET_STATUS = 1
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE
> BEGIN
> INSERT STATEMENT
> END
> ELSE IF @.SET_STATUS = 2
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> ELSE IF @.SET_STATUS = 3
> IF EXISTS (SELECT STATEMENT)
> BEGIN
> UPDATE STATEMENT
> END
> GO
>
> When @.SET_STATUS is set to either 1 or 2, the sequel statements run
> fine and the corresponding row gets updated or inserted accordingly.
> If however the @.SET_STATUS is passed as 3, the procedure executes fine,
> but the update statement is not run.
> I've manually plugged in the "exists" condition and the update
> statement for this part of the procedure and they fire off correctly
> when run in QA. I'm at a loss. I've pretty much determined that it is
> failing at the "ELSE IF @.SET_STATUS = 3" condition, but I don't know
> why since it passes the syntax check.
> Am I missing something here? Thanks in advance!
>

If Else Condition

Hi guys,

I have 2 tables which are connected to each other by casenumber:

CodeTable

CaseNumber OldCode

001 05

002 05

003 05

004 05

005 06

ConnectionTable

CaseNumber ConnectionType

001 G

001 H

001 N

002 M

002 H

003 G

003 H

003 I

003 N

003 N

004 X

004 N

--

With following Mapping Condition:

OldCode NewCode

05 (with connectionType G) GE

05 (with ConnectionType H) GP

05 (With ConnectionType I) GPE

05 (With ConnectionType G OR H) GPE

--

With the following Rules:

1. Search for connection type that is mapped and disregard all other connection types.

2. If none of the mapped types are connected to the case, map as follows : 05 to GPE

3. If multiple types are connected to the case, map as follows: 05 to GPE.

I would like to map the old code to the new code on the CodeTable without getting duplicate CaseNumber.

My code:

SELECT DISTINCT c1.CaseNumber,

CASE WHEN c1.OldCode = '05' THEN

CASE c2.ConnectionType WHEN 'G' THEN 'GE'

WHEN 'H' THEN 'GP'

WHEN 'I' THEN 'GPE'

ELSE 'GPE' END END AS NewCode,

FROM CodeTable AS c1

LEFT JOIN(SELECT MIN(ConnectionType) [ConnectionType],CaseNumber

FROM ConnectionTable GROUP BY CaseNumber) AS c2

ON (c1.CaseNumber = c2.CaseNumber)

I did not get all answer correctly because let say i have Casenumber 001 with connection G, H, and N; as given in the rule, I need to map the multiple types to GPE, but what i get is GE (which is OldCode 'G'). This is probably because i select MIN from connectionType and the first one i get is G, that's why i get GE for the new code instead of GPE.

I hope you guys will help me on this. Thanks so much!!!

Jul.

If I understand your requirements correctly, perhaps something like this:

Code Snippet

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'G' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '008', 'H' )

SELECT DISTINCT
c1.CaseNumber,
c1.OldCode,
NewCode = CASE
WHEN ( c1.OldCode = '05' ) THEN
CASE
WHEN ( dt.CaseCount = 1 ) THEN
CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'
WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'
ELSE 'GPE'
END
WHEN ( dt.CaseCount <> 1 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'
END
ELSE 'GPE'
END
FROM @.Codes c1
LEFT JOIN (SELECT
CaseNumber,
CaseCount = count( CaseNumber )
FROM @.Connections
GROUP BY CaseNumber
) dt
ON c1.CaseNumber = dt.CaseNumber
LEFT JOIN @.Connections c2
ON c1.CaseNumber = c2.CaseNumber

CaseNumber OldCode NewCode
- - -
001 05 GPE
002 05 GPE
003 05 GPE
004 05 GE
005 05 GP
006 05 GPE
007 05 GE
008 06 GPE
009 06 GPE

|||

Thanks Arnie,

CaseNumber ConnectionType

001 G

001 H

001 N

002 M

002 H

003 G

003 H

003 I

003 N

003 N

004 X

004 N

The appropriate result is supposed to be:

CaseNumber NewCode
001 GPE (Because there are multiple types refer rul 3)
002 GP (Because H is in there, and ignore M because M is not

in one of the mapping criteria of code 05) [refer to rule 1.)
003 GPE (Because there are multiple types refer rule 3)
004 GPE (Because none of the mapped types are connected to the case -- refer rule 2)

Can you help me on this please? thanks........

|||

Hi Jul

Hope this suits your every requirement, I have done few modification to the code Arnie. Assuming that u require something similar like this.

Regards

Vijai K

SET NOCOUNT ON

Declare @.final table(

CaseNumber varchar(10),

NewCode varchar(5)

);

DECLARE @.Codes table(

CaseNumber varchar(10),

OldCode varchar(5)

)

INSERT INTO @.Codes VALUES ( '001', '05' )

INSERT INTO @.Codes VALUES ( '002', '05' )

INSERT INTO @.Codes VALUES ( '003', '05' )

INSERT INTO @.Codes VALUES ( '004', '05' )

INSERT INTO @.Codes VALUES ( '005', '05' )

INSERT INTO @.Codes VALUES ( '006', '05' )

INSERT INTO @.Codes VALUES ( '007', '05' )

INSERT INTO @.Codes VALUES ( '008', '06' )

INSERT INTO @.Codes VALUES ( '009', '06' )

INSERT INTO @.Codes VALUES ( '010', '05' )

DECLARE @.Connections table(

CaseNumber varchar(10),

ConnectionType varchar(5)

)

INSERT INTO @.Connections VALUES ( '001', 'G' )

INSERT INTO @.Connections VALUES ( '001', 'H' )

INSERT INTO @.Connections VALUES ( '001', 'N' )

INSERT INTO @.Connections VALUES ( '002', 'M' )

INSERT INTO @.Connections VALUES ( '002', 'H' )

INSERT INTO @.Connections VALUES ( '003', 'G' )

INSERT INTO @.Connections VALUES ( '003', 'H' )

INSERT INTO @.Connections VALUES ( '003', 'I' )

INSERT INTO @.Connections VALUES ( '003', 'N' )

INSERT INTO @.Connections VALUES ( '003', 'N' )

INSERT INTO @.Connections VALUES ( '004', 'X' )

INSERT INTO @.Connections VALUES ( '004', 'N' )

INSERT INTO @.Connections VALUES ( '005', 'H' )

INSERT INTO @.Connections VALUES ( '005', 'X' )

INSERT INTO @.Connections VALUES ( '005', 'N' )

INSERT INTO @.Connections VALUES ( '006', 'I' )

INSERT INTO @.Connections VALUES ( '007', 'G' )

INSERT INTO @.Connections VALUES ( '007', 'N' )

INSERT INTO @.Connections VALUES ( '008', 'H' )

INSERT INTO @.Connections VALUES ( '009', 'H' )

INSERT INTO @.Connections VALUES ( '010', 'G' )

INSERT INTO @.Connections VALUES ( '010', 'X' )

insert into @.final

SELECT DISTINCT

c1.CaseNumber,

NewCode = CASE

WHEN ( c1.OldCode = '05' ) THEN

CASE

WHEN ( dt.CaseCount = 1 ) OR ( dt.CaseCount = 2 ) THEN

CASE WHEN ( c2.ConnectionType = 'G' ) THEN 'GE'

WHEN ( c2.ConnectionType = 'H' ) THEN 'GP'

ELSE 'GPE'

END

WHEN ( dt.CaseCount > 2 ) OR ( dt.CaseNumber IS NULL ) THEN 'GPE'

END

ELSE 'GPE'

END

FROM @.Codes c1

LEFT JOIN (SELECT CaseNumber, CaseCount = count(CaseNumber)

FROM @.Connections

GROUP BY CaseNumber) dt

ON c1.CaseNumber = dt.CaseNumber

LEFT JOIN @.Connections c2

ON c1.CaseNumber = c2.CaseNumber

delete from @.final where Newcode = 'GPE' and casenumber in( select Casenumber from @.final group by casenumber having count(*) >1 )

select * from @.final

|||Here's a guess. It's probably not quite there yet, but it may give you some ideas.

Code Snippet

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '05' )
INSERT INTO @.Codes VALUES ( '006', '05' )
INSERT INTO @.Codes VALUES ( '007', '05' )
INSERT INTO @.Codes VALUES ( '008', '06' )
INSERT INTO @.Codes VALUES ( '009', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'G' )
INSERT INTO @.Connections VALUES ( '005', 'H' )
INSERT INTO @.Connections VALUES ( '006', 'I' )
INSERT INTO @.Connections VALUES ( '007', 'G' )
INSERT INTO @.Connections VALUES ( '008', 'H' )

DECLARE @.MappedTypes table (
ID int,
OldCode varchar(5),
mappedType varchar(5)
)
INSERT INTO @.MappedTypes VALUES (0, '05', 'G')
INSERT INTO @.MappedTypes VALUES (1, '05', 'H')
INSERT INTO @.MappedTypes VALUES (2, '05', 'I')

DECLARE @.Mapping table (
OldCode varchar(5),
Connections varchar(10),
Bitmask varbinary(16),
newCode varchar(5)
)
INSERT INTO @.Mapping VALUES ( '05', 'G', 0x1, 'GE')
INSERT INTO @.Mapping VALUES ( '05', 'H', 0x2, 'GP')
INSERT INTO @.Mapping VALUES ( '05', 'I', 0x4, 'GPE')
INSERT INTO @.Mapping VALUES ( '05', NULL, NULL, 'GPE');
-- last row indicates code to use when there is some
-- mapped value present, but when the particular collection
-- of mapped values does not have a specific mapping.

with B(CaseNumber, Bitmask, OldCode) as (
select
C.CaseNumber,
sum(distinct coalesce(power(2,ID),0)) as Bitmask,
D.OldCode

from @.Connections as C
join @.Codes as D
on D.CaseNumber = C.CaseNumber
join @.MappedTypes AS M
on C.ConnectionType = M.mappedType
and D.OldCode = M.OldCode
group by C.CaseNumber, D.OldCode
)
select
CaseNumber,
coalesce(newCode,(select newCode from @.Mapping where Bitmask is null))
from B
left outer join @.Mapping as M
on M.OldCode = B.OldCode
and M.Bitmask = B.Bitmask

Steve Kass
Drew University
http://www.stevekass.com
|||

Jul,

I looked at this problem again, and came to the conclusion that using a Mapping table would be useful. I think that this satisfies your rules and matches your expected output.

Code Snippet

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )

DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)

INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )

SELECT
c.CaseNumber,
NewCode = CASE
WHEN count( m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
ELSE 'GPE'
END
FROM @.Codes c
LEFT JOIN @.Connections c2
ON c.CaseNumber = c2.CaseNumber
LEFT JOIN @.CodeMap m
ON c2.ConnectionType = m.OldType
GROUP BY c.CaseNumber

CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE

|||

The following Code BY Arnie is the most appropriate answer. Thank's guys!

SET NOCOUNT ON

DECLARE @.Codes table
( CaseNumber varchar(10),
OldCode varchar(5)
)

INSERT INTO @.Codes VALUES ( '001', '05' )
INSERT INTO @.Codes VALUES ( '002', '05' )
INSERT INTO @.Codes VALUES ( '003', '05' )
INSERT INTO @.Codes VALUES ( '004', '05' )
INSERT INTO @.Codes VALUES ( '005', '06' )

DECLARE @.Connections table
( CaseNumber varchar(10),
ConnectionType varchar(5)
)

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )
INSERT INTO @.Connections VALUES ( '002', 'H' )
INSERT INTO @.Connections VALUES ( '002', 'M' )
INSERT INTO @.Connections VALUES ( '003', 'G' )
INSERT INTO @.Connections VALUES ( '003', 'H' )
INSERT INTO @.Connections VALUES ( '003', 'I' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '003', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'N' )
INSERT INTO @.Connections VALUES ( '004', 'X' )

DECLARE @.CodeMap table
( OldCode char(2),
OldType char(1),
NewCode varchar(3)
)

INSERT INTO @.CodeMap VALUES ( '05', 'G', 'GE' )
INSERT INTO @.CodeMap VALUES ( '05', 'H', 'GP' )

SELECT
c.CaseNumber,
NewCode = CASE
WHEN count( m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )
ELSE 'GPE'
END
FROM @.Codes c
LEFT JOIN @.Connections c2
ON c.CaseNumber = c2.CaseNumber
LEFT JOIN @.CodeMap m
ON c2.ConnectionType = m.OldType
GROUP BY c.CaseNumber

CaseNumber NewCode
- -
001 GPE
002 GP
003 GPE
004 GPE
005 GPE

|||

Arnie, seems like it does not work if instead of having

INSERT INTO @.Connections VALUES ( '001', 'G' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )

but i'm having these values:

INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'H' )
INSERT INTO @.Connections VALUES ( '001', 'N' )

On the rules given, if I have H (without G or I) then map to 'GP'

If i have multiple matched connections let say 'H' AND 'G' (or 'H' AND 'I') then map to 'GPE'.

But i have a case where the connection in the case is like the values above, which is H, H, and N. When i tried your code, it gives me 'GPE', what i need is 'GE'. Everything else works perfect. I am really a beginner in sql, hope you can help. thanks.

|||

Of course, I overlooked that possiblitilty. Thanks for bringing it to my attention.

If you add 'DISTINCT' to the count( m.NewCode ) it 'should' take care of that situation.

Code Snippet

NewCode = CASE
WHEN count( DISTINCT m.NewCode ) = 1 AND min( m.NewCode ) IS NOT NULL THEN min( m.NewCode )

And this 'should' handle any additional 'OldCodes' just by inserting rows into the Mapping table. (Unless your rules change substaintially.)

|||

Awesome........Thanks so much!!!!!!!!

Julia

|||A tip 'o the hat to Steve Kass for nudging me to consider a mapping table...

if condition within select query sql server 2000

Hi all,

I have to write a select query which need some logic to be implemented.

Query is like

select name,number,active,

if active ="true" then

select @.days=days from tbl_shdsheet

else

@.days=''

end

from tbl_emp

In the above query there will be days row for that employee if active is true else there won't be any data for that emp in the tbl_shdsheet

So how can i write queery for this.

You probably want to implement this using a Left Join:http://www.w3schools.com/sql/sql_join.asp

|||

Hi Thanks for replying.

My Problem has solved.I wrote a user defined function with the if else condition to be checked by sending the value to be checked as parameter to that function

|||

You can use user defined function to achieve this but you can easily do this by using case..when..else..end statement and joins. Below is your modified statement. You need to fill in the column names for mapping both the tables.

select name,number,active,
case
when te.active ='true'then ts.days
else''
end as Days
from tbl_emp teleftjoin tbl_shdsheeton te.[column to map] = ts.[column to map]

if condition with stored procedure

i am trying to use INSERT statement based on some condition not WHERE but using IF condition on the database. something like
INSERT INTO table1 if id = @.id or order = @.fdfdfd.

i think it will return no of rows affected with insert statement. am i right?
any help will be appreciated.First of all, i am not clear with your question.

If you want to insert based on some condition, then specify the insert statment inside an if statement.

Regards
Ravi

If Condition In Select Statement...

Hi,
I need to write an if condition in SELECT statement. Below is the code for the same. But its throwing error. Can some refine the code below.

SELECT tblCustomer.Customer_LegalName,
(IF (tblCustomer.IsNRACustomer = TRUE) SELECT tblCustomer.Customer_PassportNo ELSE
ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)) AS TAXID,

tblCustomer.Customer_DoingBusinessAs, tblSeed_EDDCategory.CategoryName, '2' AS DCS, tblUser_OfficerCode.User_OfficerCode,
tblCustomer.Customer_AreaId, tblCustomer.Customer_BranchId, CONVERT(VARCHAR(11), tblCustomer_EDDCategory.CreateDate)
AS CreateDate, tblSeed_EDDCategory.EDDCategoryId, tblCustomer_EDDCategory.Category_CreateEmpId,
tblCustomer_EDDCategory.CustCatId, tblCustomer.Customer_Id, tblSeed_Area.AreaName, tblSeed_Employee.Name,
tblUser_OfficerCode.User_OfficerName, tblCustomer.Customer_TaxId, tblCustomer.IsNRACustomer,
tblCustomer.Customer_PassportNo
FROM tblCustomer INNER JOIN
blCustomer_EDDCategory ON tblCustomer.Customer_Id = tblCustomer_EDDCategory.CustomerId INNER JOIN
tblSeed_EDDCategory ON tblCustomer_EDDCategory.EDDCategoryId = tblSeed_EDDCategory.EDDCategoryId INNER JOIN
tblSeed_Employee ON tblCustomer.Customer_CreateEmpId = tblSeed_Employee.EmployeeId INNER JOIN
tblUser_OfficerCode ON tblCustomer.Customer_CreateEmpId = tblUser_OfficerCode.EmployeeId INNER JOIN
tblSeed_Area ON tblCustomer.Customer_AreaId = tblSeed_Area.AreaId

Thanks,
Rahul JhaIt might help if you checked for the correct syntax in Books Online...
Use a CASE statement int he SELECT clause:

SELECT tblCustomer.Customer_LegalName,
case tblCustomer.IsNRACustomer
when TRUE then tblCustomer.Customer_PassportNo
else ISNULL(tblCustomer.Customer_TaxId, tblCustomer.Customer_PassportNo)
end AS TAXID,
...

...but you are still going to have to define what "TRUE" is. What datatype is IsNRACustomer?|||Thanks Blindman :-)

IF Condition in Join?

Dear Group

I'd be grateful if you can send me on the right track in achieving this.

I have three tables A,B,C outlined as follows:

Table: A
Field: RowID
Field: EntityID
Field: TypeIdentifier

Table: B
Field: RowID
Field: Name

Table: C
Field: RowID
Field: Name

Let's assume I've the following records:

Table A:
1,1,0
2,1,1

Table B:
1,Smith

Table C:
1,XYZCorporation

The table joins are as follows:

A.EntityID = B.RowID
A.EntityID = C.RowID

I would like to select all records from Table A and display the Names from
either Table B or Table C, depending on the Field TypeIdentifier.
E.g.: SELECT Name FROM A JOIN B ON (A.EntityID = B.RowID) JOIN C ON
(A.EntityID = C.RowID) IF TypeIdentifier = 0 SELECT Name FROM B IF
TypeIdentifier = 1 SELECT Name FROM C

Resultset:

Smith
XYZCorporation

Is this somehow possible?

Thanks very much for your time & efforts!

MartinSELECT COALESCE(B.name,C.name) AS name
FROM A
LEFT JOIN B
ON A.entityid = B.rowid
AND A.typeidentifier = 1
LEFT JOIN C
ON A.entityid = C.rowid
AND A.typeidentifier = 0

--
David Portas
SQL Server MVP
--|||Thanks very much David!

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:X4GdnRjemKI4mOndRVn-uA@.giganews.com...|||In the past, I have done something like this:

LEFT JOIN B ON A.entityid = B.rowid
LEFT JOIN C ON A.entityid = C.rowid"

with a Case statement in the select. Your version
is much nicer, thanks.

Bill

David Portas wrote:

> SELECT COALESCE(B.name,C.name) AS name
> FROM A
> LEFT JOIN B
> ON A.entityid = B.rowid
> AND A.typeidentifier = 1
> LEFT JOIN C
> ON A.entityid = C.rowid
> AND A.typeidentifier = 0

if condition and batch statements

Is it possible in tsql ? How can I do the following?
if ( 1 = 1)
begin
print 'condition was true'
return --exit the script completely -- how do I do that?
end
-- otherwise continue with the script
print 'continue'
go
print 'with'
go
print 'the'
go
print 'script'
go
Please let me know if this needs further clarification and TIA..sqlster wrote:
> Is it possible in tsql ? How can I do the following?
> if ( 1 = 1)
> begin
> print 'condition was true'
> return --exit the script completely -- how do I do that?
> end
> -- otherwise continue with the script
> print 'continue'
> go
> print 'with'
> go
> print 'the'
> go
> print 'script'
> go
> Please let me know if this needs further clarification and TIA..
Strictly speaking GO isn't a TSQL statement. It's a batch separator
that tells the client to submit a batch of TSQL statements. So the
answer is that it isn't possible in TSQL, it is possible using the
client utilities. Unfortunately you didn't specify what you are using
to execute this script.
On the OSQL command line you can use the -b option to terminate when an
error occurs. Then just put a RAISERROR in your IF statement block.
If you are executing the script as a symin then you can use
RAISERROR with a severity level >= 20 to terminate the connection.
David Portas
SQL Server MVP
--|||No, each batch is separate. If you don't need the GO batch separators:
IF (1=1)
BEGIN
RAISERROR(condition was true',1,1) WITH NOWAIT
RETURN
END
PRINT 'continue'
PRINT 'with'
PRINT 'the'
PRINT 'script'
or
IF (1=1)
BEGIN
PRINT 'Condition was true'
GOTO QuitLabel
END
PRINT 'continue'
PRINT 'with'
PRINT 'the'
PRINT 'script'
QuitLabel:
"sqlster" <nospam@.nospam.com> wrote in message
news:5D6CD06D-134F-4BCF-8A35-C7526405C915@.microsoft.com...
> Is it possible in tsql ? How can I do the following?
> if ( 1 = 1)
> begin
> print 'condition was true'
> return --exit the script completely -- how do I do that?
> end
> -- otherwise continue with the script
> print 'continue'
> go
> print 'with'
> go
> print 'the'
> go
> print 'script'
> go
> Please let me know if this needs further clarification and TIA..|||> RAISERROR(condition was true',1,1) WITH NOWAIT
-- sorry,
RAISERROR('condition was true',1,1) WITH NOWAIT|||David, I am using sql query analyzer.
"David Portas" wrote:

> sqlster wrote:
> Strictly speaking GO isn't a TSQL statement. It's a batch separator
> that tells the client to submit a batch of TSQL statements. So the
> answer is that it isn't possible in TSQL, it is possible using the
> client utilities. Unfortunately you didn't specify what you are using
> to execute this script.
> On the OSQL command line you can use the -b option to terminate when an
> error occurs. Then just put a RAISERROR in your IF statement block.
> If you are executing the script as a symin then you can use
> RAISERROR with a severity level >= 20 to terminate the connection.
> --
> David Portas
> SQL Server MVP
> --
>|||Aaron,
I have whole bunch of create/drop database objects such as views, stored
procs, tables, functions etc. I am not sure what would be impact of removing
all the go statements.
In other words, is there any thing wrong with the following:
IF (1=1)
BEGIN
RAISERROR('condition was true,1,1) WITH NOWAIT
RETURN
END
drop myproc1
create myproc1
...
--go take out go statement
drop myproc1
create myproc1
...
--go take out go statement
drop mytable1
create mytable1
...
TIA...
"Aaron Bertrand [SQL Server MVP]" wrote:

> No, each batch is separate. If you don't need the GO batch separators:
> IF (1=1)
> BEGIN
> RAISERROR(condition was true',1,1) WITH NOWAIT
> RETURN
> END
> PRINT 'continue'
> PRINT 'with'
> PRINT 'the'
> PRINT 'script'
> or
> IF (1=1)
> BEGIN
> PRINT 'Condition was true'
> GOTO QuitLabel
> END
> PRINT 'continue'
> PRINT 'with'
> PRINT 'the'
> PRINT 'script'
> QuitLabel:
>
>
> "sqlster" <nospam@.nospam.com> wrote in message
> news:5D6CD06D-134F-4BCF-8A35-C7526405C915@.microsoft.com...
>
>|||> I have whole bunch of create/drop database objects such as views, stored
> procs, tables, functions etc. I am not sure what would be impact of
> removing
> all the go statements.
Yes, for example, CREATE PROCEDURE must be the first statement in a batch.
Various people deal with this kind of situation in various ways. The way
Management Studio prepares scripts for multiple objects without GO, IIRC, is
that it wraps the CREATE PROCEDURE in dynamic SQL, e.g. EXEC('CREATE
PROCEDURE dbo.whatever ...');
A|||Thank you all for the help...
"Aaron Bertrand [SQL Server MVP]" wrote:

> Yes, for example, CREATE PROCEDURE must be the first statement in a batch.
> Various people deal with this kind of situation in various ways. The way
> Management Studio prepares scripts for multiple objects without GO, IIRC,
is
> that it wraps the CREATE PROCEDURE in dynamic SQL, e.g. EXEC('CREATE
> PROCEDURE dbo.whatever ...');
> A
>
>sql