If 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

If statement

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-04-22 : 14:56:06

I am still having a problem.

In my query I have a statement that says

IF 'TATDays' > '1' and 'TATDays' <= '3' as [D: Greater than 1 day to 3 Days]

End

I get an error messages that says

Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'IF'.
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'as'.

Am i getting that message because I have my TATdays field like this?

DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) as "TATDays"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 15:19:03

You cant use Alis name in if. use the full statement instead

IF DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) > '1' and DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) <= '3'

Also didnt get why you are using AS after if. If you are trying to conditionally return value for a field use CASE instead

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 15:19:20

Please post your full query as what you have shown doesn't make sense.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-04-22 : 15:25:04

Here is my full query. I am just basing this off of the person who created it a long time ago in Crystal reports. I am just trying to translate it to SQL.

SELECT

CONVERT(CHAR(10), clm_dout, 110) as "ProcessedDate",

CONVERT(CHAR(10), clm_rcvd, 110) as "ReceivedDate",

DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) as "TATDays" (this right here I need it to look like this....(

if 'TATDays' > .5 and 'TATDays' <= 1 as "C: 13 to 24 Hours"

iF 'TATDays' > '1' and 'TATDays' <= '3' as [D: Greater than 1 day to 3 Days]

if 'TATDays' > 3 and 'TATDays' <= 5 as "E: Greater than 3 days to 5 Days"

if 'TATDays' > 5 and 'TATDays' <= 10 as "F: Greater than 5 days to 10 Days"

if 'TATDays' > 10 as "G: Greater than 10 Days"

End

DATEDIFF(n,clm_rcvd,clm_dout) /60 AS Hours,

DATEDIFF(n,clm_rcvd,clm_dout) %60 AS Minutes,

clm_dout as "ProcessFullDateTime",
clm_rcvd as "ReceivedFullDateTime",

CASE clm_ips
WHEN 'C' THEN clm_pclm
ELSE clm_id1
END as "claimnumber1", --ClmClaims."CLAIMNUMBER",
CLM_PCLM as "CLAIMNUMBER",
--'' as DuplicateClaimNumber,
CLM_STADES as "CLAIMSTATUS",
CLM_6A as "STATEMENTFROMDATE",
CLM_6B as "STATEMENTTODATE",
clm_adjto,
clm_adjfm,
clm_adjsc,
CLM_65A as "EMPLOYERNAME",
CLM_1A as "PROVIDERGROUPNAME",
CLM_1 as "PROVIDERID",
mem_altid as "MEMBERNUMBER",
clm_12a as "LASTNAME",
cli_lev1 + '-' + cli_lev2 + '-' + cli_lev3 as CLIENTNUMBER, --Cli_1, Cli_2, Cli_2 as "CLIENTNUMBER"
CLM_12A as "PATIENTLASTNAME",
CLM_12B as "PATIENTFIRSTNAME",
Cli_id1 as "clientID",
cli_altid

/**
CLM_65A as "EMPLOYERGROUPNAME",
Cli_name as "CLIENTNAME",
CLM_5 as "PROVIDERTAXIDNUMBER",
**/

FROM dbo.clm
--inner join dbo.clms on dbo.clm.clm_id1 = dbo.clms.clms_id
inner join dbo.pro
on dbo.clm.clm_1 = dbo.pro.pro_id1
inner join dbo.mem
on dbo.clm.clm_38 = dbo.mem.mem_id1
inner join dbo.cli
on dbo.clm.clm_clir = dbo.cli.cli_id1

WHERE
clm_adjto = ' ' And
cli_altid IN ('Trust', 'ACEC', 'TMK', 'star','trusteesspecial', 'trusteessindemnity', 'trustees ppo', 'corestar')

quote:
Originally posted by tkizer

Please post your full query as what you have shown doesn't make sense.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 15:28:52

quote:
Originally posted by werhardt

Here is my full query. I am just basing this off of the person who created it a long time ago in Crystal reports. I am just trying to translate it to SQL.

SELECT

CONVERT(CHAR(10), clm_dout, 110) as "ProcessedDate",

CONVERT(CHAR(10), clm_rcvd, 110) as "ReceivedDate",

DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) as "TATDays" (this right here I need it to look like this....(

if 'TATDays' > .5 and 'TATDays' <= 1 as "C: 13 to 24 Hours"

iF 'TATDays' > '1' and 'TATDays' <= '3' as [D: Greater than 1 day to 3 Days]

if 'TATDays' > 3 and 'TATDays' <= 5 as "E: Greater than 3 days to 5 Days"

if 'TATDays' > 5 and 'TATDays' <= 10 as "F: Greater than 5 days to 10 Days"

if 'TATDays' > 10 as "G: Greater than 10 Days"

End

DATEDIFF(n,clm_rcvd,clm_dout) /60 AS Hours,

DATEDIFF(n,clm_rcvd,clm_dout) %60 AS Minutes,

clm_dout as "ProcessFullDateTime",
clm_rcvd as "ReceivedFullDateTime",

CASE clm_ips
WHEN 'C' THEN clm_pclm
ELSE clm_id1
END as "claimnumber1", --ClmClaims."CLAIMNUMBER",
CLM_PCLM as "CLAIMNUMBER",
--'' as DuplicateClaimNumber,
CLM_STADES as "CLAIMSTATUS",
CLM_6A as "STATEMENTFROMDATE",
CLM_6B as "STATEMENTTODATE",
clm_adjto,
clm_adjfm,
clm_adjsc,
CLM_65A as "EMPLOYERNAME",
CLM_1A as "PROVIDERGROUPNAME",
CLM_1 as "PROVIDERID",
mem_altid as "MEMBERNUMBER",
clm_12a as "LASTNAME",
cli_lev1 + '-' + cli_lev2 + '-' + cli_lev3 as CLIENTNUMBER, --Cli_1, Cli_2, Cli_2 as "CLIENTNUMBER"
CLM_12A as "PATIENTLASTNAME",
CLM_12B as "PATIENTFIRSTNAME",
Cli_id1 as "clientID",
cli_altid

/**
CLM_65A as "EMPLOYERGROUPNAME",
Cli_name as "CLIENTNAME",
CLM_5 as "PROVIDERTAXIDNUMBER",
**/

FROM dbo.clm
--inner join dbo.clms on dbo.clm.clm_id1 = dbo.clms.clms_id
inner join dbo.pro
on dbo.clm.clm_1 = dbo.pro.pro_id1
inner join dbo.mem
on dbo.clm.clm_38 = dbo.mem.mem_id1
inner join dbo.cli
on dbo.clm.clm_clir = dbo.cli.cli_id1

WHERE
clm_adjto = ' ' And
cli_altid IN ('Trust', 'ACEC', 'TMK', 'star','trusteesspecial', 'trusteessindemnity', 'trustees ppo', 'corestar')

quote:
Originally posted by tkizer

Please post your full query as what you have shown doesn't make sense.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

As suspected i think you need a case construct here

modify it like this;-

CASE
WHEN DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) > .5 and DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) <= 1 THEN 'C: 13 to 24 Hours'

WHEN DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) > '1' and DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) <= '3' THEN 'D: Greater than 1 day to 3 Days'

