case statement using cast

Hi guys,

The value in the field ACCOUNTS.ACCOUNTKEY is something like: '3130005'

I need to read the characters from position 4 to 6. If the value of that Substring is equal to the VALUE Zero then write "Zero".

In the code below, the first "case" is working nice, but the second (red one) is getting ERROR.

Please Help.

"SELECT ACCOUNTS.ACCOUNTKEY," _
& " Case When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " Case When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3) as int) =0 then 'Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Finding Zero' "
Thanks in advance,

Aldo.

Hi,

What is the errormessage?

Is it possible that there are non-nummeric values at these positions?

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

the data in the field is a string containing nummeric characters...

I solved the problem using the code below:

SLC = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)= 0 then 'Zero'" _
& " When CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)>= 1 " _
& "And CAST(SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)as int)<= 699 then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values', "

The ERROR in the code I uploaded earlier was using the word "Case" in both cases:

... Case When ...

...Case When...

instead of:

... Case

When

When

This code is working too:

sqlString = "SELECT ACCOUNTS.ACCOUNTKEY AS 'X'," _
& " Case " _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)= '000' then 'Zero'" _
& " When SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)>= '001' And SUBSTRING(ACCOUNTS.ACCOUNTKEY, 4, 3)<= '699' then 'Non-Zero'" _
& " Else 'Unknown'" _
& " End " _
& "AS 'Clasifying Values' "

I'll be glad to learn some other good idea.

Thanks,

Aldo.|||Just an FYI, this is a transact-sql question, not an SSIS question. To tie this into SSIS, you could avoid doing the case statement in your SQL and do it in a derived column transformation.

Case order by

Hello
I was wondering if you guys could help me, I have a table with Firstname and
Lastname columns.
And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can sort
Firstname ASC/DESC or Lastname ASC/DESC....
how's that done, i need to put 4 different Cases?
TIA
/LasseWhat about that
Use northwind
DECLARE @.col varchar(200)
DECLARE @.Direc varchar(200)
SET @.col = 'OrderID'
SET @.Direc = 'ASC'
EXEC('Select * from Orders Order by ' + @.col + ' ' + @.Direc)
http://www.sommarskog.se/dynamic_sql.html
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Lasse Edsvik" <lasse@.nospam.com> schrieb im Newsbeitrag
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||SELECT <column list>
FROM <table>
ORDER BY CASE @.AscDesc WHEN 'ASC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END ASC,
CASE @.AscDesc WHEN 'DESC' THEN
CASE @.SortCol
WHEN 'Firstname' THEN Firstname
WHEN 'Lastname' THEN Lastname
END END DESC,
Jacco Schalkwijk
SQL Server MVP
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
> and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
> sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||Lasse
ORDER BY CASE WHEN @.sort = 'col1' AND @.dir = 1 THEN col1 END ASC,
CASE WHEN @.sort = 'col1' AND @.dir = 0 THEN col1 END DESC,
CASE WHEN @.sort = 'col2' AND @.dir = 1 THEN col2 END ASC,
CASE WHEN @.sort = 'col2' AND @.dir = 0 THEN col2 END DESC
"Lasse Edsvik" <lasse@.nospam.com> wrote in message
news:OKlIIzxSFHA.1384@.TK2MSFTNGP09.phx.gbl...
> Hello
> I was wondering if you guys could help me, I have a table with Firstname
and
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can
sort
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>|||How do I use a variable in an ORDER BY clause?
http://www.aspfaq.com/show.asp?id=2501
AMB
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>|||The below uses one input variable to control which column to sort by, and
whether to cort asc or desc...
Use NorthWind
Declare @.Order TinyInt
Set @.Order = 1 -- 2,3,4
-- --
Select * From Customers
Order By Case @.Order
When 1 Then CompanyName
When 3 Then ContactName
End,
Case @.Order
When 2 Then CompanyName
When 4 Then ContactName
End Desc
"Lasse Edsvik" wrote:

> Hello
> I was wondering if you guys could help me, I have a table with Firstname a
nd
> Lastname columns.
> And I want to pass a parameter @.SortCol char(10) and @.AscDesc, so I can so
rt
> Firstname ASC/DESC or Lastname ASC/DESC....
> how's that done, i need to put 4 different Cases?
> TIA
> /Lasse
>
>sql

