How To Set The Value Of Variable In Select Case

Oct 10, 2007

Select Case l.GLTransactionTypeID
When 'Asset' --AND l.GLTransactionDate BETWEEN convert(datetime,'1/1/2007')AND convert(datetime,'12/31/2007') and l.GLTransactionSource=@assetID
Then Begin Set @Cost=l.GLtransactionAmount end
end from LedgerTransactions l,FixedAssets f where l.GLtransactionsource=f.assetID and l.GLtransactionsource= @assetID

View 1 Replies


ADVERTISEMENT

Select Declared Variable With Case Statements

Apr 19, 2008

I am trying to gather counts for table imports made for files from friday - sunday and create a variable that will be the body of an email.
I'd like to use either a case statement or a while statement since the query is the same but the values must be collected for each day (friday, saturday and sunday) and will all be included in the same email.

I have declared all variables appropriately but I will leave that section of the code out.


Select @ifiledate = iFileDate from tblTelemark where
iFileDate = CASE
WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-3, 101), '/','') THEN

Select @countfri1 = Count(*) from tbl1
Select @countfri2 = Count(*) from tbl2
Select @countfri3 = Count(*) from tbl3
Select @countfri4 = Count(*) from tbl4


WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-2, 101), '/','') THEN
Select @countsat1 = Count(*) from tbl1
Select @countsat2 = Count(*) from tbl2
Select @countsat3 = Count(*) from tbl3
Select @countsat4 = Count(*) from tbl4

WHEN iFileDate = REPLACE(CONVERT(VARCHAR(10), GETDATE()-1, 101), '/','') THEN
Select @countsun1 = Count(*) from tbl1
Select @countsun2 = Count(*) from tbl2
Select @countsun3 = Count(*) from tbl3
Select @countsun4 = Count(*) from tbl4


END

Is there a way to do what this that works???

View 3 Replies View Related

Using CASE WHEN With Variable

Jun 6, 2008

Hello

I'm producing a script to output a file containing data in a required format. Part of this script requires me to change a person id field to something else for a number of people.

I can do this no problem using a script like:

CASE WHEN person_id = '12345678' THEN '24681012'

However I have about 400 people to do and don't really want a 400 line script that will take me about a week to write....!!

Is there a way of doing this passing a variable??

I have created a table (TRANSFER) containing the old ID (OLD_ID) and a field containing the new id (NEW_ID), with all the appropriate data in.

How could I use this table with a variable to get the required results?

Thank you

View 2 Replies View Related

Set Variable Value In CASE Statement?

Jun 29, 2012

Is it possible to set a value to a variable if a case statement is true?

like this:

CASE
WHEN Utable.type = 2 THEN U.Username2 + (@Uname= 2)
WHEN Utable.type = 3 THEN U.Username3 + (@Uname= 3)

Ive tried above and:

"+ (select @Uname = 2)"
"+ (SET @uname = 2)"
"; SET @uname = 2"
...

and several other things but non works.

variable's = INT
"Utable.type" = INT
"U.Username"2/3 = nvarchar(50)

View 2 Replies View Related

Can I Set A Variable Equal To A Case Statment?

Feb 25, 2008

I have a case statement I need to refer to several times in my select statement and it's quite long. I would like to just refer to if by name. I tried to create a variable for the Case statement (see below) but I get an error that says, "
Sub query returned more than 1 value. This is not permitted when the sub query follows =, !=, <, <= , >, >= or when the sub query is used as an expression."

USE GP05
GO

DECLARE @EmpID nvarchar (6)

SET @EmpID = (SELECT CASE WHEN NOT(dbo.BE010130.EMPID_I IS NULL) THEN dbo.BE010130.EMPID_I
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND dbo.UPR00600.EMPLOYID IS NULL THEN dbo.UPR00500.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND dbo.UPR00500.EMPLOYID IS NULL AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00600.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00500.EMPLOYID
END FROM dbo.BE010130 FULL OUTER JOIN dbo.UPR00500 ON dbo.BE010130.EMPID_I = dbo.UPR00500.EMPLOYID AND dbo.BE010130.BENEFIT = dbo.UPR00500.DEDUCTON
FULL OUTER JOIN dbo.UPR00600 ON dbo.BE010130.BENEFIT = dbo.UPR00600.BENEFIT AND dbo.BE010130.EMPID_I = dbo.UPR00600.EMPLOYID)

