Hi
I am looking for freequently using fuction like Find number of characters in a word.
Ex:
[code]
Declare @.Str='AXG00023'
If I use the function like getnumberofChars(@.Str) Then it has to give the result is 3.
Because rest of all numeric valus.
Is we have any function like this in SqlServer
please Help me
:confused:Yes,
You can use the DATALENGTH(), which will return the number of characters in a string.
If you are using that on a char datatype, you may want to use a Rtrim() on the field first before getting that datalenght.
ie. datalength(rtrim(@.stringname))
Scooter Mcfly|||DECLARE
@.Str varchar(50),
@.i int,
@.x int
SET @.Str ='AXG00X023'
SET @.i = 0
SET @.x=1
WHILE @.x <= DATALENGTH(@.Str) BEGIN
IF ISNUMERIC(SUBSTRING(@.Str,@.x,1)) = 0
SET @.i=@.i+1
SET @.x=@.x+1
END
print @.i
If you are using SQL Server 2000 you could put this code into your own user defined function.
Showing posts with label function. Show all posts
Showing posts with label function. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Please HELP
I wrote a UDF in SQL server 2005 that looks like this:
You need to close the bracket.
Yea that's a mistake i did when i pasted it here...but in the actual server it has the bracket.
CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)
IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END
Which gives me this error:
Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.
I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!
Quote:
Originally Posted by gggram2000
I wrote a UDF in SQL server 2005 that looks like this:
CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)
IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END
Which gives me this error:
Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.
I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!
You need to close the bracket.
INSERT INTO Sales(InvoiceNumber)|||
Quote:
Originally Posted by amitpatel66
You need to close the bracket.
INSERT INTO Sales(InvoiceNumber)
Yea that's a mistake i did when i pasted it here...but in the actual server it has the bracket.
A question would be how to insert a value (eg. Name) into a table row block. Like: Insert Into Table (ClolumnName) Value(@.ColumnName) But I want to enter it into and existing column/row to replace the value of that specific column with "Name". Let's say for that column it has "None"...I want to change it to "Name"...how do i do that?
Friday, March 23, 2012
Please help
Hi,
How can i use a java script function in sql reporting services.
I have a textbox - which i want to make as a hyperlink and when the user
clicks on it - it has to open another report or say url in another browser.
i am usin
<hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
but it doesnt work . Can you please help me how to do this ?
Thanks
RPDon't be doing this by hand. Use RS capabilities. For the textbox do a right
mouse click, properties, advanced, navigation, jump to URL.
Then put in this for a report with parameters:
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
For a website put in this:
="javascript:void(window.open('http://www.google.com','_blank'))"
Note that you have to have SP1 or SP2 installed. This functionality was
added in SP1.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RP" <RP@.discussions.microsoft.com> wrote in message
news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> Hi,
> How can i use a java script function in sql reporting services.
> I have a textbox - which i want to make as a hyperlink and when the user
> clicks on it - it has to open another report or say url in another
> browser.
> i am using
> <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> but it doesnt work . Can you please help me how to do this ?
> Thanks
> RP|||Bruce,
I didnt do this by hand. I rt clicked on the textbox, properties - advanced
- navigation
jump to url.
The funny thing is :
if i give the
="javascript:void(window.open('http://www.google.com','_blank'))"
and click on the link, i get 'page cannont be displayed' with
"javascript:void(window.open('http://www.google.com','_blank'))"
in the url.
click on that - will open a new window.
2. if i access the report from localhost\reports\reportname - this is the
situation
but if i access the report from localhost\reportserver\reportname - it works
absolutely fine.
what is the diff. between reports and reportserver ?
Thanks
"Bruce L-C [MVP]" wrote:
> Don't be doing this by hand. Use RS capabilities. For the textbox do a right
> mouse click, properties, advanced, navigation, jump to URL.
> Then put in this for a report with parameters:
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> For a website put in this:
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> Note that you have to have SP1 or SP2 installed. This functionality was
> added in SP1.
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> > Hi,
> > How can i use a java script function in sql reporting services.
> > I have a textbox - which i want to make as a hyperlink and when the user
> > clicks on it - it has to open another report or say url in another
> > browser.
> > i am using
> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> > but it doesnt work . Can you please help me how to do this ?
> > Thanks
> > RP
>
>|||I have a little test report that has what looks exactly the same to me.
= "javascript:void(window.open('http://www.google.com','_blank'))"
It works regardless of how the report is pulled up (I did it both ways:
reports and reportserver).
Don't know what to tell you, it works for me (I am on SP2).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RP" <RP@.discussions.microsoft.com> wrote in message
news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> Bruce,
> I didnt do this by hand. I rt clicked on the textbox, properties -
> advanced
> - navigation
> jump to url.
> The funny thing is :
> if i give the
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> and click on the link, i get 'page cannont be displayed' with
> "javascript:void(window.open('http://www.google.com','_blank'))"
> in the url.
> click on that - will open a new window.
> 2. if i access the report from localhost\reports\reportname - this is the
> situation
> but if i access the report from localhost\reportserver\reportname - it
> works
> absolutely fine.
> what is the diff. between reports and reportserver ?
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Don't be doing this by hand. Use RS capabilities. For the textbox do a
>> right
>> mouse click, properties, advanced, navigation, jump to URL.
>> Then put in this for a report with parameters:
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up
>> with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> For a website put in this:
>> ="javascript:void(window.open('http://www.google.com','_blank'))"
>> Note that you have to have SP1 or SP2 installed. This functionality was
>> added in SP1.
>>
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "RP" <RP@.discussions.microsoft.com> wrote in message
>> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
>> > Hi,
>> > How can i use a java script function in sql reporting services.
>> > I have a textbox - which i want to make as a hyperlink and when the
>> > user
>> > clicks on it - it has to open another report or say url in another
>> > browser.
>> > i am using
>> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
>> > but it doesnt work . Can you please help me how to do this ?
>> > Thanks
>> > RP
>>|||Bruce,
Can you pls post the code of your sample report once. my javascript looks
exactly the same. unfortunately it works if i say reportserver instead of
report. is there anyother way to check?
Thanks
RP
"Bruce L-C [MVP]" wrote:
> I have a little test report that has what looks exactly the same to me.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> It works regardless of how the report is pulled up (I did it both ways:
> reports and reportserver).
> Don't know what to tell you, it works for me (I am on SP2).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> > Bruce,
> > I didnt do this by hand. I rt clicked on the textbox, properties -
> > advanced
> > - navigation
> > jump to url.
> > The funny thing is :
> > if i give the
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > and click on the link, i get 'page cannont be displayed' with
> > "javascript:void(window.open('http://www.google.com','_blank'))"
> > in the url.
> > click on that - will open a new window.
> >
> > 2. if i access the report from localhost\reports\reportname - this is the
> > situation
> > but if i access the report from localhost\reportserver\reportname - it
> > works
> > absolutely fine.
> >
> > what is the diff. between reports and reportserver ?
> > Thanks
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Don't be doing this by hand. Use RS capabilities. For the textbox do a
> >> right
> >> mouse click, properties, advanced, navigation, jump to URL.
> >>
> >> Then put in this for a report with parameters:
> >> Here is an example of a Jump to URL link I use. This causes Excel to come
> >> up
> >> with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >> For a website put in this:
> >>
> >> ="javascript:void(window.open('http://www.google.com','_blank'))"
> >>
> >> Note that you have to have SP1 or SP2 installed. This functionality was
> >> added in SP1.
> >>
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "RP" <RP@.discussions.microsoft.com> wrote in message
> >> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> >> > Hi,
> >> > How can i use a java script function in sql reporting services.
> >> > I have a textbox - which i want to make as a hyperlink and when the
> >> > user
> >> > clicks on it - it has to open another report or say url in another
> >> > browser.
> >> > i am using
> >> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> >> > but it doesnt work . Can you please help me how to do this ?
> >> > Thanks
> >> > RP
> >>
> >>
> >>
>
>|||oh b.t.w,
even i have SP2
"Bruce L-C [MVP]" wrote:
> I have a little test report that has what looks exactly the same to me.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> It works regardless of how the report is pulled up (I did it both ways:
> reports and reportserver).
> Don't know what to tell you, it works for me (I am on SP2).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> > Bruce,
> > I didnt do this by hand. I rt clicked on the textbox, properties -
> > advanced
> > - navigation
> > jump to url.
> > The funny thing is :
> > if i give the
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > and click on the link, i get 'page cannont be displayed' with
> > "javascript:void(window.open('http://www.google.com','_blank'))"
> > in the url.
> > click on that - will open a new window.
> >
> > 2. if i access the report from localhost\reports\reportname - this is the
> > situation
> > but if i access the report from localhost\reportserver\reportname - it
> > works
> > absolutely fine.
> >
> > what is the diff. between reports and reportserver ?
> > Thanks
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Don't be doing this by hand. Use RS capabilities. For the textbox do a
> >> right
> >> mouse click, properties, advanced, navigation, jump to URL.
> >>
> >> Then put in this for a report with parameters:
> >> Here is an example of a Jump to URL link I use. This causes Excel to come
> >> up
> >> with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >> For a website put in this:
> >>
> >> ="javascript:void(window.open('http://www.google.com','_blank'))"
> >>
> >> Note that you have to have SP1 or SP2 installed. This functionality was
> >> added in SP1.
> >>
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "RP" <RP@.discussions.microsoft.com> wrote in message
> >> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> >> > Hi,
> >> > How can i use a java script function in sql reporting services.
> >> > I have a textbox - which i want to make as a hyperlink and when the
> >> > user
> >> > clicks on it - it has to open another report or say url in another
> >> > browser.
> >> > i am using
> >> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> >> > but it doesnt work . Can you please help me how to do this ?
> >> > Thanks
> >> > RP
> >>
> >>
> >>
>
>|||bruce -
got it - figured it myself.
"Bruce L-C [MVP]" wrote:
> I have a little test report that has what looks exactly the same to me.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> It works regardless of how the report is pulled up (I did it both ways:
> reports and reportserver).
> Don't know what to tell you, it works for me (I am on SP2).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> > Bruce,
> > I didnt do this by hand. I rt clicked on the textbox, properties -
> > advanced
> > - navigation
> > jump to url.
> > The funny thing is :
> > if i give the
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > and click on the link, i get 'page cannont be displayed' with
> > "javascript:void(window.open('http://www.google.com','_blank'))"
> > in the url.
> > click on that - will open a new window.
> >
> > 2. if i access the report from localhost\reports\reportname - this is the
> > situation
> > but if i access the report from localhost\reportserver\reportname - it
> > works
> > absolutely fine.
> >
> > what is the diff. between reports and reportserver ?
> > Thanks
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Don't be doing this by hand. Use RS capabilities. For the textbox do a
> >> right
> >> mouse click, properties, advanced, navigation, jump to URL.
> >>
> >> Then put in this for a report with parameters:
> >> Here is an example of a Jump to URL link I use. This causes Excel to come
> >> up
> >> with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >> For a website put in this:
> >>
> >> ="javascript:void(window.open('http://www.google.com','_blank'))"
> >>
> >> Note that you have to have SP1 or SP2 installed. This functionality was
> >> added in SP1.
> >>
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "RP" <RP@.discussions.microsoft.com> wrote in message
> >> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> >> > Hi,
> >> > How can i use a java script function in sql reporting services.
> >> > I have a textbox - which i want to make as a hyperlink and when the
> >> > user
> >> > clicks on it - it has to open another report or say url in another
> >> > browser.
> >> > i am using
> >> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> >> > but it doesnt work . Can you please help me how to do this ?
> >> > Thanks
> >> > RP
> >>
> >>
> >>
>
>
How can i use a java script function in sql reporting services.
I have a textbox - which i want to make as a hyperlink and when the user
clicks on it - it has to open another report or say url in another browser.
i am usin
<hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
but it doesnt work . Can you please help me how to do this ?
Thanks
RPDon't be doing this by hand. Use RS capabilities. For the textbox do a right
mouse click, properties, advanced, navigation, jump to URL.
Then put in this for a report with parameters:
Here is an example of a Jump to URL link I use. This causes Excel to come up
with the data in a separate window:
="javascript:void(window.open('" & Globals!ReportServerUrl &
"?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
"&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
For a website put in this:
="javascript:void(window.open('http://www.google.com','_blank'))"
Note that you have to have SP1 or SP2 installed. This functionality was
added in SP1.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RP" <RP@.discussions.microsoft.com> wrote in message
news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> Hi,
> How can i use a java script function in sql reporting services.
> I have a textbox - which i want to make as a hyperlink and when the user
> clicks on it - it has to open another report or say url in another
> browser.
> i am using
> <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> but it doesnt work . Can you please help me how to do this ?
> Thanks
> RP|||Bruce,
I didnt do this by hand. I rt clicked on the textbox, properties - advanced
- navigation
jump to url.
The funny thing is :
if i give the
="javascript:void(window.open('http://www.google.com','_blank'))"
and click on the link, i get 'page cannont be displayed' with
"javascript:void(window.open('http://www.google.com','_blank'))"
in the url.
click on that - will open a new window.
2. if i access the report from localhost\reports\reportname - this is the
situation
but if i access the report from localhost\reportserver\reportname - it works
absolutely fine.
what is the diff. between reports and reportserver ?
Thanks
"Bruce L-C [MVP]" wrote:
> Don't be doing this by hand. Use RS capabilities. For the textbox do a right
> mouse click, properties, advanced, navigation, jump to URL.
> Then put in this for a report with parameters:
> Here is an example of a Jump to URL link I use. This causes Excel to come up
> with the data in a separate window:
> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> For a website put in this:
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> Note that you have to have SP1 or SP2 installed. This functionality was
> added in SP1.
>
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> > Hi,
> > How can i use a java script function in sql reporting services.
> > I have a textbox - which i want to make as a hyperlink and when the user
> > clicks on it - it has to open another report or say url in another
> > browser.
> > i am using
> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> > but it doesnt work . Can you please help me how to do this ?
> > Thanks
> > RP
>
>|||I have a little test report that has what looks exactly the same to me.
= "javascript:void(window.open('http://www.google.com','_blank'))"
It works regardless of how the report is pulled up (I did it both ways:
reports and reportserver).
Don't know what to tell you, it works for me (I am on SP2).
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"RP" <RP@.discussions.microsoft.com> wrote in message
news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> Bruce,
> I didnt do this by hand. I rt clicked on the textbox, properties -
> advanced
> - navigation
> jump to url.
> The funny thing is :
> if i give the
> ="javascript:void(window.open('http://www.google.com','_blank'))"
> and click on the link, i get 'page cannont be displayed' with
> "javascript:void(window.open('http://www.google.com','_blank'))"
> in the url.
> click on that - will open a new window.
> 2. if i access the report from localhost\reports\reportname - this is the
> situation
> but if i access the report from localhost\reportserver\reportname - it
> works
> absolutely fine.
> what is the diff. between reports and reportserver ?
> Thanks
> "Bruce L-C [MVP]" wrote:
>> Don't be doing this by hand. Use RS capabilities. For the textbox do a
>> right
>> mouse click, properties, advanced, navigation, jump to URL.
>> Then put in this for a report with parameters:
>> Here is an example of a Jump to URL link I use. This causes Excel to come
>> up
>> with the data in a separate window:
>> ="javascript:void(window.open('" & Globals!ReportServerUrl &
>> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
>> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
>> For a website put in this:
>> ="javascript:void(window.open('http://www.google.com','_blank'))"
>> Note that you have to have SP1 or SP2 installed. This functionality was
>> added in SP1.
>>
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>> "RP" <RP@.discussions.microsoft.com> wrote in message
>> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
>> > Hi,
>> > How can i use a java script function in sql reporting services.
>> > I have a textbox - which i want to make as a hyperlink and when the
>> > user
>> > clicks on it - it has to open another report or say url in another
>> > browser.
>> > i am using
>> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
>> > but it doesnt work . Can you please help me how to do this ?
>> > Thanks
>> > RP
>>|||Bruce,
Can you pls post the code of your sample report once. my javascript looks
exactly the same. unfortunately it works if i say reportserver instead of
report. is there anyother way to check?
Thanks
RP
"Bruce L-C [MVP]" wrote:
> I have a little test report that has what looks exactly the same to me.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> It works regardless of how the report is pulled up (I did it both ways:
> reports and reportserver).
> Don't know what to tell you, it works for me (I am on SP2).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> > Bruce,
> > I didnt do this by hand. I rt clicked on the textbox, properties -
> > advanced
> > - navigation
> > jump to url.
> > The funny thing is :
> > if i give the
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > and click on the link, i get 'page cannont be displayed' with
> > "javascript:void(window.open('http://www.google.com','_blank'))"
> > in the url.
> > click on that - will open a new window.
> >
> > 2. if i access the report from localhost\reports\reportname - this is the
> > situation
> > but if i access the report from localhost\reportserver\reportname - it
> > works
> > absolutely fine.
> >
> > what is the diff. between reports and reportserver ?
> > Thanks
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Don't be doing this by hand. Use RS capabilities. For the textbox do a
> >> right
> >> mouse click, properties, advanced, navigation, jump to URL.
> >>
> >> Then put in this for a report with parameters:
> >> Here is an example of a Jump to URL link I use. This causes Excel to come
> >> up
> >> with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >> For a website put in this:
> >>
> >> ="javascript:void(window.open('http://www.google.com','_blank'))"
> >>
> >> Note that you have to have SP1 or SP2 installed. This functionality was
> >> added in SP1.
> >>
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "RP" <RP@.discussions.microsoft.com> wrote in message
> >> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> >> > Hi,
> >> > How can i use a java script function in sql reporting services.
> >> > I have a textbox - which i want to make as a hyperlink and when the
> >> > user
> >> > clicks on it - it has to open another report or say url in another
> >> > browser.
> >> > i am using
> >> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> >> > but it doesnt work . Can you please help me how to do this ?
> >> > Thanks
> >> > RP
> >>
> >>
> >>
>
>|||oh b.t.w,
even i have SP2
"Bruce L-C [MVP]" wrote:
> I have a little test report that has what looks exactly the same to me.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> It works regardless of how the report is pulled up (I did it both ways:
> reports and reportserver).
> Don't know what to tell you, it works for me (I am on SP2).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> > Bruce,
> > I didnt do this by hand. I rt clicked on the textbox, properties -
> > advanced
> > - navigation
> > jump to url.
> > The funny thing is :
> > if i give the
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > and click on the link, i get 'page cannont be displayed' with
> > "javascript:void(window.open('http://www.google.com','_blank'))"
> > in the url.
> > click on that - will open a new window.
> >
> > 2. if i access the report from localhost\reports\reportname - this is the
> > situation
> > but if i access the report from localhost\reportserver\reportname - it
> > works
> > absolutely fine.
> >
> > what is the diff. between reports and reportserver ?
> > Thanks
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Don't be doing this by hand. Use RS capabilities. For the textbox do a
> >> right
> >> mouse click, properties, advanced, navigation, jump to URL.
> >>
> >> Then put in this for a report with parameters:
> >> Here is an example of a Jump to URL link I use. This causes Excel to come
> >> up
> >> with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >> For a website put in this:
> >>
> >> ="javascript:void(window.open('http://www.google.com','_blank'))"
> >>
> >> Note that you have to have SP1 or SP2 installed. This functionality was
> >> added in SP1.
> >>
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "RP" <RP@.discussions.microsoft.com> wrote in message
> >> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> >> > Hi,
> >> > How can i use a java script function in sql reporting services.
> >> > I have a textbox - which i want to make as a hyperlink and when the
> >> > user
> >> > clicks on it - it has to open another report or say url in another
> >> > browser.
> >> > i am using
> >> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> >> > but it doesnt work . Can you please help me how to do this ?
> >> > Thanks
> >> > RP
> >>
> >>
> >>
>
>|||bruce -
got it - figured it myself.
"Bruce L-C [MVP]" wrote:
> I have a little test report that has what looks exactly the same to me.
> = "javascript:void(window.open('http://www.google.com','_blank'))"
> It works regardless of how the report is pulled up (I did it both ways:
> reports and reportserver).
> Don't know what to tell you, it works for me (I am on SP2).
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "RP" <RP@.discussions.microsoft.com> wrote in message
> news:893EB9DB-C59F-4FBD-9E0F-DDB2B17E3FB7@.microsoft.com...
> > Bruce,
> > I didnt do this by hand. I rt clicked on the textbox, properties -
> > advanced
> > - navigation
> > jump to url.
> > The funny thing is :
> > if i give the
> > ="javascript:void(window.open('http://www.google.com','_blank'))"
> > and click on the link, i get 'page cannont be displayed' with
> > "javascript:void(window.open('http://www.google.com','_blank'))"
> > in the url.
> > click on that - will open a new window.
> >
> > 2. if i access the report from localhost\reports\reportname - this is the
> > situation
> > but if i access the report from localhost\reportserver\reportname - it
> > works
> > absolutely fine.
> >
> > what is the diff. between reports and reportserver ?
> > Thanks
> >
> > "Bruce L-C [MVP]" wrote:
> >
> >> Don't be doing this by hand. Use RS capabilities. For the textbox do a
> >> right
> >> mouse click, properties, advanced, navigation, jump to URL.
> >>
> >> Then put in this for a report with parameters:
> >> Here is an example of a Jump to URL link I use. This causes Excel to come
> >> up
> >> with the data in a separate window:
> >>
> >> ="javascript:void(window.open('" & Globals!ReportServerUrl &
> >> "?/SomeFolder/SomeReport&ParamName=" & Parameters!ParamName.Value &
> >> "&rs:Format=CSV&rc:Encoding=ASCII','_blank'))"
> >>
> >> For a website put in this:
> >>
> >> ="javascript:void(window.open('http://www.google.com','_blank'))"
> >>
> >> Note that you have to have SP1 or SP2 installed. This functionality was
> >> added in SP1.
> >>
> >>
> >>
> >>
> >> --
> >> Bruce Loehle-Conger
> >> MVP SQL Server Reporting Services
> >>
> >>
> >>
> >> "RP" <RP@.discussions.microsoft.com> wrote in message
> >> news:CD453D6F-085D-4EFB-94B1-D43A6D6D677A@.microsoft.com...
> >> > Hi,
> >> > How can i use a java script function in sql reporting services.
> >> > I have a textbox - which i want to make as a hyperlink and when the
> >> > user
> >> > clicks on it - it has to open another report or say url in another
> >> > browser.
> >> > i am using
> >> > <hyperlink>="javascript:window.open('http://www.google.com',_blank)"</hyperlink>
> >> > but it doesnt work . Can you please help me how to do this ?
> >> > Thanks
> >> > RP
> >>
> >>
> >>
>
>
Friday, March 9, 2012
pl/sql package will not compile
hi,
trying to concat 1:m relationsships data to a string fails :
what did I do wrong?
PACKAGE denorm_PKG IS
FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2 ;
END denorm_PKG;
/
PACKAGE BODY denorm_PKG AS
/*
*/
FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2
IS
-- Cursor fr die Datenbankabfrage
CURSOR netzcrs (lv_netzwerkdienst n.netzwerkdienst%TYPE) IS
select n.netzwerkdienst
from netzwerkdienst n, knoten_netzwerkdienst kn
where n.id_netzwerkdienst = kn.id_netzwerkdienst
and kn.id_plan = ix_plan
and kn.id_knoten = ix_knoten;
tmpVar varchar2;
netz_rec netzcrs%ROWTYPE;
BEGIN
tmpVar := NULL;
IF NOT netzcrs%ISOPEN
THEN
OPEN netzcrs(lv_netzwerkdienst);
END IF;
FETCH netzcrs INTO netz_rec;
-- Schleife ber alle Resultdatenstze, konkateniert alle
netzwerkdienste
WHILE (netzcrs%FOUND)
LOOP
IF tmpVar is NULL
THEN
tmpVar := netz_rec.lv_netzwerkdienst;
ELSE
tmpVar := tmpVar ||','|| netz_rec.lv_netzwerkdienst;
END IF;
FETCH netzcrs INTO netz_rec;
END LOOP;
CLOSE netzcrs;
RETURN tmpVar;
END GetNetz;
END denorm_PKG;
/
Oracle Version 8.1.7.4
error msges are the following
SP2-0734: Unbekannter Befehl ab "PACKAGE de..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "FUNCTION G..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "ix_plan IN..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "END denorm..." - restliche Zeile
ignoriert.
SP2-0044: Zum Auflisten bekannter Befehle HELP
und zum Verlassen EXIT eingeben.
END denorm_PKG;
*
FEHLER in Zeile 32:
ORA-06550: line 32, column 1:
PLS-00103: Encountered the symbol "END"
SP2-0734: Unbekannter Befehl ab "PACKAGE BO..." - restliche Zeile
ignoriert.
DOC>
DOC>*/
SP2-0734: Unbekannter Befehl ab "FUNCTION G..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "ix_plan IN..." - restliche Zeile
ignoriert.
SP2-0042: Unbekannter Befehl "IS" - restliche Zeile wurde ignoriert.
SP2-0734: Unbekannter Befehl ab "CURSOR net..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "from netzw..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "where n.id..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "and kn.id_..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "and kn.id_..." - restliche Zeile
ignoriert.
SP2-0044: Zum Auflisten bekannter Befehle HELP
und zum Verlassen EXIT eingeben.
SP2-0734: Unbekannter Befehl ab "tmpVar var..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "netz_rec n..." - restliche Zeile
ignoriert.
END denorm_PKG;
*
FEHLER in Zeile 32:
ORA-06550: line 32, column 1:
PLS-00103: Encountered the symbol "END"
SQL> PACKAGE denorm_PKG IS
SP2-0734: Unbekannter Befehl ab "PACKAGE de..." - restliche Zeile
ignoriert.
SQL>Hello,
which program do you use ... I never read such error statements !!!
As I was testing your posted package with AlligatorSQL, it compiles
with any error - except that I do not have your table "knoten".
Perhaps you have to place a "CREATE OR REPLACE" before your "PACKAGE BODY" and "PACKAGE" statement ?!!?
Hope that helps
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hello,
which program do you use ... I never read such error statements !!!
As I was testing your posted package with AlligatorSQL, it compiles
with any error - except that I do not have your table "knoten".
Perhaps you have to place a "CREATE OR REPLACE" before your "PACKAGE BODY" and "PACKAGE" statement ?!!?
Hope that helps
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
I 've used sqlplus and the stmts as a script.sql|||Hello,
do you have tested the "CREATE OR REPLACE PACKAGE"
command ?
Just insert "CREATE OR REPLACE" before your package and package specification.
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
trying to concat 1:m relationsships data to a string fails :
what did I do wrong?
PACKAGE denorm_PKG IS
FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2 ;
END denorm_PKG;
/
PACKAGE BODY denorm_PKG AS
/*
*/
FUNCTION GetNetz (ix_knoten IN knoten.id_knoten%TYPE,
ix_plan IN knoten.id_plan%TYPE) RETURN VARCHAR2
IS
-- Cursor fr die Datenbankabfrage
CURSOR netzcrs (lv_netzwerkdienst n.netzwerkdienst%TYPE) IS
select n.netzwerkdienst
from netzwerkdienst n, knoten_netzwerkdienst kn
where n.id_netzwerkdienst = kn.id_netzwerkdienst
and kn.id_plan = ix_plan
and kn.id_knoten = ix_knoten;
tmpVar varchar2;
netz_rec netzcrs%ROWTYPE;
BEGIN
tmpVar := NULL;
IF NOT netzcrs%ISOPEN
THEN
OPEN netzcrs(lv_netzwerkdienst);
END IF;
FETCH netzcrs INTO netz_rec;
-- Schleife ber alle Resultdatenstze, konkateniert alle
netzwerkdienste
WHILE (netzcrs%FOUND)
LOOP
IF tmpVar is NULL
THEN
tmpVar := netz_rec.lv_netzwerkdienst;
ELSE
tmpVar := tmpVar ||','|| netz_rec.lv_netzwerkdienst;
END IF;
FETCH netzcrs INTO netz_rec;
END LOOP;
CLOSE netzcrs;
RETURN tmpVar;
END GetNetz;
END denorm_PKG;
/
Oracle Version 8.1.7.4
error msges are the following
SP2-0734: Unbekannter Befehl ab "PACKAGE de..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "FUNCTION G..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "ix_plan IN..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "END denorm..." - restliche Zeile
ignoriert.
SP2-0044: Zum Auflisten bekannter Befehle HELP
und zum Verlassen EXIT eingeben.
END denorm_PKG;
*
FEHLER in Zeile 32:
ORA-06550: line 32, column 1:
PLS-00103: Encountered the symbol "END"
SP2-0734: Unbekannter Befehl ab "PACKAGE BO..." - restliche Zeile
ignoriert.
DOC>
DOC>*/
SP2-0734: Unbekannter Befehl ab "FUNCTION G..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "ix_plan IN..." - restliche Zeile
ignoriert.
SP2-0042: Unbekannter Befehl "IS" - restliche Zeile wurde ignoriert.
SP2-0734: Unbekannter Befehl ab "CURSOR net..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "from netzw..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "where n.id..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "and kn.id_..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "and kn.id_..." - restliche Zeile
ignoriert.
SP2-0044: Zum Auflisten bekannter Befehle HELP
und zum Verlassen EXIT eingeben.
SP2-0734: Unbekannter Befehl ab "tmpVar var..." - restliche Zeile
ignoriert.
SP2-0734: Unbekannter Befehl ab "netz_rec n..." - restliche Zeile
ignoriert.
END denorm_PKG;
*
FEHLER in Zeile 32:
ORA-06550: line 32, column 1:
PLS-00103: Encountered the symbol "END"
SQL> PACKAGE denorm_PKG IS
SP2-0734: Unbekannter Befehl ab "PACKAGE de..." - restliche Zeile
ignoriert.
SQL>Hello,
which program do you use ... I never read such error statements !!!
As I was testing your posted package with AlligatorSQL, it compiles
with any error - except that I do not have your table "knoten".
Perhaps you have to place a "CREATE OR REPLACE" before your "PACKAGE BODY" and "PACKAGE" statement ?!!?
Hope that helps
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com|||Originally posted by alligatorsql.com
Hello,
which program do you use ... I never read such error statements !!!
As I was testing your posted package with AlligatorSQL, it compiles
with any error - except that I do not have your table "knoten".
Perhaps you have to place a "CREATE OR REPLACE" before your "PACKAGE BODY" and "PACKAGE" statement ?!!?
Hope that helps
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
I 've used sqlplus and the stmts as a script.sql|||Hello,
do you have tested the "CREATE OR REPLACE PACKAGE"
command ?
Just insert "CREATE OR REPLACE" before your package and package specification.
Best regards
Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com
Wednesday, March 7, 2012
PIVOT with dynamic columns names created
I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.
For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :
SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @.period
OR Period = @.period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt
Let's assume that any value 2 is selected for the @.period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).
Division CurrentPeriod PreviousPeriod
A 400 3000
B 400 100
C 470 300
D 800 2500
E 1000 1900
What if the value @.period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?
Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @.period parameter.
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FOR Period IN ( [2], [1] )
I have tried to use the @.period but it doesn't work.
Thanks in advance.
Kenny
This is a one drawback to the current Pivot feature. You will have to use dynamic sql for this.
Itzik has written a good article on this.
http://www.sqlmag.com/Article/ArticleID/94268/sql_server_94268.html
Saturday, February 25, 2012
PIVOT statement whitout knowing values
Just a small issue...
I'm trying the new SQL 2005 (Express) because the PIVOT function was finally added.
I've a table with three columns ID, Height and Width
Now I'd like to have a table with for each height the number of ID for each Width
The easiest way is to use the PIVOT statement.....but..... to use it in SQL2005 I should use:
SELECT Height, [100] AS Width01, [200] AS Width02
FROM (
SELECT ID, Height, Width FROM TestTable) p
PIVOT ( COUNT (ID) FOR Width IN([100], [200]) ) AS pvt
This kind of querry works perfectly in a static situation, but if I add new record in the table referencing the "300" Width to obtain the correct result I have to modify the query.
Is there an options or a technique for having the list of the Width dinamically filled according the table contents.
Thank you very much to anyone how can help me
H
You have to use dynamic SQL to execute the SELECT statement after generating the values for the IN list. There is no other way using static SQL code.|||To be clear, there are good reasons for this restriction.
SQL Server's PIVOT can exist anywhere in the query tree (unlike in Access), supports UNPIVOT (unlike Access), and does not require recompilation for each execution (unlike Access). These are good things for complex queries, as compilation time would be significantly worse if these did not exist.
SQL Server's query optimizer has a requirement that the column list be known before compilation begins. This allows faster compiles because we can identify duplicate alternatives more easily and avoid doing extra work during compilation. This also helps us to determine if we can avoid searching portions of the possible plan space that obviously will not help find a faster plan than what has been found so far during optimization.
I understand the desire to not have to bother specifying a column list, and perhaps that is something we can add in a future release. The reasons above are reasons it was not added in SQL 2005. Even if such a feature were added, it would be likely better if you could specify a column list to speed system throughput.
Conor Cunningham
SQL Server Query Optimization Development Lead
|||Thank you all for the clear answer, now I understood that the restriction is due to performances.
Of course this type of restriction have very few impact over small databases like the ones I working on (~100 MB). So I will keep my application over access where the power of the TRANSFORM-PIVOT scheme will help me reducing the programming effort.
Thanks again
H
Monday, February 20, 2012
Pivot misunderstood ?
Hello, i've been looking for some samples on the pivot function, but somewhere i'm making a mistake and can't find where.
This is my statement
SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, 
AS FRIDAY, [7] AS SATURDAY
FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK
PIVOT (SUM(TICKETS)
FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT
ORDER BY MOV_UI
I though to receive a line per mov_ui with the 7 sums, but .. i'm getting a line for each day ....
MOV_UI SUNDAY MONDAY THUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY
29 NULL NULL NULL 73 NULL NULL NULL
29 NULL NULL NULL NULL 72 NULL NULL
29 NULL NULL NULL NULL NULL 129 NULL
29 NULL NULL NULL NULL NULL NULL 138
29 104 NULL NULL NULL NULL NULL NULL
29 NULL 68 NULL NULL NULL NULL NULL
29 NULL NULL 58 NULL NULL NULL NULL
What am i missing .... ?
Hmm, this works for me:
CREATE TABLE test
(
movie_ui int,
dayOfWeek int,
tickets int
)
INSERT INTO test
SELECT 29,1,20
UNION ALL
SELECT 29,2,38
UNION ALL
SELECT 30,1,20
UNION ALL
SELECT 30,2,44
GO
SELECT movie_ui, [1] AS SUNDAY, [2] AS MONDAY
FROM test PIVOT (SUM(TICKETS) FOR DAYOFWEEK IN ([1], [2])) AS PVT
ORDER BY movie_ui
movie_ui SUNDAY MONDAY
-- -- --
29 20 38
30 20 44
Thx for the answer, i've found the problem ...
this was my syntax
SELECT MOV_UI, [1] AS SUNDAY, [2] AS MONDAY, [3] AS THUESDAY, [4] AS WEDNESDAY, [5] AS THURSDAY, AS FRIDAY, [7] AS SATURDAY
FROM XX.UGENT_DAILYSALES_OF_MOVIE_FIRST_WEEK
PIVOT (SUM(TICKETS)
FOR DAYOFWEEK IN ([1], [2], [3], [4], [5], , [7])) AS PVT
ORDER BY MOV_UI
Problem was that from the view xx.ugent.. there where other columns returned that where not used in the pivot, but they cause this effect, so adding them in the select mov_ui, ... and the order by helpen the problem, or chaning the 'from xx.ugent' to a
from (select mov_ui, dayofweek, tickets from xx_ugent_dailysales_of_movie_first_Week) p' solved the problem too.
Kind Regards
Subscribe to:
Posts (Atom)