Friday, March 23, 2012

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

No comments:

Post a Comment