SELECT @EmpID

I can get around this by simply retyping the case statement when I refer to it in the where clause, but I would prefer not to do this. Can someone point a newbie in the right direction? I have 5 case statements in this query and it starts to look pretty ugly when you have to retype them multiple times.

Thanks!

View 9 Replies View Related

Wrong Evaluation Of A Variable With Expression In Case It Is Used Simultaneously By Several Tasks

Aug 22, 2007



Hi guys,

I have experienced problem while trying to use variable with expression based on several other variables in tasks running parallel.

The details are as following:

There is a SSIS package with simple Control flow: one Script Task which actually do nothing and two Execute Process Tasks, they run after Script Task in parallel. Then there are three simple (EvaluateAsExpression = False) string variables ServerName, Folder and JobNumber with values ServerName = €œ\test€?, Folder = €œtest€? and JobNumber = €œ12345€?. And there is one variable FullPath with expression @[User:: ServerName] + "\" + @[User::Folder] + "_" + @[User::JobNumber]. All the variables are of the Package scope. Then in Execute Process Tasks I have similar expressions based on FullPath variable: Execute Process Task 1 has expression @[User::FullPath] + "\date.bat" and Execute Process Task 2 has @[User::FullPath] + "\time.bat" one. As you understand these expressions define what exactly task should execute.

Then I€™m going to execute package from command line so appropriate XML configuration file has been created. The file contains following values for variables described above: ServerName = €œ\LiveServer€?, Folder = €œJob€? and JobNumber = €œ33091€?.

After series of consequent executions I have got following log file:

€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\Test est_12345date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\Test est_12345 ime.bat€?



€¦ Execute Process Task 1€¦ Executing the process €œ\LiveServerJob_33091date.bat€?

€¦ Execute Process Task 2€¦ Executing the process €œ\LiveServerJob_33091 ime.bat€?



€¦



As you can see one of Execute Process Tasks usually receive correct value of the expression (based on values of variables from the configuration file) while another - incorrect one (based on €œdefault€? values of variables set directly in package). Sometimes wrong value appears in Task 1, next time in Task 2. Situations when both expressions in tasks evaluated correctly are very rare.

Then if you add some more Execute Process Tasks with similar expressions in the package (for ex. simply by copying existing tasks) you€™ll get a good chance to catch error like this:



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073450774,0x,Reading the variable "User::FullPath" failed with error code 0xC0047084.



OnError,,,Execute Process Task 1,,,8/17/2007 2:07:12 PM,8/17/2007 2:07:12 PM,-1073647613,0x,The expression "@[User::FullPath] + "\time.bat"" on property "Executable" cannot be evaluated. Modify the expression to be valid.



Seems variable with expression FullPath is locked during evaluation by one of the parallel tasks in such a way that another task can€™t read it value correctly. Can someone help me with the issue? Maybe there are some options I missed which could prevent such behavior of application? Please let me know how I can make the package work correctly.

View 5 Replies View Related

Cannot Set A Variable From A Select Statement That Contains A Variable??? Help Please

Oct 4, 2006

I am trying to set a vaiable from a select statement

DECLARE @VALUE_KEEP NVARCHAR(120),

@COLUMN_NAME NVARCHAR(120)



SET @COLUMN_NAME = (SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'CONTACTS' AND COLUMN_NAME = 'FIRSTNAME')



SET @VALUE_KEEP = (SELECT @COLUMN_NAME FROM CONTACTS WHERE CONTACT_ID = 3)



PRINT @VALUE_KEEP

PRINT @COLUMN_NAME



RESULTS

-------------------------------------------------------------------------------------------

FirstName <-----------@VALUE_KEEP

FirstName <-----------@COLUMN_NAME



SELECT @COLUMN_NAME FROM CONTACTS returns: FirstName

SELECT FirstName from Contacts returns: Brent



How do I make this select statement work using the @COLUMN_NAME variable?

Any help greatly appreciated!

View 2 Replies View Related

Select Case - A Better Way?

Jul 7, 2005

