case statement with an sql query statement

a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??Originally posted by ONIL
a case statement in VB is ment for a string or numeric expression. if i place a sql parameter query statement it shows type mismatch. what do i do??

can you show the statement you are trying to execute? Or a better understanding of what you are after?|||ok here's the issue
i have the following: -
1. a access table: telephone_directory (fields are first name, last name, extension_no, building_name)
2. a form with menu find and sub menus "by extension number", "by first name", "by last name". ALSO another form named PF with a text box and a listbox

3. a sql parameter query as: -
cq.SQL = "PARAMETERS something1 INTEGER; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(EXTENSION_NO,1) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,2) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,3) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,4) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,5) LIKE [something1] " & _
" OR LEFT(EXTENSION_NO,6) LIKE [something1] " & _
" OR EXTENSION_NO LIKE [something1] " & _
" ORDER BY EXTENSION_No; "
4. a bas file with the parameter query wherein case 1 is for extension number case 2 is for last names wherein the sql query is : -

cq.SQL = "PARAMETERS something1 text; SELECT * FROM TELEPHONE_DIRECTORY" & _
" WHERE LEFT(LAST_NAME,1)LIKE [something1] " & _
" OR LEFT(LAST_NAME,2)LIKE [something1] " & _
" OR LEFT(LAST_NAME,3)LIKE [something1] " & _
" OR LEFT(LAST_NAME,4)LIKE [something1] " & _
" OR LEFT(LAST_NAME,5)LIKE [something1] " & _
" OR LEFT(LAST_NAME,6)LIKE [something1] " & _
" OR LAST_NAME LIKE [something1] " & _
" ORDER BY LAST_NAME; "
.... and so on

5. now how do i call for the "case" in the PF form so that the text box takes the input and listbox displays result for all types of find. I am succesful with different forms for each FIND but i want to use ONLY ONE form.|||Since it's an Access database I don't know if IIf() or Switch() would help at all.sql

case statement in where clause

I need a SQL statement that selects a specific year (@.yr type int) in the "createddate" column...if this @.yr is equal to 0 then I want to select ALL columns regardless of the year...

This is what I have so far, but it doesnt work...

SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE
WHEN @.yr<>'0' THEN @.yr
ELSE NOT NULL
END

SELECT * FROM tblUsers
WHERE year(CreatedDate)=CASE WHEN (@.yr= 0) THEN year(CreatedDate) ELSE @.yr END

CASE Query

Hi i'm trying to run a CASE, Database is TS_Positions Column is Position Type, we usually get data being -1 or 1, i would like use the Function CASE to change that in my query(easier to read) 1=being a BUY... -1=being a SELL.
For Some reason my query will NOT Work, Every other part works just not the CASE part.. Any ideas?? Query:

SELECT CASE PositionType
WHEN PositionType '1' THEN 'BUY'
WHEN PositionType '-1' THEN 'SELL' AS [BS}, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, comment, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE]
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY ContractFor starters, there's no need to repeat PositionType in the WHEN lines. You've specified it in the CASE line. Secondly, if the 1 or -1 is a numeric value, they shouldn't be surrounded by quotes. Third, the "}" is wrong. Fourth, no END.|||Hi,

You can re-edit the CASE statement as
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],

SELECT
CASE PositionType
WHEN 1 THEN 'BUY'
WHEN -1 THEN 'SELL'
END AS [BS],
CAST(TradePrice as float(20,8) ) AS [Price],
Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike,
expiringdate,
comment,
SUBSTRING(contract+CONVERT(varchar,expiringdate),1 ,20) AS [FEEDCODE]
FROM TS_Positions
WHERE
(Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY Contract

Eralper
http://www.kodyaz.com|||Thanks Eralper! That worked but i decided to do it this way.

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S]...

Is there anyway where i can put on there to NOT show the NONE for the else? So i would only show the B(1) and S(-1).

Also on my query i would like to add 2 new columns at the end for example:

select col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets

Soo i should put that at the end of my query so it will look like this correct?

SELECT case positiontype when '1' then 'buy' when '-1' then 'sell' else 'none' end AS [B/S], comment, CAST(TradePrice as float(20,8) )AS [Price], Quantity AS Volume,
LEFT(Contracttype,1) as KIND,
strike, expiringdate, (SUBSTRING (contract+CONVERT(varchar,expiringdate),1,20)) AS [FEEDCODE], col1,col2, , ‘portfolio’ as Portfolio,‘markets’ as Markets
FROM TS_Positions
WHERE (Contract LIKE 'LI%') OR
(Contract LIKE 'LK%') OR
(Contract LIKE 'LL%') OR
(Contract LIKE 'LM%')
ORDER BY PositionType

2 new columns for my query would be Portfolio and Markets at the end..
Right now the columns without the config has:
B/S comment Price Volume Kind Strike Expiringdate Feedcode

New query would include 2 columns
B/S comment Price Volume Kind Strike Expiringdate Feedcode Portfolio Markets

Case Conditional in SQL Statement - MS SQL 2000

Hi,

I'm trying to do calculations in a SQL statement, but depending on one
variable (a.type in example) I'll need to pull another variable from
seperate tables.

Here is my code thus far:

select a.DeptCode DeptCode,
a.Type Type,
(a.ExpenseUnit / (select volume from TargetData b where b.type =
a.type)
) Expense
Fromcalc1 a

The problem... a.Type can be FYTD, Budget, or Target... and depending
on which one it is, I need to make b either FYTDData, TargetData, or
BudgetData. I'm thinking a case statement might do the trick, but I
can't find any syntax on how to use Case in an MS SQL statement. Even
If statements will work (if that's possible), though case would be
less messy.

Any suggestions would be much appriciative. Thanks...

Alex.Hi

Is it not totally clear how you are joining these tables, but this may be a
start.

SELECT a.DeptCode DeptCode,
a.Type Type,
a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
WHEN a.Type = 'Budget' THEN
c.volume
WHEN a.Type = 'Target' THEN
d.volume
ELSE 1 END ) AS Expense
From calc1 a
LEFT JOIN FYTDData d ON b.type = a.type
LEFT JOIN BudgetData d ON c.type = a.type
LEFT JOIN TargetData d ON d.type = a.type

John

"Alex" <alex@.totallynerd.com> wrote in message
news:2ba4b4eb.0310010840.5910e221@.posting.google.c om...
> Hi,
> I'm trying to do calculations in a SQL statement, but depending on one
> variable (a.type in example) I'll need to pull another variable from
> seperate tables.
> Here is my code thus far:
> select a.DeptCode DeptCode,
> a.Type Type,
> (a.ExpenseUnit / (select volume from TargetData b where b.type =
> a.type)
> ) Expense
> From calc1 a
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement. Even
> If statements will work (if that's possible), though case would be
> less messy.
> Any suggestions would be much appriciative. Thanks...
> Alex.|||Alex (alex@.totallynerd.com) writes:
> The problem... a.Type can be FYTD, Budget, or Target... and depending
> on which one it is, I need to make b either FYTDData, TargetData, or
> BudgetData. I'm thinking a case statement might do the trick, but I
> can't find any syntax on how to use Case in an MS SQL statement.

Books Online is a very resource for this kind of information, just
look up CASE. Be careful to notice that this is not a statement, but
an expression.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message news:<3f7b0da9$0$8765$ed9e5944@.reading.news.pipex.net>...
> Hi
> Is it not totally clear how you are joining these tables, but this may be a
> start.
> SELECT a.DeptCode DeptCode,
> a.Type Type,
> a.ExpenseUnit / ( CASE WHEN a.Type = 'FYTD' THEN b.volume
> WHEN a.Type = 'Budget' THEN
> c.volume
> WHEN a.Type = 'Target' THEN
> d.volume
> ELSE 1 END ) AS Expense
> From calc1 a
> LEFT JOIN FYTDData d ON b.type = a.type
> LEFT JOIN BudgetData d ON c.type = a.type
> LEFT JOIN TargetData d ON d.type = a.type
> John

Hi John...

I did get it going yesterday after spending about an hour testing
syntax. Below is the final SQL statement. Works great!

select a.DeptCode,
a.Type,
(((a.TotalPaidHoursUnit/(Case a.type
When 'FYTD04' Then null
When 'Budget' Then (select b.monthly from it_budvol b where
b.deptcode = a.deptcode)
When 'Prior Year' Then (select b.avemonth from it_pyvolume b
where b.deptcode = a.deptcode)
Else (select b.AveMonthVolume from solucient_dss b where
b.deptcode = a.deptcode and b.type = a.type)
end)
- a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE

Fromdss_calc a

Thanks for the feedback.

Alex.|||alex@.totallynerd.com (Alex) wrote in message news:<2ba4b4eb.0310020704.4c08463e@.posting.google.com>...
> Hi John...
> I did get it going yesterday after spending about an hour testing
> syntax. Below is the final SQL statement. Works great!
>
> select a.DeptCode,
> a.Type,
> (((a.TotalPaidHoursUnit/(Case a.type
> When 'FYTD04' Then null
> When 'Budget' Then (select b.monthly from it_budvol b where
> b.deptcode = a.deptcode)
> When 'Prior Year' Then (select b.avemonth from it_pyvolume b
> where b.deptcode = a.deptcode)
> Else (select b.AveMonthVolume from solucient_dss b where
> b.deptcode = a.deptcode and b.type = a.type)
> end)
> - a.FYTD_TotalPaidHoursUnit ) / a.Hours) * a.FYTD_Volume) LaborFTE
> Fromdss_calc a
> Thanks for the feedback.
> Alex.
Hi

You should make sure that you are not dividing by zero.

John

Cascading(?) Parameters Help

I have 2 parameters that are of type string. The user can enter anything they want in them. The third parameter is query based and uses the first 2 parameters to get a list of people. Is there a way I can prevent the third parameter from propigating until the first 2 are both filled in? The first 2 cannot be drop downs however, they are used in a wild card fashion.

Thanks!

Yes. Set the default on the text boxes to NULL.

Then it shouldnt try to populate until both are not null.

I just created a test report and it did not populate until both names were entered.

BobP

|||

In RS2005? I just did the same thing, and the 3rd Parameter became available after I entered something in the 1st parameter (no values because the query is where firstname = @.param1 and lastname = @.param2). However, the real query is using firstname LIKE @.param1 and lastname LIKE param2 and thus populates the list as soon as I enter the first name. Which is confusing because param2 is empty. Any ideas?

|||

Ah.. I see. I had the 'Allow blank value' selected. However, not pretty, it works.

|||

So it IS working for you, right?

BobP

|||Yes.. thanks.

cascading parameters

Hello,
I have a total of 5 parameters in my report. Year, season, list type,
startdate, enddate.
Year and season are simple queries.
List type depends on year and season
Startdate depends on year,season and list type. it has a default value
(dataset) to pull the min(date) given the year, season and list type
Enddate depends on year,season and list type. it has a default value
(dataset) to pull the max(date) given the year, season and list type
The report parameters are ok when the user selects the values for the
first time. but then when they try to change just the season parameter,
the startdate and enddate do not default to the correct values. Is
there a way I can force the startdate and enddate parameters to
re-query or re populate?
thanks in advanceActually the refresh depends on the first parameter, when it is changed then
it refreshes all since it is cascading. before changing 2nd param you need to
refresh and do the changes, because since season is dependent on year so if
you change the year then season will get changed.
Amarnath
"clemlau@.yahoo.com" wrote:
> Hello,
> I have a total of 5 parameters in my report. Year, season, list type,
> startdate, enddate.
> Year and season are simple queries.
> List type depends on year and season
> Startdate depends on year,season and list type. it has a default value
> (dataset) to pull the min(date) given the year, season and list type
> Enddate depends on year,season and list type. it has a default value
> (dataset) to pull the max(date) given the year, season and list type
> The report parameters are ok when the user selects the values for the
> first time. but then when they try to change just the season parameter,
> the startdate and enddate do not default to the correct values. Is
> there a way I can force the startdate and enddate parameters to
> re-query or re populate?
> thanks in advance
>

carriage return inside a field of text data type?

how can i insert a carriage return when i update the field?

say i want to put the following inside a field:
firstline
secondline

how can i update/insert a column to have a return carriage inside it?
UPDATE table SET column = 'firstline secondline'

the reason i want this is because when using a program (Solomon, by microsoft, purchasing software) to grab a field out of the database and when it displays that field in the programs textbox, i want it to be displayed on two separate lines

i tried doing
UPDATE table SET column = 'firstline' + char(13) 'secondline'

but when in the solomon program, it displays an ascii character between firstline and secondline like: firstline||secondline

thankstry char(10) instead
or the combination of the two characters|||ive actually tried them both :(

edit: just tried using char(13) + char(10) and it works! thanks!|||you welcome ;)

Capturing Data Type Mismatch

Hi,
Create Table tb_mismatch
(x int)
Create Procedure proc_mismatch
as
begin
insert into tb_mismatch values('s')
if @.@.error<>0
begin
print ' entered error loop'
end
print 'successfully exited'
end
exec proc_mismatch --executing the proc
Now, when i try to capture the above error its not getting trapped..its directly going to the final end statement.
I have even tried calling subprocedures so that it comes out of the inner procedure and by some means i can move forward in the outer proc,but even that failed.
The proc. is able to capture all the other errors like primary key violation,binary data truncated etc but not the datatype mismatch error (mainly int with varchar...)
any ideas are highly appreciated.
Thanks & regards,
Pavan.It looks like a little data checking is needed somewhere. Here is one example:

Create Table dbo.tb_mismatch
(x int)


Create Procedure dbo.proc_mismatch @.var varchar(50), @.err int OUTPUT
as

if isnumeric(@.var) = 1
BEGIN
insert into dbo.tb_mismatch (x) values(@.Var)
END
ELSE
BEGIN
set @.err = -1
END


if @.err<>0
begin
print 'encountered error'
end
else
begin
print 'successfully exited'
end



declare @.var varchar(50), @.errreturn int
set @.var = 's'
set @.errreturn = 0

exec dbo.proc_mismatch @.var, @.errreturn OUTPUT --executing the proc
select @.errreturn

drop proc dbo.proc_mismatch
drop table dbo.tb_mismatch|||Thanks for your quick response but my requirement is:
I have several update and insert statements in my actual procedure which fetches the data from an oracle DB and updates the sql database.. during these updates and inserts Business wants me to capture all the system related errors and when i am trying to capture the data mismatch error(manually placing a varchar value in a float field) the cursor is directly moving to the end of procedure,instead of populating the log file.
I dont think placing isnumeric for all int and float fields is the feasible solution,
any other ways??

Many Thanks
Pavan.|||No

And this sounds like a batch process...

I woul unload the data from oracle, bcp the data in to sql server, perform my audits, then load the data in|||Sounds good but it doesn't help my requirement as i have lots of validations to be done before performing any transactions and even need to Rollback transactions in some cases..
Do we have any exception handling mechanism to handle this ..other than raiseerror as it didn't worked out.. or is this a bug in sqlserver?? like we have when VALUE_ERROR exception in oracle|||SQL Server error handling is kludgey in 2000. SQL 2005, takes for steps to address that, but I haven't looked in to it.

So why can't you do basic aduitng in batches in a set based manner? What's the difficulty. You will need some staging tables, bit so what?

You need to divorce yourself from sequential cursor processing that you're accostomed too in Oracle...even in Oracle, it is over used a lot of times.

Good Luck.

If you continue to do it this way, create a second stored procedure that gets called...like a nested stored procedure...when the nested proc fails, it will rais out to the calling stored procedure, and the driver can then handle the error...but that's the long way around the mountain|||Brett,
thanks for ur concern.
I have tried the second option but it hasn't helped me out.
My code goes something like this..
gets the jobnumber and its related info from the oracle job master table..checks for its existance in sql db and then creating 2 cursors for diff tables checks and then lots of if's and else's,calculations..and once it goes through all the validations we will start inserting the details into some tables,move data to history and then update the main job table..if it fails in any of the case just rollback the whole operations..now the turn of next job comes into picture..
As of now it works fine until we dont get varied data from oracledb which has the similar db structure of sql server.
My scope is till its developed.|||I still don't know why you can't do something like this

USE Northwind
GO

-- Set up the situation
SET NOCOUNT ON
CREATE TABLE ORACLE_TABLE(Col1 varchar(10))
CREATE TABLE SQL_TABLE(Col1 int)
GO

-- Create some sample Data

INSERT INTO ORACLE_TABLE(Col1)
SELECT '1' UNION ALL
SELECT '2' UNION ALL
SELECT '3' UNION ALL
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'd'
GO

-- Report On Bad Data

SELECT * FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 0

-- Place the good data in to SQL
INSERT INTO SQL_TABLE(Col1)
SELECT (Col1) FROM ORACLE_TABLE WHERE ISNUMERIC(Col1) = 1
GO

SELECT * FROM SQL_TABLE
GO

SET NOCOUNT OFF
DROP TABLE ORACLE_TABLE, SQL_TABLE
GO|||Try doing the same with a small change,changing the datatype from varchar to int,as this is my current structure,without using isnumeric option as my table has lots of columns and there are lots of insert and update statements.
CREATE TABLE ORACLE_TABLE(Col1 int)

Came to know that this error cannot be captured by sqlserver 2000 which is resolved in the next version sqlserver 2005 using the try catch block.

Capture time alone in SQL Server database

Is there any data type in SQL Server 2005 which captures only the TIME in default DATETIME type?

For example, if the datatime field has a value2007-12-11 12:31:00.000, i need a datatype which can capture12:31:00.000 alone. The data type should be in a fashion so that i can find differences in time also...

Any ideas??

Hi,

SQL Server does not have any type which can store only time. One way is to store it as datetime, and when fetching these times, you can convert them to only show time.

|||

venkatesh_ur:

Is there any data type in SQL Server 2005 which captures only the TIME in default DATETIME type?

I'm afraid there is no Datatype to fetch the time only. But of course there are some functions that can be used to get the time part out of any datatime value. Below query will get you the time (converted as varchar):

select getdate() , convert ( varchar , getdate() , 8 )

venkatesh_ur:

he data type should be in a fashion so that i can find differences in time also...

To compare date and time values theDatepartandDateNamefunctions can be useful. They both are quite similar to each other. Read Books Online for more help on these functions.

capture line of flat file [Error]

Hi,

I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?

I'd like to help but I am not sure I followed your question. Can you calrify?

Rafael Salas

|||

PastillaReturn wrote:

Hi,

I have a flat file with several rows of entire type in one of the rows a string comes and when it goes away to guard in the BD it falls, since I can know in that this row of the flat file the string?

Sorry, this is getting lost in translation. Do you mean that you require to know which row(s) of the flat file failed?

-Jamie

|||

yes

|||

This isn't currently possible. If you want to change that, go here, vote, and add a comment:

Row numbers added to pipeline
(https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=131335)

-Jamie

Cant use the NTEXT datatype in SQLCLR scalar-valued functions

From the SQL Server documentation :

"The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, exceptrowversion,text,ntext,image,timestamp,table, orcursor"

This is a problem for me. Here's what I'm trying to do :

I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.

Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.

The sql server 2005 nvarchar(max) is the same as sql server 2000 nText

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

|||Cool. Thanks for the tip. I'll give this a shot and report back.