capturing reportviewer parameters

Hi Guys
I have an application which consists of a navigation bar and a frame.
When a user selects a hyperlink on the navigation bar that particular
report is displayed in the frame. Now these reports contains links
through which the user can jump to some other report which is not
present in the navigation bar. Now when the user clicks any other link
on the navigation bar the new report is starting up using the default
parameters set for that report. But i dont want this to happen. The
report viewer should take the latest parameters set in the report
viewer and pass them to the new report that will be generated when the
user clicks on the navigation bar. Can any body help with this aspect.
it is blowing up my brains. dont even know that whether this is
possible.
Also can we somehow get the url of the report being displayed in the
report viewer." i know that report viewer is an iframe which uses url
access beneath it to access the reports." Only the first report server
url is encoded in the application. But when the user navigated to some
other report through the links in reports how do i get access to that
particular report url .
any help will really be great. I am wondering is it really possible to
do the stuff i just mentioned above. Any tips on this will really save
me a lot of time.
Thanks in advance.../......
Passxunlimitedyou have to add some code to save the parameters and reuse them when you
open a new report.
you have to intercept some events (from the reportviewer control) to
retrieve the parameters when the first report is refreshed with new
parameters, save the values into a the session, in the page load event if
you open a new report change the values of the parameters of these reports
regarding what you have saved before.
its not complicated, the API is easy to use to do this.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> Hi Guys
> I have an application which consists of a navigation bar and a frame.
> When a user selects a hyperlink on the navigation bar that particular
> report is displayed in the frame. Now these reports contains links
> through which the user can jump to some other report which is not
> present in the navigation bar. Now when the user clicks any other link
> on the navigation bar the new report is starting up using the default
> parameters set for that report. But i dont want this to happen. The
> report viewer should take the latest parameters set in the report
> viewer and pass them to the new report that will be generated when the
> user clicks on the navigation bar. Can any body help with this aspect.
> it is blowing up my brains. dont even know that whether this is
> possible.
> Also can we somehow get the url of the report being displayed in the
> report viewer." i know that report viewer is an iframe which uses url
> access beneath it to access the reports." Only the first report server
> url is encoded in the application. But when the user navigated to some
> other report through the links in reports how do i get access to that
> particular report url .
> any help will really be great. I am wondering is it really possible to
> do the stuff i just mentioned above. Any tips on this will really save
> me a lot of time.
> Thanks in advance.../......
> Passxunlimited
>|||i was trying to do exactly the same thing . But could not find any
events associated with report viewer wherein i can catch the parameter
values or session state. Any idea or resources regarding this.
thanks
passx
Jeje wrote:
> you have to add some code to save the parameters and reuse them when you
> open a new report.
> you have to intercept some events (from the reportviewer control) to
> retrieve the parameters when the first report is refreshed with new
> parameters, save the values into a the session, in the page load event if
> you open a new report change the values of the parameters of these reports
> regarding what you have saved before.
> its not complicated, the API is easy to use to do this.
>
> "Passx" <passxunlimited@.gmail.com> wrote in message
> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
> > Hi Guys
> >
> > I have an application which consists of a navigation bar and a frame.
> > When a user selects a hyperlink on the navigation bar that particular
> > report is displayed in the frame. Now these reports contains links
> > through which the user can jump to some other report which is not
> > present in the navigation bar. Now when the user clicks any other link
> > on the navigation bar the new report is starting up using the default
> > parameters set for that report. But i dont want this to happen. The
> > report viewer should take the latest parameters set in the report
> > viewer and pass them to the new report that will be generated when the
> > user clicks on the navigation bar. Can any body help with this aspect.
> > it is blowing up my brains. dont even know that whether this is
> > possible.
> >
> > Also can we somehow get the url of the report being displayed in the
> > report viewer." i know that report viewer is an iframe which uses url
> > access beneath it to access the reports." Only the first report server
> > url is encoded in the application. But when the user navigated to some
> > other report through the links in reports how do i get access to that
> > particular report url .
> >
> > any help will really be great. I am wondering is it really possible to
> > do the stuff i just mentioned above. Any tips on this will really save
> > me a lot of time.
> >
> > Thanks in advance.../......
> > Passxunlimited
> >|||try the onunload event or any event after the rendering step.
"Passx" <passxunlimited@.gmail.com> wrote in message
news:1167942475.840285.10930@.51g2000cwl.googlegroups.com...
>i was trying to do exactly the same thing . But could not find any
> events associated with report viewer wherein i can catch the parameter
> values or session state. Any idea or resources regarding this.
>
> thanks
> passx
> Jeje wrote:
>> you have to add some code to save the parameters and reuse them when you
>> open a new report.
>> you have to intercept some events (from the reportviewer control) to
>> retrieve the parameters when the first report is refreshed with new
>> parameters, save the values into a the session, in the page load event if
>> you open a new report change the values of the parameters of these
>> reports
>> regarding what you have saved before.
>> its not complicated, the API is easy to use to do this.
>>
>> "Passx" <passxunlimited@.gmail.com> wrote in message
>> news:1167432103.071025.84650@.a3g2000cwd.googlegroups.com...
>> > Hi Guys
>> >
>> > I have an application which consists of a navigation bar and a frame.
>> > When a user selects a hyperlink on the navigation bar that particular
>> > report is displayed in the frame. Now these reports contains links
>> > through which the user can jump to some other report which is not
>> > present in the navigation bar. Now when the user clicks any other link
>> > on the navigation bar the new report is starting up using the default
>> > parameters set for that report. But i dont want this to happen. The
>> > report viewer should take the latest parameters set in the report
>> > viewer and pass them to the new report that will be generated when the
>> > user clicks on the navigation bar. Can any body help with this aspect.
>> > it is blowing up my brains. dont even know that whether this is
>> > possible.
>> >
>> > Also can we somehow get the url of the report being displayed in the
>> > report viewer." i know that report viewer is an iframe which uses url
>> > access beneath it to access the reports." Only the first report server
>> > url is encoded in the application. But when the user navigated to some
>> > other report through the links in reports how do i get access to that
>> > particular report url .
>> >
>> > any help will really be great. I am wondering is it really possible to
>> > do the stuff i just mentioned above. Any tips on this will really save
>> > me a lot of time.
>> >
>> > Thanks in advance.../......
>> > Passxunlimited
>> >
>