Hi, i have just written this:
select distinct department = case department
when 'ict' then 'Partnership Office'
when 'finance' then 'Partnership Office'
when 'Employee Development' then 'Partnership Office'
when 'Contract Compliance & MIS' then 'Partnership Office'
when 'Quality, Standards & Effective' then 'Partnership Office'
when 'Partnership administration Exe' then 'Partnership Office'
when 'Partnership Executive' then 'Partnership Office'
when 'Personnel' then 'Partnership Office'
when 'Operations' then 'Partnership Office'
when 'Quality Standards and Effectiv' then 'Partnership Office'
when 'Marketing and Communications' then 'Partnership Office'
when 'Marketing and Information' then 'Partnership Office'
else department end from personnel_records

is there some way to do this on one line, like
...
when in ('ict','finance','personnel', etc etc) then 'Partnership Office'
else
... ?

TIA

View 2 Replies View Related

Is This Possible To Use Select Case

Feb 8, 2007

hi, this is my procedure.

CREATE procedure yard_retrieve
(
@yard varchar(50),@vessel varchar(50),@customer varchar(50)
)
AS
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@yard and category_code=(select category_code from categoryMaster where category_description='Yard') )
select 'found' as result
else
select 'not found' as reault
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@vessel and category_code=(select category_code from categoryMaster where category_description='vessel'))
select 'found' as reault1
else
select 'not found' as reault1
end
begin
IF EXISTS (select code_description from codeMaster where active='1' and code_description=@customer and category_code=(select category_code from categoryMaster where category_description='customer'))
select 'found' as reault2
else
select 'not found' as reault2
end
GO

if i run this procedure i need the result as following

result result1 result2
found notfound found

is this posible to do using select case ? please help me to get my need please

View 4 Replies View Related

Select Case Issue

Sep 19, 2007

I need to do the following so that I can convert a Null value to 0 in my db... however I am getting errors "Incorrect Syntax near '='.
Facilities.PortfolioID AS PortfolioID =
CaseWhen Facilities.PortfolioID = Null then Facilities.PortfolioID = 0
End,

View 5 Replies View Related

CASE In Select Statement

Jan 17, 2008

Hi, i've got this stored procedure:
 @LidFederatieNummer varchar(20),
@ClubID int,
@LidClubNummer varchar(50),
@PersoonNaam varchar(100),
@ClubStamnummer varchar(10),
@ClubNaam varchar(50),
@SeizoenID int,
@ShowAllJN bit,
@LidFederatieJN int,
@CategorieID int
AS
BEGIN

IF @CategorieID IS NOT NULL BEGIN
DECLARE @LicentieVerplicht int
SET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
END

IF (@ShowAllJN = 0)
BEGIN
SET RowCount 100
END

CASE @LicentieVerplicht
WHEN '1' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 1
Order By Persoon
END

WHEN '2' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN)
ORDER BY Persoon
END

WHEN '3' THEN
BEGIN
SELECTDISTINCT
ClubStamnummer,
ClubID,
ClubNaam,
LidID,
PersoonID,
Persoon,
LidFederatieNummer,
LidClubNummer,
vwClubLidPersoonAlgemeen.LidClubID,
vwClubLidPersoonAlgemeen.LicentieJN,
UserName,
PersoonActiefJN,
LI.SeizoenID,
LI.LidFederatieJN
FROM vwClubLidPersoonAlgemeen
INNER JOIN Persoon P ON vwClubLidPersoonAlgemeen.PersoonID = P.ID
INNER JOIN LidInschrijving LI ON LI.LidClubID = vwClubLidPersoonAlgemeen.LidClubID AND LI.ID = (SELECT TOP 1 LidInschrijving.ID FROM LidInschrijving WHERE LidInschrijving.LidClubID = vwClubLidPersoonAlgemeen.LidClubID ORDER BY ID DESC)
WHERE
LidFederatieNummer LIKE '%'+@LidFederatieNummer+'%'AND
ISNULL(LidClubNummer,'') LIKE '%'+@LidClubNummer+'%'AND
Persoon LIKE '%'+@PersoonNaam+'%'AND
ClubStamnummer LIKE @ClubStamnummer AND
ClubNaam LIKE '%' + @ClubNaam + '%' AND
(@ClubID = 0 OR ClubID = @ClubID) AND
(@SeizoenID = 0 OR vwClubLidPersoonAlgemeen.SeizoenID = @SeizoenID) AND
(@LidFederatieJN = -1 OR CAST(@LidFederatieJN as bit) = LI.LidFederatieJN) AND
vwClubLidPersoonAlgemeen.LicentieJN = 0
Order By Persoon
END
 According the value of my @LicentieVerplicht variable:
