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 cursors with sql query

i have written a cursor to select records in a sequence for processing but i think the performance is not upto the mark.i have read in many places that sql cursor should not be used.but i am unable to find a substitute for cursor.could you help me out with this..

this is the cursor we are using:

CREATE proc sp_process_cdr
as
declare @.last_call_time datetime
set @.last_call_time=(select max(calltime_gmt) from processed_cdr)
declare process_cdr cursor
read_only
for
select srcip,username,callto,calltime,duration from rawcdr
where calltime>@.last_call_time
declare
@.gatewayip varchar(50),
@.username varchar (50),
@.callto varchar(100),
@.calltime datetime,
@.duration float,
@.accountid varchar(50),
@.subscriberid varchar(10),
@.cost money,
@.country varchar(100)

open process_cdr
fetch next from process_cdr into @.gatewayip,@.username,@.callto,@.calltime,@.duration
while(@.@.fetch_status<>-1)
begin
if(@.@.fetch_status<>-2)
begin
set @.accountid=''
set @.subscriberid=''
set @.country=''
set @.cost=0

if(charindex('00',@.callto)=5)
begin
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+3,50)
end
else
if(charindex('011',@.callto)=5)
begin
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+4,50)
end
else
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+1,50)

exec sp_process_call @.gatewayip,@.username,@.duration,@.callto,@.accountid output,@.subscriberid output,@.country output,@.cost output
insert into processed_cdr
select @.accountid,@.subscriberid,@.gatewayip,@.username,@.callto,@.country,@.calltime,@.duration,@.cost
end
fetch next from process_cdr into @.gatewayip,@.username,@.callto,@.calltime,@.duration
end
close process_cdr
deallocate process_cdr

GO

i dont have a unique column in my table.

YOu are executing a stored procedure within your code, unless that you can′t change your stored procedure, you can′t keep away from that cursor (not touching the solution that you could put out some statement and execute them froma temporary table one by one, which would be nearly the same performance). Anyway you shouldn′t name your procedure with a "sp_" prefix unless these is predefined for master database procs and this will lead to performance (due to recompiling) issues.

HTH, Jens Suessmeyer.

|||

Dear Vignesh, below is an example of how to do update using query rather than cursor.

UPDATE t1
SET t1.column4 = t2.column4, t1.column5 = t3.column5
FROM table1 t1
INNER JOIN table2 t2 ON t1.column1 = t2.column1
INNER JOIN table3 t3 on t1.column2 = t3.column2
WHERE t1.column3 >= t2.column3 AND t3.column3 <> 0

Vincent

Replacing a portion of text string in column

I need to replace a portion of a url in a column as a result of
changing servers. Is there a SELECT/REPLACE/UPDATE combination query
that can do this. The table has close to a thousand entries and would
be nice if a query can be set to do this. Tried the REPLACE example
in the BOOKS ONLINE but it creates syntax error, apparently because it
does not like the characters in the url and/or wildcards. I don't need
to replace the entire url, only the portion before ".com". Thanks in
anticipation of your help.

Pradip SagdeoPradip,
This statement:
select replace('http://www.technicalvideos.net','s.net','s14.net')
seems to work OK, so, perhaps you could give your exact update and the exact
error along with some sample data.
Best regards,
Chuck Conover
www.TechnicalVideos.net

"Pradip Sagdeo" <pradip.m.sagdeo@.pfizer.com> wrote in message
news:8dbc5a0f.0401280726.708a1c6@.posting.google.co m...
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.
> Pradip Sagdeo|||Thanks. I will try again. Must have made a typing mistake.

Pradip

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Pradip Sagdeo (pradip.m.sagdeo@.pfizer.com) writes:
> I need to replace a portion of a url in a column as a result of
> changing servers. Is there a SELECT/REPLACE/UPDATE combination query
> that can do this. The table has close to a thousand entries and would
> be nice if a query can be set to do this. Tried the REPLACE example
> in the BOOKS ONLINE but it creates syntax error, apparently because it
> does not like the characters in the url and/or wildcards. I don't need
> to replace the entire url, only the portion before ".com". Thanks in
> anticipation of your help.

Unfortunately, the replace() function does not support wildcards, so
if you need to use that, you have to be creative. Or descend to use
some client code to handle it.

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

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

Replacement for sysprocesses dbid?

In SQLServer 2005, how can you select just the processes that are
connected to a specific database? In 2000, I could do it with this
query:
select *
from sysprocesses
where dbid = 5
BOL recommends using any of the dynamic management views
sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
as a replacement for sysprocesses, but none of these has a database or
dbid column that I can find. Am I overlooking something? How do you
do this? Thanks.select * from sys.dm_exec_requests
where database_id = 5
to just see who is using a database, who might not be running any queries,
you can use sys.dm_tran_locks
select request_session_id from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = 5
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<stavros@.mailinator.com> wrote in message
news:1161904818.742946.108590@.m7g2000cwm.googlegroups.com...
> In SQLServer 2005, how can you select just the processes that are
> connected to a specific database? In 2000, I could do it with this
> query:
> select *
> from sysprocesses
> where dbid = 5
> BOL recommends using any of the dynamic management views
> sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
> as a replacement for sysprocesses, but none of these has a database or
> dbid column that I can find. Am I overlooking something? How do you
> do this? Thanks.
>sql

Replacement for sysprocesses dbid?

In SQLServer 2005, how can you select just the processes that are
connected to a specific database? In 2000, I could do it with this
query:
select *
from sysprocesses
where dbid = 5
BOL recommends using any of the dynamic management views
sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
as a replacement for sysprocesses, but none of these has a database or
dbid column that I can find. Am I overlooking something? How do you
do this? Thanks.select * from sys.dm_exec_requests
where database_id = 5
to just see who is using a database, who might not be running any queries,
you can use sys.dm_tran_locks
select request_session_id from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = 5
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<stavros@.mailinator.com> wrote in message
news:1161904818.742946.108590@.m7g2000cwm.googlegroups.com...
> In SQLServer 2005, how can you select just the processes that are
> connected to a specific database? In 2000, I could do it with this
> query:
> select *
> from sysprocesses
> where dbid = 5
> BOL recommends using any of the dynamic management views
> sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
> as a replacement for sysprocesses, but none of these has a database or
> dbid column that I can find. Am I overlooking something? How do you
> do this? Thanks.
>

Replace, the character, and yea...

I have the following:

------

WHILE PATINDEX('%,%',@.Columns)<> 0BEGIN
SELECT @.Separator_position = PATINDEX('%,%',@.Columns)
SELECT @.array_Value =LEFT(@.Columns, @.separator_position - 1)
SET @.FieldTypeID = (SELECT FieldTypeIDFROM [Form].[Fields]WHERE FieldID = (CAST(@.array_ValueAS INT)))
SET @.FieldName = (SELECT [Name]FROM [Form].[Fields]WHERE FieldID = @.array_Value)
print'arry value' +CONVERT(VarChar(500), @.array_value)
print'FieldTypeID: ' +CONVERT(VARCHAR(500), @.FieldTypeID)
PRINT'FieldName: ' + @.FieldName
BEGIN
IF @.FieldTypeID = 1OR @.FieldTypeID = 2OR @.FieldTypeID = 3OR @.FieldTypeID = 9OR @.FieldTypeID = 10OR @.FieldTypeID = 7
BEGIN
SET @.InnerItemSelect =' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[ItemDetailFieldRecords] IDFR WHERE IDFR.ItemDetailID = ID.ItemDetailID AND IDFR.FieldID = ' + @.array_Value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT ISNULL(CONVERT(VARCHAR(MAX),[Value]),'''') FROM [Item].[TaskFieldRecords] TFR WHERE TFR.TaskID = T.TaskID AND TFR.FieldID = ' + @.array_Value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeID = 4OR @.FieldTypeID = 8--DropDownList/RadioButtonlist
BEGIN
SET @.InnerItemSelect =' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[ItemDetailFieldListRecords] IDFLR ON FFLV.FieldListValueID = IDFLR.FieldListValueID WHERE IDFLR.ItemDetailID = ID.ItemDetailID AND FFLV.FIeldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT [Value] FROM [Form].[FieldListValues] FFLV INNER JOIN [Item].[TaskFieldListRecords] TFLR ON FFLV.FieldListValueID = TFLR.FieldListValueID WHERE TFLR.TaskID = T.TaskID AND FFLV.FIeldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeiD = 5--Cascading
BEGIN
SET @.InnerItemSelect =' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[ItemDetailFieldCascadingRecords] IDFCR ON IDFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE IDFCR.ItemDetailID = ID.ItemDetailID AND LT.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT [FCV].[Value] FROM [Form].[FieldCascadingValues] FCV INNER JOIN [Form].[FieldCascadingLookUpTables] LT ON FCV.FIeldCascadingLookupTableID = LT.FieldCascadingLookupTableID INNER JOIN [Item].[TaskFieldCascadingRecords] TFCR ON TFCR.FieldCascadingValueID = FCV.FieldCascadingValueID WHERE TFCR.TaskID = T.TaskID AND LT.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeiD = 6--ListBox
BEGIN
SET @.InnerItemSelect =' (SELECT i.[CSV] FROM @.ItemDetailLV i WHERE i.ID = ID.ItemDetailID AND i.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT it.[CSV] FROM @.TaskLV it WHERE it.ID = T.TaskID AND it.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FieldTypeID = 11--Users
BEGIN
SET @.InnerItemSelect =' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[ItemDetailUserRecords] IDUR ON SU.UserID = IDUR.UserID WHERE IDUR.ItemDetailID = ID.ItemDetailID AND IDUR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT SU.[UserID] FROM [Security].[Users] SU INNER JOIN [Item].[TaskUserRecords] TUR ON SU.UserID = TUR.UserID WHERE TUR.TaskID = T.TaskID AND TUR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
ELSE IF @.FIelDTypeID = 12--Group
BEGIN
SET @.InnerItemSelect =' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[ItemDetailGroupRecords] IDGR ON SG.GroupID = IDGR.GroupID WHERE IDGR.ItemDetailID = ID.ItemDetailID AND IDGR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
SET @.InnerTaskSelect =' (SELECT SG.[GroupID] FROM [Security].[Groups] SG INNER JOIN [Item].[TaskGroupRecords] TGR ON SG.GroupID = TGR.GroupID WHERE TGR.TaskID = T.TaskID AND TGR.FieldID = ' + @.array_value +') AS ''' + @.FieldName +''' '
END
END
PRINT'Inner Item Select:' + @.InnerItemSelect
PRINT'Inner Task Select:' + @.InnerTaskSelect
SET @.IDSelect = @.IDSelect + @.InnerItemSelect +', '
SET @.TSelect = @.TSelect + @.InnerTaskSelect +', '
SELECT @.Columns = STUFF(@.Columns, 1, @.separator_position,'')

END 

-----

That is only part of a large query that writs a SQL Query to a column in a Database. That Query (in the column) is just ran normally so I don't need to compile it each time I want to run it.

THe problem I have is @.FieldName might be: ryan's field.

That apostrophe is killing me because the SQL keeps it asryan's field, notryan''s field(note the 2 apostrophes). I cannot do: REPLACE(@.FieldName, ''', '''') because it's not closing the apostrophes. Is there an escape character that I can use to say only one: ' ?

Would the only solution be to put: ryan''s field into the Database, and just format it properly on the output?

Thanks.

What I normally do is replace it with an obsure character like a ^ or | and then properly format it on the output.

|||

Thought about that, but I'm not a fan of putting obscure characters in the system. It makes it a pain later on... and seeing as we have more than 300 tables and 900 Stored procedures i'm not really looking forward to working with it that way.

|||

You best bet would be to replace the single quotes with doubles before you concatenate.

|||

I figured it out:

PRINT REPLACE(@.FieldName, '''', '''''')

I didn't think this would work as I assumed it woudl look for the double apostrophes, but it escapes it to the single, and then replaces with the 2. Worked like a charm :P

Replace() & upper () in stored procedure

Hi;

I have a stored procedure :

<code
Create Procedure ControlDept
(

@.DeptID nvarchar(10)
)
As
If Exists
(
Select DeptName From Departments Where
DeptID LIKE @.DeptID
)
Return 1
Else
Return 0

Now I want to apply replace and upper functions to DeptID in database before saying
"DeptID LIKE @.DeptID".

for example the parameter is :"D&V"
DeptID in database is:"d & v" //there are spaces

if I say DeptID LIKE @.DeptID nothing is found because of character nonmatching
So I have to apply replace & upper functions to the column DeptID in database

but how?
can you help me please??You probably don't need the "Upper" function since *most* SQL Server functions are case insensitive by default.

As for the spaces, not sure what to tell you there. That's a one-off solution that you will have to code manually. For instance, what happens when the value is "D& V"? There are 2 spaces now, and you'd have to code a check for that too.

Also, if you are using LIKE, you need to have a % character. For example:
DeptID LIKE @.DeptID + '%'

replace T-SQL fonction

I would like to know how to use a replace fonction on a table
Like, currently, the syntax for replace is

SELECT REPLACE('abcdefghicde','cde','xxx')

but I would like to do something more like

SELECT REPLACE ((SELECT * FROM TABLE1) , 'cde', 'xxx')

Thanks to reply!I've found my problem ... which was in fact simple

it's:

select replace (FieldName , 'abc' , 'xxx') from MyTable...

the problem was that my FieldName was a Text field... which doesn't work... you need a varchar or nvarchar there...
so:

select replace (CAST(FieldName AS varchar(8000)), 'abc', 'xxx') from myTable

Thanks anyway!|||You should consider changing your fieldtype to VARCHAR if TEXT is not required. It will make coding easier and improve efficiency.

blindmansql

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

Replace Multiple Characters

Hi
I need a select statement replace multiple characters from every row
in a column.
I know about replace :
REPLACE ( 'string_expression1' , 'string_expression2' ,
'string_expression3' ) but the question is
how can i do the replace if there are multiple 'string_expression2' ?
For example:
I use replace when i make a select statement in a table like this:
SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
FROM TableName
if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
TTT and ZZZ to be replaced with OOO. how can i modify this select
statement?
Thanks in advance
.Try
SELECT *,
REPLACE(REPLACE(ColumnName, 'XXX', 'TTT'), 'ZZZ','OOO') AS Expr1,
FROM TableName
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1131957553.550242.182530@.g14g2000cwa.googlegroups.com...
> Hi
> I need a select statement replace multiple characters from every row
> in a column.
> I know about replace :
> REPLACE ( 'string_expression1' , 'string_expression2' ,
> 'string_expression3' ) but the question is
> how can i do the replace if there are multiple 'string_expression2' ?
> For example:
> I use replace when i make a select statement in a table like this:
> SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
> FROM TableName
> if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
> TTT and ZZZ to be replaced with OOO. how can i modify this select
> statement?
> Thanks in advance
>
> .
>|||If you know the total number of replacements in advance, you can nest the
REPLACE statements. So, you will write something like REPLACE(REPLACE (...),
..., ...)
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
<stelioshalkiotis@.yahoo.gr> wrote in message
news:1131957553.550242.182530@.g14g2000cwa.googlegroups.com...
> Hi
> I need a select statement replace multiple characters from every row
> in a column.
> I know about replace :
> REPLACE ( 'string_expression1' , 'string_expression2' ,
> 'string_expression3' ) but the question is
> how can i do the replace if there are multiple 'string_expression2' ?
> For example:
> I use replace when i make a select statement in a table like this:
> SELECT *, REPLACE(ColumnName, 'XXX', 'TTT'), AS Expr1,
> FROM TableName
> if i have the string XXXYYYZZZMMM and i want XXX to be replaced with
> TTT and ZZZ to be replaced with OOO. how can i modify this select
> statement?
> Thanks in advance
>
> .
>|||Thanks!
It works great!sql

Replace in Select Statement??

Hi ;)

I have a select statement where i need to Replace some Chars
Maybe someone can help

SELECT * FROM reguser WHERE REPLACE(" & whereTable & ",'-',')

I want to Replace the"-" and the"/"

Thanks in advanceIt looks like you are concatenating this string from a .NET app (?). If so, is it possible in your scenario to do the replace before you concatenate the qeury together? If not, you are certainly on the right track with the T-SQL REPLACE function. Here's thedoc on that just in case.|||Hi :)

Thanks for the answer ..
The code like it is ..works and replaces the"-" ...but i just wanna add another replacement for the"/"...but i cant find the right way to do it

Thanks|||did you try this:

SELECT * FROM reguser WHERE REPLACE(REPLACE(" & whereTable & ",'-','') ,'/','')|||Thanks a Lot mate ;)

Thats it!!

Cheers

Replace function not working

Hi,
I posted a request here and am still working on it when I landed on this bug.

select top 10 replace(comma_separated_string,',','giveaverylongp assagehere') from table

The function works fine if the comma separated string is small or if the passage is small. It fails for long passages..

Is this a mssql bug?What's long? Replace works fine for me on 8000. Does it break off at 1024 in the Analyzer, while it's len(..) says otherwise?sql

Replace - simple MDX question

I am using the following MDX statement to retrun data to populate a drop dow
n
list allowing the user to select a time period:
WITH
MEMBER [Measures].[DisplayName] AS
'SPACE([Period].CurrentMember.Level.Ordinal * 4) +
[Period].CurrentMember.Name'
MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Unique
Name'
SELECT
{[Measures].[UniqueName], [Measures].[DisplayName]} ON
Columns,
[Period].Members ON Rows
FROM MyCube
The idea of the "SPACE([Period].CurrentMember.Level.Ordinal * 4)" bit in
the
first MEMBER declaration is to provide indentation to give a sense of the
hierarchy in the drop down list display.
I now need to serve a client application that doesn't allow leading spaces
in the drop down list display. Is there a function I can use instead of SPAC
E
to put an alterntive indentation character at the start?
For example, a hyphen. So the returned data would look something like:
ALL
--2004
--Qtr1
--Jan
--Feb
--MarWITH
MEMBER [Measures].[DisplayName] AS
'String([Period].CurrentMember.Level.Ordinal * 4, "-") +
[Period].CurrentMember.Name'
MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Unique
Name'
SELECT
{[Measures].[UniqueName], [Measures].[DisplayName]} ON
Columns,
[Period].Members ON Rows
FROM MyCube
"Dave Morrow" wrote:

> I am using the following MDX statement to retrun data to populate a drop d
own
> list allowing the user to select a time period:
> WITH
> MEMBER [Measures].[DisplayName] AS
> 'SPACE([Period].CurrentMember.Level.Ordinal * 4) +
> [Period].CurrentMember.Name'
> MEMBER [Measures].[UniqueName] AS '[Period].CurrentMember.Uniq
ueName'
> SELECT
> {[Measures].[UniqueName], [Measures].[DisplayName]} O
N Columns,
> [Period].Members ON Rows
> FROM MyCube
> The idea of the "SPACE([Period].CurrentMember.Level.Ordinal * 4)" bit
in the
> first MEMBER declaration is to provide indentation to give a sense of the
> hierarchy in the drop down list display.
> I now need to serve a client application that doesn't allow leading spaces
> in the drop down list display. Is there a function I can use instead of SP
ACE
> to put an alterntive indentation character at the start?
> For example, a hyphen. So the returned data would look something like:
> ALL
> --2004
> --Qtr1
> --Jan
> --Feb
> --Mar
>

replace

Hi Guys
Stupid question,
How would I replace all euro signs with pound signs within a column in a
database?
would I use SELECT REPLACE?Yes, why not,
SELECT REPLACE('TESTthis','this','here')
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Jaco Wessels" <jaco_wess@.yahoo.co.uk> schrieb im Newsbeitrag
news:%23gHMnuoRFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi Guys
> Stupid question,
> How would I replace all euro signs with pound signs within a column in a
> database?
> would I use SELECT REPLACE?
>|||Try this:
replace(fieldname, '?', '$')
I got the symbol from Word.
"Jaco Wessels" <jaco_wess@.yahoo.co.uk> wrote in message
news:%23gHMnuoRFHA.2748@.TK2MSFTNGP09.phx.gbl...
> Hi Guys
> Stupid question,
> How would I replace all euro signs with pound signs within a column in a
> database?
> would I use SELECT REPLACE?
>|||I think this works better for you:
SELECT REPLACE('128?',CHAR(128),CHAR(163))
Jens Suessmeyer.
"Ross Culver" <rculver@.alliant-solutions.com> schrieb im Newsbeitrag
news:OGee72oRFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Try this:
> replace(fieldname, '?', '$')
> I got the symbol from Word.
> "Jaco Wessels" <jaco_wess@.yahoo.co.uk> wrote in message
> news:%23gHMnuoRFHA.2748@.TK2MSFTNGP09.phx.gbl...
>|||Thanks, on closer inspection the user was mistaking a euro sign with oe
ligature (o)
SELECT REPLACE(Instructions,char(156),char(163)
) from Event
GO
Update Event
Set Instructions = REPLACE(Instructions,char(156),char(163)
)
"Ross Culver" <rculver@.alliant-solutions.com> wrote in message
news:OGee72oRFHA.1348@.TK2MSFTNGP15.phx.gbl...
> Try this:
> replace(fieldname, '?', '$')
> I got the symbol from Word.
> "Jaco Wessels" <jaco_wess@.yahoo.co.uk> wrote in message
> news:%23gHMnuoRFHA.2748@.TK2MSFTNGP09.phx.gbl...
>

Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0 -- Opening Balance for Container
,LPD.Qty -- Quantity Change
,@.ActionType -- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty -- Balance Quantity for Container
,LP.LabelBatchStatus -- Item Status
,NULL -- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah AdarshOn 30 Mar 2006 23:05:08 -0800, Adarsh wrote:
>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
> + (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
--
Hugo Kornelis, SQL Server MVP

Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0-- Opening Balance for Container
,LPD.Qty-- Quantity Change
,@.ActionType-- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty-- Balance Quantity for Container
,LP.LabelBatchStatus-- Item Status
,NULL-- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah Adarsh
On 30 Mar 2006 23:05:08 -0800, Adarsh wrote:

>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
>+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
Hugo Kornelis, SQL Server MVP

Repeated SubQuery

Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0 -- Opening Balance for Container
,LPD.Qty -- Quantity Change
,@.ActionType -- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty -- Balance Quantity for Container
,LP.LabelBatchStatus -- Item Status
,NULL -- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah AdarshOn 30 Mar 2006 23:05:08 -0800, Adarsh wrote:

>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
> + (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
Hugo Kornelis, SQL Server MVP

repeated posts

I have a database where I collect student information from three different tables.
When I write a select case to see all the student information, the problem is that a student can have more than onte contact person from ex AF. How can I see all this information as one record?

I wrote like this:

Select distinct Studieinfo.PersNR, Elev.Fornamn + ' ' + Elev.Efternamn AS Namn, Studieinfo.Startvecka, Studieinfo.slutvecka,
Studieinfo.startdatum, Studieinfo.slutdatum, Studieinfo.Kursort, Studieinfo.Studietid, Studieinfo.Forlangning,
Studieinfo.beraknad_studietid, Studieinfo.mal, Studieinfo.delrapport, Studieinfo.moduler,
KontaktPersoner_FK.Fornamn + ' ' + KontaktPersoner_FK.Efternamn AS KontaktFK, KontaktPersoner_AF.Fornamn + ' ' +KontaktPersoner_AF.Efternamn AS KontaktAF
From Studieinfo, KontaktPersoner_FK, Kontakt_FK, KontaktPersoner_AF, Kontakt_AF, Elev
WHERE Elev.PersNR=Kontakt_FK.PersNR
and Elev.PersNR=Kontakt_AF.PersNR
and Elev.PersNR=Studieinfo.PersNR
and Elev.PersNR='691215-3638'
and Kontakt_FK.KontaktNR_FK=KontaktPersoner_FK.Kontakt NR_FK
and Kontakt_AF.KontaktNR_AF=KontaktPersoner_AF.Kontakt NR_AF
goIf you have defined contact types (Mothe, Father, Guardian, ParoleOfficer...) then you can write a CROSSTAB query to do this. Look it up in Books Online for instructions. Otherwise you may need to use a cursor to loop through related records and concatenate multiple contact records into a single character string. A user-defined function would be ideal for this.

I'd also say that this type of formatting (which is purely for the sake of appearance) is often best delegated to the reporting interface (crystal, VB, Excel, Access, ect...). In a sense, when you try to formulate a query like this you are asking a relational database to be non-relational.

blindman|||Thanks for your advice. I was actually thinking of correcting it within the asp on the page. Like you suggested but was just wondering if it was possible to do with the sql.