...

End

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-22 : 15:32:50

werhardt,

You can't use IF in the middle of a query. You must use a CASE instead. You can use IF statements outside of queries like this:

IF @var1 = 0
Do Something

IF EXISTS (SELECT * FROM Table1 WHERE Column1 = 1)
Do Something

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2008-04-22 : 15:38:23

Thank you visakh16 it worked.

quote:
Originally posted by visakh16

quote:
Originally posted by werhardt

Here is my full query. I am just basing this off of the person who created it a long time ago in Crystal reports. I am just trying to translate it to SQL.

SELECT

CONVERT(CHAR(10), clm_dout, 110) as "ProcessedDate",

CONVERT(CHAR(10), clm_rcvd, 110) as "ReceivedDate",

DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) as "TATDays" (this right here I need it to look like this....(

if 'TATDays' > .5 and 'TATDays' <= 1 as "C: 13 to 24 Hours"

iF 'TATDays' > '1' and 'TATDays' <= '3' as [D: Greater than 1 day to 3 Days]

if 'TATDays' > 3 and 'TATDays' <= 5 as "E: Greater than 3 days to 5 Days"

if 'TATDays' > 5 and 'TATDays' <= 10 as "F: Greater than 5 days to 10 Days"

if 'TATDays' > 10 as "G: Greater than 10 Days"

End

DATEDIFF(n,clm_rcvd,clm_dout) /60 AS Hours,

DATEDIFF(n,clm_rcvd,clm_dout) %60 AS Minutes,

clm_dout as "ProcessFullDateTime",
clm_rcvd as "ReceivedFullDateTime",

CASE clm_ips
WHEN 'C' THEN clm_pclm
ELSE clm_id1
END as "claimnumber1", --ClmClaims."CLAIMNUMBER",
CLM_PCLM as "CLAIMNUMBER",
--'' as DuplicateClaimNumber,
CLM_STADES as "CLAIMSTATUS",
CLM_6A as "STATEMENTFROMDATE",
CLM_6B as "STATEMENTTODATE",
clm_adjto,
clm_adjfm,
clm_adjsc,
CLM_65A as "EMPLOYERNAME",
CLM_1A as "PROVIDERGROUPNAME",
CLM_1 as "PROVIDERID",
mem_altid as "MEMBERNUMBER",
clm_12a as "LASTNAME",
cli_lev1 + '-' + cli_lev2 + '-' + cli_lev3 as CLIENTNUMBER, --Cli_1, Cli_2, Cli_2 as "CLIENTNUMBER"
CLM_12A as "PATIENTLASTNAME",
CLM_12B as "PATIENTFIRSTNAME",
Cli_id1 as "clientID",
cli_altid

/**
CLM_65A as "EMPLOYERGROUPNAME",
Cli_name as "CLIENTNAME",
CLM_5 as "PROVIDERTAXIDNUMBER",
**/

FROM dbo.clm
--inner join dbo.clms on dbo.clm.clm_id1 = dbo.clms.clms_id
inner join dbo.pro
on dbo.clm.clm_1 = dbo.pro.pro_id1
inner join dbo.mem
on dbo.clm.clm_38 = dbo.mem.mem_id1
inner join dbo.cli
on dbo.clm.clm_clir = dbo.cli.cli_id1

WHERE
clm_adjto = ' ' And
cli_altid IN ('Trust', 'ACEC', 'TMK', 'star','trusteesspecial', 'trusteessindemnity', 'trustees ppo', 'corestar')

quote:
Originally posted by tkizer

Please post your full query as what you have shown doesn't make sense.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

As suspected i think you need a case construct here

modify it like this;-

CASE
WHEN DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) > .5 and DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) <= 1 THEN 'C: 13 to 24 Hours'

WHEN DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) > '1' and DATEDIFF(day, CONVERT(CHAR(10), clm_rcvd, 110), CONVERT(CHAR(10), clm_dout, 110)) <= '3' THEN 'D: Greater than 1 day to 3 Days'

...

End

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources