Replacing NULLS

Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

JeffreyI see a cursor in your future (ducks and hides)...

Seriously, the only way the I know of to transpose one row of data onto another in the type of scenario you describe is to do it with a cursor:

-- Declare Local variables (one for each column, plus an
-- additional variable for each column to store the previous value)
DECLARE
@.TimeStamp datetime,
@.System201 int,
@.System500 int,
@.System345 int,
...
@.System201_Old int,
@.System500_Old int,
@.System345_Old int,
...
@.Counter int

-- Declare the cursor
DECLARE rolling_cursor CURSOR
FROM SELECT * FROM TABLE1 ORDER BY TimeStamp

-- Open the cursor
OPEN rolling_cursor

-- Set an initial counter
Set @.Counter = 1

-- Fetch the first row of values into the local variables
FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345

-- WHILE your way through the table, updating the next row
-- with the previous rows data when the next row's columns
-- are null (see COALESCE)
WHILE fetch_status <> -1
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
IF @.Counter = 1
BEGIN
@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
END
ELSE
BEGIN
UPDATE TABLE1 SET
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
@.System201 = COALESCE(@.System201, @.System201_Old),
...
WHERE TimeStamp = @.TimeStamp

@.System201_Old = @.System_201
@.System500_Old = @.System_500
@.System345_Old = @.System_345
@.Counter = @.Counter + 1
END

FETCH NEXT FROM rolling_cursor INTO
@.TimeStamp,
@.System201,
@.System500,
@.System345
END

-- Close and deallocate the cursor
CLOSE rolling_cursor
DEALLOCATE rolling_cursor

-- Print a message to the user to show how many records were updated
Print @.Counter + ' records updated.'

Notes:
1. Not tested
2. Not guaranteed
3. Mileage may vary
4. Please don't sue me
5. Don't sic the DBA gods on me for suggesting a cursor
6. I am assuming that the PK on this table is TimeStamp (and that Timestamp is sequential). This solution does not work otherwise.

Best of luck,

Hugh Scott
Originally posted by JeffreyELewis
Table1 Definition
[timestamp], datetime
[System201], int
[System500], int
[System345], int
... (the number of [System###] fields will be different for each facility I deploy this in)

Current Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 NULL NULL
1/3/03 NULL 5 8

I would like to replace the NULL's with the value from the previous record. Is there a quick and dirty way to do this?

Future Table1 Data
[timestamp] [System201] [System500] [System345] ...
1/1/03 1 2 4
1/2/02 3 2 4
1/3/03 3 5 8

Jeffrey|||Jeez, i spend all that time formatting the script with spaces and everything (to show nesting) and it comes out looking out looking like that!?!?!

First, I humbly apologize if it is difficult to read through. Second, it anyone knows how to make spaces or tabs work on this forum, I would be grateful.

Regards,

Hugh Scott|||keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||I appreciate your amendments. You're right on the comment about timestamps -- I missed that in the original statement of requirements.

Oh well, back to the drawing board!!!

Regards,

Hugh Scott

Originally posted by Paul Young
keeping my comments on cursors to myself...

edit your post and put a code at the beggining and a /code at the end like using bold, that should do it.

Also I think your logic may be flawed, your cursor sorts by time stamp and the examples don't reflect that.|||1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||I assumed that the PK was Timestamp. I inferred that the poster made an error and meant to put /03 instead of /02. I know what happens when you assume...

Kindest regards,

Hugh Scott

Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?|||Your right. That was a typo. The year should have been 03 instead of 02 and the PK is the [timestamp] field.|||it looks like hmscott provided the answer. Post back and let us know if it worked.|||Now, when your information is clear, I can post my solution.

/*
Script drops Table1, uses transaction to support 2 solutions in one script
*/

--INIT
drop table Table1
GO
create table Table1(
[timestamp] datetime primary key clustered
,[System201] int null
,[System500] int null
,[System345] int null
)
GO
set dateformat MDY
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/1/03', 1, 2, 4)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/2/03', 3,NULL,NULL)
insert Table1( [timestamp],[System201],[System500],[System345] ) values ('1/3/03',NULL, 5, 8)
GO

begin tran
select * from Table1

--SOLUTION WITHOUT CURSOR OR PSEDOCURSOR
/*
Recomended use of clustered PK on [timestamp] and separate index on each (System...) column for large table
*/
update t1 set
t1.[System201]=
(
select t2.[System201]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System201] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System201] is null
update t1 set
t1.[System500]=
(
select t2.[System500]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System500] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System500] is null
update t1 set
t1.[System345]=
(
select t2.[System345]
from Table1 t2
join
(
select [timestamp]=max(t3.[timestamp])
from Table1 t3
where (t1.[timestamp]>=t3.[timestamp]) and (t3.[System345] is not null)
) x on t2.[timestamp]=x.[timestamp]
)
from Table1 t1
where t1.[System345] is null

select * from Table1
rollback tran
begin tran
select * from Table1