DECLARE @LicentieVerplicht intSET @LicentieVerplicht = (SELECT LicentieVerplicht FROM Categorie WHERE ID = @CategorieID)
My where clausule should change... how can  i achieve this cause my case is not working because it is not inside the select statement

View 4 Replies View Related

Case When In A Select Statement

Apr 13, 2006

Hi everyone,
I have the following problem withj this query:

SELECT DocNumber,
       Title,FirstName,LastName,Paper,
       ANNO,Location,Keywords,
       URL= case WHEN URL IS NULL THEN
'|<a
href=''https://illiad.library.nova.edu/illiad/FNN/illiad.dll?ILLiadRedirect=OpenURL&genre=thesis&isbn='+CONVERT(varchar,DocNumber)+'&title='+CONVERT(varchar,Title)+'&rft.pub='+CONVERT(varchar,Program)+'&rft.edition='+
CONVERT(varchar,Paper) +'&date='+ CONVERT(varchar,ANNO)+
'&aulast=' + CONVERT(varchar,Lastname) + '&aufirst=' +
CONVERT(varchar,Firstname)+
'&url_ver=Z39.88-2004&rfr_id=info:sid/MARPS:Practicum''target=''blank'')>
Order through ILLiad</a>' ELSE URL END
FROM DocTable1 WHERE FREETEXT....

When URL is null, I don't get the string showing. It works properly only in the query analyzer.

Any ideas?

Christian

View 3 Replies View Related

Search Or Select CASE????

Nov 21, 2001

I'm using a 'searched case' however I don't care which method I use.

I'm open to using anything....

I just want to correct a problem in my pivot table where I need to create a ficticous eventdate for 'S' records, those that do not have an event date in the past 7 days.

-- eventdate is a datetime column
select User
,res =
case res --event result
when 'S' then '0' --S generic non-event
when 'SUC' then '1' --successfull event
end
,eventdate =
case eventdate
when enventdate < getdate()-8 then getdate()-8
end
from eventlog_1 --my view of events

Server: Msg 170, Level 15, State 1, Line 8
Line 8: Incorrect syntax near '<'.

TIA

JeffP...

View 2 Replies View Related

Select Case Question

Jul 7, 2005

I am doing a mail merge from data returned from a query. My problem is one of my fields is to long for the document. I want to be able to break up the line into 2 sections. I'm assuming I can do this within the case statement but am unsure how.
This is the code I have. I want the break to come before the "at".


case
when rb.building in (9420,420) then 'DEERFIELD SUMMER SCHOOL at BAKER WOODS #99'
when rb.building=9722 then 'JAMES SUMMER SCHOOL' 'at JOHN MARSHALL MS'
when rb.building=9723 then 'NORTHWEST SUMMER SCHOOL at CRISPUS ATTUCS MS'
when rb.building=9717 then 'BROAD SUMMER SCHOOL at SIDENER MS'
when rb.building=9715 then 'MANUAL SUMMER SCHOOL at MCFARLAND MS'
else upper(trim(rb.building_name)) end,

View 2 Replies View Related

Case Statement Within A Select

Sep 7, 2006

Hey guys, i'm trying to use a CASE within a select but i'm not sure how to get started.

Here is my select so far, i'm inserting into a temp table that is used within a loop to gather data from my trigger:


Code:


insert into @temp_par
select CONVERT(varchar(20),d.PAT_ID),
ep.EXT_PAT_ID,
d.DRUG_DESC_ID,
d.ALLERGY_CD,
CONVERT(varchar(20),d.PAR_DT,120),
ISNULL(d.PAR_TXT, 'NULL'),
ISNULL(REPLACE(m.DRUG_NM,'"','^"'),'NULL'),
ISNULL(m.DOSAGE_FORM_CD,'NULL'),
ISNULL(m.STRGTH,'NULL'),
ISNULL(m.STRGTH_UM,'NULL')
from deleted d --PATIENT_PRIOR_ADVERSE_REACTIONS
join EXTERNAL_PATIENT ep on ep.PAT_ID = d.PAT_ID
join MEDNAME m on m.DRUG_DESC_ID = d.DRUG_DESC_ID