Can't use integer parameter with dateadd?

Hey guys I have the following in my SQL statement:

DATEADD(hh, @.hours, @.startTime)

hours is an integer and startTime is actually a string, but the thing is that this statement works fine if I use it like this:

DATEADD(hh, 8, @.startTime)

The first version is what I need so that the user can control from a parameter and I get the following error:

Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.

Thanks!

BJ

have you tried to convert the variable?

Code Snippet

DATEADD(hh, convert(int,(@.hours)), @.startTime)

Simone

Can't use integer parameter with dateadd?

Hey guys I have the following in my SQL statement:
DATEADD(hh, @.hours, @.startTime)
hours is an integer and startTime is actually a string, but the thing is
that this statement works fine if I use it like this:
DATEADD(hh, 8, @.startTime)
The first version is what I need so that the user can control from a
parameter and I get the following error:
Error Source: System.Data
Error Message: Failed to convert parameter value from a Decimal to a DateTime.
Thanks!
BJOn May 11, 1:50 pm, bjkaledas <bjkale...@.discussions.microsoft.com>
wrote:
> Hey guys I have the following in my SQL statement:
> DATEADD(hh, @.hours, @.startTime)
> hours is an integer and startTime is actually a string, but the thing is
> that this statement works fine if I use it like this:
> DATEADD(hh, 8, @.startTime)
> The first version is what I need so that the user can control from a
> parameter and I get the following error:
> Error Source: System.Data
> Error Message: Failed to convert parameter value from a Decimal to a DateTime.
> Thanks!
> BJ
You might need to do something like this:
DATEADD(hh, @.hours, CAST(@.startTime AS DATETIME))
- or- something like this in the report:
=Dateadd("h", Parameters!Hours.Value, CDate(Fields!startTime.Value))
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant

Cant think how to do....

Hi guys..if anyone has the time, could some1 help me with a querie i am trying to do on my DB..i attached the tables..and well,

i am trying to do...given the start and end dates, produce report showing the performance of each sales rep. over that period. should begin with the rep who has most orders by value and include total units sold and total order value