--PSEDOCURSOR SOLUTION
declare @.timestamp datetime
declare @.System201 int
declare @.System500 int
declare @.System345 int
declare @.timestampPre datetime
declare @.System201Pre int
declare @.System500Pre int
declare @.System345Pre int
set nocount on
select @.timestampPre=[timestamp],@.System201Pre=[System201],@.System500Pre=[System500],@.System345Pre=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2)
while 1=1 begin
select @.timestamp=[timestamp],@.System201=[System201],@.System500=[System500],@.System345=[System345] from Table1 t1
where t1.[timestamp]=(select min(t2.[timestamp]) from Table1 t2 where t2.[timestamp]>@.timestampPre)
if @.@.rowcount=0 break
set @.System201Pre=isnull(@.System201,@.System201Pre)
set @.System500Pre=isnull(@.System500,@.System500Pre)
set @.System345Pre=isnull(@.System345,@.System345Pre)
if @.System201 is null or @.System500 is null or @.System345 is null
update Table1 set
[System201]=@.System201Pre
,[System500]=@.System500Pre
,[System345]=@.System345Pre
where [timestamp]=@.timestamp
set @.timestampPre=@.timestamp
end
set nocount off

select * from Table1
rollback tran|||Forgive me for being simple but there is a quick and dirty way to update previous record with data from next record.

Select *, identity(int ,1,1) as seq
into #temp
from "your table"
order by timestamp

update t1
set t1.System201 = t2.System201
from #temp t1 inner join #temp t2 on
t1.seq = t2.seq - 1|||Yes, I call that algorithm "ordered streams". But I was trying to solve
situation like this

1 1
2 NULL
3 NULL
->
1 1
2 1
3 1

by query without cursor.|||Why not use a subquery?

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld )

I'm suppose it isn't the finnest but I'm shure it's cheaper than cursors, no?|||Cesar, I do not uderstand what you mean by your code.

It can be solved by single quary

select t.keyfld,t.valfld,t1.keyfld,t1.valfld
from tblnam t
join tblnam t1 on t1.keyfld=(select max(t2.keyfld) from tblnam t2 where t2.keyfld<=t.keyfld and t2.valfld is not null)

But this query is not scalable (N^3), cursor is better algorithm.|||I don't know exactly how to calculate the difference between subqueries and joins, focusing in througput, because I used to work only with lower than 100,000 records tables, so my point is I agree about cursor structures and algorithms in theoretical terms, but not in speed, at least in MSSQL7 implementation.
So I'm always preffer to complicate my scripts with more sophisticated selects/joins/temporary tables/indexes, etc...
The only drawback I've found is maintenance, because almost any VB programmer can deal with cursors but complex SQL statements.
So in my record's range its a question of maintenance/performance for me and I've choseen performance until now, against cursors.
What do you think?|||I already compared cursor and noncursor solutions at this forum.
Problem solvable without cursor can be faster without cursor always.

1. I just say, that single query solution of this problem is slower than simple cursor solution.
2. I do not understand your code

select t.fldnam
from tblnam t
where t.valfld is not null
and t.valfld < ( select t1.valfld
from tblnam t1
where t1.keyfld = t.keyfld
)|||Originally posted by ispaleny
1/1/03 1/2/02 1/3/03

Q1 What PK is on Table1?
Q2 How do you know the record is previous?

I just tried to insert a non-cursor sample about finding previous record.

replacing nulls

Using the DTS ive successfully transfered data from access to sql - however
i need to replace the <nulls>
with '' - is there an easy way to do this ?
thanks
mark
UPDATE SomeTable
SET col1 = COALESCE(col1, ''),
col2 = COALESCE(col2, ''),
...
WHERE col1 IS NULL
OR col2 IS NULL
...
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IfednUh_ya-M_ALdRVn-gQ@.giganews.com...
> UPDATE SomeTable
> SET col1 = COALESCE(col1, ''),
> col2 = COALESCE(col2, ''),
> ...
> WHERE col1 IS NULL
> OR col2 IS NULL
> ...
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the help!
mark
sql

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||Not sure how many height x width x depth combinations are possible or
acceptable, however one try may be:
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+
COALESCE
(
@.height+'x'+@.width+'x'+@.depth,
@.height+'x'+@.width,
''
)
+' '+ @.measuretype+'
'+COALESCE('Editions: ' +@.edition, '') + ...
Or better yet, just return the data to the client/presentation tier and let
it handle NULLs and formatting.|||thanks but with '+COALESCE('Editions: ' +@.edition, '') since
'Editions' is hard coded even if @.edition is null
i get the Editions in the output
how do i make that null if @.edition is null as well|||My guess is that @.edition is '' which is not the same as NULL.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147881891.316429.181680@.y43g2000cwc.googlegroups.com...
> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>|||Actually no. A NULL value concatenated with any string will result in NULL.
So 'Editions: ' + NULL will yield NULL, instead of 'Editions: '.
The following example shows the result:
declare @.edition varchar(30)
set @.edition = null
select COALESCE('Editions: ' +@.edition, '') -- returns empty string ''
set @.edition = 'foo'
select COALESCE('Editions: ' +@.edition, '') -- returns "Editions: foo"
"VJ" wrote:

