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
Showing posts with label dataset. Show all posts
Showing posts with label dataset. Show all posts
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
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!
>
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!
>
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!
>
Monday, March 12, 2012
Repeating data
I want a table with fields from the dataset that repeats for every page in
the report. How can I do this...?
DanHello,
Have you tried to put your fields in table or group header and ask for
repeat on noew page in report properties ?
Jerome Berthaud (MCSD)
http://www.winsight.fr
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:umDmb4$eEHA.1356@.TK2MSFTNGP09.phx.gbl...
> I want a table with fields from the dataset that repeats for every page in
> the report. How can I do this...?
> Dan
>
the report. How can I do this...?
DanHello,
Have you tried to put your fields in table or group header and ask for
repeat on noew page in report properties ?
Jerome Berthaud (MCSD)
http://www.winsight.fr
"alien2_51" <dan.billow"at"n.o.s.p.a.m.monacocoach.commercialversion> wrote
in message news:umDmb4$eEHA.1356@.TK2MSFTNGP09.phx.gbl...
> I want a table with fields from the dataset that repeats for every page in
> the report. How can I do this...?
> Dan
>
repeating a header with fields from the dataset
Hi,
I'm designing a report and have the requirement that the report title needs to be repeated on each page. However, the report title is dynamic, retrieved from the database, so i cannot place it in the page header (what an annoyance). The solution i first thought of was placing the title in a table, repeating the table header on each page, however the problem i'm encountering is that when i have a subreport in that table, that subreport is in one row
--> if the subreport spans across multiple pages, the header will only be shown on the first page the row where the subreport is actually in...
any thoughts anyone on a possible solution?
1. Place a hidden textbox in the report body with a value of =First(Fields!<TitleField>.Value).
2. Place another textbox in the page header referencing the first textbox with a value of =ReportItems!<Textbox1Name>.Value.
|||hi
thanks for the reply but that actually doesn't quite fully solve my problem; apparently it will only display the value of the textbox on the page where that textbox WOULD be rendered; my report consists of several subreports, i placed the hidden textbox "after" all the subreports, and the title would only appear on the last page.
Putting the textbox at the first place in the report yielded similar results; it would only display on the first page...
|||i'm testing the report by exporting to pdf, by the way|||Yes, the report item will come back as null if not rendered on the page. If possible, make the hidden textbox repeatable on every page. For example, if the report has a table put the hidden textbox on a detail table row. You will need to change the scope of the hidden textbox expression to reference the dataset, e.g. =First(Fields!<TitleField>.Value, "<DatasetName").
An ugly hack, I know.
repeating a header with fields from the dataset
Hi,
I'm designing a report and have the requirement that the report title needs to be repeated on each page. However, the report title is dynamic, retrieved from the database, so i cannot place it in the page header (what an annoyance). The solution i first thought of was placing the title in a table, repeating the table header on each page, however the problem i'm encountering is that when i have a subreport in that table, that subreport is in one row
--> if the subreport spans across multiple pages, the header will only be shown on the first page the row where the subreport is actually in...
any thoughts anyone on a possible solution?
1. Place a hidden textbox in the report body with a value of =First(Fields!<TitleField>.Value).
2. Place another textbox in the page header referencing the first textbox with a value of =ReportItems!<Textbox1Name>.Value.
|||hi
thanks for the reply but that actually doesn't quite fully solve my problem; apparently it will only display the value of the textbox on the page where that textbox WOULD be rendered; my report consists of several subreports, i placed the hidden textbox "after" all the subreports, and the title would only appear on the last page.
Putting the textbox at the first place in the report yielded similar results; it would only display on the first page...
|||i'm testing the report by exporting to pdf, by the way|||Yes, the report item will come back as null if not rendered on the page. If possible, make the hidden textbox repeatable on every page. For example, if the report has a table put the hidden textbox on a detail table row. You will need to change the scope of the hidden textbox expression to reference the dataset, e.g. =First(Fields!<TitleField>.Value, "<DatasetName").
An ugly hack, I know.
Monday, February 20, 2012
Rendering report when one dataset times out
We use Report Server reports to provide our support staff with views into several production databases on various servers. The most commonly used report is a summary of the data on several production servers. The report contains multiple datasets. Each dataset points at a different server. If one of the servers is down some reason, one dataset fails to execute, and the entire report fails to to render.
What I would like is some control over this behaviour. If a given data set fails to execute, I would like the report to continue and render the results. The failed data set would obviously show no data but this is fine by me.
I know I can place timeout values on each dataset. But this doesn't help me.
Can I structure my report or SQL in such a way that the report survives the failure of a dataset to execute because a remote server is down?
Thanks in advance for your help
Hi,
no actually not. YOu have to put the query within a stored procedure, catching it up with exception handling (Hope you are on SQL 2k5) or create a own data extension with .NET to provide a dataset to the report. In the custom coded data extension you will have all the functionalities of the .NET Framework to catch those errors.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||You may use link server to collect the data on others DataBase, And put them in Temp DB...
Subscribe to:
Posts (Atom)