I would like to use a CASE for the d.ALLERGY_CD column. The data in this column is usually a 1,2,3,4,5 but i'd like to convert those to their real data, for example:


Code:

1 = A
2 = B
3 = C
4 = D
5 = E



Any help?

View 3 Replies View Related

Select Within Case Statement?

Jan 20, 2005

Limitations: Needs to be single sql statement for exec, no udf/stored proc
Objective: select aggregate count from tblPerson grouped by s_fk, division,Desc, areaDesc with rollup for totals
if mail_goes_to=1, get the s_fk value via tblPerson/tblAffiliation/tblOrg/tblCity/tblCounty
if mail_goes_to=2, get the s_fk value via tblPerson/tblCity/tblCounty, skipping tblAffiliation/tblOrg

What's the best method? A case statement? Would a cross-join work? Thanks so much for any and all advice!

tblPerson: person_pk, affiliation_fk; home_city_fk, mail_goes_to
tblAffiliation: affiliation_pk, organization_fk
tblOrg: organization_pk, city_fk
tblCity: city_pk, county_fk
tblCounty: county_pk, s_fk

For resulting display only:
tblPersonArea: personArea_pk, area_fk, person_fk
tblArea: area_pk, areaDesc, division_fk
tblDivision: division_pk, divisionDesc

View 1 Replies View Related

SELECT CASE WHEN Problem

Dec 1, 2006

Hello !
For MS SQL 2000/2500

I am having a table ::

User
Ident int NOT NULL,
Numb int NOT NULL,
Info nvarchar(100) NOT NULL,

with this query ::

SELECT Numb, Info, Ident
FROM Users
WHERE (Numb IN (100,200, 300))
ORDER BY Ident

I get ::

Numb | Info | Ident

100 | dgwrg | 1
200 | dgeth yiuyi | 1
300 | Agt hgn ngtn | 1

100 | jttytujky | 2
200 | yjtut iuiu | 2
300 | dgd 2gggwekew | 2


what I need is something like::

SELECT
(CASE WHEN Users.Numb = 100 THEN Users.Info END) AS InfoA,
(CASE WHEN Users.Numb = 200 THEN Users.Info END) AS InfoB,
(CASE WHEN Users.Numb = 300 THEN Users.Info END) AS InfoC,
Ident
FROM Users
WHERE Ident = Ident (for the same Ident)

to get ::

InfoA | InfoB | InfoC | Ident

dgwrg | dgeth yiuyi | Agt hgn ngtn | 1

jttytujky | yjtut iuiu | dgd 2gggwekew | 2


