I have a query that has serveral Left Joins. Obviously when there are no
results in the right tables a value of NULL is returned. When creating an
MS Reporting Services report, the tables seem to ignore the NULL values
(because they aren't recognized as real values I assume). How do I alter my
query to replace a NULL value with an actually value, such as 999 so I can
count the "NULL" results?
I appreciate any help.
Thankstry ISNULL function
ISNULL(<fieldname>, 999) AS SomeThing
"Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>I have a query that has serveral Left Joins. Obviously when there are no
>results in the right tables a value of NULL is returned. When creating an
>MS Reporting Services report, the tables seem to ignore the NULL values
>(because they aren't recognized as real values I assume). How do I alter
>my query to replace a NULL value with an actually value, such as 999 so I
>can count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Thanks for the reply SharkSpeed. That makes sense, but where would I put
it?
"SharkSpeed" <sharkspeedtr@.yahoo.com> wrote in message
news:eqqHn8e9FHA.1020@.TK2MSFTNGP15.phx.gbl...
> try ISNULL function
> ISNULL(<fieldname>, 999) AS SomeThing
>
> "Amon Borland" <AmonBorland@.+nospam+gmail.com>, haber iletisinde unlar
> yazd:OOLRV1e9FHA.1420@.TK2MSFTNGP09.phx.gbl...
>|||Hi Amon
Have you tried the ISNULL([ColumnName], [NewValue]) function?
Lucas
"Amon Borland" wrote:
> I have a query that has serveral Left Joins. Obviously when there are no
> results in the right tables a value of NULL is returned. When creating an
> MS Reporting Services report, the tables seem to ignore the NULL values
> (because they aren't recognized as real values I assume). How do I alter
my
> query to replace a NULL value with an actually value, such as 999 so I can
> count the "NULL" results?
> I appreciate any help.
> Thanks
>
>|||Lucas, where would I use this at. In the Select or after the table in the
join?
Thanks
"Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote in
message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
> Hi Amon
> Have you tried the ISNULL([ColumnName], [NewValue]) function?
> Lucas
> "Amon Borland" wrote:
>|||in the Select I assume..
SELECT a, b, n FROM table LEFT JOIN SELECT x, y, z FROM table2 ON ...
becomes
SELECT a, b, ISNULL(n, 999) AS n FROM table LEFT JOIN SELECT x, y, ISNULL(z,
999) FROM table2 ...
"Amon Borland" <AmonBorland@.+nospam+gmail.com> wrote in message
news:eZMs3Mf9FHA.220@.TK2MSFTNGP14.phx.gbl...
> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
> in message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>|||You can use it on the Select part of your SQL Statement. For example:
SELECT [ColumnName1], ISNULL([ColumnName2], [NewValue])
FROM [TableName]
Lucas
"Amon Borland" wrote:
> Lucas, where would I use this at. In the Select or after the table in the
> join?
> Thanks
> "Lucas Kartawidjaja" <Lucas Kartawidjaja@.discussions.microsoft.com> wrote
in
> message news:9EABF668-2E78-4445-B5C0-8505B1298B82@.microsoft.com...
>
>
Showing posts with label query. Show all posts
Showing posts with label query. Show all posts
Friday, March 30, 2012
Replacing Null values in query
Hello,
I'm using the query wizard in VB.net to write a query for SQL CE. I want to replace null values with text. I expected the COALESCE function to do this but I get an error message saying its not a valid function. This is a sample.
Select COALESCE(table.Name,'No Name') as Name from table
Any help appreciated
Thanks
Wouldn't ISNULL() do the trick for you?|||I'm connected to SQL compact. isnull() will only return a logical value. NVL doesn't work in SQL compact and when I use coalesce the editor bangs square brackets around it and returns an error message|||
You can use something like
SELECT CASE WHEN c1 IS NULL THEN 'No name' ELSE c1 END AS EXPR1
FROM t1
COALESCE not being recognized by querydesigner looks like a bug. I'll log it. Thanks for reporting!
|||
Pragya Agarwal [MSFT] wrote:
COALESCE not being recognized by querydesigner looks like a bug. I'll log it. Thanks for reporting!
I checked in the newer Visual Studio 'Orcas' builds and this bug has already been fixed :- ).
|||Many thanks for your help
replacing cursors with sql query
i have written a cursor to select records in a sequence for processing but i think the performance is not upto the mark.i have read in many places that sql cursor should not be used.but i am unable to find a substitute for cursor.could you help me out with this..
this is the cursor we are using:
CREATE proc sp_process_cdr
as
declare @.last_call_time datetime
set @.last_call_time=(select max(calltime_gmt) from processed_cdr)
declare process_cdr cursor
read_only
for
select srcip,username,callto,calltime,duration from rawcdr
where calltime>@.last_call_time
declare
@.gatewayip varchar(50),
@.username varchar (50),
@.callto varchar(100),
@.calltime datetime,
@.duration float,
@.accountid varchar(50),
@.subscriberid varchar(10),
@.cost money,
@.country varchar(100)
open process_cdr
fetch next from process_cdr into @.gatewayip,@.username,@.callto,@.calltime,@.duration
while(@.@.fetch_status<>-1)
begin
if(@.@.fetch_status<>-2)
begin
set @.accountid=''
set @.subscriberid=''
set @.country=''
set @.cost=0
if(charindex('00',@.callto)=5)
begin
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+3,50)
end
else
if(charindex('011',@.callto)=5)
begin
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+4,50)
end
else
set @.callto=substring(stuff(@.callto,charindex('@.',@.callto),50,''),charindex(':',@.callto)+1,50)
exec sp_process_call @.gatewayip,@.username,@.duration,@.callto,@.accountid output,@.subscriberid output,@.country output,@.cost output
insert into processed_cdr
select @.accountid,@.subscriberid,@.gatewayip,@.username,@.callto,@.country,@.calltime,@.duration,@.cost
end
fetch next from process_cdr into @.gatewayip,@.username,@.callto,@.calltime,@.duration
end
close process_cdr
deallocate process_cdr
GO
i dont have a unique column in my table.
YOu are executing a stored procedure within your code, unless that you can′t change your stored procedure, you can′t keep away from that cursor (not touching the solution that you could put out some statement and execute them froma temporary table one by one, which would be nearly the same performance). Anyway you shouldn′t name your procedure with a "sp_" prefix unless these is predefined for master database procs and this will lead to performance (due to recompiling) issues.
HTH, Jens Suessmeyer.
|||Dear Vignesh, below is an example of how to do update using query rather than cursor.
UPDATE t1
SET t1.column4 = t2.column4, t1.column5 = t3.column5
FROM table1 t1
INNER JOIN table2 t2 ON t1.column1 = t2.column1
INNER JOIN table3 t3 on t1.column2 = t3.column2
WHERE t1.column3 >= t2.column3 AND t3.column3 <> 0
Vincent
Wednesday, March 28, 2012
Replacement Root Query string
anyone can tell me how it is used it in Reportviewer to render
drillthrough links in the same page. I have been experimenting for a
while. would like to know if anything i need to take care of
thanx
raviHere's a thread on this topic:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
> anyone can tell me how it is used it in Reportviewer to render
> drillthrough links in the same page. I have been experimenting for a
> while. would like to know if anything i need to take care of
> thanx
> ravi
>|||Building on the thread I linked below...
In the code behind the page that contains ReportViewer, you need to set the
ReplacementRoot property, something like this:
reportView.ReplacementRoot = Protocol +
Request.ServerVariables["SERVER_NAME"] + Port + Request.FilePath +
"?report=";
You also have to add a bit more code to strip out extra rc: parameters that
build up as the user clicks on links. I hope that makes sense.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uWnN6inHFHA.2620@.tk2msftngp13.phx.gbl...
> Here's a thread on this topic:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
> news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
>> anyone can tell me how it is used it in Reportviewer to render
>> drillthrough links in the same page. I have been experimenting for a
>> while. would like to know if anything i need to take care of
>> thanx
>> ravi
>|||Hello Jeff,
Thanks for the reply. And I will make sure I dont create multiple
threads of the same topic.
Sadly, the problem still exists. I havent had time to work much on the
interface for the past week or so. I tried all you have said and also
looked at your past posts regarding this topic. I am send the code I
have regarding this whole thing. If you can tell where the problem is i
will appreciate very much.
I have a treeview control, which uses the ListChildren method of RS web
service to get all the reports listed as a tree. When I click on a
report (or rather the selected node changes) the following event is
fired.
BEGIN SUB---
Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
TreeView1.SelectedIndexChange
Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
curnode = TreeView1.GetNodeFromIndex(e.NewNode)
Xflag = True
ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
ReportViewer1.ReportPath = curnode.ID
ReportViewer1.Toolbar = ReportViewer.multiState.True
ReportViewer1.Zoom = "95"
ReportViewer1.ReplacementRoot =Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
ReportViewer1.ReportPath =ReportViewer1.ReportPath.Substring(0,
ReportViewer1.ReportPath.IndexOf("&rc"))
End If
ReportViewer1.Visible = True
END SUB.--
And this is the code i use to populate the Treeview will loading the
default page
BEGIN SUB--
Private Sub LoadSampleReports()
Dim rs As New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
For Each item In rs.ListChildren("/PGR1", False)
If item.Type = ItemTypeEnum.Report Then
newNode = New Microsoft.Web.UI.WebControls.TreeNode
newNode.Text = item.Name
newNode.ID = item.Path
newNode.Type = item.Type.ToString
newNode.HoverStyle.CssText ="font-size:8pt;color:dimgray;font-family:MS Sans
Serif;font-weight:bold;"
newNode.DefaultStyle.CssText = "font:MS Sans
Serif;color:Indigo; background:white;font-size:8pt"
& "/default.aspx?/PGR1/Averiguaciones" &
"&rc:LinkTarget=Myframe"
newNode.Expanded = False
newNode.Expandable =Microsoft.Web.UI.WebControls.ExpandableValue.Auto
TreeView1.Nodes.Add(newNode)
End If
Next
End Sub
END SUB---
When I click on a drillthrough link, it is opening the default page
again but without the report.
and the following is the URL associated with a drillthrough link
--
http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
--
Reportdashboard is the Web app.
Thanks very much
Ravi|||Okay, here's a tangent. If you test the reports in the report manager, does
the drill through work correctly?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ravi R" <bofobofo@.yahoo.com> wrote in message
news:1109898353.744329.74340@.z14g2000cwz.googlegroups.com...
> Hello Jeff,
> Thanks for the reply. And I will make sure I dont create multiple
> threads of the same topic.
> Sadly, the problem still exists. I havent had time to work much on the
> interface for the past week or so. I tried all you have said and also
> looked at your past posts regarding this topic. I am send the code I
> have regarding this whole thing. If you can tell where the problem is i
> will appreciate very much.
> I have a treeview control, which uses the ListChildren method of RS web
> service to get all the reports listed as a tree. When I click on a
> report (or rather the selected node changes) the following event is
> fired.
> BEGIN SUB---
> Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
> e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
> TreeView1.SelectedIndexChange
> Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
> curnode = TreeView1.GetNodeFromIndex(e.NewNode)
> Xflag = True
> ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
> ReportViewer1.ReportPath = curnode.ID
> ReportViewer1.Toolbar = ReportViewer.multiState.True
> ReportViewer1.Zoom = "95"
> ReportViewer1.ReplacementRoot => Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
> If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
> ReportViewer1.ReportPath => ReportViewer1.ReportPath.Substring(0,
> ReportViewer1.ReportPath.IndexOf("&rc"))
> End If
> ReportViewer1.Visible = True
> END SUB.--
> And this is the code i use to populate the Treeview will loading the
> default page
> BEGIN SUB--
> Private Sub LoadSampleReports()
> Dim rs As New ReportingService
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
> For Each item In rs.ListChildren("/PGR1", False)
> If item.Type = ItemTypeEnum.Report Then
> newNode = New Microsoft.Web.UI.WebControls.TreeNode
> newNode.Text = item.Name
> newNode.ID = item.Path
> newNode.Type = item.Type.ToString
> newNode.HoverStyle.CssText => "font-size:8pt;color:dimgray;font-family:MS Sans
> Serif;font-weight:bold;"
> newNode.DefaultStyle.CssText = "font:MS Sans
> Serif;color:Indigo; background:white;font-size:8pt"
> & "/default.aspx?/PGR1/Averiguaciones" &
> "&rc:LinkTarget=Myframe"
> newNode.Expanded = False
> newNode.Expandable => Microsoft.Web.UI.WebControls.ExpandableValue.Auto
> TreeView1.Nodes.Add(newNode)
> End If
> Next
> End Sub
> END SUB---
> When I click on a drillthrough link, it is opening the default page
> again but without the report.
> and the following is the URL associated with a drillthrough link
> --
> http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
> --
> Reportdashboard is the Web app.
>
> Thanks very much
> Ravi
>|||Hello Jeff,
Yes the drillthrough and the drilldown both work fine in the
ReportManager.
Thanks
Ravi|||Hello Jeff,
The following are the URLs First one from the ReportManager where the
drillthrough works. And the second one from my web app.
The reportserver is running on another machine.
http://servename/Reports/Pages/Report.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252fservername%252fReports%252fPages%252fReport.aspx%253fServerUrl%253d
------
http://localhost/Reportdashboard/default.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fServerUrl%253d
------
Thanks
Ravisql
drillthrough links in the same page. I have been experimenting for a
while. would like to know if anything i need to take care of
thanx
raviHere's a thread on this topic:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
> anyone can tell me how it is used it in Reportviewer to render
> drillthrough links in the same page. I have been experimenting for a
> while. would like to know if anything i need to take care of
> thanx
> ravi
>|||Building on the thread I linked below...
In the code behind the page that contains ReportViewer, you need to set the
ReplacementRoot property, something like this:
reportView.ReplacementRoot = Protocol +
Request.ServerVariables["SERVER_NAME"] + Port + Request.FilePath +
"?report=";
You also have to add a bit more code to strip out extra rc: parameters that
build up as the user clicks on links. I hope that makes sense.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Jeff A. Stucker" <jeff@.mobilize.net> wrote in message
news:uWnN6inHFHA.2620@.tk2msftngp13.phx.gbl...
> Here's a thread on this topic:
> http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/fc71a51d020123ed/a295a0d77da59b6c?q=replacementroot&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fq%3Dreplacementroot%26start%3D10%26&_doneTitle=Back+to+Search&&d#a295a0d77da59b6c
> --
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "RemoteDeploy" <bofobofo@.yahoo.com> wrote in message
> news:1109378507.771712.185840@.g14g2000cwa.googlegroups.com...
>> anyone can tell me how it is used it in Reportviewer to render
>> drillthrough links in the same page. I have been experimenting for a
>> while. would like to know if anything i need to take care of
>> thanx
>> ravi
>|||Hello Jeff,
Thanks for the reply. And I will make sure I dont create multiple
threads of the same topic.
Sadly, the problem still exists. I havent had time to work much on the
interface for the past week or so. I tried all you have said and also
looked at your past posts regarding this topic. I am send the code I
have regarding this whole thing. If you can tell where the problem is i
will appreciate very much.
I have a treeview control, which uses the ListChildren method of RS web
service to get all the reports listed as a tree. When I click on a
report (or rather the selected node changes) the following event is
fired.
BEGIN SUB---
Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
TreeView1.SelectedIndexChange
Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
curnode = TreeView1.GetNodeFromIndex(e.NewNode)
Xflag = True
ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
ReportViewer1.ReportPath = curnode.ID
ReportViewer1.Toolbar = ReportViewer.multiState.True
ReportViewer1.Zoom = "95"
ReportViewer1.ReplacementRoot =Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
ReportViewer1.ReportPath =ReportViewer1.ReportPath.Substring(0,
ReportViewer1.ReportPath.IndexOf("&rc"))
End If
ReportViewer1.Visible = True
END SUB.--
And this is the code i use to populate the Treeview will loading the
default page
BEGIN SUB--
Private Sub LoadSampleReports()
Dim rs As New ReportingService
rs.Credentials = System.Net.CredentialCache.DefaultCredentials
Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
For Each item In rs.ListChildren("/PGR1", False)
If item.Type = ItemTypeEnum.Report Then
newNode = New Microsoft.Web.UI.WebControls.TreeNode
newNode.Text = item.Name
newNode.ID = item.Path
newNode.Type = item.Type.ToString
newNode.HoverStyle.CssText ="font-size:8pt;color:dimgray;font-family:MS Sans
Serif;font-weight:bold;"
newNode.DefaultStyle.CssText = "font:MS Sans
Serif;color:Indigo; background:white;font-size:8pt"
& "/default.aspx?/PGR1/Averiguaciones" &
"&rc:LinkTarget=Myframe"
newNode.Expanded = False
newNode.Expandable =Microsoft.Web.UI.WebControls.ExpandableValue.Auto
TreeView1.Nodes.Add(newNode)
End If
Next
End Sub
END SUB---
When I click on a drillthrough link, it is opening the default page
again but without the report.
and the following is the URL associated with a drillthrough link
--
http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
--
Reportdashboard is the Web app.
Thanks very much
Ravi|||Okay, here's a tangent. If you test the reports in the report manager, does
the drill through work correctly?
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Ravi R" <bofobofo@.yahoo.com> wrote in message
news:1109898353.744329.74340@.z14g2000cwz.googlegroups.com...
> Hello Jeff,
> Thanks for the reply. And I will make sure I dont create multiple
> threads of the same topic.
> Sadly, the problem still exists. I havent had time to work much on the
> interface for the past week or so. I tried all you have said and also
> looked at your past posts regarding this topic. I am send the code I
> have regarding this whole thing. If you can tell where the problem is i
> will appreciate very much.
> I have a treeview control, which uses the ListChildren method of RS web
> service to get all the reports listed as a tree. When I click on a
> report (or rather the selected node changes) the following event is
> fired.
> BEGIN SUB---
> Private Sub TreeView1_SelectedIndexChange(ByVal sender As Object, ByVal
> e As Microsoft.Web.UI.WebControls.TreeViewSelectEventArgs) Handles
> TreeView1.SelectedIndexChange
> Dim curnode As Microsoft.Web.UI.WebControls.TreeNode
> curnode = TreeView1.GetNodeFromIndex(e.NewNode)
> Xflag = True
> ReportViewer1.ServerUrl = "http://serverIP/ReportServer"
> ReportViewer1.ReportPath = curnode.ID
> ReportViewer1.Toolbar = ReportViewer.multiState.True
> ReportViewer1.Zoom = "95"
> ReportViewer1.ReplacementRoot => Server.UrlEncode("http://localhost/Reportdashboard/default.aspx?Report=")
> If (ReportViewer1.ReportPath.IndexOf("&rc") > 0) Then
> ReportViewer1.ReportPath => ReportViewer1.ReportPath.Substring(0,
> ReportViewer1.ReportPath.IndexOf("&rc"))
> End If
> ReportViewer1.Visible = True
> END SUB.--
> And this is the code i use to populate the Treeview will loading the
> default page
> BEGIN SUB--
> Private Sub LoadSampleReports()
> Dim rs As New ReportingService
> rs.Credentials = System.Net.CredentialCache.DefaultCredentials
> Dim newNode As Microsoft.Web.UI.WebControls.TreeNode
> For Each item In rs.ListChildren("/PGR1", False)
> If item.Type = ItemTypeEnum.Report Then
> newNode = New Microsoft.Web.UI.WebControls.TreeNode
> newNode.Text = item.Name
> newNode.ID = item.Path
> newNode.Type = item.Type.ToString
> newNode.HoverStyle.CssText => "font-size:8pt;color:dimgray;font-family:MS Sans
> Serif;font-weight:bold;"
> newNode.DefaultStyle.CssText = "font:MS Sans
> Serif;color:Indigo; background:white;font-size:8pt"
> & "/default.aspx?/PGR1/Averiguaciones" &
> "&rc:LinkTarget=Myframe"
> newNode.Expanded = False
> newNode.Expandable => Microsoft.Web.UI.WebControls.ExpandableValue.Auto
> TreeView1.Nodes.Add(newNode)
> End If
> Next
> End Sub
> END SUB---
> When I click on a drillthrough link, it is opening the default page
> again but without the report.
> and the following is the URL associated with a drillthrough link
> --
> http://localhost/Reportdashboard/default.aspx?Report=http%3a%2f%2fserverIP%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_O2%3dAAAJ710623%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fReport%253d
> --
> Reportdashboard is the Web app.
>
> Thanks very much
> Ravi
>|||Hello Jeff,
Yes the drillthrough and the drilldown both work fine in the
ReportManager.
Thanks
Ravi|||Hello Jeff,
The following are the URLs First one from the ReportManager where the
drillthrough works. And the second one from my web app.
The reportserver is running on another machine.
http://servename/Reports/Pages/Report.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252fservername%252fReports%252fPages%252fReport.aspx%253fServerUrl%253d
------
http://localhost/Reportdashboard/default.aspx?ServerUrl=http%3a%2f%2fservername%2fReportServer%3f%252fPGR1%252fPersonalPGR%26param_1%3dAACA7113%26rs%253aParameterLanguage%3d%26rc%253aParameters%3dCollapsed%26rc%253aReplacementRoot%3dhttp%253a%252f%252flocalhost%252fReportdashboard%252fdefault.aspx%253fServerUrl%253d
------
Thanks
Ravisql
Replacement for sysprocesses dbid?
In SQLServer 2005, how can you select just the processes that are
connected to a specific database? In 2000, I could do it with this
query:
select *
from sysprocesses
where dbid = 5
BOL recommends using any of the dynamic management views
sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
as a replacement for sysprocesses, but none of these has a database or
dbid column that I can find. Am I overlooking something? How do you
do this? Thanks.select * from sys.dm_exec_requests
where database_id = 5
to just see who is using a database, who might not be running any queries,
you can use sys.dm_tran_locks
select request_session_id from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = 5
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<stavros@.mailinator.com> wrote in message
news:1161904818.742946.108590@.m7g2000cwm.googlegroups.com...
> In SQLServer 2005, how can you select just the processes that are
> connected to a specific database? In 2000, I could do it with this
> query:
> select *
> from sysprocesses
> where dbid = 5
> BOL recommends using any of the dynamic management views
> sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
> as a replacement for sysprocesses, but none of these has a database or
> dbid column that I can find. Am I overlooking something? How do you
> do this? Thanks.
>sql
connected to a specific database? In 2000, I could do it with this
query:
select *
from sysprocesses
where dbid = 5
BOL recommends using any of the dynamic management views
sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
as a replacement for sysprocesses, but none of these has a database or
dbid column that I can find. Am I overlooking something? How do you
do this? Thanks.select * from sys.dm_exec_requests
where database_id = 5
to just see who is using a database, who might not be running any queries,
you can use sys.dm_tran_locks
select request_session_id from sys.dm_tran_locks
where resource_type = 'DATABASE'
and resource_database_id = 5
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
<stavros@.mailinator.com> wrote in message
news:1161904818.742946.108590@.m7g2000cwm.googlegroups.com...
> In SQLServer 2005, how can you select just the processes that are
> connected to a specific database? In 2000, I could do it with this
> query:
> select *
> from sysprocesses
> where dbid = 5
> BOL recommends using any of the dynamic management views
> sys.dm_exec_connections, sys.dm_exec_sessions, or sys.dm_exec_requests
> as a replacement for sysprocesses, but none of these has a database or
> dbid column that I can find. Am I overlooking something? How do you
> do this? Thanks.
>sql
Replacement for SQL Query Analyser
Does anybody know if there is one in 2005.
Sure I can run the sp's but can't see how I can step through them.
Thanks
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in
news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl:
> Does anybody know if there is one in 2005.
SQL Server Management Studio (SSMS) is the replacement.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||But you cannot debug ..?
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971772D96ED48olekristianbangaas@.207.46.248 .16...
> "Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in
> news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl:
>
> SQL Server Management Studio (SSMS) is the replacement.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||>From another thread answered few time ago:
Demystifying Debugging in SQL Server 2005
http://www.windowsitpro.com/SQLServe...754/SQLServ...
4.html
How to: Debug a SQL CLR Stored Procedure
http://msdn2.microsoft.com/en-us/library/ms165051.aspx
How to: Enable SQL Server 2005 Debugging
http://msdn2.microsoft.com/en-us/library/s0fk6z6e.aspx
Creating SQL Server 2005 Objects in Managed Code
http://msdn2.microsoft.com/en-us/library/6s0s2at1.aspx
SQL Server 2005 Debugging
http://www.sqljunkies.com/WebLog/nie...8/04/3758.aspx
HTH, Jens Suessmeyer.
|||Good point,
To debug in SQL 2005 you need Visual Studio.
I have a link on my website that points to the related MSDN article
http://www.dandyman.net/sql/articles.aspx
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:%230OTxiB8FHA.1416@.TK2MSFTNGP09.phx.gbl...
> But you cannot debug ..?
>
> "Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
> news:Xns971772D96ED48olekristianbangaas@.207.46.248 .16...
>
|||In VS 2005. New Query from the Server Explorer or within a DB project.
SSMS has the same 4 pane deal, but it works different. It requires, IIRC,
that you design the query and then paste that into a .sql tab/file and run
it.
William Stacey [MVP]
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Does anybody know if there is one in 2005.
> Sure I can run the sp's but can't see how I can step through them.
> Thanks
>
Sure I can run the sp's but can't see how I can step through them.
Thanks
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in
news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl:
> Does anybody know if there is one in 2005.
SQL Server Management Studio (SSMS) is the replacement.
Ole Kristian Bangs
MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||But you cannot debug ..?
"Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
news:Xns971772D96ED48olekristianbangaas@.207.46.248 .16...
> "Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in
> news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl:
>
> SQL Server Management Studio (SSMS) is the replacement.
> --
> Ole Kristian Bangs
> MCT, MCDBA, MCDST, MCSE:Security, MCSE:Messaging
|||>From another thread answered few time ago:
Demystifying Debugging in SQL Server 2005
http://www.windowsitpro.com/SQLServe...754/SQLServ...
4.html
How to: Debug a SQL CLR Stored Procedure
http://msdn2.microsoft.com/en-us/library/ms165051.aspx
How to: Enable SQL Server 2005 Debugging
http://msdn2.microsoft.com/en-us/library/s0fk6z6e.aspx
Creating SQL Server 2005 Objects in Managed Code
http://msdn2.microsoft.com/en-us/library/6s0s2at1.aspx
SQL Server 2005 Debugging
http://www.sqljunkies.com/WebLog/nie...8/04/3758.aspx
HTH, Jens Suessmeyer.
|||Good point,
To debug in SQL 2005 you need Visual Studio.
I have a link on my website that points to the related MSDN article
http://www.dandyman.net/sql/articles.aspx
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:%230OTxiB8FHA.1416@.TK2MSFTNGP09.phx.gbl...
> But you cannot debug ..?
>
> "Ole Kristian Bangs" <olekristian.bangas@.masterminds.no> wrote in message
> news:Xns971772D96ED48olekristianbangaas@.207.46.248 .16...
>
|||In VS 2005. New Query from the Server Explorer or within a DB project.
SSMS has the same 4 pane deal, but it works different. It requires, IIRC,
that you design the query and then paste that into a .sql tab/file and run
it.
William Stacey [MVP]
"Chubbly Geezer" <chubbly_geezer@.newsgroup.nospam> wrote in message
news:OdAWjDB8FHA.2576@.TK2MSFTNGP12.phx.gbl...
> Does anybody know if there is one in 2005.
> Sure I can run the sp's but can't see how I can step through them.
> Thanks
>
Monday, March 26, 2012
Replace the First Character
Hi All,
My query is
Select ID, ProdName from Product
I would like to replace the first character from ProdName (in every row)
with *.
Please let me know how to do it.
Thanks.SELECT ID, '*'+SUBSTRING(ProdName,2,8000) as ProdName FROM Product
Razvan|||On Sun, 18 Sep 2005 02:22:26 -0700, msnews wrote:
>Hi All,
>My query is
>Select ID, ProdName from Product
>I would like to replace the first character from ProdName (in every row)
>with *.
>Please let me know how to do it.
>Thanks.
>
Hi msnews,
Here's another way:
SELECT ID, STUFF(ProdName, 1, 1, '*') FROM Product
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
My query is
Select ID, ProdName from Product
I would like to replace the first character from ProdName (in every row)
with *.
Please let me know how to do it.
Thanks.SELECT ID, '*'+SUBSTRING(ProdName,2,8000) as ProdName FROM Product
Razvan|||On Sun, 18 Sep 2005 02:22:26 -0700, msnews wrote:
>Hi All,
>My query is
>Select ID, ProdName from Product
>I would like to replace the first character from ProdName (in every row)
>with *.
>Please let me know how to do it.
>Thanks.
>
Hi msnews,
Here's another way:
SELECT ID, STUFF(ProdName, 1, 1, '*') FROM Product
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, March 23, 2012
Replace report query
I design a report template which can serve for serveral reports. The
template and parameter are same, but the difference is 'select' statment. I
have another web application to retrieve different reports. Can I
dynamically change the report query by calling report web service ? I
can't find any example in RS books online. Is there any other example ?Hi May,
can you use a stored procedure and handle the logic internally (making the
results parameter driven)? That way your report is always pointing to the
same database object.
regards
Matt
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> I design a report template which can serve for serveral reports. The
> template and parameter are same, but the difference is 'select' statment.
I
> have another web application to retrieve different reports. Can I
> dynamically change the report query by calling report web service ? I
> can't find any example in RS books online. Is there any other example ?|||This is one way of doing it. Alternatively, you could:
1. Call SetReportDefinition SOAP API to update the report query.
2. Use an expression-based query. For example, you can check User!UserId and
if it gives you the web app identity change the report query respectively.
Or, you could use parameters to control your query statement.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> Hi May,
> can you use a stored procedure and handle the logic internally (making the
> results parameter driven)? That way your report is always pointing to the
> same database object.
> regards
> Matt
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > I design a report template which can serve for serveral reports. The
> > template and parameter are same, but the difference is 'select'
statment.
> I
> > have another web application to retrieve different reports. Can I
> > dynamically change the report query by calling report web service ? I
> > can't find any example in RS books online. Is there any other example
?
>|||Is there any example ?
Thanks !!!
"Teo Lachev" wrote:
> This is one way of doing it. Alternatively, you could:
> 1. Call SetReportDefinition SOAP API to update the report query.
> 2. Use an expression-based query. For example, you can check User!UserId and
> if it gives you the web app identity change the report query respectively.
> Or, you could use parameters to control your query statement.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > Hi May,
> >
> > can you use a stored procedure and handle the logic internally (making the
> > results parameter driven)? That way your report is always pointing to the
> > same database object.
> >
> > regards
> >
> > Matt
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > I design a report template which can serve for serveral reports. The
> > > template and parameter are same, but the difference is 'select'
> statment.
> > I
> > > have another web application to retrieve different reports. Can I
> > > dynamically change the report query by calling report web service ? I
> > > can't find any example in RS books online. Is there any other example
> ?
> >
> >
>
>|||Anyone can help me ? I search this topic in the internet but there is no
related result. This problem annoy me so much and I have so many reports
that will be implemented using this approach.
Thanks a lot !!!
"May Liu" wrote:
> Is there any example ?
> Thanks !!!
> "Teo Lachev" wrote:
> > This is one way of doing it. Alternatively, you could:
> >
> > 1. Call SetReportDefinition SOAP API to update the report query.
> > 2. Use an expression-based query. For example, you can check User!UserId and
> > if it gives you the web app identity change the report query respectively.
> > Or, you could use parameters to control your query statement.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > > Hi May,
> > >
> > > can you use a stored procedure and handle the logic internally (making the
> > > results parameter driven)? That way your report is always pointing to the
> > > same database object.
> > >
> > > regards
> > >
> > > Matt
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > > I design a report template which can serve for serveral reports. The
> > > > template and parameter are same, but the difference is 'select'
> > statment.
> > > I
> > > > have another web application to retrieve different reports. Can I
> > > > dynamically change the report query by calling report web service ? I
> > > > can't find any example in RS books online. Is there any other example
> > ?
> > >
> > >
> >
> >
> >
template and parameter are same, but the difference is 'select' statment. I
have another web application to retrieve different reports. Can I
dynamically change the report query by calling report web service ? I
can't find any example in RS books online. Is there any other example ?Hi May,
can you use a stored procedure and handle the logic internally (making the
results parameter driven)? That way your report is always pointing to the
same database object.
regards
Matt
"May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> I design a report template which can serve for serveral reports. The
> template and parameter are same, but the difference is 'select' statment.
I
> have another web application to retrieve different reports. Can I
> dynamically change the report query by calling report web service ? I
> can't find any example in RS books online. Is there any other example ?|||This is one way of doing it. Alternatively, you could:
1. Call SetReportDefinition SOAP API to update the report query.
2. Use an expression-based query. For example, you can check User!UserId and
if it gives you the web app identity change the report query respectively.
Or, you could use parameters to control your query statement.
--
Hope this helps.
----
Teo Lachev, MCSD, MCT
Author: "Microsoft Reporting Services in Action"
Publisher website: http://www.manning.com/lachev
Buy it from Amazon.com: http://shrinkster.com/eq
Home page and blog: http://www.prologika.com/
----
"Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> Hi May,
> can you use a stored procedure and handle the logic internally (making the
> results parameter driven)? That way your report is always pointing to the
> same database object.
> regards
> Matt
> "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > I design a report template which can serve for serveral reports. The
> > template and parameter are same, but the difference is 'select'
statment.
> I
> > have another web application to retrieve different reports. Can I
> > dynamically change the report query by calling report web service ? I
> > can't find any example in RS books online. Is there any other example
?
>|||Is there any example ?
Thanks !!!
"Teo Lachev" wrote:
> This is one way of doing it. Alternatively, you could:
> 1. Call SetReportDefinition SOAP API to update the report query.
> 2. Use an expression-based query. For example, you can check User!UserId and
> if it gives you the web app identity change the report query respectively.
> Or, you could use parameters to control your query statement.
> --
> Hope this helps.
> ----
> Teo Lachev, MCSD, MCT
> Author: "Microsoft Reporting Services in Action"
> Publisher website: http://www.manning.com/lachev
> Buy it from Amazon.com: http://shrinkster.com/eq
> Home page and blog: http://www.prologika.com/
> ----
> "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > Hi May,
> >
> > can you use a stored procedure and handle the logic internally (making the
> > results parameter driven)? That way your report is always pointing to the
> > same database object.
> >
> > regards
> >
> > Matt
> >
> > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > I design a report template which can serve for serveral reports. The
> > > template and parameter are same, but the difference is 'select'
> statment.
> > I
> > > have another web application to retrieve different reports. Can I
> > > dynamically change the report query by calling report web service ? I
> > > can't find any example in RS books online. Is there any other example
> ?
> >
> >
>
>|||Anyone can help me ? I search this topic in the internet but there is no
related result. This problem annoy me so much and I have so many reports
that will be implemented using this approach.
Thanks a lot !!!
"May Liu" wrote:
> Is there any example ?
> Thanks !!!
> "Teo Lachev" wrote:
> > This is one way of doing it. Alternatively, you could:
> >
> > 1. Call SetReportDefinition SOAP API to update the report query.
> > 2. Use an expression-based query. For example, you can check User!UserId and
> > if it gives you the web app identity change the report query respectively.
> > Or, you could use parameters to control your query statement.
> >
> > --
> > Hope this helps.
> >
> > ----
> > Teo Lachev, MCSD, MCT
> > Author: "Microsoft Reporting Services in Action"
> > Publisher website: http://www.manning.com/lachev
> > Buy it from Amazon.com: http://shrinkster.com/eq
> > Home page and blog: http://www.prologika.com/
> > ----
> >
> > "Matt" <NoSpam:Matthew.Moran@.Computercorp.com.au> wrote in message
> > news:uceRTlLlEHA.3104@.TK2MSFTNGP14.phx.gbl...
> > > Hi May,
> > >
> > > can you use a stored procedure and handle the logic internally (making the
> > > results parameter driven)? That way your report is always pointing to the
> > > same database object.
> > >
> > > regards
> > >
> > > Matt
> > >
> > > "May Liu" <MayLiu@.discussions.microsoft.com> wrote in message
> > > news:6D30CF1B-C166-46A3-B069-BCDD32A38302@.microsoft.com...
> > > > I design a report template which can serve for serveral reports. The
> > > > template and parameter are same, but the difference is 'select'
> > statment.
> > > I
> > > > have another web application to retrieve different reports. Can I
> > > > dynamically change the report query by calling report web service ? I
> > > > can't find any example in RS books online. Is there any other example
> > ?
> > >
> > >
> >
> >
> >
Replace Query? Possible?
Is it possible to make a replace query in SQL?
So Find What, Replace With
I have a table with email addresses. But now for more than 500 people it must be changed from user@.company.nl to user@.COMPANY2.nl
Is this possible?
Thanks in advance!Sure: Check out REPLACE in BOL.|||I can't find it. Thanks anyway. But is it also possible with a query?
Can somebody give me and example? ThX!!|||From BOL:
REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.
Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
Arguments
'string_expression1'
Is the string expression to be searched. string_expression1 can be of character or binary data.
'string_expression2'
Is the string expression to try to find. string_expression2 can be of character or binary data.
'string_expression3'
Is the replacement string expression string_expression3 can be of character or binary data.
Return Types
Returns character data if string_expression (1, 2, or 3) is one of the supported character data types. Returns binary data if string_expression (1, 2, or 3) is one of the supported binary data types.
Examples
This example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Here is the result set:
----
abxxxfghixxx
(1 row(s) affected)|||ThX mate. I will try to make a good one now.|||I got it, ThX!
For example:
SELECT REPLACE(email_address, 'Pedro.nl', 'Pedro.com') from crm5.email|||Hmmm, I was to early. It doens't change anything in the colum.|||Now i got it.
This is the query i want:
Update crm5.email
SET email_address =REPLACE(email_address, 'Company.nl', 'Pedro.com')
from crm5.email|||You will have to write a update query
some thing like
update crm5.email
set email_address = REPLACE(email_address, 'Pedro.nl', 'Pedro.com')
So Find What, Replace With
I have a table with email addresses. But now for more than 500 people it must be changed from user@.company.nl to user@.COMPANY2.nl
Is this possible?
Thanks in advance!Sure: Check out REPLACE in BOL.|||I can't find it. Thanks anyway. But is it also possible with a query?
Can somebody give me and example? ThX!!|||From BOL:
REPLACE
Replaces all occurrences of the second given string expression in the first string expression with a third expression.
Syntax
REPLACE ( 'string_expression1' , 'string_expression2' , 'string_expression3' )
Arguments
'string_expression1'
Is the string expression to be searched. string_expression1 can be of character or binary data.
'string_expression2'
Is the string expression to try to find. string_expression2 can be of character or binary data.
'string_expression3'
Is the replacement string expression string_expression3 can be of character or binary data.
Return Types
Returns character data if string_expression (1, 2, or 3) is one of the supported character data types. Returns binary data if string_expression (1, 2, or 3) is one of the supported binary data types.
Examples
This example replaces the string cde in abcdefghi with xxx.
SELECT REPLACE('abcdefghicde','cde','xxx')
GO
Here is the result set:
----
abxxxfghixxx
(1 row(s) affected)|||ThX mate. I will try to make a good one now.|||I got it, ThX!
For example:
SELECT REPLACE(email_address, 'Pedro.nl', 'Pedro.com') from crm5.email|||Hmmm, I was to early. It doens't change anything in the colum.|||Now i got it.
This is the query i want:
Update crm5.email
SET email_address =REPLACE(email_address, 'Company.nl', 'Pedro.com')
from crm5.email|||You will have to write a update query
some thing like
update crm5.email
set email_address = REPLACE(email_address, 'Pedro.nl', 'Pedro.com')
REPLACE query help plz!
Hello!
I am posting this problem again. I have gotten somebody's help with the
right solution but I have one more case to work on. I would appreciate
your time and help on this.
create table #temp
(ID int, Cust varchar(80))
insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
-- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
WORKS!!!
select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
substring(Cust, charindex('DBX:', Cust) + 4,
charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
from #temp
-- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
replace only one time which is in bw Name: and DBX: and leave the other
one untouched. how do I do that?
select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
from #temp
My SQL for Col2 gives me this:
Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
Name: CLASSIFIED DBX: opqr Addr1: 1234
And I want this..
Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
Name: CLASSIFIED DBX: xyz Addr1: 9999
Name: CLASSIFIED DBX: opqr Addr1: 1234
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***Test,
Try:
SELECT STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX(' ',CUST)+1),'CLASSIFIED ')
FROM #TEMP
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23irrn3l1FHA.1852@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am posting this problem again. I have gotten somebody's help with the
> right solution but I have one more case to work on. I would appreciate
> your time and help on this.
> create table #temp
> (ID int, Cust varchar(80))
> insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
> insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
> insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
>
> -- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
> WORKS!!!
> select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
> replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
> substring(Cust, charindex('DBX:', Cust) + 4,
> charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
> from #temp
> -- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
> DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
> replace only one time which is in bw Name: and DBX: and leave the other
> one untouched. how do I do that?
> select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
> charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
> from #temp
> My SQL for Col2 gives me this:
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> And I want this..
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: xyz Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> Thanks for your help!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Jerry. It is not perfectly working:
1. Your SQL is also deleting 'Name:' which I want to keep it. But that
can be fixed by adding 'Name: ' in the SQL i.e.
SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
',CUST)+1),'CLASSIFIED ')
FROM #TEMP
2. The main issue is there can a space in the name field (it will not
always be a continuation). So, the whole name needs to be replaced with
CLASSIFIED. Your SQL is not currently handling this.
Example: Insert these two records in the #temp table:
insert into #temp values(25, 'Name: xxx yyy DBX: xxx yyy Addr1: 1234')
insert into #temp values(25, 'Name: ijk 123 DBX: aaa Addr1: 1234')
SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
',CUST)+1),'CLASSIFIED ')
FROM #TEMP
The result is:
Name: CLASSIFIED yyy DBX: xxx yyy Addr1: 1234
Name: CLASSIFIED 123 DBX: aaa Addr1: 1234
The corerct result would be:
Name: CLASSIFIED DBX: xxx yyy Addr1: 1234
Name: CLASSIFIED DBX: aaa Addr1: 1234
*** Sent via Developersdex http://www.examnotes.net ***|||Test,
Mod the code to use the : after DBX (then count back 3) for the end number
and change the start postion in the STUFF statment to ensure Name: is
retained. I just had to reboot my machine so I lost all of the code i had
for this. Let me know if you don't get it working and I'll redo and post
the solution.
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uOj4Pom1FHA.2792@.tk2msftngp13.phx.gbl...
> Thanks Jerry. It is not perfectly working:
> 1. Your SQL is also deleting 'Name:' which I want to keep it. But that
> can be fixed by adding 'Name: ' in the SQL i.e.
> SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
> ',CUST)+1),'CLASSIFIED ')
> FROM #TEMP
> 2. The main issue is there can a space in the name field (it will not
> always be a continuation). So, the whole name needs to be replaced with
> CLASSIFIED. Your SQL is not currently handling this.
> Example: Insert these two records in the #temp table:
> insert into #temp values(25, 'Name: xxx yyy DBX: xxx yyy Addr1: 1234')
> insert into #temp values(25, 'Name: ijk 123 DBX: aaa Addr1: 1234')
> SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
> ',CUST)+1),'CLASSIFIED ')
> FROM #TEMP
> The result is:
> Name: CLASSIFIED yyy DBX: xxx yyy Addr1: 1234
> Name: CLASSIFIED 123 DBX: aaa Addr1: 1234
> The corerct result would be:
> Name: CLASSIFIED DBX: xxx yyy Addr1: 1234
> Name: CLASSIFIED DBX: aaa Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Jerry, Thanks for all your help! I think I'll make it work.
*** Sent via Developersdex http://www.examnotes.net ***|||Try this out...
select
substring(Name,1,patindex('%DBX:%',Name)
-1)+
replace(substring(Name,patindex('%DBX:%'
,Name),len(Name)),
substring(Name,patindex('%DBX:%',Name)+5
,(charindex('Addr1:',Name,1)-patinde
x('%DBX:%',Name)-6)),
'APPLE')
as Output
from #temp
Regards
sudarshan
"Test Test" wrote:
> Hello!
> I am posting this problem again. I have gotten somebody's help with the
> right solution but I have one more case to work on. I would appreciate
> your time and help on this.
> create table #temp
> (ID int, Cust varchar(80))
> insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
> insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
> insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
>
> -- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
> WORKS!!!
> select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
> replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
> substring(Cust, charindex('DBX:', Cust) + 4,
> charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
> from #temp
> -- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
> DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
> replace only one time which is in bw Name: and DBX: and leave the other
> one untouched. how do I do that?
> select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
> charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
> from #temp
> My SQL for Col2 gives me this:
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> And I want this..
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: xyz Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> Thanks for your help!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>
I am posting this problem again. I have gotten somebody's help with the
right solution but I have one more case to work on. I would appreciate
your time and help on this.
create table #temp
(ID int, Cust varchar(80))
insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
-- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
WORKS!!!
select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
substring(Cust, charindex('DBX:', Cust) + 4,
charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
from #temp
-- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
replace only one time which is in bw Name: and DBX: and leave the other
one untouched. how do I do that?
select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
from #temp
My SQL for Col2 gives me this:
Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
Name: CLASSIFIED DBX: opqr Addr1: 1234
And I want this..
Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
Name: CLASSIFIED DBX: xyz Addr1: 9999
Name: CLASSIFIED DBX: opqr Addr1: 1234
Thanks for your help!
*** Sent via Developersdex http://www.examnotes.net ***Test,
Try:
SELECT STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX(' ',CUST)+1),'CLASSIFIED ')
FROM #TEMP
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23irrn3l1FHA.1852@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I am posting this problem again. I have gotten somebody's help with the
> right solution but I have one more case to work on. I would appreciate
> your time and help on this.
> create table #temp
> (ID int, Cust varchar(80))
> insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
> insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
> insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
>
> -- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
> WORKS!!!
> select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
> replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
> substring(Cust, charindex('DBX:', Cust) + 4,
> charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
> from #temp
> -- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
> DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
> replace only one time which is in bw Name: and DBX: and leave the other
> one untouched. how do I do that?
> select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
> charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
> from #temp
> My SQL for Col2 gives me this:
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> And I want this..
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: xyz Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> Thanks for your help!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks Jerry. It is not perfectly working:
1. Your SQL is also deleting 'Name:' which I want to keep it. But that
can be fixed by adding 'Name: ' in the SQL i.e.
SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
',CUST)+1),'CLASSIFIED ')
FROM #TEMP
2. The main issue is there can a space in the name field (it will not
always be a continuation). So, the whole name needs to be replaced with
CLASSIFIED. Your SQL is not currently handling this.
Example: Insert these two records in the #temp table:
insert into #temp values(25, 'Name: xxx yyy DBX: xxx yyy Addr1: 1234')
insert into #temp values(25, 'Name: ijk 123 DBX: aaa Addr1: 1234')
SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
',CUST)+1),'CLASSIFIED ')
FROM #TEMP
The result is:
Name: CLASSIFIED yyy DBX: xxx yyy Addr1: 1234
Name: CLASSIFIED 123 DBX: aaa Addr1: 1234
The corerct result would be:
Name: CLASSIFIED DBX: xxx yyy Addr1: 1234
Name: CLASSIFIED DBX: aaa Addr1: 1234
*** Sent via Developersdex http://www.examnotes.net ***|||Test,
Mod the code to use the : after DBX (then count back 3) for the end number
and change the start postion in the STUFF statment to ensure Name: is
retained. I just had to reboot my machine so I lost all of the code i had
for this. Let me know if you don't get it working and I'll redo and post
the solution.
HTH
Jerry
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uOj4Pom1FHA.2792@.tk2msftngp13.phx.gbl...
> Thanks Jerry. It is not perfectly working:
> 1. Your SQL is also deleting 'Name:' which I want to keep it. But that
> can be fixed by adding 'Name: ' in the SQL i.e.
> SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
> ',CUST)+1),'CLASSIFIED ')
> FROM #TEMP
> 2. The main issue is there can a space in the name field (it will not
> always be a continuation). So, the whole name needs to be replaced with
> CLASSIFIED. Your SQL is not currently handling this.
> Example: Insert these two records in the #temp table:
> insert into #temp values(25, 'Name: xxx yyy DBX: xxx yyy Addr1: 1234')
> insert into #temp values(25, 'Name: ijk 123 DBX: aaa Addr1: 1234')
> SELECT 'Name: ' + STUFF(CUST,1,CHARINDEX(' ',CUST,CHARINDEX('
> ',CUST)+1),'CLASSIFIED ')
> FROM #TEMP
> The result is:
> Name: CLASSIFIED yyy DBX: xxx yyy Addr1: 1234
> Name: CLASSIFIED 123 DBX: aaa Addr1: 1234
> The corerct result would be:
> Name: CLASSIFIED DBX: xxx yyy Addr1: 1234
> Name: CLASSIFIED DBX: aaa Addr1: 1234
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Jerry, Thanks for all your help! I think I'll make it work.
*** Sent via Developersdex http://www.examnotes.net ***|||Try this out...
select
substring(Name,1,patindex('%DBX:%',Name)
-1)+
replace(substring(Name,patindex('%DBX:%'
,Name),len(Name)),
substring(Name,patindex('%DBX:%',Name)+5
,(charindex('Addr1:',Name,1)-patinde
x('%DBX:%',Name)-6)),
'APPLE')
as Output
from #temp
Regards
sudarshan
"Test Test" wrote:
> Hello!
> I am posting this problem again. I have gotten somebody's help with the
> right solution but I have one more case to work on. I would appreciate
> your time and help on this.
> create table #temp
> (ID int, Cust varchar(80))
> insert into #temp values(23, 'Name: abcd DBX: abcdefgh Addr1: 1234')
> insert into #temp values(27, 'Name: xyz DBX: xyz Addr1: 9999')
> insert into #temp values(25, 'Name: lmn DBX: opqr Addr1: 1234')
>
> -- Case 1: Put CENSORED between DBX: and Addr1: in the Cust column...IT
> WORKS!!!
> select Col1 = Left(Cust, charindex('DBX:', Cust) + 3) +
> replace(substring(Cust, charindex('DBX:', Cust) + 4, LEN(Cust)),
> substring(Cust, charindex('DBX:', Cust) + 4,
> charindex('Addr1:', Cust) - charindex('DBX:', Cust)-4), ' CENSORED ')
> from #temp
> -- Case 2: Put CLASSIFIED between Name: and DBX: in the Cust column..IT
> DOES NOT WORK for 'xyz' case bc 'xyz is in two places. I need it to
> replace only one time which is in bw Name: and DBX: and leave the other
> one untouched. how do I do that?
> select Col2 = replace(Cust, substring(Cust, charindex(':', Cust)+2,
> charindex('DBX:', Cust)- charindex(':', Cust)-2), ' CLASSIFIED ')
> from #temp
> My SQL for Col2 gives me this:
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: CLASSIFIED Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> And I want this..
> Name: CLASSIFIED DBX: abcdefgh Addr1: 1234
> Name: CLASSIFIED DBX: xyz Addr1: 9999
> Name: CLASSIFIED DBX: opqr Addr1: 1234
> Thanks for your help!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>
Replace Query
I have 4 records in my table having a field with bit datatype.
use an SQL update
update dbo.tblYourTableName
set YourFieldName = case YourFieldName when 0 then 1 else 0 end
Jim :)sql
I want to change the values of that in one replace statment
without giving where condition.(value 0 by 1 and 1 by 0)
Recd 1) 0
2) 0
3) 1
4) 1
Quote:
Originally Posted by bipinskulkarni
I have 4 records in my table having a field with bit datatype.
I want to change the values of that in one replace statment
without giving where condition.(value 0 by 1 and 1 by 0)
Recd 1) 0
2) 0
3) 1
4) 1
use an SQL update
update dbo.tblYourTableName
set YourFieldName = case YourFieldName when 0 then 1 else 0 end
Jim :)sql
Replace negative values
hi,
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maartenYou can modify select statement to something like this-
select item, qty, value=
case
when value < 0 then '0' else value
end
from test
Roshmi Choudhurysql
In the result of a function in my query, there are negative numbers.
How do I replace them with a 0 or is there a function like ISNULL that replaces the values that are negative?
thanks,
maartenYou can modify select statement to something like this-
select item, qty, value=
case
when value < 0 then '0' else value
end
from test
Roshmi Choudhurysql
Replace Multiple LIKEs
I have a query below that performs horribly:
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
>
do this:
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
>
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
>
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')|||I need a way to improve performace of the query with the existing table
There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.
>>
>Normalize tblStuff by removing column Keyword and replacing it with a
>second table tblStuffKeyword. For instance, instead of
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50),
> Keyword text
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (1, 'A', 'B', '123456,321654,987987,345987')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (2, 'C', 'D', '')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (3, 'E', 'F', '123456,987987')
>>
>do this:
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50)
>)
>>
>create table tblStuffKeyword (
> StuffKey int,
> Keyword varchar(50)
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (1, 'A', 'B')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (2, 'C', 'D')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (3, 'E', 'F')
>>
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '321654')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '987987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '345987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '987987')
>|||Dan,
Thanks.
>
There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.
>
Consider this a lesson on one of the many reasons why one shouldn't store a
delimited list in a relational table column.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
What can I do to get this to perform reasonably? I cannot use full-text
search.
Any help is appreciated.
lqlaurenquantrell wrote:
Quote:
Originally Posted by
I have a query below that performs horribly:
>
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
>
>
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
>
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
>
What can I do to get this to perform reasonably? I cannot use full-text
search.
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
do this:
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')|||Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq
Ed Murphy wrote:
Quote:
Originally Posted by
laurenquantrell wrote:
>
Quote:
Originally Posted by
I have a query below that performs horribly:
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
What can I do to get this to perform reasonably? I cannot use full-text
search.
>
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
>
do this:
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
>
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
>
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')|||I need a way to improve performace of the query with the existing table
Quote:
Originally Posted by
structure.
There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.
Consider this a lesson on one of the many reasons why one shouldn't store a
delimited list in a relational table column.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...
Quote:
Originally Posted by
Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq
>
>
Ed Murphy wrote:
Quote:
Originally Posted by
>laurenquantrell wrote:
>>
Quote:
Originally Posted by
I have a query below that performs horribly:
>
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
>
>
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
>
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
>
What can I do to get this to perform reasonably? I cannot use full-text
search.
>>
>Normalize tblStuff by removing column Keyword and replacing it with a
>second table tblStuffKeyword. For instance, instead of
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50),
> Keyword text
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (1, 'A', 'B', '123456,321654,987987,345987')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (2, 'C', 'D', '')
>insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
> values (3, 'E', 'F', '123456,987987')
>>
>do this:
>>
>create table tblStuff (
> StuffKey int,
> StuffField1 varchar(50),
> StuffField2 varchar(50)
>)
>>
>create table tblStuffKeyword (
> StuffKey int,
> Keyword varchar(50)
>)
>>
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (1, 'A', 'B')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (2, 'C', 'D')
>insert into tblStuff (StuffKey, StuffField1, StuffField2)
> values (3, 'E', 'F')
>>
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '321654')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '987987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (1, '345987')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '123456')
>insert into tblStuffKeyword (StuffKey, Keyword)
> values (3, '987987')
>|||Dan,
Thanks.
Dan Guzman wrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
I need a way to improve performace of the query with the existing table
structure.
>
There isn't much you can do because of the leading '%' in the LIKE
expressions. The only approach I can think of is to add a covering index on
the SomeColumnName, Keyword and Something columns. At least this will limit
the scan to the rows matching the SomeColumnName value specified.
>
Consider this a lesson on one of the many reasons why one shouldn't store a
delimited list in a relational table column.
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
>
"laurenquantrell" <laurenquantrell@.hotmail.comwrote in message
news:1159628053.900888.278690@.m7g2000cwm.googlegro ups.com...
Quote:
Originally Posted by
Ed,
Thanks. However, rebuilding the database architecture is currently not
an option.
I need a way to improve performace of the query with the existing table
structure.
lq
Ed Murphy wrote:
Quote:
Originally Posted by
laurenquantrell wrote:
>
I have a query below that performs horribly:
@.KeywordOne char(6),
@.KeywordTwo char(6),
@.KeywordThree char(6),
@.KeywordFour char(6),
@.KeywordFive char(6)
SELECT
c.Something
FROM
dbo.tblStuff c
WHERE
c.SomeColumnName = 0
AND (c.Keyword LIKE '%' + @.KeywordOne + '%' OR @.KeywordOne is Null)
AND (c.Keyword LIKE '%' + @.KeywordTwo + '%' OR @.KeywordTwo is Null)
AND (c.Keyword LIKE '%' + @.KeywordThree + '%' OR @.KeywordThree is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFour + '%' OR @.KeywordFour = is
Null)
AND (c.Keyword LIKE '%' + @.KeywordFive + '%' OR @.KeywordFive = is
Null)
The contents of column c.Keyword looks like this:
Row1: 123456,321654,987987,345987
Row2:
Row3: 123456,987987
etc.
What can I do to get this to perform reasonably? I cannot use full-text
search.
>
Normalize tblStuff by removing column Keyword and replacing it with a
second table tblStuffKeyword. For instance, instead of
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50),
Keyword text
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (1, 'A', 'B', '123456,321654,987987,345987')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (2, 'C', 'D', '')
insert into tblStuff (StuffKey, StuffField1, StuffField2, Keyword)
values (3, 'E', 'F', '123456,987987')
>
do this:
>
create table tblStuff (
StuffKey int,
StuffField1 varchar(50),
StuffField2 varchar(50)
)
>
create table tblStuffKeyword (
StuffKey int,
Keyword varchar(50)
)
>
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (1, 'A', 'B')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (2, 'C', 'D')
insert into tblStuff (StuffKey, StuffField1, StuffField2)
values (3, 'E', 'F')
>
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '321654')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '987987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (1, '345987')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '123456')
insert into tblStuffKeyword (StuffKey, Keyword)
values (3, '987987')
Wednesday, March 21, 2012
Replace column value with MAX() query in dataflow before insert
Hi,
We have a dataflow task that imports data from excel to a sql2005 database table. One of the columns is never filled in in the excel source. For updates we can use the lookup transformation to fill in that column, but for new values we need to calculate a new value for it (it's a PK) with MAX(column) +1 and replace the null value in the dataflow with this new value.
Just to be clear:
column id (int)
column name (string)
Based on the 'name' column, we can look up existing ids, and update these in the table. Ids that don't exist yet need to be filled in with the maximum value of the column + 1 (we can't use identity columns) and inserted in the table
Which transformation do I use to replace the value of the id column with this new id?
thanks,
Stephane
Hi Stephane,
You would first need to get the maximum value of the id column into another column (called maxid, for the purposes of this example). You could do this with a script component, or by splitting the flow and using an Aggregate transform,and merging it back. Then, you can then use a derived column transform to replace the value of the column by setting a replace expression such as the following:
ISNULL(id) ? maxid + 1 : id
Mark
|||Hi Mark,
Thanks for your reply.
Your solution would work if all the data was in the dataflow, which unfortunately it isn't for me (we iterate over excel files with one row per file). We now do a select max() ... at the start of the control flow and store it in a variable. When we detect a new row, we increment the value and use a derived column transform like you suggested as well to put the value in the column.
Thanks,
Stephane
sqlTuesday, March 20, 2012
Replace <br> tag in CRLF
Hi all,
I made a migration operation and when I am looking inside database I can see the <BR> tags.
The question is how to write a query that replace all of this occurances of <BR> inside the colum table, with CLRF which is a new line code.
Thanks in advance.
REPLACE(my_string_field, '<br>', chr(13) + chr(10)) will do the replace in a field...so maybe a query like this would work?
UPDATE MY_TABLE
SET MY_STRING_FIELD = REPLACE(MY_STRING_FIELD, '<br>', chr(13) + chr(10));
give or take|||
We are close but not yet. I needded to change the code so it will work.
The conversion is working but all the characters become question mark like this: "?? ? ?? ?? ?? ?? ???? ? ?? ?"
I tried 2 types of the unicode that one of them should be the one I am using, but still the quotation marks is there...here is the query please help:
UPDATE [E:\WEBSITES\A1\TBH_WEB\APP_DB.MDF].[dbo].[tbh_Comments]
SET
[Body] =REPLACE((CAST([Body]AS varChar(1000)) COLLATE SQL_Latin1_General_CP1_CI_AS),'<br>',char(13) +char(10))
Replace '' with a 0 ......... replace nothing with a 0
Hi guys
Im sure this is easy but I have a query to calculate spaces on a
booking system and need to replace a blank ie no bookings yet with a 0
so that i can add the numbers up to get a total.
Trouble is its not a NULL value and when i try
REPLACE([fieldname],'','0') it just doesnt work.
I can get round it but rather do it properly
MattieHi
When you say "it does not work" , what does that mean? Is an error message,
is it not given you a right result ?
SELECT REPLACE(column ,' ' ,'0')
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> Hi guys
> Im sure this is easy but I have a query to calculate spaces on a
> booking system and need to replace a blank ie no bookings yet with a 0
> so that i can add the numbers up to get a total.
> Trouble is its not a NULL value and when i try
> REPLACE([fieldname],'','0') it just doesnt work.
> I can get round it but rather do it properly
> Mattie
>|||Hi
Basically the query still have blanks in that particular field, no
error message.
SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
space in the field.
Im thinkin i might have to use ISNUM or somet like that.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> When you say "it does not work" , what does that mean? Is an error messag
e,
> is it not given you a right result ?
> SELECT REPLACE(column ,' ' ,'0')
>
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...|||> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
One method to use COALESCE to return ' ' or '0' instead of NULL:
SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
It seems to me that this is a flaw in your table design. The column should
be defined with the appropriate data type so that spaces can't be inserted
into a numeric column in the first place. You can still use SELECT
COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
application code.
Hope this helps.
Dan Guzman
SQL Server MVP
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...[vbcol=seagreen]
> Hi
> Basically the query still have blanks in that particular field, no
> error message.
> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
> Im thinkin i might have to use ISNUM or somet like that.
>
> Uri Dimant wrote:
>|||Hi Dan, thanks for that will look at it now.
To describe in more detail, i have a qurey to calculate sales sold in a
particular session and a maximum for that session. If there are no
sales then "maximum - nothing = nothing as apposed to 0 which is what i
need.
Ill go have a play about now and let you know how it goes.
Mattie
Dan Guzman wrote:[vbcol=seagreen]
> One method to use COALESCE to return ' ' or '0' instead of NULL:
> SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
>
> It seems to me that this is a flaw in your table design. The column shoul
d
> be defined with the appropriate data type so that spaces can't be inserted
> into a numeric column in the first place. You can still use SELECT
> COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
> application code.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...|||On 27 Aug 2006 07:31:33 -0700, MattieG wrote:
>Hi guys
>Im sure this is easy but I have a query to calculate spaces on a
>booking system and need to replace a blank ie no bookings yet with a 0
>so that i can add the numbers up to get a total.
>Trouble is its not a NULL value and when i try
>REPLACE([fieldname],'','0') it just doesnt work.
>I can get round it but rather do it properly
Hi Mattie,
Two ways:
SELECT CASE WHEN fieldname = '' THEN '0' ELSE fieldname END
FROM ...
SELECT COALESCE(NULLIF(fieldname, ''), '0')
FROM ...
Hugo Kornelis, SQL Server MVP|||On 27 Aug 2006 15:12:24 -0700, MattieG wrote:
>Hi Dan, thanks for that will look at it now.
>To describe in more detail, i have a qurey to calculate sales sold in a
>particular session and a maximum for that session. If there are no
>sales then "maximum - nothing = nothing as apposed to 0 which is what i
>need.
Hi Mattie,
I don't understand. How can maximum - nothing be equal to nothing
instead of maximum?
But more important: how can the result of a substraction ever be an
empty string? Can yoou please post some repro code? (I.e. table
definitions as CREATE TABLE statements, sample data as INSERT statements
and the statement that produces the incorrect output)
Hugo Kornelis, SQL Server MVP|||I'm guessing here, but...
The problem isn't that maximum minus nothing = nothing.
The problem is that (implicit conversion of conforming string) minus (empty
string) isn't math.
You can fight this forever, or you can alter your table structure so that
you have a nullable integer column. Then you can use select
max(column)-ISNULL(min(column),0).
good luck!
"MattieG" wrote:
> Hi Dan, thanks for that will look at it now.
> To describe in more detail, i have a qurey to calculate sales sold in a
> particular session and a maximum for that session. If there are no
> sales then "maximum - nothing = nothing as apposed to 0 which is what i
> need.
> Ill go have a play about now and let you know how it goes.
> Mattie
>
> Dan Guzman wrote:
>
Im sure this is easy but I have a query to calculate spaces on a
booking system and need to replace a blank ie no bookings yet with a 0
so that i can add the numbers up to get a total.
Trouble is its not a NULL value and when i try
REPLACE([fieldname],'','0') it just doesnt work.
I can get round it but rather do it properly
MattieHi
When you say "it does not work" , what does that mean? Is an error message,
is it not given you a right result ?
SELECT REPLACE(column ,' ' ,'0')
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> Hi guys
> Im sure this is easy but I have a query to calculate spaces on a
> booking system and need to replace a blank ie no bookings yet with a 0
> so that i can add the numbers up to get a total.
> Trouble is its not a NULL value and when i try
> REPLACE([fieldname],'','0') it just doesnt work.
> I can get round it but rather do it properly
> Mattie
>|||Hi
Basically the query still have blanks in that particular field, no
error message.
SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
space in the field.
Im thinkin i might have to use ISNUM or somet like that.
Uri Dimant wrote:[vbcol=seagreen]
> Hi
> When you say "it does not work" , what does that mean? Is an error messag
e,
> is it not given you a right result ?
> SELECT REPLACE(column ,' ' ,'0')
>
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...|||> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
One method to use COALESCE to return ' ' or '0' instead of NULL:
SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
It seems to me that this is a flaw in your table design. The column should
be defined with the appropriate data type so that spaces can't be inserted
into a numeric column in the first place. You can still use SELECT
COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
application code.
Hope this helps.
Dan Guzman
SQL Server MVP
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...[vbcol=seagreen]
> Hi
> Basically the query still have blanks in that particular field, no
> error message.
> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
> Im thinkin i might have to use ISNUM or somet like that.
>
> Uri Dimant wrote:
>|||Hi Dan, thanks for that will look at it now.
To describe in more detail, i have a qurey to calculate sales sold in a
particular session and a maximum for that session. If there are no
sales then "maximum - nothing = nothing as apposed to 0 which is what i
need.
Ill go have a play about now and let you know how it goes.
Mattie
Dan Guzman wrote:[vbcol=seagreen]
> One method to use COALESCE to return ' ' or '0' instead of NULL:
> SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
>
> It seems to me that this is a flaw in your table design. The column shoul
d
> be defined with the appropriate data type so that spaces can't be inserted
> into a numeric column in the first place. You can still use SELECT
> COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
> application code.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...|||On 27 Aug 2006 07:31:33 -0700, MattieG wrote:
>Hi guys
>Im sure this is easy but I have a query to calculate spaces on a
>booking system and need to replace a blank ie no bookings yet with a 0
>so that i can add the numbers up to get a total.
>Trouble is its not a NULL value and when i try
>REPLACE([fieldname],'','0') it just doesnt work.
>I can get round it but rather do it properly
Hi Mattie,
Two ways:
SELECT CASE WHEN fieldname = '' THEN '0' ELSE fieldname END
FROM ...
SELECT COALESCE(NULLIF(fieldname, ''), '0')
FROM ...
Hugo Kornelis, SQL Server MVP|||On 27 Aug 2006 15:12:24 -0700, MattieG wrote:
>Hi Dan, thanks for that will look at it now.
>To describe in more detail, i have a qurey to calculate sales sold in a
>particular session and a maximum for that session. If there are no
>sales then "maximum - nothing = nothing as apposed to 0 which is what i
>need.
Hi Mattie,
I don't understand. How can maximum - nothing be equal to nothing
instead of maximum?
But more important: how can the result of a substraction ever be an
empty string? Can yoou please post some repro code? (I.e. table
definitions as CREATE TABLE statements, sample data as INSERT statements
and the statement that produces the incorrect output)
Hugo Kornelis, SQL Server MVP|||I'm guessing here, but...
The problem isn't that maximum minus nothing = nothing.
The problem is that (implicit conversion of conforming string) minus (empty
string) isn't math.
You can fight this forever, or you can alter your table structure so that
you have a nullable integer column. Then you can use select
max(column)-ISNULL(min(column),0).
good luck!
"MattieG" wrote:
> Hi Dan, thanks for that will look at it now.
> To describe in more detail, i have a qurey to calculate sales sold in a
> particular session and a maximum for that session. If there are no
> sales then "maximum - nothing = nothing as apposed to 0 which is what i
> need.
> Ill go have a play about now and let you know how it goes.
> Mattie
>
> Dan Guzman wrote:
>
Replace '' with a 0 ......... replace nothing with a 0
Hi guys
Im sure this is easy but I have a query to calculate spaces on a
booking system and need to replace a blank ie no bookings yet with a 0
so that i can add the numbers up to get a total.
Trouble is its not a NULL value and when i try
REPLACE([fieldname],'','0') it just doesnt work.
I can get round it but rather do it properly :)
MattieHi
When you say "it does not work" , what does that mean? Is an error message,
is it not given you a right result ?
SELECT REPLACE(column ,' ' ,'0')
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> Hi guys
> Im sure this is easy but I have a query to calculate spaces on a
> booking system and need to replace a blank ie no bookings yet with a 0
> so that i can add the numbers up to get a total.
> Trouble is its not a NULL value and when i try
> REPLACE([fieldname],'','0') it just doesnt work.
> I can get round it but rather do it properly :)
> Mattie
>|||Hi
Basically the query still have blanks in that particular field, no
error message.
SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
space in the field.
Im thinkin i might have to use ISNUM or somet like that.
Uri Dimant wrote:
> Hi
> When you say "it does not work" , what does that mean? Is an error message,
> is it not given you a right result ?
> SELECT REPLACE(column ,' ' ,'0')
>
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> > Hi guys
> >
> > Im sure this is easy but I have a query to calculate spaces on a
> > booking system and need to replace a blank ie no bookings yet with a 0
> > so that i can add the numbers up to get a total.
> >
> > Trouble is its not a NULL value and when i try
> > REPLACE([fieldname],'','0') it just doesnt work.
> >
> > I can get round it but rather do it properly :)
> >
> > Mattie
> >|||> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
One method to use COALESCE to return ' ' or '0' instead of NULL:
SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
>> > need to replace a blank ie no bookings yet with a 0
>> > so that i can add the numbers up to get a total.
It seems to me that this is a flaw in your table design. The column should
be defined with the appropriate data type so that spaces can't be inserted
into a numeric column in the first place. You can still use SELECT
COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
application code.
Hope this helps.
Dan Guzman
SQL Server MVP
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...
> Hi
> Basically the query still have blanks in that particular field, no
> error message.
> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
> Im thinkin i might have to use ISNUM or somet like that.
>
> Uri Dimant wrote:
>> Hi
>> When you say "it does not work" , what does that mean? Is an error
>> message,
>> is it not given you a right result ?
>> SELECT REPLACE(column ,' ' ,'0')
>>
>> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
>> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
>> > Hi guys
>> >
>> > Im sure this is easy but I have a query to calculate spaces on a
>> > booking system and need to replace a blank ie no bookings yet with a 0
>> > so that i can add the numbers up to get a total.
>> >
>> > Trouble is its not a NULL value and when i try
>> > REPLACE([fieldname],'','0') it just doesnt work.
>> >
>> > I can get round it but rather do it properly :)
>> >
>> > Mattie
>> >
>|||Hi Dan, thanks for that will look at it now.
To describe in more detail, i have a qurey to calculate sales sold in a
particular session and a maximum for that session. If there are no
sales then "maximum - nothing = nothing as apposed to 0 which is what i
need.
Ill go have a play about now and let you know how it goes.
Mattie
Dan Guzman wrote:
> > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > space in the field.
> One method to use COALESCE to return ' ' or '0' instead of NULL:
> SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
> >> > need to replace a blank ie no bookings yet with a 0
> >> > so that i can add the numbers up to get a total.
> It seems to me that this is a flaw in your table design. The column should
> be defined with the appropriate data type so that spaces can't be inserted
> into a numeric column in the first place. You can still use SELECT
> COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
> application code.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...
> > Hi
> >
> > Basically the query still have blanks in that particular field, no
> > error message.
> > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > space in the field.
> >
> > Im thinkin i might have to use ISNUM or somet like that.
> >
> >
> > Uri Dimant wrote:
> >> Hi
> >> When you say "it does not work" , what does that mean? Is an error
> >> message,
> >> is it not given you a right result ?
> >>
> >> SELECT REPLACE(column ,' ' ,'0')
> >>
> >>
> >>
> >> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> >> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> >> > Hi guys
> >> >
> >> > Im sure this is easy but I have a query to calculate spaces on a
> >> > booking system and need to replace a blank ie no bookings yet with a 0
> >> > so that i can add the numbers up to get a total.
> >> >
> >> > Trouble is its not a NULL value and when i try
> >> > REPLACE([fieldname],'','0') it just doesnt work.
> >> >
> >> > I can get round it but rather do it properly :)
> >> >
> >> > Mattie
> >> >
> >|||On 27 Aug 2006 07:31:33 -0700, MattieG wrote:
>Hi guys
>Im sure this is easy but I have a query to calculate spaces on a
>booking system and need to replace a blank ie no bookings yet with a 0
>so that i can add the numbers up to get a total.
>Trouble is its not a NULL value and when i try
>REPLACE([fieldname],'','0') it just doesnt work.
>I can get round it but rather do it properly :)
Hi Mattie,
Two ways:
SELECT CASE WHEN fieldname = '' THEN '0' ELSE fieldname END
FROM ...
SELECT COALESCE(NULLIF(fieldname, ''), '0')
FROM ...
--
Hugo Kornelis, SQL Server MVP|||On 27 Aug 2006 15:12:24 -0700, MattieG wrote:
>Hi Dan, thanks for that will look at it now.
>To describe in more detail, i have a qurey to calculate sales sold in a
>particular session and a maximum for that session. If there are no
>sales then "maximum - nothing = nothing as apposed to 0 which is what i
>need.
Hi Mattie,
I don't understand. How can maximum - nothing be equal to nothing
instead of maximum?
But more important: how can the result of a substraction ever be an
empty string? Can yoou please post some repro code? (I.e. table
definitions as CREATE TABLE statements, sample data as INSERT statements
and the statement that produces the incorrect output)
--
Hugo Kornelis, SQL Server MVP|||I'm guessing here, but...
The problem isn't that maximum minus nothing = nothing.
The problem is that (implicit conversion of conforming string) minus (empty
string) isn't math.
You can fight this forever, or you can alter your table structure so that
you have a nullable integer column. Then you can use select
max(column)-ISNULL(min(column),0).
good luck!
"MattieG" wrote:
> Hi Dan, thanks for that will look at it now.
> To describe in more detail, i have a qurey to calculate sales sold in a
> particular session and a maximum for that session. If there are no
> sales then "maximum - nothing = nothing as apposed to 0 which is what i
> need.
> Ill go have a play about now and let you know how it goes.
> Mattie
>
> Dan Guzman wrote:
> > > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > > space in the field.
> >
> > One method to use COALESCE to return ' ' or '0' instead of NULL:
> >
> > SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
> >
> > >> > need to replace a blank ie no bookings yet with a 0
> > >> > so that i can add the numbers up to get a total.
> >
> > It seems to me that this is a flaw in your table design. The column should
> > be defined with the appropriate data type so that spaces can't be inserted
> > into a numeric column in the first place. You can still use SELECT
> > COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
> > application code.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> > news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...
> > > Hi
> > >
> > > Basically the query still have blanks in that particular field, no
> > > error message.
> > > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > > space in the field.
> > >
> > > Im thinkin i might have to use ISNUM or somet like that.
> > >
> > >
> > > Uri Dimant wrote:
> > >> Hi
> > >> When you say "it does not work" , what does that mean? Is an error
> > >> message,
> > >> is it not given you a right result ?
> > >>
> > >> SELECT REPLACE(column ,' ' ,'0')
> > >>
> > >>
> > >>
> > >> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> > >> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> > >> > Hi guys
> > >> >
> > >> > Im sure this is easy but I have a query to calculate spaces on a
> > >> > booking system and need to replace a blank ie no bookings yet with a 0
> > >> > so that i can add the numbers up to get a total.
> > >> >
> > >> > Trouble is its not a NULL value and when i try
> > >> > REPLACE([fieldname],'','0') it just doesnt work.
> > >> >
> > >> > I can get round it but rather do it properly :)
> > >> >
> > >> > Mattie
> > >> >
> > >
>
Im sure this is easy but I have a query to calculate spaces on a
booking system and need to replace a blank ie no bookings yet with a 0
so that i can add the numbers up to get a total.
Trouble is its not a NULL value and when i try
REPLACE([fieldname],'','0') it just doesnt work.
I can get round it but rather do it properly :)
MattieHi
When you say "it does not work" , what does that mean? Is an error message,
is it not given you a right result ?
SELECT REPLACE(column ,' ' ,'0')
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> Hi guys
> Im sure this is easy but I have a query to calculate spaces on a
> booking system and need to replace a blank ie no bookings yet with a 0
> so that i can add the numbers up to get a total.
> Trouble is its not a NULL value and when i try
> REPLACE([fieldname],'','0') it just doesnt work.
> I can get round it but rather do it properly :)
> Mattie
>|||Hi
Basically the query still have blanks in that particular field, no
error message.
SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
space in the field.
Im thinkin i might have to use ISNUM or somet like that.
Uri Dimant wrote:
> Hi
> When you say "it does not work" , what does that mean? Is an error message,
> is it not given you a right result ?
> SELECT REPLACE(column ,' ' ,'0')
>
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> > Hi guys
> >
> > Im sure this is easy but I have a query to calculate spaces on a
> > booking system and need to replace a blank ie no bookings yet with a 0
> > so that i can add the numbers up to get a total.
> >
> > Trouble is its not a NULL value and when i try
> > REPLACE([fieldname],'','0') it just doesnt work.
> >
> > I can get round it but rather do it properly :)
> >
> > Mattie
> >|||> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
One method to use COALESCE to return ' ' or '0' instead of NULL:
SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
>> > need to replace a blank ie no bookings yet with a 0
>> > so that i can add the numbers up to get a total.
It seems to me that this is a flaw in your table design. The column should
be defined with the appropriate data type so that spaces can't be inserted
into a numeric column in the first place. You can still use SELECT
COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
application code.
Hope this helps.
Dan Guzman
SQL Server MVP
"MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...
> Hi
> Basically the query still have blanks in that particular field, no
> error message.
> SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> space in the field.
> Im thinkin i might have to use ISNUM or somet like that.
>
> Uri Dimant wrote:
>> Hi
>> When you say "it does not work" , what does that mean? Is an error
>> message,
>> is it not given you a right result ?
>> SELECT REPLACE(column ,' ' ,'0')
>>
>> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
>> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
>> > Hi guys
>> >
>> > Im sure this is easy but I have a query to calculate spaces on a
>> > booking system and need to replace a blank ie no bookings yet with a 0
>> > so that i can add the numbers up to get a total.
>> >
>> > Trouble is its not a NULL value and when i try
>> > REPLACE([fieldname],'','0') it just doesnt work.
>> >
>> > I can get round it but rather do it properly :)
>> >
>> > Mattie
>> >
>|||Hi Dan, thanks for that will look at it now.
To describe in more detail, i have a qurey to calculate sales sold in a
particular session and a maximum for that session. If there are no
sales then "maximum - nothing = nothing as apposed to 0 which is what i
need.
Ill go have a play about now and let you know how it goes.
Mattie
Dan Guzman wrote:
> > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > space in the field.
> One method to use COALESCE to return ' ' or '0' instead of NULL:
> SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
> >> > need to replace a blank ie no bookings yet with a 0
> >> > so that i can add the numbers up to get a total.
> It seems to me that this is a flaw in your table design. The column should
> be defined with the appropriate data type so that spaces can't be inserted
> into a numeric column in the first place. You can still use SELECT
> COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
> application code.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...
> > Hi
> >
> > Basically the query still have blanks in that particular field, no
> > error message.
> > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > space in the field.
> >
> > Im thinkin i might have to use ISNUM or somet like that.
> >
> >
> > Uri Dimant wrote:
> >> Hi
> >> When you say "it does not work" , what does that mean? Is an error
> >> message,
> >> is it not given you a right result ?
> >>
> >> SELECT REPLACE(column ,' ' ,'0')
> >>
> >>
> >>
> >> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> >> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> >> > Hi guys
> >> >
> >> > Im sure this is easy but I have a query to calculate spaces on a
> >> > booking system and need to replace a blank ie no bookings yet with a 0
> >> > so that i can add the numbers up to get a total.
> >> >
> >> > Trouble is its not a NULL value and when i try
> >> > REPLACE([fieldname],'','0') it just doesnt work.
> >> >
> >> > I can get round it but rather do it properly :)
> >> >
> >> > Mattie
> >> >
> >|||On 27 Aug 2006 07:31:33 -0700, MattieG wrote:
>Hi guys
>Im sure this is easy but I have a query to calculate spaces on a
>booking system and need to replace a blank ie no bookings yet with a 0
>so that i can add the numbers up to get a total.
>Trouble is its not a NULL value and when i try
>REPLACE([fieldname],'','0') it just doesnt work.
>I can get round it but rather do it properly :)
Hi Mattie,
Two ways:
SELECT CASE WHEN fieldname = '' THEN '0' ELSE fieldname END
FROM ...
SELECT COALESCE(NULLIF(fieldname, ''), '0')
FROM ...
--
Hugo Kornelis, SQL Server MVP|||On 27 Aug 2006 15:12:24 -0700, MattieG wrote:
>Hi Dan, thanks for that will look at it now.
>To describe in more detail, i have a qurey to calculate sales sold in a
>particular session and a maximum for that session. If there are no
>sales then "maximum - nothing = nothing as apposed to 0 which is what i
>need.
Hi Mattie,
I don't understand. How can maximum - nothing be equal to nothing
instead of maximum?
But more important: how can the result of a substraction ever be an
empty string? Can yoou please post some repro code? (I.e. table
definitions as CREATE TABLE statements, sample data as INSERT statements
and the statement that produces the incorrect output)
--
Hugo Kornelis, SQL Server MVP|||I'm guessing here, but...
The problem isn't that maximum minus nothing = nothing.
The problem is that (implicit conversion of conforming string) minus (empty
string) isn't math.
You can fight this forever, or you can alter your table structure so that
you have a nullable integer column. Then you can use select
max(column)-ISNULL(min(column),0).
good luck!
"MattieG" wrote:
> Hi Dan, thanks for that will look at it now.
> To describe in more detail, i have a qurey to calculate sales sold in a
> particular session and a maximum for that session. If there are no
> sales then "maximum - nothing = nothing as apposed to 0 which is what i
> need.
> Ill go have a play about now and let you know how it goes.
> Mattie
>
> Dan Guzman wrote:
> > > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > > space in the field.
> >
> > One method to use COALESCE to return ' ' or '0' instead of NULL:
> >
> > SELECT REPLACE(COALESCE(column, '0'), ' ', '0')
> >
> > >> > need to replace a blank ie no bookings yet with a 0
> > >> > so that i can add the numbers up to get a total.
> >
> > It seems to me that this is a flaw in your table design. The column should
> > be defined with the appropriate data type so that spaces can't be inserted
> > into a numeric column in the first place. You can still use SELECT
> > COALESCE(column, 0) to treat NULLs as 0 or treat NULLs as zero in your
> > application code.
> >
> >
> > --
> > Hope this helps.
> >
> > Dan Guzman
> > SQL Server MVP
> >
> > "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> > news:1156694244.396948.101820@.p79g2000cwp.googlegroups.com...
> > > Hi
> > >
> > > Basically the query still have blanks in that particular field, no
> > > error message.
> > > SELECT REPLACE(column ,' ' ,'0') ->only works if there is actually a
> > > space in the field.
> > >
> > > Im thinkin i might have to use ISNUM or somet like that.
> > >
> > >
> > > Uri Dimant wrote:
> > >> Hi
> > >> When you say "it does not work" , what does that mean? Is an error
> > >> message,
> > >> is it not given you a right result ?
> > >>
> > >> SELECT REPLACE(column ,' ' ,'0')
> > >>
> > >>
> > >>
> > >> "MattieG" <mattiegriffin@.hotmail.co.uk> wrote in message
> > >> news:1156689093.555748.280040@.75g2000cwc.googlegroups.com...
> > >> > Hi guys
> > >> >
> > >> > Im sure this is easy but I have a query to calculate spaces on a
> > >> > booking system and need to replace a blank ie no bookings yet with a 0
> > >> > so that i can add the numbers up to get a total.
> > >> >
> > >> > Trouble is its not a NULL value and when i try
> > >> > REPLACE([fieldname],'','0') it just doesnt work.
> > >> >
> > >> > I can get round it but rather do it properly :)
> > >> >
> > >> > Mattie
> > >> >
> > >
>
Monday, March 12, 2012
Repeated Value
Hi I have a query that displays 5 column but I would like to supress the repeated values from one of them...
like this
__Event___|___Reference___|___Name___|____Date____ _|
________1_|__AB01000005__|__Diogo____|__01/01/2005_|
________2_|__AB01000005__|__Diogo____|__02/01/2005_|
________3_|__AB01000005__|__Diogo____|__03/01/2005_|
________4_|__AB01000001__|__MAria____|__04/01/2005_|
________5_|__AB01000002__|__Joao_____|__05/01/2005_|
this table is what I am getting
I want it to return this
__Event___|__Reference____|__Name____|___Date_____ __|
_______3__|__AB01000005__|__Diogo____|___03/01/2005_|
_______4__|__AB01000001__|__Maria____|___04/01/2005_|
_______5__|__AB01000002__|__Joao____|____05/01/2005_|
This way I only get the latest reference....
There is any way to do this?select Event
, Reference
, Name
, theDate
from yourtable as t
where theDate
= ( select max(theDate)
from yourtable
where Reference = t.Reference )|||That does not seem a good solution since I am using 3 tables to get the values
if you have another ideia I would apreciate|||What is the difference between this problem and your other one entitled "Can't Use Distinct Here"?|||guess this one is less detailed....
Oh if you don't mind... I have 2 tables... one as all the references as text, and the other only have the used ones....
I wantes to query all of the references that are not being used...
I think this one is easy but I am a newbie at SQL!!!
guess you can tell me how to do it :)|||guess you can tell me how to do it :)actually, we would prefer that you do a little bit of this work yourself ;)
hint: left outer join|||I've been trying something like this
SELECT Badges.BadgeReference
FROM Badges, Visitors
WHERE (((Badges.BadgeReference) Like Not[Visitors].[VisitorReference]));
And it does not work
then I tryied
SELECT Badges.BadgeReference
FROM Badges LEFT JOIN Visitors ON Badges.BadgeReference = Visitors.VisitorReference
WHERE (((Badges.BadgeReference) Like Not [Visitors].[VisitorReference]));
Nothing... and finally
SELECT Badges.BadgeReference
FROM Badges Right JOIN Visitors ON Badges.BadgeReference = Visitors.VisitorReference
WHERE (((Badges.BadgeReference) Like Not [Visitors].[VisitorReference]));|||oooooohh... forget... outer joins... I'll be trying
hehehe|||ok guess I didn't make it....
:( what a failure!!!!|||...
I can only get the ones that I am using and not the inverse... :(|||keep looking , you are almost there
what are you using as a reference for left outer join?
textbook? tutorial web site?|||I'm trying by myself, and searching for something on the internet...|||SELECT Badges.BadgeReference
FROM Badges Left Outer Join Visitors On Badges.BadgeReference = Visitors.VisitorReference
WHERE Badges.BadgeReference <> Visitors.VIsitorReference
I've reached here...... It returns nothing... it was suposed to return 1,2,3 and 4, the 5 was being used...
if I take off the where it shows all the references including the 5 and I don't want it to do it....
Getting desperated with this one|||you need a good SQL tutorial
there are several on my SQL Links (http://r937.com/sqllinks.cfm) page
meanwhile, in a LEFT OUTER JOIN, rows from the left table which have no matching row in the right table are still returned in the results, but the columns from the right table are all null
therefore if all you want is the unmatched rows,
SELECT Badges.BadgeReference
FROM Badges Left Outer Join Visitors
On Badges.BadgeReference = Visitors.VisitorReference
WHERE Visitors.VIsitorReference is null
:)
like this
__Event___|___Reference___|___Name___|____Date____ _|
________1_|__AB01000005__|__Diogo____|__01/01/2005_|
________2_|__AB01000005__|__Diogo____|__02/01/2005_|
________3_|__AB01000005__|__Diogo____|__03/01/2005_|
________4_|__AB01000001__|__MAria____|__04/01/2005_|
________5_|__AB01000002__|__Joao_____|__05/01/2005_|
this table is what I am getting
I want it to return this
__Event___|__Reference____|__Name____|___Date_____ __|
_______3__|__AB01000005__|__Diogo____|___03/01/2005_|
_______4__|__AB01000001__|__Maria____|___04/01/2005_|
_______5__|__AB01000002__|__Joao____|____05/01/2005_|
This way I only get the latest reference....
There is any way to do this?select Event
, Reference
, Name
, theDate
from yourtable as t
where theDate
= ( select max(theDate)
from yourtable
where Reference = t.Reference )|||That does not seem a good solution since I am using 3 tables to get the values
if you have another ideia I would apreciate|||What is the difference between this problem and your other one entitled "Can't Use Distinct Here"?|||guess this one is less detailed....
Oh if you don't mind... I have 2 tables... one as all the references as text, and the other only have the used ones....
I wantes to query all of the references that are not being used...
I think this one is easy but I am a newbie at SQL!!!
guess you can tell me how to do it :)|||guess you can tell me how to do it :)actually, we would prefer that you do a little bit of this work yourself ;)
hint: left outer join|||I've been trying something like this
SELECT Badges.BadgeReference
FROM Badges, Visitors
WHERE (((Badges.BadgeReference) Like Not[Visitors].[VisitorReference]));
And it does not work
then I tryied
SELECT Badges.BadgeReference
FROM Badges LEFT JOIN Visitors ON Badges.BadgeReference = Visitors.VisitorReference
WHERE (((Badges.BadgeReference) Like Not [Visitors].[VisitorReference]));
Nothing... and finally
SELECT Badges.BadgeReference
FROM Badges Right JOIN Visitors ON Badges.BadgeReference = Visitors.VisitorReference
WHERE (((Badges.BadgeReference) Like Not [Visitors].[VisitorReference]));|||oooooohh... forget... outer joins... I'll be trying
hehehe|||ok guess I didn't make it....
:( what a failure!!!!|||...
I can only get the ones that I am using and not the inverse... :(|||keep looking , you are almost there
what are you using as a reference for left outer join?
textbook? tutorial web site?|||I'm trying by myself, and searching for something on the internet...|||SELECT Badges.BadgeReference
FROM Badges Left Outer Join Visitors On Badges.BadgeReference = Visitors.VisitorReference
WHERE Badges.BadgeReference <> Visitors.VIsitorReference
I've reached here...... It returns nothing... it was suposed to return 1,2,3 and 4, the 5 was being used...
if I take off the where it shows all the references including the 5 and I don't want it to do it....
Getting desperated with this one|||you need a good SQL tutorial
there are several on my SQL Links (http://r937.com/sqllinks.cfm) page
meanwhile, in a LEFT OUTER JOIN, rows from the left table which have no matching row in the right table are still returned in the results, but the columns from the right table are all null
therefore if all you want is the unmatched rows,
SELECT Badges.BadgeReference
FROM Badges Left Outer Join Visitors
On Badges.BadgeReference = Visitors.VisitorReference
WHERE Visitors.VIsitorReference is null
:)
Repeated SubQuery
Hi,
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0 -- Opening Balance for Container
,LPD.Qty -- Quantity Change
,@.ActionType -- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty -- Balance Quantity for Container
,LP.LabelBatchStatus -- Item Status
,NULL -- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah AdarshOn 30 Mar 2006 23:05:08 -0800, Adarsh wrote:
>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
> + (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
--
Hugo Kornelis, SQL Server MVP
Check this query:
SELECT @.ItemCodeID
,@.ItemCategory
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,@.TableID
,(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID) AS ItemOpenBal
-- Opening Balance for Item
,0 -- Opening Balance for Container
,LPD.Qty -- Quantity Change
,@.ActionType -- ActionType for Label Creation
-- This query needs to be optimized...
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID + LPD.Qty) AS ItemBalQty
-- Balance Quantity for Item
,LPD.Qty -- Balance Quantity for Container
,LP.LabelBatchStatus -- Item Status
,NULL -- Container Status
,@.ItemStatusTaskID
,@.ContainerStatusTaskID
,@.ActionStatus
,@.ActionBy
,getdate()
FROM tbl_Inventory AS I
RIGHT OUTER JOIN tbl_LabelProduction AS LP
ON LP.LabelBatchRecID = I.ItemCodeID AND I.ItemCategory = 3
INNER JOIN tbl_LabelProductionDetail AS LPD
ON LPD.LabelBatchRecID = LP.LabelBatchRecID
WHERE LP.LabelBatchRecID = @.ItemCodeID
GROUP BY I.ItemCodeID
,I.ItemCategory
,LPD.LabelBatchRecID
,LPD.LabelBatchContainerID
,LPD.ContainerNumber
,LPD.Qty
,LP.LabelBatchStatus
In the above query following part is repeated twice:
(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
+ (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
LPD1.LabelBatchRecID = @.ItemCodeID)
Is there any way to execute it only once and use it at both places in
the query.
Regards,
Shah AdarshOn 30 Mar 2006 23:05:08 -0800, Adarsh wrote:
>Hi,
>Check this query:
(snip)
>In the above query following part is repeated twice:
>(SELECT ISNULL(SUM(QtyChange), 0) FROM tbl_Inventory AS I WHERE
>I.ItemCodeID = @.ItemCodeID AND I.ItemCategory = @.ItemCategory)
> + (SELECT ISNULL(SUM(LPD1.Qty), 0) FROM tbl_LabelProductionDetail
>AS LPD1 WHERE (LPD1.ContainerNumber < LPD.ContainerNumber) AND
>LPD1.LabelBatchRecID = @.ItemCodeID)
>Is there any way to execute it only once and use it at both places in
>the query.
Hi Adarsh,
The query is a bit too long to give you a complete solution, but I'll
give you an example that you can use.
Instead of writing
SELECT a, b, c, (a + b) AS d, (a + b) * c AS e
FROM SomeTable
You can write:
SELECT a, b, c, d, d * c AS e
FROM (SELECT a, b, c, (a + b) AS d
FROM SomeTable) AS Derived
--
Hugo Kornelis, SQL Server MVP
Saturday, February 25, 2012
Renumbering Invoice Nos. through query ?
How can I renumber the following Invoice Nos. through Query.
After sorting by date, the 1st invoice no. to be started with 2006050001
Current Records in table
Before Query
InvNo Date
2006050133 12-MAY-06
2006050134 12-MAY-06
2006050135 12-MAY-06
2006050136 15-MAY-06
2006050137 04-MAY-06
2006050138 03-MAY-06
2006050139 03-MAY-06
2006050140 03-MAY-06
2006050141 03-MAY-06
2006050142 03-MAY-06
2006050143 03-MAY-06
After Query
InvNo Date
2006050001 03-MAY-06
2006050002 03-MAY-06
2006050003 03-MAY-06
2006050004 03-MAY-06
2006050005 03-MAY-06
2006050006 03-MAY-06
2006050007 04-MAY-06
2006050008 12-MAY-06
2006050009 12-MAY-06
2006050010 12-MAY-06
2006050011 15-MAY-06
Any idea please ?
Best Regards,
LuqmanCreate a new table with
Select InvNo ,Date Into NewTable From OriginalTable Order By Date -- I hope
you have more field to use in order by since the date is repeating
Then goto newtable and add a new Identity Field as ID
Now you should have
New Table
ID InvNo Date
1 2006050138 03-MAY-06
2 2006050139 03-MAY-06
3 etc
4
5
Now
you can update your original table by joining with new table and and
formating your new InvNo using ID field.
hope this helps,
erdal
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OjrLclVhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>|||create table invoice_sam(
InvNo varchar(25),
Date smalldatetime)
insert into invoice_sam values('2006050133','12-MAY-06')
insert into invoice_sam values('2006050134','12-MAY-06')
insert into invoice_sam values('2006050135','12-MAY-06')
insert into invoice_sam values('2006050136','15-MAY-06')
insert into invoice_sam values('2006050137','04-MAY-06')
insert into invoice_sam values('2006050138','03-MAY-06')
insert into invoice_sam values('2006050139','03-MAY-06')
insert into invoice_sam values('2006050140','03-MAY-06')
insert into invoice_sam values('2006050141','03-MAY-06')
insert into invoice_sam values('2006050142','03-MAY-06')
insert into invoice_sam values('2006050143','03-MAY-06')
select identity(int,1,1) as sno,
invno,
date
into #step1
from invoice_sam
order by date asc
select substring(invno,1,6) +
case when sno between 1 and 9 then '000' + convert(varchar(5),sno)
when sno between 10 and 99 then '00' + convert(varchar(5),sno)
when sno between 100 and 999 then '0' + convert(varchar(5),sno)
else convert(varchar(5),sno)
End as invno,
date
from #step1
Regards
Sudarshan Selvaraja
"Luqman" wrote:
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||I hope you want the number to start from 1 for each month ..
You can try this Query
UPDATE table1 SET InvNum =
LEFT(CONVERT(VARCHAR,InvDate,112),6) +
REPLACE
(
STR(
(SELECT
COUNT(*)
FROM
Table1 t2
WHERE
t2.Num1 <= Table1.Num1
AND YEAR(Table1.InvDate) = YEAR(t2.InvDate) AND MONTH(Table1.InvDate) =
MONTH(t2.InvDate)
)
,4),
' ','0')
- Sha Anand
"Luqman" wrote:
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||A bit simpler, (not requiring a temporary table)
select left(s1.InvNo,6) +
right('0000'+
cast((select count(*)
from invoice_sam s2
where s2.Date < s1.Date
or (s2.Date = s1.Date
and s2.InvNo <= s1.InvNo)) as varchar(4)),4) as
InvNo,
Date
from invoice_sam s1
order by 1
After sorting by date, the 1st invoice no. to be started with 2006050001
Current Records in table
Before Query
InvNo Date
2006050133 12-MAY-06
2006050134 12-MAY-06
2006050135 12-MAY-06
2006050136 15-MAY-06
2006050137 04-MAY-06
2006050138 03-MAY-06
2006050139 03-MAY-06
2006050140 03-MAY-06
2006050141 03-MAY-06
2006050142 03-MAY-06
2006050143 03-MAY-06
After Query
InvNo Date
2006050001 03-MAY-06
2006050002 03-MAY-06
2006050003 03-MAY-06
2006050004 03-MAY-06
2006050005 03-MAY-06
2006050006 03-MAY-06
2006050007 04-MAY-06
2006050008 12-MAY-06
2006050009 12-MAY-06
2006050010 12-MAY-06
2006050011 15-MAY-06
Any idea please ?
Best Regards,
LuqmanCreate a new table with
Select InvNo ,Date Into NewTable From OriginalTable Order By Date -- I hope
you have more field to use in order by since the date is repeating
Then goto newtable and add a new Identity Field as ID
Now you should have
New Table
ID InvNo Date
1 2006050138 03-MAY-06
2 2006050139 03-MAY-06
3 etc
4
5
Now
you can update your original table by joining with new table and and
formating your new InvNo using ID field.
hope this helps,
erdal
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:OjrLclVhGHA.4404@.TK2MSFTNGP05.phx.gbl...
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>|||create table invoice_sam(
InvNo varchar(25),
Date smalldatetime)
insert into invoice_sam values('2006050133','12-MAY-06')
insert into invoice_sam values('2006050134','12-MAY-06')
insert into invoice_sam values('2006050135','12-MAY-06')
insert into invoice_sam values('2006050136','15-MAY-06')
insert into invoice_sam values('2006050137','04-MAY-06')
insert into invoice_sam values('2006050138','03-MAY-06')
insert into invoice_sam values('2006050139','03-MAY-06')
insert into invoice_sam values('2006050140','03-MAY-06')
insert into invoice_sam values('2006050141','03-MAY-06')
insert into invoice_sam values('2006050142','03-MAY-06')
insert into invoice_sam values('2006050143','03-MAY-06')
select identity(int,1,1) as sno,
invno,
date
into #step1
from invoice_sam
order by date asc
select substring(invno,1,6) +
case when sno between 1 and 9 then '000' + convert(varchar(5),sno)
when sno between 10 and 99 then '00' + convert(varchar(5),sno)
when sno between 100 and 999 then '0' + convert(varchar(5),sno)
else convert(varchar(5),sno)
End as invno,
date
from #step1
Regards
Sudarshan Selvaraja
"Luqman" wrote:
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||I hope you want the number to start from 1 for each month ..
You can try this Query
UPDATE table1 SET InvNum =
LEFT(CONVERT(VARCHAR,InvDate,112),6) +
REPLACE
(
STR(
(SELECT
COUNT(*)
FROM
Table1 t2
WHERE
t2.Num1 <= Table1.Num1
AND YEAR(Table1.InvDate) = YEAR(t2.InvDate) AND MONTH(Table1.InvDate) =
MONTH(t2.InvDate)
)
,4),
' ','0')
- Sha Anand
"Luqman" wrote:
> How can I renumber the following Invoice Nos. through Query.
> After sorting by date, the 1st invoice no. to be started with 2006050001
> Current Records in table
> Before Query
> InvNo Date
> 2006050133 12-MAY-06
> 2006050134 12-MAY-06
> 2006050135 12-MAY-06
> 2006050136 15-MAY-06
> 2006050137 04-MAY-06
> 2006050138 03-MAY-06
> 2006050139 03-MAY-06
> 2006050140 03-MAY-06
> 2006050141 03-MAY-06
> 2006050142 03-MAY-06
> 2006050143 03-MAY-06
> After Query
> InvNo Date
> 2006050001 03-MAY-06
> 2006050002 03-MAY-06
> 2006050003 03-MAY-06
> 2006050004 03-MAY-06
> 2006050005 03-MAY-06
> 2006050006 03-MAY-06
> 2006050007 04-MAY-06
> 2006050008 12-MAY-06
> 2006050009 12-MAY-06
> 2006050010 12-MAY-06
> 2006050011 15-MAY-06
> Any idea please ?
>
> Best Regards,
> Luqman
>
>
>|||A bit simpler, (not requiring a temporary table)
select left(s1.InvNo,6) +
right('0000'+
cast((select count(*)
from invoice_sam s2
where s2.Date < s1.Date
or (s2.Date = s1.Date
and s2.InvNo <= s1.InvNo)) as varchar(4)),4) as
InvNo,
Date
from invoice_sam s1
order by 1
Subscribe to:
Posts (Atom)