geez, just writing what i wanna do sounds complicated.
if anyone can gimme a hand it would be much appreciated!
thanksoh, this sounds so much like a school assignment

the only way i help people with homework is if they've already written the sql and shown their thinking process, so that i can offer corrections or suggestions for improvement|||Hi, yes it is a question sheet i have to do...and i understand why you said what you said...so ill try to explain what i understand from the question and hopefully you can help me :)

well from what i can see, i am going to have to join the salesRep table with ShopOrder. and use the SalesRepID as the link. but, its what comes next that is just too mixed up for me...i am guessing i have to do a sum of the salesrep...that then mean i have to link to the orderline table to see the quantity right??
hmmmm, ok...i am gonna have another think..i am talking rubbish!
ill edit this in a bit....tbc|||you're on the right track

join SalesRep to ShopOrder

can't stop there, must also join to OrderLine, to get the value of Quantity*UnitSellingPrice

now sum those up, so we need a GROUP BY

how about by SalesRep.Name

don't forget to sum the number of units sold, too|||ok, this is where i am at...thanks for the help :)

SELECT salesRep.salesRepId, salesRep.Name, SUM(Quantity*UnitSellingPrice) AS "units sold"
WHERE salesRep.salesRepID = ShopOrder.salesRepID, orderLine.ShopOrderID = ShopOrder.ShopOrderID
FROM salesRep, ShopOrder, OrderLine
GROUP BY salesRep.Name;

something is wrong there right??
thanks for the guidance :)|||WHERE comes after FROM

clauses in the WHERE must be separated by ANDs, not commas

alternative: use JOIN syntax, and replace your FROM and WHERE with this:
select ...
from salesRep
inner
join ShopOrder
on salesRep.salesRepID = ShopOrder.salesRepID
inner
join OrderLine
on ShopOrder.ShopOrderID = orderLine.ShopOrderID
group
by ...

the GROUP BY must have all the non-aggregate columns in the SELECT list|||i think i understand that way of joining.....this way is better?
i am getting a error msg:

mysql> select salesRep.salesRepId, salesRep.Name
-> from salesRep inner join ShopOrder on salesRep.salesRepID = ShopOrder.sal
esRepID
-> inner join OrderLine on ShopOrder.ShopOrderID = orderLine.ShopOrderID
-> group by salesRep.salesRepId, salesRep.Name;
ERROR 1109: Unknown table 'orderLine' in on clause

i didnt put the quantity yet because i want to check that it works...am i way off?|||case sensitive table name

sorry, that was my bad

:(|||thats ok, i cant believe i didnt spot it!!!! must cos my brain has turned into jello!!!....

select salesRep.salesRepId, salesRep.Name, SUM(Quantity*UnitSellingPrice) AS "Total"
from salesRep inner join ShopOrder on salesRep.salesRepID = ShopOrder.salesRepID
inner join OrderLine on ShopOrder.ShopOrderID = OrderLine.ShopOrderID
group by salesRep.salesRepId, salesRep.Name;

i gota that working...but i am having trouble understanding, or breaking down the question..ie what it wants, its confusing me!! :( this bit..

" the rep who has most orders by value and include total units sold and total order value "

thanks for all the help...ur a star|||"begin with the rep who has most orders by value"

i.e sort the results by Total descending

don't forget your other sum for number of units|||select salesRep.salesRepId, salesRep.Name, SUM(Quantity*UnitSellingPrice) AS "Total", SUM(Quantity) AS "Units", SUM(OrderLine.ShopOrderID) AS "Total Orders"
from salesRep inner join ShopOrder on salesRep.salesRepID = ShopOrder.salesRepID
inner join OrderLine on ShopOrder.ShopOrderID = OrderLine.ShopOrderID
group by salesRep.salesRepId, salesRep.Name
ORDER by "Total" DESC;

i think i got it ? right...seems to work...

now to do the bit about given start and end dates?
i tried putting a WHEN and giving dates but it didnt work :(
honestly i do try before bugging you, i feel useless having to ask so much.....but thanks, if you have time to show me, brilliant, thanks...|||not WHEN, WHERE

and if fred dobbs sold an order with ShopOrderID = 21, if there were 5 items in that order, his "Total Orders" would be 105, and would be wrong