Actually I am getting a NULL value everywhere
of course I cannot use MAX(CASE WHEN ... , or I am getting only a few rows and not all

thanks a ton for helping

View 6 Replies View Related

Select Case With Date

Aug 2, 2013

OK so I want to use datepart(month,getdate() with a Case statement with this logic:

SELECT CASE
datepart(month,getdate()='8' then
SELECT *
FROM [Master]
WHERE [Month]='Jul'
END

OK what's wrong with the code?

View 6 Replies View Related

Case Not Making The Select?

Aug 2, 2013

The following code is a front end parameter - Quantity but when the user enters '0' it is not returning any rows (there are). Is there something wrong here or to add something?

(Select {?Quantity}
CASE "ALL":
1=1
CASE "Negative":
({ACBALMPK.IRQOH#}<0) OR ({ACBALMPK.IEQOH#}<0)
CASE "Zero":
({ACBALMPK.IRQOH#}=0) OR ({ACBALMPK.IEQOH#}=0)
CASE "Greater than Zero":
({ACBALMPK.IRQOH#}>0;) OR ({ACBALMPK.IEQOH#}>0;))

View 4 Replies View Related

Select Case Statement

Aug 12, 2005

Hi,

I'm wondering what syntax to use for a select case statement.

I have a list of questions. If a particular question is on the list, then it shouldn't show some other related questions.

For example, "have you been in the military?". If you haven't , then you wouldn't be asked all the military related questions.

So- I'm saying:
select case when question_id = 488 then don't return question_id 220, 962, 963

I'm trying something like this:

select q.client_id,
case when q.question_id = 488 then end q.question_id = 220 else q.question_id = 220 end q.question_id
from questions_yes_no q

How would I say 'dont show' question 220? End is not the way to go, obviously.

If you can see what I'm trying to do, can you point me to an article or bol search?

Thank you for any help.

View 11 Replies View Related

Help With Syntax (select Case)

Mar 4, 2008

Hi,

I looked up select case statements and have used them for returning single values, but can't seem to get it working when returning a select statement..

I have my SPROC configured as below, can anyone help me out as to why its not working?

btw previously I had been using something like below, but in this case it wont work as I have to make more changes than just the WHERE genderID = @genderID (hmm, hopefully that makes sense, if not ignore my example lol)


IF @genderID > 2
..
ELSE
........WHERE genderID = @genderID


thanks very much once again!
mike123



CREATE PROCEDURE [dbo].[select_123]
(
@genderID tinyint
)

AS SET NOCOUNT ON


SELECT

CASE @genderID
WHEN 1 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......


WHEN 2 THEN

SELECT TOP 40 *

FROM tbl
WHERE .......
ELSE
SELECT TOP 40 *

FROM tbl
WHERE .......



END

View 3 Replies View Related

VB 'Select Case' Equivalent In T-SQL?

Jan 24, 2008

I'm still new to T-SQL and have been trynig to find an equivalent to VB's select case. Is the IF statement all that's available?

View 6 Replies View Related

How To Select A Case Sensitive Value In SQL With C#

Feb 11, 2006

Hello,

I have an application that at the begining a user will login with a user name and password which is stored in the database. The SQL statement is as follows:

"SELECT id_employee FROM employee WHERE employee_number='" + txtUserName.Text + "' AND passWord='" + txtPassword.Text + "'";

For testing purposes I have set the password to the word test. The problem is, if the user enters in TEST or TeSt or TESt it will grant them access. How do I set it to force the correct case?

I am using SQL 2005 for the database.

Thanks!

~zero

View 11 Replies View Related

Select Case Statement

Apr 30, 2008

INSERT INTO #rpt(Hic, DtImported, Source, PlanID, LastName, FirstName, MemID, CaseStatus)
SELECT
s.Hic,
s.DtImported,
s.Source,
dbo.LastErolledPlan_C_D(s.Hic,s.IsPart_C) as PlanID,
m.LastName,
m.FirstName,
dbo.GetMemID_PartC_D(s.Hic,s.IsPart_C) as MemID,
s.CaseStatus = CASE WHEN CaseStatus ='1' or CaseStatus='0' THEN 'New'
CASE WHEN CaseStatus <>'1' or <> CaseStatus <>'0' Old

ElSE 'No Valid'
End


from tbEsccSuspects s inner join tbPDMMembers m
on s.Hic = m.HicNumber
Where s.EUFStatus = 1 -- AND DtEUFSent is NULL


what I am doing wrong in the case statment that I am getting an error. ?????

View 8 Replies View Related

SELECT QUERY Without USING CASE

May 29, 2006

I have a select query

select col1-tbl2,col2-tbl3,col3-tbl4 ,col4-tbl1,col5-tbl1 from tbl1where condition,

The above quey col1-tbl2,col2-tbl3,col3-tbl4 are from table1 only, but it have only id(ie) the foreign key references of tables 2,3,4

I want the column with their values to join with the tables 2,3,4.

Except USING CASE
Except USING CASE

Please Guide me

View 1 Replies View Related

SQL Select Statement With Case SENSITIVE

Feb 8, 2007

Hi,I am wondering how do I write a query that will take case sensitive into consideration. For example, I have "ABCD", "abcD", ABcd", "AbCd" in a table. I want to retrieve "AbCd"  only from the table. This is the sql statement that I have:sqlSelect = "SELECT * FROM [al_table] WHERE [alphabet]= @alpha"Dim selectCmd As New SqlCommand(sqlSelect, myConnection)       selectCmd.Parameters.AddWithValue("@alpha", "AbCd") From what I have it will return all those value. But that is not what I want. Thanks 

View 4 Replies View Related

Select Only Lower Case String

Apr 6, 2007

For example, 
select fieldA form tableA where fieldA = 'aaa'
I got following output
fieldA
---------
aaa
aAa
AAA
AAa
...
if I want select only the lower case 'aaa', how can I do that?
 

View 2 Replies View Related

Can Anyone Tell Me How To Use 'Case' Statement In Select Query In SQL ?

Mar 14, 2008

I need to pull one field from one table and one field from  another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table  but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement???
is this correct ?? its showing me errors
Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ???
(case when 'DataSource =1' then 'ABC' then 'BCD' endcase)
Where .....
 Pls correct me
Thanks

View 8 Replies View Related

SELECT CASE Problem... Me Thinks!!

Jun 11, 2008

DECLARE @UserEmail as Varchar(50)
DECLARE @UserZipPassword as Varchar(50)
DECLARE @CoupMember as int
DECLARE @member As Bit
--Check if Email Exists…. If yes Then
--
SELECT @RewardsMember = COUNT(*),
@member =CASE M.EMAIL
WHEN ((LEN(EMAIL)) <> 1)
THEN 1
WHEN (LEN(EMAIL)) IS NULL THEN 0
END
FROM MemberTable M
Trying to get information from table for User based on data provided. What I am trying to do up there is check to see if User provided correct Email, if User provided right password and if User provided right zip code. Basically, I need to return bit values to my client application from this. Ran into some problem with my SELECT CASE statement up there. Need help please. Thanks in Advance.

View 3 Replies View Related

Use 'select Case' Statment In Sql Query.

Mar 18, 2005

hi,friend

is it possible to use 'select case' statment in sql query.

give any idea or solution.

thanks in advance.

View 2 Replies View Related

Using Case In A Select Statement,urgent!

Feb 15, 2002

Hi All,

I need a hand in this script.
SELECT a.id ,value = CASE
when (x1-x0) = 0 then
((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0))
ELSE
((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0))
END
,value2 = CASE
when (x1-x0) = 0 then
((Y2-Y0)-(Y1-Y0))/(0.000000001*(X2-X0))
ELSE
((-111.48277-Y0)-(Y1-Y0))/((X1-X0)*(32.72383-X0))
END
FROM table_name a
where value > 0
ORDER BY VALUE

The problem I'm having is in the where clause. Because the value is not a column name sql server is giving me an error. I need to evaluate the value returned. How can I get around this problem? Any sort of advise will help!
Thank you in advance!

View 1 Replies View Related

Using A CASE Statement Within A Select Query

Jul 25, 2006

Hi folks,

Hope you are all well.

I am using a CASE statement within a SELECT query to sum up values for different customers.

SELECT CR_CUST.Customer_Code,
'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END
FROM CR_CUST
INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code
INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code
WHERE (CR_PROD.Part_Class_Code = 1 OR
CR_PROD.Part_Class_Code = 7 OR
CR_PROD.Part_Class_Code = 8)
GROUP BY CR_CUST.Customer_Code,
CR_PROD.Part_Class_Code

The above query produces the following results...

Customer_Code General_01 General_07 General_08
------------- ---------------- ---------------- ----------------
02210 10074.30 .00 .00
02347 7606.49 .00 .00
02210 .00 12618.42 .00
02347 .00 13131.63 .00
02210 .00 .00 4505.44
02347 .00 .00 5018.03


My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so...


Customer_Code General_01 General_07 General_08
------------- ---------------- ---------------- ----------------
02210 10074.30 12618.42 4505.44
02347 7606.49 13131.63 5018.03


I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table.

SELECT Customer_Code,
SUM(General_01),
SUM(General_07),
SUM(General_08)
FROM #MyTempTable
GROUP BY Customer_Code


Thanks in advance,
Kev

View 4 Replies View Related

Multiple Case Statements In One Select

Dec 16, 2014

I know I should know the answer to this, but I just can't quite get the syntax down

Code:
Select case when zipCode = '10185' Then 'Deliver'
Else when zipCode = '2309' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '1291' And paid = 'Yes' Then 'Deliver'
Else When zipCode = '88221' And paid = 'No' Then 'Hold'
Else when zipCode = '34123' Then 'Deliver'
End
From postalDeliveryDatabase

View 7 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved