Help with query - 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

Help with query

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-08 : 10:11:04

How do I add a if statement to this query... if mvt_loc is null then write not assigned

Here's the query:


Select p.area,  p.dist, p.doc, p.reg, p.cos, p.[type], convert(char,p.flg_cdt, 101) as flg_cdt, t.mvt_loc
FROM   Getinfo p
left join claims t on p.cos = t.cos
Where  type = '1' and doc = '194'order by cos

Here are the results. I want null to be listed as not assigned instead.


Area  dist  doc   reg   cos     type           flg_cdt               mvt_loc
01	219	194	C	269877	1	03/25/2010           	S66
01	219	194	C	569874	1	09/02/2010             	R48
01	219	194	C	632148	1	07/26/2010             	219
01	219	194	C	653897	1	09/27/2010             	NULL

Here's the table info:

CREATE TABLE [dbo].[GetInfo](
[Area] [nvarchar](2) NULL,
[dist] [char](3) NULL,
[doc] [varchar](4) NULL,
[reg] [char] (1) NULL,
[cos] [varchar](6) NULL,
[type] [varchar](1) NULL,
[flg_cdt][datetime] NULL,
[mvt_loc] [nvarchar](15) NULL
) ON [PRIMARY]

insert into GetInfo
select '01', '219', '194', 'C', '569874', '1', '9/02/2010', 'R48' union all
select '01', '219', '194', 'C', '569874', '1', '7/26/2010', '219' union all
select '01', '219', '194', 'C', '269877', '1', '3/25/2010', 'S66' union all
select '01', '219', '194', 'C', '653897', '1', '9/27/2010', 'NULL'

CREATE TABLE [dbo].[Claims](
[Cos] [varchar](6) NULL,
[mvt_typ] [nvarchar](1) NULL,
[mvt_cdt] [nvarchar](8) NULL,
[mvt_loc] [nvarchar] (4) NULL,
[mvt_dest] [nvarchar](4) NULL,
[clms] [varchar](6) NULL,
[Conv_mvt_Cdt][datetime] NULL,
) ON [PRIMARY]

insert into Claims
select '569874', 'R', '20110719', 'R48', '', '569874', '2011-07-19 00:00:00.000' union all
select '632148', 'R', '20110405', '219', '', '632148', '2011-04-05 00:00:00.000' union all
select '269877', 'R', '20110816', 'S66', '', '269877', '2011-08-16 00:00:00.000' union all
select '653897', 'R', '20110901', 'NULL', '', '653897', '2011-09-01 00:00:00.000'

Thanks!

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 10:15:02

[code]
Select p.area, p.dist, p.doc, p.reg, p.cos, p.[type], convert(char,p.flg_cdt, 101) as flg_cdt,
COALESCE(t.mvt_loc, 'not assigned') AS [mvt_loc]
FROM Getinfo p
left join claims t on p.cos = t.cos
Where type = '1' and doc = '194'order by cos
[/code]

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 10:17:28

"select '01', '219', '194', 'C', '653897', '1', '9/27/2010', 'NULL'"

Did you mean that NULL to be quoted? i.e. is it the string value "NULL" or a NULL value ?

If its the string value "NULL" you need:


COALESCE(NullIf(t.mvt_loc, 'NULL'), 'not assigned') AS [mvt_loc]


CASE WHEN t.mvt_loc = 'NULL' THEN 'not assigned' ELSE t.mvt_loc END AS [mvt_loc]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-08 : 10:49:53

Thanks Kristen. How would I add the case statement to this query?

Select p.area, p.dist, p.doc, p.reg, p.cos, p.[type], convert(char,p.flg_cdt, 101) as flg_cdt,
COALESCE(t.mvt_loc, 'not assigned') AS [mvt_loc]
FROM Getinfo p
left join claims t on p.cos = t.cos
Where type = '1' and doc = '194'order by cos

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-08 : 20:15:39

Hello is this correct of my understanding of coalesce...

COALESCE(t.mvt_loc, 'not assigned') AS [mvt_loc]

Coalesce is looking for NULL and in my case since it is NULL, it's being assigned "not assigned" to the column instead.

So using this is easier than a case statement huh. Can someone explain the difference to me a bit more please.

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-09 : 01:47:01

COALESCE is work fine with you.. you can also use..

ISNULL(t.mvt_loc, 'not assigned')

CASE WHEN t.mvt_loc IS NULL THEN 'not assigned' ELSE t.mvt_loc END

All will work in same way i think

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Kristen
Test

22859 Posts

Posted - 2011-09-09 : 02:10:04

"All will work in same way i think"

IsNull() has (in my opinion) undesireable behaviour when the datatypes of the arguments are different, so I prefer COALESCE()

COALESCE() will take more than 2 arguments. Given that at times I may use more than 2 arguments I prefer to be consistent and not mix the two, so I only use COALESCE()

COALESCE() should be faster than CASE statement, particularly if Argument1 is NOT NULL most of the time

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2011-09-09 : 02:29:56

Thanks for the follow-up Kristen. There is always new to learn in every post from you guys.

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-09-09 : 08:50:00

Thanks to you both!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources