Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..
I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi
|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA
|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:
> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>
sql
Showing posts with label view. Show all posts
Showing posts with label view. Show all posts
Friday, March 30, 2012
Replacing CRLF's with <BR> in a view
Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column
)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:
> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column
)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:
> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>
Replacing CRLF's with <BR> in a view
Hi All,
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:
> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>
Can I make a view of a database that replaces CRLF's with <BR>. I am trying
to look at a particular text field using a data view in WSS. Or am I going
about this the wrong way completely..I don't know what WSS is, so I can't tell you if you are going in the
wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
you can use the replace function. Here is a small example:
use tempdb
go
create table test (c varchar(150))
go
insert into test (c) values (
'this is a test
should see one line
instead of 3 lines.')
insert into test (c) values (
'second
test
3 lines')
go
create view TestView
as
select replace(c, char(13) + char(10), '<BR>') as c from test
go
select * from TestView
go
--cleanup
drop view TestView
drop table test
Adi|||"RF" <RF@.discussions.microsoft.com> wrote in message
news:9F335BF2-BECD-453D-ACE7-8BB7A2A0BA96@.microsoft.com...
> Hi All,
> Can I make a view of a database that replaces CRLF's with <BR>. I am
trying
> to look at a particular text field using a data view in WSS. Or am I
going
> about this the wrong way completely..
>
I am assuming that WSS is Sharepoint. Since you are using text fields, you
cannot simply use a REPLACE as it doesn't work with the text datatype. You
will need to use a loop and PATINDEX along with some other text related
functionality like READTEXT and WRITETEXT.
Here is some sample code to get you started. In this example, I am assuming
that there is only 0 or 1 instance of the value that needs to be replaced.
I am then updating the column in the table with my replacement value. For
what you are doing, you will most likely need to create a temp table, copy
the text column to it, make the updates to it in a loop (so you can catch
multiple CrLf's) and then select from the temp table to return your row(s).
Note: This should really be done in the front-end somewhere as it has far
better string functionality and capabilities.
DECLARE @.idx int
SELECT @.idx = PATINDEX('%[value_you_are_looking_for_here%', text_column)
FROM Table
WHERE_clause
IF @.idx > 0
BEGIN
SELECT @.ptr = TEXTPTR(text_column)
FROM Table
WHERE_clause
UPDATETEXT Table.text_column @.ptr @.idx 0 'replacement_value'
END
I hope this helps to get you started.
Rick Sawtell
MCT, MCSD, MCDBA|||Hi Adi,
WSS is Windows Sharepoint Services...I'll try the example you gave ...Does
"go" mean anything besides go ? And can this been done on an existing
database ?
"Adi" wrote:
> I don't know what WSS is, so I can't tell you if you are going in the
> wrong way. In anycase if you wan't to replace the CRLF with <BR>, then
> you can use the replace function. Here is a small example:
>
> use tempdb
> go
> create table test (c varchar(150))
> go
> insert into test (c) values (
> 'this is a test
> should see one line
> instead of 3 lines.')
> insert into test (c) values (
> 'second
> test
> 3 lines')
> go
> create view TestView
> as
> select replace(c, char(13) + char(10), '<BR>') as c from test
> go
> select * from TestView
> go
> --cleanup
> drop view TestView
> drop table test
> Adi
>
Wednesday, March 28, 2012
Replacing a view in merge
We have SQL 2005 and I need to update a view used in a publication. The
views are in a separate pub so it should be pretty easy. My thought is that
I run sp_dropmergearticle, update the view and then run sp_addmergearticle.
Am I correct? Also, will the subscribers get the new publication/snapshot
when synching? Thanks.
David
I'd use sp_addscriptexec. Simply because it avoids the need for a new
snapshot of all the articles.
Rgds,
Paul Ibison
|||But the users have limited rights. Isn't this a problem in this solution?
Or is that requirement only to run the sp on the publication? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3C9223C0-069E-48A5-9CD6-C0A3B7743276@.microsoft.com...
> I'd use sp_addscriptexec. Simply because it avoids the need for a new
> snapshot of all the articles.
> Rgds,
> Paul Ibison
>
|||Yes - just run it on the publisher and it'll go down to the subscribers on
synchronization.
HTH,
Paul Ibison
|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:7BBAE4B2-76CF-48E5-AE87-36A77D6F4DCE@.microsoft.com...
> Yes - just run it on the publisher and it'll go down to the subscribers on
> synchronization.
> HTH,
> Paul Ibison
>
views are in a separate pub so it should be pretty easy. My thought is that
I run sp_dropmergearticle, update the view and then run sp_addmergearticle.
Am I correct? Also, will the subscribers get the new publication/snapshot
when synching? Thanks.
David
I'd use sp_addscriptexec. Simply because it avoids the need for a new
snapshot of all the articles.
Rgds,
Paul Ibison
|||But the users have limited rights. Isn't this a problem in this solution?
Or is that requirement only to run the sp on the publication? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3C9223C0-069E-48A5-9CD6-C0A3B7743276@.microsoft.com...
> I'd use sp_addscriptexec. Simply because it avoids the need for a new
> snapshot of all the articles.
> Rgds,
> Paul Ibison
>
|||Yes - just run it on the publisher and it'll go down to the subscribers on
synchronization.
HTH,
Paul Ibison
|||Thank you.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:7BBAE4B2-76CF-48E5-AE87-36A77D6F4DCE@.microsoft.com...
> Yes - just run it on the publisher and it'll go down to the subscribers on
> synchronization.
> HTH,
> Paul Ibison
>
Replacing a NULL in a view
I have created a view over a table that extracts production
information. The undelying table contains rows not just for product
code changed but also for speed changes.
The folloing view fillers out the speed changes and seems to work bar
one small problem. The EndTime for the currently running product will
be NULL as it is still running. This is a proble as I miss the current
production information. Is there a way to make the EndTime NULL in the
MAX function if the table value is NULL or could NULL be replaced with
the current Datetime
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
Many thanks
JimYou can use ISNULL to provide a different value for one that is NULL.
for example
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, ISNULL(MAX(EndTime), GETDATE()) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230512.64cddbc6@.posting.google.com...
> I have created a view over a table that extracts production
> information. The undelying table contains rows not just for product
> code changed but also for speed changes.
> The folloing view fillers out the speed changes and seems to work bar
> one small problem. The EndTime for the currently running product will
> be NULL as it is still running. This is a proble as I miss the current
> production information. Is there a way to make the EndTime NULL in the
> MAX function if the table value is NULL or could NULL be replaced with
> the current Datetime
> SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
> HoursRun
> FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime
> FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode) ProdLog
> ORDER BY StartTime, Unit, Line, ProductCode
> Many thanks
> Jim
information. The undelying table contains rows not just for product
code changed but also for speed changes.
The folloing view fillers out the speed changes and seems to work bar
one small problem. The EndTime for the currently running product will
be NULL as it is still running. This is a proble as I miss the current
production information. Is there a way to make the EndTime NULL in the
MAX function if the table value is NULL or could NULL be replaced with
the current Datetime
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
Many thanks
JimYou can use ISNULL to provide a different value for one that is NULL.
for example
SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
HoursRun
FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, ISNULL(MAX(EndTime), GETDATE()) AS EndTime
FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode) ProdLog
ORDER BY StartTime, Unit, Line, ProductCode
--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230512.64cddbc6@.posting.google.com...
> I have created a view over a table that extracts production
> information. The undelying table contains rows not just for product
> code changed but also for speed changes.
> The folloing view fillers out the speed changes and seems to work bar
> one small problem. The EndTime for the currently running product will
> be NULL as it is still running. This is a proble as I miss the current
> production information. Is there a way to make the EndTime NULL in the
> MAX function if the table value is NULL or could NULL be replaced with
> the current Datetime
> SELECT TOP 100 PERCENT *, DATEDIFF(hh, StartTime, EndTime) AS
> HoursRun
> FROM (SELECT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime
> FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode) ProdLog
> ORDER BY StartTime, Unit, Line, ProductCode
> Many thanks
> Jim
replacing [XFO=BM] in a column
Hi,
I have a table that contains text columns with data containing some tags
like [XFO=BM] in it.
I'd like to remove these and am using a view that contains calls to a
function to do this, but it doesn't work if the tags have square brackets
surrounding them.
i.e. set @.working = replace (@.working, '[XFO=DN]', '')
doesn't work.
Does anyone know how I can do this, or how I need to delimit the []s to make
the tags vanish?
Thanks in advance,
IanYou need to escape the opening bracket:
set @.working = replace (@.working, '[[]XFO=DN]', '')
ML
http://milambda.blogspot.com/|||Not sure i've understood correctly as the following works for me on SQL2k
DECLARE @.WORKING VARCHAR(50)
SET @.wORKING = '[XFO=DN]'
SELECT replace (@.working, '[XFO=DN]', '')
Does the problem lie in the function ...? Are you using LIKE anywhere ...?
From Books Online
Symbol
Meaning
LIKE '[ [ ]' [
HTH. Ryan
"Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
> Hi,
> I have a table that contains text columns with data containing some tags
> like [XFO=BM] in it.
> I'd like to remove these and am using a view that contains calls to a
> function to do this, but it doesn't work if the tags have square brackets
> surrounding them.
> i.e. set @.working = replace (@.working, '[XFO=DN]', '')
> doesn't work.
> Does anyone know how I can do this, or how I need to delimit the []s to
> make
> the tags vanish?
> Thanks in advance,
> Ian|||
"ML" wrote:
> You need to escape the opening bracket:
> set @.working = replace (@.working, '[[]XFO=DN]', '')
Thanks for that, only unfortunately that didn't work. Delimiting both sets
of brackets didn't work either.
Any other ideas?
Ian|||Sorry, my bad. Ryan has a better answer. What was I thinking? No one knows.
ML
http://milambda.blogspot.com/|||
> Not sure i've understood correctly as the following works for me on SQL2k
> DECLARE @.WORKING VARCHAR(50)
> SET @.wORKING = '[XFO=DN]'
> SELECT replace (@.working, '[XFO=DN]', '')
> Does the problem lie in the function ...?
Hmmm, well the function although long is relatively straightforward...
ALTER FUNCTION convertPropertyTitle (@.inp text)
returns varchar (8000)
as
begin
if @.inp is null
return ' '
declare @.working varchar (8000)
set @.working = replace (cast (@.inp as varchar (8000)),
'{\rtf1\ansi\ansicpg1252\deff0\deflang10
33', '')
set @.working = replace (@.working, '{\rtf1\ansi\deff0{\fonttbl{\f0\fnil MS
Sans Serif;}}', '')
set @.working = replace (@.working,
'\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fprq
2\fcharset0 Times New Roman;', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;}}', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '{\f1\fnil\fcharset0 MS Sans Serif;}}', ''
)
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fcharset0 MS Sans
Serif;}{\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fprq2\fcharset0 Times
New Roman;', '')
set @.working = replace (@.working, '\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\f0\fs16', '<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang1033\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\f0\fs16',
'<u><b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\f0\fs16', '<u>')
set @.working = replace (@.working, '\viewkind4\uc1 d\i\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\i\f0\fs16', '<i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\i\f0\fs16',
'<u><b><i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\i\f0\fs16', '<b><i>
')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs24', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\f0\fs20', ''
)
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs20', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '}', '')
set @.working = replace (@.working, '{', '')
set @.working = replace (@.working, ' ', '')
set @.working = replace (@.working, '\ulnone', '</u>')
set @.working = replace (@.working, '\ul', '<u>')
set @.working = replace (@.working, '\lang2057\b\f1', '<b>')
set @.working = replace (@.working, '\lang1033\b\f0', '<b>')
set @.working = replace (@.working, '\lang1033', '')
set @.working = replace (@.working, '\lang2057', '')
set @.working = replace (@.working, '\lang2057\fs20', '')
set @.working = replace (@.working, '\b0', '</b>')
set @.working = replace (@.working, '\b', '<b>')
set @.working = replace (@.working, '\i0', '</i>')
set @.working = replace (@.working, '\i', '<i>')
set @.working = replace (@.working, 'SIGNED
\f1''85''85''85''85''85''85''85'
'85''85''85''85''85''85''85''
85''85', '')
set @.working = replace (@.working,
'DATE''85''85''85''85''85''85''85
''85''85''85''85''85''85''85\
''85''85''85', '')
set @.working = replace (@.working, '\f0', '')
set @.working = replace (@.working, '\f1', '')
set @.working = replace (@.working, '\fs20', '')
set @.working = replace (@.working, '\fs16', '')
set @.working = replace (@.working, char (10), '')
set @.working = replace (@.working, char (13), '')
set @.working = replace (@.working, '\tab', '')
set @.working = replace (@.working, '\super', '')
set @.working = replace (@.working, '\nosupersub', '')
set @.working = replace (@.working, '[WAITPHOTO]', '')
set @.working = replace (@.working, '''a3', '￡')
set @.working = replace (@.working, '\par', '<BR>')
set @.working = replace (@.working, '[[]XFO=DN]', '')
set @.working = replace (@.working, '[[]XFO=BN]', '')
set @.working = replace (@.working, '[[]XFO=CN]', '')
set @.working = replace (@.working, '[[]XFO=BM]', '')
set @.working = replace (@.working, '[[]XFO=CM]', '')
return @.working
end
> Are you using LIKE anywhere ...?
I then do a
ALTER view propertiesProperty
as
select reference, dbo. convertPropertyTitle(PropertyAccommodati
on)
PropertyAccommodation, dbo. convertPropertyTitle(propertyfulldetails
)
propertyfulldetails, dbo.convertPropertyTitle(propertysituation)
propertysituation, dbo.convertPropertyTitle(propertytitle) propertytitle
from properties
then reference it as
select * from propertiesproperty
where reference = 'xxx3333'
So no likes anywhere.
Thanks,
Ian
> From Books Online
> Symbol
> Meaning
> LIKE '[ [ ]' [
>
> --
> HTH. Ryan
> "Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
> news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
>
>
I have a table that contains text columns with data containing some tags
like [XFO=BM] in it.
I'd like to remove these and am using a view that contains calls to a
function to do this, but it doesn't work if the tags have square brackets
surrounding them.
i.e. set @.working = replace (@.working, '[XFO=DN]', '')
doesn't work.
Does anyone know how I can do this, or how I need to delimit the []s to make
the tags vanish?
Thanks in advance,
IanYou need to escape the opening bracket:
set @.working = replace (@.working, '[[]XFO=DN]', '')
ML
http://milambda.blogspot.com/|||Not sure i've understood correctly as the following works for me on SQL2k
DECLARE @.WORKING VARCHAR(50)
SET @.wORKING = '[XFO=DN]'
SELECT replace (@.working, '[XFO=DN]', '')
Does the problem lie in the function ...? Are you using LIKE anywhere ...?
From Books Online
Symbol
Meaning
LIKE '[ [ ]' [
HTH. Ryan
"Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
> Hi,
> I have a table that contains text columns with data containing some tags
> like [XFO=BM] in it.
> I'd like to remove these and am using a view that contains calls to a
> function to do this, but it doesn't work if the tags have square brackets
> surrounding them.
> i.e. set @.working = replace (@.working, '[XFO=DN]', '')
> doesn't work.
> Does anyone know how I can do this, or how I need to delimit the []s to
> make
> the tags vanish?
> Thanks in advance,
> Ian|||
"ML" wrote:
> You need to escape the opening bracket:
> set @.working = replace (@.working, '[[]XFO=DN]', '')
Thanks for that, only unfortunately that didn't work. Delimiting both sets
of brackets didn't work either.
Any other ideas?
Ian|||Sorry, my bad. Ryan has a better answer. What was I thinking? No one knows.
ML
http://milambda.blogspot.com/|||
> Not sure i've understood correctly as the following works for me on SQL2k
> DECLARE @.WORKING VARCHAR(50)
> SET @.wORKING = '[XFO=DN]'
> SELECT replace (@.working, '[XFO=DN]', '')
> Does the problem lie in the function ...?
Hmmm, well the function although long is relatively straightforward...
ALTER FUNCTION convertPropertyTitle (@.inp text)
returns varchar (8000)
as
begin
if @.inp is null
return ' '
declare @.working varchar (8000)
set @.working = replace (cast (@.inp as varchar (8000)),
'{\rtf1\ansi\ansicpg1252\deff0\deflang10
33', '')
set @.working = replace (@.working, '{\rtf1\ansi\deff0{\fonttbl{\f0\fnil MS
Sans Serif;}}', '')
set @.working = replace (@.working,
'\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fprq
2\fcharset0 Times New Roman;', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;}}', '')
set @.working = replace (@.working, '{\fonttbl{\f0\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '{\f1\fnil\fcharset0 MS Sans Serif;}}', ''
)
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fcharset0 MS Sans
Serif;}{\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\fonttbl{\f0\fnil\fprq2\fcharset0 Times
New Roman;', '')
set @.working = replace (@.working, '\f1\fnil MS Sans Serif;', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\f0\fs16', '<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang1033\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\f0\fs16',
'<u><b>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\f0\fs16', '<u>')
set @.working = replace (@.working, '\viewkind4\uc1 d\i\fs16', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\i\f0\fs16', '<i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\ul\b\i\f0\fs16',
'<u><b><i>')
set @.working = replace (@.working, '\viewkind4\uc1\pard\b\i\f0\fs16', '<b><i>
')
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs24', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\f0\fs20', ''
)
set @.working = replace (@.working, '\viewkind4\uc1\pard\f0\fs20', '')
set @.working = replace (@.working, '\viewkind4\uc1\pard\lang2057\b\f0\fs16'
,
'<b>')
set @.working = replace (@.working, '}', '')
set @.working = replace (@.working, '{', '')
set @.working = replace (@.working, ' ', '')
set @.working = replace (@.working, '\ulnone', '</u>')
set @.working = replace (@.working, '\ul', '<u>')
set @.working = replace (@.working, '\lang2057\b\f1', '<b>')
set @.working = replace (@.working, '\lang1033\b\f0', '<b>')
set @.working = replace (@.working, '\lang1033', '')
set @.working = replace (@.working, '\lang2057', '')
set @.working = replace (@.working, '\lang2057\fs20', '')
set @.working = replace (@.working, '\b0', '</b>')
set @.working = replace (@.working, '\b', '<b>')
set @.working = replace (@.working, '\i0', '</i>')
set @.working = replace (@.working, '\i', '<i>')
set @.working = replace (@.working, 'SIGNED
\f1''85''85''85''85''85''85''85'
'85''85''85''85''85''85''85''
85''85', '')
set @.working = replace (@.working,
'DATE''85''85''85''85''85''85''85
''85''85''85''85''85''85''85\
''85''85''85', '')
set @.working = replace (@.working, '\f0', '')
set @.working = replace (@.working, '\f1', '')
set @.working = replace (@.working, '\fs20', '')
set @.working = replace (@.working, '\fs16', '')
set @.working = replace (@.working, char (10), '')
set @.working = replace (@.working, char (13), '')
set @.working = replace (@.working, '\tab', '')
set @.working = replace (@.working, '\super', '')
set @.working = replace (@.working, '\nosupersub', '')
set @.working = replace (@.working, '[WAITPHOTO]', '')
set @.working = replace (@.working, '''a3', '￡')
set @.working = replace (@.working, '\par', '<BR>')
set @.working = replace (@.working, '[[]XFO=DN]', '')
set @.working = replace (@.working, '[[]XFO=BN]', '')
set @.working = replace (@.working, '[[]XFO=CN]', '')
set @.working = replace (@.working, '[[]XFO=BM]', '')
set @.working = replace (@.working, '[[]XFO=CM]', '')
return @.working
end
> Are you using LIKE anywhere ...?
I then do a
ALTER view propertiesProperty
as
select reference, dbo. convertPropertyTitle(PropertyAccommodati
on)
PropertyAccommodation, dbo. convertPropertyTitle(propertyfulldetails
)
propertyfulldetails, dbo.convertPropertyTitle(propertysituation)
propertysituation, dbo.convertPropertyTitle(propertytitle) propertytitle
from properties
then reference it as
select * from propertiesproperty
where reference = 'xxx3333'
So no likes anywhere.
Thanks,
Ian
> From Books Online
> Symbol
> Meaning
> LIKE '[ [ ]' [
>
> --
> HTH. Ryan
> "Ian Jagger" <IanJagger@.discussions.microsoft.com> wrote in message
> news:C3850ACE-88F8-437E-94C5-D5FCD83DAEE9@.microsoft.com...
>
>
Monday, March 26, 2012
Replace View with Join or SubQuery
My application uses a View stored in a database. I have queries that join
this view with other tables. I'd like to eliminate the view. For example, if
the view was defined by:
CREATE VIEW dbo.AcctBalance
AS
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
My VB code (using ADO) creates this T-SQL query:
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
All my attempts to replace the View have failed so far. Can someone provide
guidance?
Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
RichardRichard
Why do you want to eliminate the VIEW? Any reasons?
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN
(
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
) AS AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
> My application uses a View stored in a database. I have queries that join
> this view with other tables. I'd like to eliminate the view. For example,
if
> the view was defined by:
> CREATE VIEW dbo.AcctBalance
> AS
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> My VB code (using ADO) creates this T-SQL query:
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
> All my attempts to replace the View have failed so far. Can someone
provide
> guidance?
> Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
> --
> Richard
>|||The database does not belong to me, but to the customer. I'm trying to get
my code out of the customer's database. Also, if I need to revise the View,
I must code a utility to modify the View in the customer's database. Any
other change can be implemented by building a new dll. I understand that
it's partly a philosophical thing.
Your post indicates that I can join a table that is created in the
parenthesis. I like that idea and will try it. Thanks a lot.
Richard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Richard
> Why do you want to eliminate the VIEW? Any reasons?
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN
> (
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> ) AS AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
>
> "Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
> message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
join
example,
> if
> provide
dbo.Sales.
>|||Hi,
Just to confirm, your code works perfectly for me. Thanks again.
Richard
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:O8NbIERFFHA.1392@.tk2msftngp13.phx.gbl...
> The database does not belong to me, but to the customer. I'm trying to get
> my code out of the customer's database. Also, if I need to revise the
View,
> I must code a utility to modify the View in the customer's database. Any
> other change can be implemented by building a new dll. I understand that
> it's partly a philosophical thing.
> Your post indicates that I can join a table that is created in the
> parenthesis. I like that idea and will try it. Thanks a lot.
> Richard
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> join
> example,
> dbo.Sales.
>
this view with other tables. I'd like to eliminate the view. For example, if
the view was defined by:
CREATE VIEW dbo.AcctBalance
AS
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
My VB code (using ADO) creates this T-SQL query:
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
All my attempts to replace the View have failed so far. Can someone provide
guidance?
Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
RichardRichard
Why do you want to eliminate the VIEW? Any reasons?
SELECT Desc, Addr1, Addr2, Phone, Total
FROM dbo.Account
LEFT OUTER JOIN
(
SELECT Acct_ID, SUM(Amount) AS Total
FROM dbo.Sales
GROUP BY Acct_ID
) AS AcctBalance
ON (AcctBalance.Acct_ID = Account.Acct_ID)
WHERE Account.Exclude = 0
ORDER BY Account.Desc
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
> My application uses a View stored in a database. I have queries that join
> this view with other tables. I'd like to eliminate the view. For example,
if
> the view was defined by:
> CREATE VIEW dbo.AcctBalance
> AS
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> My VB code (using ADO) creates this T-SQL query:
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
> All my attempts to replace the View have failed so far. Can someone
provide
> guidance?
> Acct_ID is the primary key in dbo.Account, and a foreign key in dbo.Sales.
> --
> Richard
>|||The database does not belong to me, but to the customer. I'm trying to get
my code out of the customer's database. Also, if I need to revise the View,
I must code a utility to modify the View in the customer's database. Any
other change can be implemented by building a new dll. I understand that
it's partly a philosophical thing.
Your post indicates that I can join a table that is created in the
parenthesis. I like that idea and will try it. Thanks a lot.
Richard
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> Richard
> Why do you want to eliminate the VIEW? Any reasons?
> SELECT Desc, Addr1, Addr2, Phone, Total
> FROM dbo.Account
> LEFT OUTER JOIN
> (
> SELECT Acct_ID, SUM(Amount) AS Total
> FROM dbo.Sales
> GROUP BY Acct_ID
> ) AS AcctBalance
> ON (AcctBalance.Acct_ID = Account.Acct_ID)
> WHERE Account.Exclude = 0
> ORDER BY Account.Desc
>
> "Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
> message news:etct3JHFFHA.2032@.tk2msftngp13.phx.gbl...
join
example,
> if
> provide
dbo.Sales.
>|||Hi,
Just to confirm, your code works perfectly for me. Thanks again.
Richard
"Richard Mueller [MVP]" <rlmueller-NOSPAM@.ameritech.NOSPAM.net> wrote in
message news:O8NbIERFFHA.1392@.tk2msftngp13.phx.gbl...
> The database does not belong to me, but to the customer. I'm trying to get
> my code out of the customer's database. Also, if I need to revise the
View,
> I must code a utility to modify the View in the customer's database. Any
> other change can be implemented by building a new dll. I understand that
> it's partly a philosophical thing.
> Your post indicates that I can join a table that is created in the
> parenthesis. I like that idea and will try it. Thanks a lot.
> Richard
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OLNkkHMFFHA.3336@.TK2MSFTNGP10.phx.gbl...
> join
> example,
> dbo.Sales.
>
Replace view by join
Hi all, ID Name 10 Bill 11 Frank 12 Carl 13 Rita 14 Jan 15 Bonny 16 Bart 17 George 18 Ann ID ClientID FieldName FieldDataString FieldDataInt FieldDataDate FieldDateBoolean 2 10 CustomerNr g146 4 11 CustomerNr g121 5 12 CustomerNr g147 6 13 CustomerNr g236 7 15 CustomerNr g245 9 10 Dog yes 10 11 Dog No 10 12 Dog yes 10 13 Dog No 10 15 Dog yes ID Name CustomerNr Dog 10 Bill g146 yes 11 Frank g121 No 12 Carl g147 yes 13 Rita g236 No 14 Jan 15 Bonny g245 yes 16 Bart 17 George 18 Ann ID CustomerNr Dog 10 g146 yes 11 g121 No 12 g147 yes 13 g236 No 15 g245 yes
-- -- --
10 g146 yes
11 g121 No
12 g147 yes
13 g236 No
15 g245 yes
I have a list of Clients like:
Table_Client
Now I want to ad some variable data to each client, so I have created a second table like:
Table_Client_Data
Now I want to have the next table as result in one query:
First I have made a VIEW to create, I had used a inner Join
SELECT Table_Client.ID,
Table_Client_Data.FieldDataString AS CustomerNr,
Table_Client_Data_1.FieldDataString AS Dog
FROM Table_Client_Data
INNER JOIN
Table_Client_Data ON Table_Client.ID = Table_Client_Data.ClientID
INNER JOIN
Table_Client_Data as Table_Client_Data_1 ON Table_Client.ID = Table_Client_Data_1.ClientID
WHERE (Table_Client_Data.FieldName = 'CustomerNr') AND (Table_Client_Data_1.FieldName = 'Dog')
View_CliuentData
Now I join View_CliuentData with Table_Client and I have the right result.
Now my question,
Is there any way to skip the View an do this all in a join. I have tried several things but ... no result.
Tks Bart
Hi bart,
I′m thinking about your problem, and one way to solve this problem, are you using the temporary tables.
All data are copied to temporary tables and you make a join with this object.
The best thing about this, are no necessary physical objects stored in bank.
|||Hello,I'm not using temporary tables.
But will this table be regenerated every time I do a request?
If yes: this will not be so performant as a view I guess.
Is there no way to do this in one query.
Gr Bart
|||Would it not be more extensible and performant to use a single XML type to represent your data? It would still be searchable, fields would be stored by their appropriate type, and association would be explicit while extensibility of the type is guaranteed generally by the XML specification.|||
First, let me say that is a very strange design, and one that will prove to continually be a problem. As with most denormalized databases, maintenance and scaling could prove to be a nightmare.
However, this suggestion provides your desired results from a single query.
Code Snippet
SET NOCOUNT ON
DECLARE @.Clients table
( [ID] int,
[Name] varchar(20)
)
INSERT INTO @.Clients VALUES ( 10, 'Bill' )
INSERT INTO @.Clients VALUES ( 11, 'Frank' )
INSERT INTO @.Clients VALUES ( 12, 'Carl' )
INSERT INTO @.Clients VALUES ( 13, 'Rita' )
INSERT INTO @.Clients VALUES ( 14, 'Jan' )
INSERT INTO @.Clients VALUES ( 15, 'Bonny' )
INSERT INTO @.Clients VALUES ( 16, 'Bart' )
INSERT INTO @.Clients VALUES ( 17, 'George' )
INSERT INTO @.Clients VALUES ( 18, 'Ann' )
DECLARE @.ClientsData table
( [ID] int,
ClientID varchar(10),
FieldName varchar(10),
FieldStr varchar(20),
FieldInt int,
FieldDate datetime,
FieldBool bit
)
INSERT INTO @.ClientsData VALUES ( 2, 10, 'CustomerNr', 'g146', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 4, 11, 'CustomerNr', 'g121', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 5, 12, 'CustomerNr', 'g147', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 6, 13, 'CustomerNr', 'g236', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 7, 15, 'CustomerNr', 'g245', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 9, 10, 'Dog', 'yes', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 10, 11, 'Dog', 'No', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 10, 12, 'Dog', 'yes', NULL, '',NULL )
INSERT INTO @.ClientsData VALUES ( 10, 13, 'Dog', 'No', NULL, '', NULL )
INSERT INTO @.ClientsData VALUES ( 10, 15, 'Dog', 'yes', NULL, '', NULL )
SELECT
c.[ID],
CustomerNr = CASE cd1.FieldName WHEN 'CustomerNr' THEN cd1.FieldStr END,
Dog = CASE cd2.FieldName WHEN 'Dog' THEN cd2.FieldStr END
FROM @.Clients c
JOIN @.ClientsData cd1
ON c.[ID] = cd1.ClientID
AND cd1.FieldName = 'CustomerNr'
JOIN @.ClientsData cd2
ON c.[ID] = cd2.ClientID
AND cd2.FieldName = 'Dog'
-- -- --
10 g146 yes
11 g121 No
12 g147 yes
13 g236 No
15 g245 yes
Friday, March 23, 2012
Replace SQL view on merge
I have a merge publication that I need to update a published view. I have
tried the following but does not work:
sp_droparticle
drop view
create view
I need to know if I can do this process without being in EM. Thanks.
David
IIRC you can use sp_addscriptexec to do this for subscribers deployed via
UNCs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:u7HXevuFGHA.3684@.TK2MSFTNGP14.phx.gbl...
>I have a merge publication that I need to update a published view. I have
>tried the following but does not work:
> sp_droparticle
> drop view
> create view
> I need to know if I can do this process without being in EM. Thanks.
> David
>
|||I found documentation for sp_dropmergearticle and sp_addmergearticle. I
tried them as follows in EM and it worked. Can I do this in a single
script file also? Thanks.
exec sp_dropmergearticle ......
drop view ...
create view ...
exec sp_addmergearticle ......
I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
operations above. Then I issued an "exec sp_start_job ...." to run the
snapshot agent.
Does this seem like the correct way to do what I want to do?
David
*** Sent via Developersdex http://www.codecomments.com ***
|||After I tried the script sequence (and it worked on the Publisher) I tried
to synch with a subscriber and got the following error:
The schema script
'\\LIFEDEVTEST\E$\Snapshots\unc\LIFEDEVTEST_MCFIDa ta_MCFIDataPub\20060111144516\vw_BillingDetail_176 7.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Cannot drop the view 'dbo.vw_BillingDetail' because it is being used for
replication.
(Source: LIFETIMEANTEC (Data source); Error number: 3724)
------
Any ideas why this is occurring? Thanks.
David
"David" <daman@.lifetime.com> wrote in message
news:OwkGNLvFGHA.3684@.TK2MSFTNGP14.phx.gbl...
> I found documentation for sp_dropmergearticle and sp_addmergearticle. I
> tried them as follows in EM and it worked. Can I do this in a single
> script file also? Thanks.
> exec sp_dropmergearticle ......
> drop view ...
> create view ...
> exec sp_addmergearticle ......
> I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
> operations above. Then I issued an "exec sp_start_job ...." to run the
> snapshot agent.
> Does this seem like the correct way to do what I want to do?
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||David,
I'd use a separate snapshot publication for these views, as you are
currently reinitializing the whole set of articles inc data when a view
changes which is a bit of an overkill. Also, when you change a view, it's
best to use alter view rather than drop and create - that way you'll be able
to keep the permissions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I wasn't aware you could have separate publications for the same database.
Would I then remove the views and stored procs from the current publication
and then create a 2nd one with just the views and stored procs? That sounds
really slick.
Since the subscribers are laptops, I assume I would need to create new
publication synchs on them also? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23wAvlvFGHA.1760@.TK2MSFTNGP10.phx.gbl...
> David,
> I'd use a separate snapshot publication for these views, as you are
> currently reinitializing the whole set of articles inc data when a view
> changes which is a bit of an overkill. Also, when you change a view, it's
> best to use alter view rather than drop and create - that way you'll be
> able to keep the permissions.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
the setup you describe is exactly how I do it. You'll need separate
subscriptions it's true, but the versatility is worth it. Actually I have a
separate publication for each programming object type - sps, views and udfs.
Another advantage is that a problem in one publication doesn't affect the
others (use independant distribution agents).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||And by "independant distribution agents" do you mean creating separate
distributors? Currently the distributor is on the same server.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23f25l0vFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> David,
> the setup you describe is exactly how I do it. You'll need separate
> subscriptions it's true, but the versatility is worth it. Actually I have
> a separate publication for each programming object type - sps, views and
> udfs. Another advantage is that a problem in one publication doesn't
> affect the others (use independant distribution agents).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
not a different distributor - in fact this is not possible to another
publication from the same publisher. What I mean is the option on the
subscription options tab - to 'Use a distribution agent that is
independant....'. This'll isolate the jobs entirely.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, but I cannot find this in the Subscription Options tab. I went into
Publisher properties and found the tab but there is no checkbox with that
name on it. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e4dewY1FGHA.3984@.TK2MSFTNGP14.phx.gbl...
> David,
> not a different distributor - in fact this is not possible to another
> publication from the same publisher. What I mean is the option on the
> subscription options tab - to 'Use a distribution agent that is
> independant....'. This'll isolate the jobs entirely.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
tried the following but does not work:
sp_droparticle
drop view
create view
I need to know if I can do this process without being in EM. Thanks.
David
IIRC you can use sp_addscriptexec to do this for subscribers deployed via
UNCs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:u7HXevuFGHA.3684@.TK2MSFTNGP14.phx.gbl...
>I have a merge publication that I need to update a published view. I have
>tried the following but does not work:
> sp_droparticle
> drop view
> create view
> I need to know if I can do this process without being in EM. Thanks.
> David
>
|||I found documentation for sp_dropmergearticle and sp_addmergearticle. I
tried them as follows in EM and it worked. Can I do this in a single
script file also? Thanks.
exec sp_dropmergearticle ......
drop view ...
create view ...
exec sp_addmergearticle ......
I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
operations above. Then I issued an "exec sp_start_job ...." to run the
snapshot agent.
Does this seem like the correct way to do what I want to do?
David
*** Sent via Developersdex http://www.codecomments.com ***
|||After I tried the script sequence (and it worked on the Publisher) I tried
to synch with a subscriber and got the following error:
The schema script
'\\LIFEDEVTEST\E$\Snapshots\unc\LIFEDEVTEST_MCFIDa ta_MCFIDataPub\20060111144516\vw_BillingDetail_176 7.sch'
could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
------
Cannot drop the view 'dbo.vw_BillingDetail' because it is being used for
replication.
(Source: LIFETIMEANTEC (Data source); Error number: 3724)
------
Any ideas why this is occurring? Thanks.
David
"David" <daman@.lifetime.com> wrote in message
news:OwkGNLvFGHA.3684@.TK2MSFTNGP14.phx.gbl...
> I found documentation for sp_dropmergearticle and sp_addmergearticle. I
> tried them as follows in EM and it worked. Can I do this in a single
> script file also? Thanks.
> exec sp_dropmergearticle ......
> drop view ...
> create view ...
> exec sp_addmergearticle ......
> I had to specify @.force_invalidate_snapshot = 1 on the 1st and last
> operations above. Then I issued an "exec sp_start_job ...." to run the
> snapshot agent.
> Does this seem like the correct way to do what I want to do?
> David
>
> *** Sent via Developersdex http://www.codecomments.com ***
|||David,
I'd use a separate snapshot publication for these views, as you are
currently reinitializing the whole set of articles inc data when a view
changes which is a bit of an overkill. Also, when you change a view, it's
best to use alter view rather than drop and create - that way you'll be able
to keep the permissions.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul,
I wasn't aware you could have separate publications for the same database.
Would I then remove the views and stored procs from the current publication
and then create a 2nd one with just the views and stored procs? That sounds
really slick.
Since the subscribers are laptops, I assume I would need to create new
publication synchs on them also? Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23%23wAvlvFGHA.1760@.TK2MSFTNGP10.phx.gbl...
> David,
> I'd use a separate snapshot publication for these views, as you are
> currently reinitializing the whole set of articles inc data when a view
> changes which is a bit of an overkill. Also, when you change a view, it's
> best to use alter view rather than drop and create - that way you'll be
> able to keep the permissions.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
the setup you describe is exactly how I do it. You'll need separate
subscriptions it's true, but the versatility is worth it. Actually I have a
separate publication for each programming object type - sps, views and udfs.
Another advantage is that a problem in one publication doesn't affect the
others (use independant distribution agents).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||And by "independant distribution agents" do you mean creating separate
distributors? Currently the distributor is on the same server.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:%23f25l0vFGHA.3936@.TK2MSFTNGP12.phx.gbl...
> David,
> the setup you describe is exactly how I do it. You'll need separate
> subscriptions it's true, but the versatility is worth it. Actually I have
> a separate publication for each programming object type - sps, views and
> udfs. Another advantage is that a problem in one publication doesn't
> affect the others (use independant distribution agents).
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||David,
not a different distributor - in fact this is not possible to another
publication from the same publisher. What I mean is the option on the
subscription options tab - to 'Use a distribution agent that is
independant....'. This'll isolate the jobs entirely.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, but I cannot find this in the Subscription Options tab. I went into
Publisher properties and found the tab but there is no checkbox with that
name on it. Thanks.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e4dewY1FGHA.3984@.TK2MSFTNGP14.phx.gbl...
> David,
> not a different distributor - in fact this is not possible to another
> publication from the same publisher. What I mean is the option on the
> subscription options tab - to 'Use a distribution agent that is
> independant....'. This'll isolate the jobs entirely.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
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
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!
>
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!
>
Wednesday, March 21, 2012
REPLACE Arguments
Hi,
I'm trying to create a view that shows columns based on user access level. I would like to be able to test for different values without having to add a REPLACE command for each value.
For example,
My UserLevelID can be 1, 2, 3, 4 or 5. 5 is an Officer level so I have the following command:
REPLACE(m.UserLevelID, 5, 'Yes'') AS 'Officer',
The problem is that I want the field to be blank for this column if the value is 1, 2, 3 or 4. I've tried the following, but none work.
REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), IN(1,2,3,4), '') AS 'Officer'
REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), NOT 5, '') AS 'Officer'
REPLACE(REPLACE(m.UserLevelID, 5, 'Yes''), < 5, '') AS 'Officer'
Any suggestions? I know I can add a REPLACE function five times, but I have a few more uses for this so I would like to find the easiest method.Thanks,
Lee
not context or sample DDL or data but how about something like
SELECT
CASE CAST(m.UserLevelID AS varchar(10))
WHEN '5' THEN 'Officer'
ELSE ''
END
|||If you are going to reuse it, a more compact form is something like:
substring (' Yes', 1+3*(UserLevelId/5), 3)
as in the example:
|||declare @.test table ( UserLevelId integer )
insert into @.test values (1)
insert into @.test values (2)
insert into @.test values (3)
insert into @.test values (4)
insert into @.test values (5)select UserLevelId,
substring (' Yes', 1+3*(UserLevelId/5), 3) as Indicator
from @.test-- UserLevelId Indicator
-- --
-- 1
-- 2
-- 3
-- 4
-- 5 Yes
You could have a small table with the UserLevedID and the replacement values, and then JOIN against that table. If you have several such 'replacements' (or lookups as they are often called), then you could add an additional column to specify the 'group' of replacements.
SET NOCOUNT ON
CREATE TABLE MyTable
( LookupGroup int,
UserLevelID int,
UserLevel varchar(20),
)
GO
A lot better and more robust than attempting to 'hard-code' a potentially changing list of values.
Subscribe to:
Posts (Atom)