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!
>