> thanks but with '+COALESCE('Editions: ' +@.edition, '') since
> 'Editions' is hard coded even if @.edition is null
> i get the Editions in the output
> how do i make that null if @.edition is null as well
>

Replacing Nulls

I have a query that has serveral Left Joins. Obviously when there are no
results in the right tables a value of NULL is returned. When creating an
MS Reporting Services report, the tables seem to ignore the NULL values
(because they aren't recognized as real values I assume). How do I alter my
query to replace a NULL value with an actually value, such as 999 so I can
count the "NULL" results?
I appreciate any help.
Thankstry ISNULL function
ISNULL(<fieldname>, 999) AS SomeThing
"Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>I have a query that has serveral Left Joins. Obviously when there are no
>results in the right tables a value of NULL is returned. When creating an
>MS Reporting Services report, the tables seem to ignore the NULL values
>(because they aren't recognized as real values I assume). How do I alter
>my query to replace a NULL value with an actually value, such as 999 so I
>can count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Thanks for the reply SharkSpeed. That makes sense, but where would I put
it?
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:eqqHn8e9FHA.1020@.TK2MSFTNGP15.phx.gbl...
> try ISNULL function
> ISNULL(<fieldname>, 999) AS SomeThing
>
> "Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
> yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>|||Hi Amon
Have you tried the ISNULL([ColumnName], [NewValue]) function?
Lucas
"Amon Borland" wrote:

> I have a query that has serveral Left Joins. Obviously when there are no
> results in the right tables a value of NULL is returned. When creating an
> MS Reporting Services report, the tables seem to ignore the NULL values
> (because they aren't recognized as real values I assume). How do I alter
my
> query to replace a NULL value with an actually value, such as 999 so I can
> count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Lucas, where would I use this at. In the Select or after the table in the
join?
Thanks
"Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote in
message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
> Hi Amon
> Have you tried the ISNULL([ColumnName], [NewValue]) function?
> Lucas
> "Amon Borland" wrote:
>|||in the Select I assume..
SELECT a, b, n FROM table LEFT JOIN SELECT x, y, z FROM table2 ON ...
becomes
SELECT a, b, ISNULL(n, 999) AS n FROM table LEFT JOIN SELECT x, y, ISNULL(z,
999) FROM table2 ...
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:eZMs3Mf9FHA.220@.TK2MSFTNGP14.phx.gbl...
> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
> in message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>|||You can use it on the Select part of your SQL Statement. For example:
SELECT [ColumnName1], ISNULL([ColumnName2], [NewValue])
FROM [TableName]
Lucas
"Amon Borland" wrote:

> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
in
> message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>
>

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Try this:
SET CONTACT_NULL_YIELDS_NULL ON
ISNULL(@.name + ', ') + ISNULL(@.work_name + ', ') etc
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147878293.952880.53040@.j33g2000cwa.googlegroups.com...
>I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||You can use CASE statements for something like that:
SELECT @.feed5 = CASE WHEN @.width IS NULL THEN ' ' ELSE 'x' + @.width + 'x'
END +
CASE WHEN @.edition IS NULL THEN ' ' ELSE 'Edition: ' + @.edition + ',' END
Or you can use COALESCE:
SELECT @.feed5 = COALESCE('x' + @.width + 'x', ' ') +
COALESCE('Edition: ' + @.edition + ',', ' ')
COALESCE will work since NULL plus anything is NULL. So 'x' + @.width + 'x'
where @.width is NULL, returns NULL.
"VJ" wrote:

> I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||thanks michael but this does not works for COALESCE('Edition: ' +
@.edition + ',', ' ') is @.edition is null it still selects the Edition:
how do i get rid of the hardcoded 'Edition:'|||Apparently you do not have SET CONCAT_NULL_YIELDS_NULL ON, which is the
ANSI-defined behavior for NULL concatenation. So you can either SET
CONCAT_NULL_YIELDS_NULL ON or you can use the CASE statement provided in
addition to the COALESCE example. I would recommend turning the ANSI
Standard-defined behavior back ON, but it's your choice.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147882316.278165.220540@.y43g2000cwc.googlegroups.com...
> thanks michael but this does not works for COALESCE('Edition: ' +
> @.edition + ',', ' ') is @.edition is null it still selects the Edition:
> how do i get rid of the hardcoded 'Edition:'
>

replacing nulls

Using the DTS ive successfully transfered data from access to sql - however
i need to replace the <nulls>
with '' - is there an easy way to do this ?
thanks
markUPDATE SomeTable
SET col1 = COALESCE(col1, ''),
col2 = COALESCE(col2, ''),
..
WHERE col1 IS NULL
OR col2 IS NULL
..
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IfednUh_ya-M_ALdRVn-gQ@.giganews.com...
> UPDATE SomeTable
> SET col1 = COALESCE(col1, ''),
> col2 = COALESCE(col2, ''),
> ...
> WHERE col1 IS NULL
> OR col2 IS NULL
> ...
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the help!
mark

replacing nulls

Using the DTS ive successfully transfered data from access to sql - however
i need to replace the <nulls>
with '' - is there an easy way to do this ?
thanks
markUPDATE SomeTable
SET col1 = COALESCE(col1, ''),
col2 = COALESCE(col2, ''),
...
WHERE col1 IS NULL
OR col2 IS NULL
...
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:IfednUh_ya-M_ALdRVn-gQ@.giganews.com...
> UPDATE SomeTable
> SET col1 = COALESCE(col1, ''),
> col2 = COALESCE(col2, ''),
> ...
> WHERE col1 IS NULL
> OR col2 IS NULL
> ...
> --
> David Portas
> SQL Server MVP
> --
>
thanks for the help!
marksql

replacing nulls

I have a string like this
select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
'')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
'')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
'+'Editions: '+' ' +@.edition+.+
Here some of the values will be null. Say width is null so my result
would be 10 x x 12
or if edition is null i will get in the result Editions: .
if the @.workedyearto is null then i will get 1980, ,
height.........
how do i write a string to replace the , or x or editions: with '
'(space).Try this:
SET CONTACT_NULL_YIELDS_NULL ON
ISNULL(@.name + ', ') + ISNULL(@.work_name + ', ') etc
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147878293.952880.53040@.j33g2000cwa.googlegroups.com...
>I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||You can use CASE statements for something like that:
SELECT @.feed5 = CASE WHEN @.width IS NULL THEN ' ' ELSE 'x' + @.width + 'x'
END +
CASE WHEN @.edition IS NULL THEN ' ' ELSE 'Edition: ' + @.edition + ',' END
Or you can use COALESCE:
SELECT @.feed5 = COALESCE('x' + @.width + 'x', ' ') +
COALESCE('Edition: ' + @.edition + ',', ' ')
COALESCE will work since NULL plus anything is NULL. So 'x' + @.width + 'x'
where @.width is NULL, returns NULL.
"VJ" wrote:
> I have a string like this
>
> select @.feed5 = @.name+', '+@.work_name +', '+isnull( @.workedyearfrom,
> '')+' '+ isnull(@.workedyearto, '')+', '+ isnull(@.height,
> '')+'x'+isnull(@.width, '')+'x'+isnull(@.depth, '')+' '+ @.measuretype+'
> '+'Editions: '+' ' +@.edition+.+
>
> Here some of the values will be null. Say width is null so my result
> would be 10 x x 12
> or if edition is null i will get in the result Editions: .
> if the @.workedyearto is null then i will get 1980, ,
> height.........
>
> how do i write a string to replace the , or x or editions: with '
> '(space).
>|||thanks michael but this does not works for COALESCE('Edition: ' +
@.edition + ',', ' ') is @.edition is null it still selects the Edition:
how do i get rid of the hardcoded 'Edition:'|||Apparently you do not have SET CONCAT_NULL_YIELDS_NULL ON, which is the
ANSI-defined behavior for NULL concatenation. So you can either SET
CONCAT_NULL_YIELDS_NULL ON or you can use the CASE statement provided in
addition to the COALESCE example. I would recommend turning the ANSI
Standard-defined behavior back ON, but it's your choice.
"VJ" <vishal.sql@.gmail.com> wrote in message
news:1147882316.278165.220540@.y43g2000cwc.googlegroups.com...
> thanks michael but this does not works for COALESCE('Edition: ' +
> @.edition + ',', ' ') is @.edition is null it still selects the Edition:
> how do i get rid of the hardcoded 'Edition:'
>

Replace zeros and nulls with 1 in table -- Using case, but not working

Hi folks,

I'm doing calculations based on data in a table, but the data has some
zeros in the field I'm dividing by. I'm trying to write a script to
replace any field with 0 or null with 1, but it's not working. HEre's
what I've got:

Update A Set A.deptcode = A.deptcode,
A.type = A.Type,
A.Volume = (case A.Volume
When Null Then 1
When 0 then 1
Else A.Volume
End)
From Data_Unsorted A Join Data_Unsorted B On
A.deptcode = B.deptcode and A.type = B.Type

My table is data_unsorted and deptcode and type are my primary keys
Volume is the item I want to put 1 if null or zero, and I'd thing the
above statement would work, but it doesn't. This table has 383 rows,
and it says it updates 383 rows, but when I run the following query to
test:

select a.deptcode, a.type, a.volume
from data_unsorted a
where a.AveMonthVolume = 0 or a.AveMonthVOlume is null

It didn't work... still TONS of nulls and zero's. Is there a trick to
this?

Thanks,

Alex.Alex,

Try this:

update YourTable
set Col = 1
where Col = 0 or Col is null

Shervin

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310091122.fc83cd5@.posting.google.co m...
> Hi folks,
> I'm doing calculations based on data in a table, but the data has some
> zeros in the field I'm dividing by. I'm trying to write a script to
> replace any field with 0 or null with 1, but it's not working. HEre's
> what I've got:
> Update A Set A.deptcode = A.deptcode,
> A.type = A.Type,
> A.Volume = (case A.Volume
> When Null Then 1
> When 0 then 1
> Else A.Volume
> End)
> From Data_Unsorted A Join Data_Unsorted B On
> A.deptcode = B.deptcode and A.type = B.Type
> My table is data_unsorted and deptcode and type are my primary keys
> Volume is the item I want to put 1 if null or zero, and I'd thing the
> above statement would work, but it doesn't. This table has 383 rows,
> and it says it updates 383 rows, but when I run the following query to
> test:
> select a.deptcode, a.type, a.volume
> from data_unsorted a
> where a.AveMonthVolume = 0 or a.AveMonthVOlume is null
> It didn't work... still TONS of nulls and zero's. Is there a trick to
> this?
> Thanks,
> Alex.|||Alex (alex@.totallynerd.com) writes:
> Update A Set A.deptcode = A.deptcode,
> A.type = A.Type,
> A.Volume = (case A.Volume
> When Null Then 1
> When 0 then 1
> Else A.Volume
> End)
> From Data_Unsorted A Join Data_Unsorted B On
> A.deptcode = B.deptcode and A.type = B.Type

You compare A.Volume to NULL, but NULL is never equal to NULL or
anything else. Write the CASE expresssion as.

CASE WHEN volume IS NULL THEN 1
WHEN volume = 0 THEN 1
ELSE volume
END

or

CASE coalesce(volume, 0) WHEN 0 THEN 1 ELSE volume END

The coalesce function returns the first non-NULL value in the list.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

REPLACE NULLS WITH A SELECT STATEMENT (maybe)

I need to create a view to support a report requirement. I need the
returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
will be available no matter what vendor the end user filters on. To do
this I have to populate the flight info in the rows that are non airline
vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
the DAN KNOWLES TOUR rows, etc. How can I do this?
I have provided the below info to help you test. I am using SQL Server
2000.
vu_BAS_SAIR
RESERVATIONIDnumeric9
SEGMENTINDEXsmallint
AIRLINECODEvarchar4
FLIGHTNUMvarchar16
DEPARTAIRPORTvarchar4
vu_BAS_SEGMENT
RESERVATIONIDnumeric9
SEGMENTINDEXsmallint2
VENDORNAMEvarchar64
SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
dbo.vu_BAS_SAIR.AIRLINECODE,
dbo.vu_BAS_SAIR.FLIGHTNUM
FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
dbo.vu_BAS_SEGMENT ON
dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
dbo.vu_BAS_SAIR.SEGMENTINDEX =
dbo.vu_BAS_SEGMENT.SEGMENTINDEX
WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
RESERVATIONIDSEGMENTINDEXVENDORNAMEAIRLINECODEFLIGHTNUM
258231Delta Air LinesDL996
258231Delta Air LinesDL996
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582311Dan Knowles Tours
2582311Dan Knowles Tours
2582312Dan Knowles Tours
2582312Dan Knowles Tours
2582313Atlantis, Paradise Island
2582314Atlantis, Paradise Island
2582315Seahorse Sailing Adventures
2582316Neptunes Water Toys
2582317Nassau Cruises Limited
2582318Document Delivery
2582319Trip Mate Insurance Inc.
2582320Package Booking
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582321Atlantis, Coral Towers
2582322Dan Knowles Tours
2582322Dan Knowles Tours
2582322Dan Knowles Tours
2582323Dan Knowles Tours
2582323Dan Knowles Tours
2582323Dan Knowles Tours
2582324Atlantis, Paradise Island
2582325Atlantis, Paradise Island
2582326Seahorse Sailing Adventures
258231Delta Air LinesDL996
258231Delta Air LinesDL996
258232Delta Air LinesDL427
2582327Neptunes Water Toys
2582328Nassau Cruises Limited
2582329Document Delivery
2582330Trip Mate Insurance Inc.
258233Delta Air LinesDL928
258234Delta Air LinesDL302
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258235Delta Air LinesDL996
258236Delta Air LinesDL427
258237Delta Air LinesDL928
258238Delta Air LinesDL302
258239Package Booking
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
2582310Atlantis, Coral Towers
Michael Hardy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
Without seeing what your data is going to be deaulted to its a bit had to
give exact code however you should probably have a look at the COALESCE
command
Given the following schema
CREATE TABLE [dbo].[Tester] (
[Part] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PartLink] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
With the following data
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, NULL
'Part4', 4, NULL
The following statement
SELECT Part, ID, COALESCE (PartLink,
(SELECT PartLink
FROM Tester
WHERE ID = 1)) AS PartLink
FROM dbo.Tester
will give
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, 45
'Part4', 4, 34
Anyway have a look at BOL and see if it helps.
"I favor the Civil Rights Act of 1964 and it must be enforced at gunpoint if
necessary."
Ronald Reagan
"Michael Hardy" wrote:

> I need to create a view to support a report requirement. I need the
> returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
> will be available no matter what vendor the end user filters on. To do
> this I have to populate the flight info in the rows that are non airline
> vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
> the DAN KNOWLES TOUR rows, etc. How can I do this?
> I have provided the below info to help you test. I am using SQL Server
> 2000.
> vu_BAS_SAIR
> RESERVATIONIDnumeric9
> SEGMENTINDEXsmallint
> AIRLINECODEvarchar4
> FLIGHTNUMvarchar16
> DEPARTAIRPORTvarchar4
> vu_BAS_SEGMENT
> RESERVATIONIDnumeric9
> SEGMENTINDEXsmallint2
> VENDORNAMEvarchar64
> SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
> dbo.vu_BAS_SAIR.AIRLINECODE,
> dbo.vu_BAS_SAIR.FLIGHTNUM
> FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
> dbo.vu_BAS_SEGMENT ON
> dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
> dbo.vu_BAS_SAIR.SEGMENTINDEX =
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX
> WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
>
> RESERVATIONIDSEGMENTINDEXVENDORNAMEAIRLINECODEFLIGHTNUM
> 258231Delta Air LinesDL996
> 258231Delta Air LinesDL996
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582311Dan Knowles Tours
> 2582311Dan Knowles Tours
> 2582312Dan Knowles Tours
> 2582312Dan Knowles Tours
> 2582313Atlantis, Paradise Island
> 2582314Atlantis, Paradise Island
> 2582315Seahorse Sailing Adventures
> 2582316Neptunes Water Toys
> 2582317Nassau Cruises Limited
> 2582318Document Delivery
> 2582319Trip Mate Insurance Inc.
> 2582320Package Booking
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582321Atlantis, Coral Towers
> 2582322Dan Knowles Tours
> 2582322Dan Knowles Tours
> 2582322Dan Knowles Tours
> 2582323Dan Knowles Tours
> 2582323Dan Knowles Tours
> 2582323Dan Knowles Tours
> 2582324Atlantis, Paradise Island
> 2582325Atlantis, Paradise Island
> 2582326Seahorse Sailing Adventures
> 258231Delta Air LinesDL996
> 258231Delta Air LinesDL996
> 258232Delta Air LinesDL427
> 2582327Neptunes Water Toys
> 2582328Nassau Cruises Limited
> 2582329Document Delivery
> 2582330Trip Mate Insurance Inc.
> 258233Delta Air LinesDL928
> 258234Delta Air LinesDL302
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258235Delta Air LinesDL996
> 258236Delta Air LinesDL427
> 258237Delta Air LinesDL928
> 258238Delta Air LinesDL302
> 258239Package Booking
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
> 2582310Atlantis, Coral Towers
>
> Michael Hardy
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
sql

REPLACE NULLS WITH A SELECT STATEMENT (maybe)

I need to create a view to support a report requirement. I need the
returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
will be available no matter what vendor the end user filters on. To do
this I have to populate the flight info in the rows that are non airline
vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
the DAN KNOWLES TOUR rows, etc. How can I do this?
I have provided the below info to help you test. I am using SQL Server
2000.
vu_BAS_SAIR
RESERVATIONID numeric 9
SEGMENTINDEX smallint
AIRLINECODE varchar 4
FLIGHTNUM varchar 16
DEPARTAIRPORT varchar 4
vu_BAS_SEGMENT
RESERVATIONID numeric 9
SEGMENTINDEX smallint 2
VENDORNAME varchar 64
SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
dbo.vu_BAS_SAIR.AIRLINECODE,
dbo.vu_BAS_SAIR.FLIGHTNUM
FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
dbo.vu_BAS_SEGMENT ON
dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
dbo.vu_BAS_SAIR.SEGMENTINDEX =
dbo.vu_BAS_SEGMENT.SEGMENTINDEX
WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
RESERVATIONID SEGMENTINDEX VENDORNAME AI
RLINECODE FLIGHTNUM
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 11 Dan Knowles Tours
25823 11 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 13 Atlantis, Paradise Island
25823 14 Atlantis, Paradise Island
25823 15 Seahorse Sailing Adventures
25823 16 Neptunes Water Toys
25823 17 Nassau Cruises Limited
25823 18 Document Delivery
25823 19 Trip Mate Insurance Inc.
25823 20 Package Booking
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 24 Atlantis, Paradise Island
25823 25 Atlantis, Paradise Island
25823 26 Seahorse Sailing Adventures
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 2 Delta Air Lines DL 427
25823 27 Neptunes Water Toys
25823 28 Nassau Cruises Limited
25823 29 Document Delivery
25823 30 Trip Mate Insurance Inc.
25823 3 Delta Air Lines DL 928
25823 4 Delta Air Lines DL 302
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 6 Delta Air Lines DL 427
25823 7 Delta Air Lines DL 928
25823 8 Delta Air Lines DL 302
25823 9 Package Booking
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
Michael Hardy
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!Without seeing what your data is going to be deaulted to its a bit had to
give exact code however you should probably have a look at the COALESCE
command
Given the following schema
CREATE TABLE [dbo].[Tester] (
[Part] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PartLink] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
With the following data
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, NULL
'Part4', 4, NULL
The following statement
SELECT Part, ID, COALESCE (PartLink,
(SELECT PartLink
FROM Tester
WHERE ID = 1)) AS PartLink
FROM dbo.Tester
will give
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, 45
'Part4', 4, 34
Anyway have a look at BOL and see if it helps.
"I favor the Civil Rights Act of 1964 and it must be enforced at gunpoint if
necessary."
Ronald Reagan
"Michael Hardy" wrote:

> I need to create a view to support a report requirement. I need the
> returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
> will be available no matter what vendor the end user filters on. To do
> this I have to populate the flight info in the rows that are non airline
> vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
> the DAN KNOWLES TOUR rows, etc. How can I do this?
> I have provided the below info to help you test. I am using SQL Server
> 2000.
> vu_BAS_SAIR
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint
> AIRLINECODE varchar 4
> FLIGHTNUM varchar 16
> DEPARTAIRPORT varchar 4
> vu_BAS_SEGMENT
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint 2
> VENDORNAME varchar 64
> SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
> dbo.vu_BAS_SAIR.AIRLINECODE,
> dbo.vu_BAS_SAIR.FLIGHTNUM
> FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
> dbo.vu_BAS_SEGMENT ON
> dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
> dbo.vu_BAS_SAIR.SEGMENTINDEX =
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX
> WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
>
> RESERVATIONID SEGMENTINDEX VENDORNAME AI
RLINECODE FLIGHTNUM
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 11 Dan Knowles Tours
> 25823 11 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 13 Atlantis, Paradise Island
> 25823 14 Atlantis, Paradise Island
> 25823 15 Seahorse Sailing Adventures
> 25823 16 Neptunes Water Toys
> 25823 17 Nassau Cruises Limited
> 25823 18 Document Delivery
> 25823 19 Trip Mate Insurance Inc.
> 25823 20 Package Booking
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 24 Atlantis, Paradise Island
> 25823 25 Atlantis, Paradise Island
> 25823 26 Seahorse Sailing Adventures
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 2 Delta Air Lines DL 427
> 25823 27 Neptunes Water Toys
> 25823 28 Nassau Cruises Limited
> 25823 29 Document Delivery
> 25823 30 Trip Mate Insurance Inc.
> 25823 3 Delta Air Lines DL 928
> 25823 4 Delta Air Lines DL 302
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 6 Delta Air Lines DL 427
> 25823 7 Delta Air Lines DL 928
> 25823 8 Delta Air Lines DL 302
> 25823 9 Package Booking
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
>
> Michael Hardy
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>

REPLACE NULLS WITH A SELECT STATEMENT (maybe)

I need to create a view to support a report requirement. I need the
returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
will be available no matter what vendor the end user filters on. To do
this I have to populate the flight info in the rows that are non airline
vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
the DAN KNOWLES TOUR rows, etc. How can I do this?
I have provided the below info to help you test. I am using SQL Server
2000.
vu_BAS_SAIR
RESERVATIONID numeric 9
SEGMENTINDEX smallint
AIRLINECODE varchar 4
FLIGHTNUM varchar 16
DEPARTAIRPORT varchar 4
vu_BAS_SEGMENT
RESERVATIONID numeric 9
SEGMENTINDEX smallint 2
VENDORNAME varchar 64
SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
dbo.vu_BAS_SAIR.AIRLINECODE,
dbo.vu_BAS_SAIR.FLIGHTNUM
FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
dbo.vu_BAS_SEGMENT ON
dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
dbo.vu_BAS_SAIR.SEGMENTINDEX = dbo.vu_BAS_SEGMENT.SEGMENTINDEX
WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
RESERVATIONID SEGMENTINDEX VENDORNAME AIRLINECODE FLIGHTNUM
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 11 Dan Knowles Tours
25823 11 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 12 Dan Knowles Tours
25823 13 Atlantis, Paradise Island
25823 14 Atlantis, Paradise Island
25823 15 Seahorse Sailing Adventures
25823 16 Neptunes Water Toys
25823 17 Nassau Cruises Limited
25823 18 Document Delivery
25823 19 Trip Mate Insurance Inc.
25823 20 Package Booking
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 21 Atlantis, Coral Towers
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 22 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 23 Dan Knowles Tours
25823 24 Atlantis, Paradise Island
25823 25 Atlantis, Paradise Island
25823 26 Seahorse Sailing Adventures
25823 1 Delta Air Lines DL 996
25823 1 Delta Air Lines DL 996
25823 2 Delta Air Lines DL 427
25823 27 Neptunes Water Toys
25823 28 Nassau Cruises Limited
25823 29 Document Delivery
25823 30 Trip Mate Insurance Inc.
25823 3 Delta Air Lines DL 928
25823 4 Delta Air Lines DL 302
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 5 Delta Air Lines DL 996
25823 6 Delta Air Lines DL 427
25823 7 Delta Air Lines DL 928
25823 8 Delta Air Lines DL 302
25823 9 Package Booking
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
25823 10 Atlantis, Coral Towers
Michael Hardy
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Without seeing what your data is going to be deaulted to its a bit had to
give exact code however you should probably have a look at the COALESCE
command
Given the following schema
CREATE TABLE [dbo].[Tester] (
[Part] [char] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[PartLink] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Tester] WITH NOCHECK ADD
CONSTRAINT [PK_Tester] PRIMARY KEY CLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
With the following data
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, NULL
'Part4', 4, NULL
The following statement
SELECT Part, ID, COALESCE (PartLink,
(SELECT PartLink
FROM Tester
WHERE ID = 1)) AS PartLink
FROM dbo.Tester
will give
'Part1', 1, 45
'Part2', 2, 34
'Part3', 3, 45
'Part4', 4, 34
Anyway have a look at BOL and see if it helps.
"I favor the Civil Rights Act of 1964 and it must be enforced at gunpoint if
necessary."
Ronald Reagan
"Michael Hardy" wrote:
> I need to create a view to support a report requirement. I need the
> returned dataset to include the AIRLINECODE and FLIGHTNUM so the info
> will be available no matter what vendor the end user filters on. To do
> this I have to populate the flight info in the rows that are non airline
> vendors. For example, I need the AIRLINECODE and FLIGHTNUM to appear in
> the DAN KNOWLES TOUR rows, etc. How can I do this?
> I have provided the below info to help you test. I am using SQL Server
> 2000.
> vu_BAS_SAIR
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint
> AIRLINECODE varchar 4
> FLIGHTNUM varchar 16
> DEPARTAIRPORT varchar 4
> vu_BAS_SEGMENT
> RESERVATIONID numeric 9
> SEGMENTINDEX smallint 2
> VENDORNAME varchar 64
> SELECT dbo.vu_BAS_SEGMENT.RESERVATIONID,
> dbo.vu_BAS_SEGMENT.SEGMENTINDEX, dbo.vu_BAS_SEGMENT.VENDORNAME,
> dbo.vu_BAS_SAIR.AIRLINECODE,
> dbo.vu_BAS_SAIR.FLIGHTNUM
> FROM dbo.vu_BAS_SAIR RIGHT OUTER JOIN
> dbo.vu_BAS_SEGMENT ON
> dbo.vu_BAS_SAIR.RESERVATIONID = dbo.vu_BAS_SEGMENT.RESERVATIONID AND
> dbo.vu_BAS_SAIR.SEGMENTINDEX => dbo.vu_BAS_SEGMENT.SEGMENTINDEX
> WHERE (dbo.vu_BAS_SEGMENT.RESERVATIONID = 25823)
>
> RESERVATIONID SEGMENTINDEX VENDORNAME AIRLINECODE FLIGHTNUM
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 11 Dan Knowles Tours
> 25823 11 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 12 Dan Knowles Tours
> 25823 13 Atlantis, Paradise Island
> 25823 14 Atlantis, Paradise Island
> 25823 15 Seahorse Sailing Adventures
> 25823 16 Neptunes Water Toys
> 25823 17 Nassau Cruises Limited
> 25823 18 Document Delivery
> 25823 19 Trip Mate Insurance Inc.
> 25823 20 Package Booking
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 21 Atlantis, Coral Towers
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 22 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 23 Dan Knowles Tours
> 25823 24 Atlantis, Paradise Island
> 25823 25 Atlantis, Paradise Island
> 25823 26 Seahorse Sailing Adventures
> 25823 1 Delta Air Lines DL 996
> 25823 1 Delta Air Lines DL 996
> 25823 2 Delta Air Lines DL 427
> 25823 27 Neptunes Water Toys
> 25823 28 Nassau Cruises Limited
> 25823 29 Document Delivery
> 25823 30 Trip Mate Insurance Inc.
> 25823 3 Delta Air Lines DL 928
> 25823 4 Delta Air Lines DL 302
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 5 Delta Air Lines DL 996
> 25823 6 Delta Air Lines DL 427
> 25823 7 Delta Air Lines DL 928
> 25823 8 Delta Air Lines DL 302
> 25823 9 Package Booking
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
> 25823 10 Atlantis, Coral Towers
>
> Michael Hardy
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!
>