MS SQL Server :: CASE In Select Statement

How To Write Select Statement Inside CASE Statement ?
Hello friends,
      I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
    CASE E.EmployeeType
        WHEN 1 THEN
            select * from Tbl1
        WHEN 2 THEN
            select * from Tbl2
        WHEN 3 THEN
            select * from Tbl3
    END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View Replies !

Case When In A Select Statement
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 Replies !

Select Within Case Statement?
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 Replies !

Case Statement Within A Select
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 Replies !

Select Case Statement
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 Replies !

Select Case Statement
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 Replies !

Can Anyone Tell Me How To Use 'Case' Statement In Select Query In SQL ?
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 Replies !

SQL Select Statement With Case SENSITIVE
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 Replies !

Using A CASE Statement Within A Select Query
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 Replies !

Using Case In A Select Statement,urgent!
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 Replies !

Select Case Inside Sql Statement ?
Code:

function findingcinemaid(nameofthecinema)

findcinemaid = "select cinemasid from cinemas" &_
" where brand = 'tgv' and cinemaplace2 like '"&nameofthecinema&"'"
set cinemaidfound = objconndb.execute (findcinemaid)

end function

select case foreachcinema

case 0

cinemaname = "ONE UTAMA"
findingcinemaid(cinemaname)

case 1

cinemaname = "MINES"
findingcinemaid(cinemaname)

case 2

cinemaname = "SEREMBAN 2"
findingcinemaid(cinemaname)

case 3

cinemaname = "KINTA CITY"
findingcinemaid(cinemaname)

case 4

cinemaname = "BUKIT RAJA"
findingcinemaid(cinemaname)

case 5

cinemaname = "TEBRAU CITY"
findingcinemaid(cinemaname)

case 6

cinemaname = "SUNWAY PYRAMID"
findingcinemaid(cinemaname)

case 7

cinemaname = "SURIA KLCC"
findingcinemaid(cinemaname)

end select

any possible way I can merge this select case statement with the sql statement ? I try if else but too many code , defeating the original purpose of simplfying it

View Replies !

T-SQL CASE Statement.. NOT In A SELECT Query - ??
How come SQL Server doesn't like the following?

CREATE PROCEDURE sp_myproc
(@myvar int)
AS
CASE @myvar
WHEN 1
    EXEC sp1
WHEN 2
    EXEC sp2
WHEN 3
    EXEC sp3
DEFAULT
    EXEC sp3
END

How can I code something like this legally in T-SQL?

Thanks
Jason

View Replies !

Case Does Not Work In My Select Statement
Hello everybody
I have problem with CASE statement. Here is select it

Code Block
select
mev.Id
,mev.MetaElementId
,mev.ElementValue
,mev.DocumentId
,me.ElementTypeId
,castedValue =
case
when me.ElementTypeId =3 then cast(mev.ElementValue as integer)
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)
end
from dbo.tbMetaElementValue mev
inner join dbo.tbMetaElement me
on mev.MetaElementId = me.Id
where mev.MetaElementId =7

it returns

Id
MetaElementId
ElementValue
DocumentId
ElementTypeId
castedValue

49
7
2006
28
3
6/30/1905 0:00

53
7
2004
30
3
6/28/1905 0:00

61
7
2006
36
3
6/30/1905 0:00

67
7
2005
38
3
6/29/1905 0:00

70
7
2004
39
3
6/28/1905 0:00

105
7
2003
63
3
6/27/1905 0:00

166
7
2006
109
3
6/30/1905 0:00

195
7
2005
129
3
6/29/1905 0:00

220
7
2005
150
3
6/29/1905 0:00

223
7
2006
151
3
6/30/1905 0:00

As you can see it should return castedValue as integer but it cast to datetime which is wrong. If I commented line

Code Block
when me.ElementTypeId =4 then cast(mev.ElementValue as datetime)

it casts everything normal, but as soon as it has more than one condition in CASE it will choose anything but not right casting

Looks like I am missing something really fundamental. Any help is apreciated !

Thanks

View Replies !

Case Sensitive Search In Sql Select Statement?
Hi ALL,
    How could you make the SQL Select statement query case sensitive, what i mean is i have this select statement below ...
Select * From Staff WHERE Staff.Staff_ID = 'KabirJ' AND Staff.Password = 'KaBir1!'
and a data row in the staff table that is the following information
Staff_ID      Password
Kabirj          kabir1!
Since the cases in database is different then in the string provided, i want sql to return me nothing, How do i go about and achieve that???????????
Thanks
Kabir

View Replies !

First Time SELECT CASE Statement User
Hi, I have created a login page (webform1) that enables me to enter my user number and password and if correct it re-directs to webform 2 where all data related to the usernumber that was entered in the login page, appears. Now I want to be able to set different re-direct pages dependant upon different users as some may require more privileges then others. As for my query I have 2 types of users: staff and managers. I want to set the login page so that if staff logins in it goes webform 3 and if manager logins in it goes webform 4. My table in the Sql database is called Users & the fields are: unumber(pk), pwd, userRole, forename, surname. I have been advised to use CASE statements, yet I do not know how to use them. Below have left my code of a simple login without the userRole validation. Please help! Sub cmdLogin_ServerClick If ValidateUser(txtUserNumber.Value, txtUserPass.Value) Then Dim tkt As FormsAuthenticationTicket Dim cookiestr As String Dim ck As HttpCookie tkt = New FormsAuthenticationTicket(1, txtUserNumber.Value, DateTime.Now(), _ DateTime.Now.AddMinutes(30), chkPersistCookie.Checked, "your custom data") cookiestr = FormsAuthentication.Encrypt(tkt) ck = New HttpCookie(FormsAuthentication.FormsCookieName(), cookiestr) If (chkPersistCookie.Checked) Then ck.Expires = tkt.Expiration ck.Path = FormsAuthentication.FormsCookiePath() Response.Cookies.Add(ck) Dim strRedirect As String strRedirect = Request("ReturnURL") If strRedirect <> "" Then Response.Redirect(strRedirect, True) Else strRedirect = "webform1.aspx" Response.Redirect(strRedirect, True) End If Else Response.Redirect("webform3.aspx", True) End If End Sub

View Replies !

Need Help Converting A Select Query Into A Case Statement
I have the following query:

(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')

I need to convert this into a case statement.
I tried various ways but did not get the result that I was after

Thanks,
Laura

View Replies !

Case Statement Within A Select Where 2 Or More Instances Of The Record Exist.
Ok,I have a data warehouse that I am pulling records from using OracleSQL. I have a select statement that looks like the one below. Now whatI need to do is where the astrics are **** create a case statement orwhatever it is in Oracle to say that for this record if a 1/19/2005record exists then End_Date needs to be=1/19/2005 else getEnd_Date=12/31/9999. Keep in mind that a record could have both a1/19/2005 and 12/31/9999 instance of that account record. If 1/19exists that takes presedent if it doesnt then 12/31/9999. The problemis that the fields I pull from the table where the end_date is inquestion change based on which date I pull(12/31/9999 being the mostrecient which in some cases as you see I dont want.) so they are notidentical. This is tricky.Please let me know if you can help.SELECTCOLLECTOR_RESULTS.USER_ID,COLLECTOR_RESULTS.LETTER_CODE,COLLECTOR_RESULTS.ACCT_NUM AS ACCT_NUM,COLLECTOR_RESULTS.ACTIVITY_DATE,COLLECTOR_RESULTS.BEGIN_DATE,COLLECTOR_RESULTS.COLLECTION_ACTIVITY_CODE,COLLECTOR_RESULTS.PLACE_CALLED,COLLECTOR_RESULTS.PARTY_CONTACTED_CODE,COLLECTOR_RESULTS.ORIG_FUNC_AREA,COLLECTOR_RESULTS.ORIG_STATE_NUMBER,COLLECTOR_RESULTS.CACS_FUNCTION_CODE,COLLECTOR_RESULTS.CACS_STATE_NUMBER,COLLECTOR_RESULTS.STATE_POSITION,COLLECTOR_RESULTS.TIME_OBTAINED,COLLECTOR_RESULTS.TIME_RELEASED,COLLECT_ACCT_SYS_DATA.DAYS_DELINQUENT_NUM,sum(WMB.COLLECT_ACCT_SYS_DATA.PRINCIPAL_AMT)As PBal,FROMCOLLECTOR_RESULTS,COLLECT_ACCT_SYS_DATA,COLLECT_ACCOUNTWHERECOLLECT_ACCOUNT.ACCT_NUM=COLLECT_ACCT_SYS_DATA.ACC T_NUM(+)ANDCOLLECT_ACCOUNT.LOCATION_CODE=COLLECT_ACCT_SYS_DAT A.LOCATION_CODE(+)AND COLLECT_ACCOUNT.ACCT_NUM=COLLECTOR_RESULTS.ACCT_NU M(+)AND COLLECT_ACCOUNT.LOCATION_CODE=COLLECTOR_RESULTS.LO CATION_CODE(+)AND COLLECTOR_RESULTS.ACTIVITY_DATE =to_date(''01/19/2005'',''mm/dd/yyyy'')AND COLLECT_ACCOUNT.END_DATE = to_date(''12/31/9999'',''mm/dd/yyyy'')AND COLLECT_ACCT_SYS_DATA.END_DATE = *****************

View Replies !

Trying To Use The Results Of A Case Statement In My Select List In My WHERE Clause?
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:

SELECT
l.loanid,
p.investorid,
l.duedate,
case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate,
pc.interestrate
FROM loan l
inner join participation p on p.loanid = l.loanid
inner join paymentchange pc on pc.loanid = l.loanid
where p.investorid = '12345' and RateDueDate is not null
order by l.loanid, pc.duedate

I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.

Thanks!

View Replies !

How To Make Select Statement With &"case Sensitive&"
Hi,

I am using SQL Server 2005 now.

I have to queries.that are

select 8 from testdb where name = 'TONY'

select 8 from testdb where name = 'tony'

the two queies get same results now. but 'TONY' and 'tony' are different if we need 'case sensitive'.

how to make the above queries get different result

My test data like following (testdb table)

name phone
TONY 12345
tony 67890

Thanks

Mark

View Replies !

Problem Using Result From CASE In Another CASE Statement
I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:

Code Block...beginning of SQL view...
shipment_status =
CASE
    [logic for condition 1]
    THEN 1
    WHEN     [logic for condition 2]
    THEN 2
WHEN [logic for condition 3]
    THEN 3
    WHEN     [logic for condition 4]
    THEN 4
    ELSE 0
END,
shipment_status_text =
CASE
    [logic for condition 1]
    THEN 'Condition 1 text'
    WHEN     [logic for condition 2]
    THEN 'Condition 2 text'
WHEN [logic for condition 3]
    THEN 'Condition 3 text'
    WHEN     [logic for condition 4]
    THEN 'Condition 4 text'
    ELSE 'Error'
END,
...remainder of SQL view...

This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:

Code Block
...beginning of SQL view...
shipment_status =
CASE
    [logic for condition 1]
    THEN 1
    WHEN     [logic for condition 2]
    THEN 2
WHEN [logic for condition 3]
    THEN 3
    WHEN     [logic for condition 4]
    THEN 4
    ELSE 0
END,

shipment_status_text =

    CASE shipment_status

    WHEN 1 THEN 'Condition 1 text'

    WHEN 2 THEN 'Condition 2 text'

    WHEN 3 THEN 'Condition 3 text'

    WHEN 4 THEN 'Condition 4 text'

    ELSE 'Error'

END,
...remainder of SQL view...

This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View Replies !

Select Statement Within Select Statement Makes My Query Slow....
Hello... im having a problem with my query optimization....

I have a query that looks like this:

SELECT * FROM table1
WHERE location_id IN (SELECT location_id from location_table WHERE account_id = 998)

it produces my desired data but it takes 3 minutes to run the query... is there any way to make this faster?... thank you so much...

View Replies !

Case Statement Error In An Insert Statement
Hi All,
I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance.
My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it.

Here is my code:
Insert into myTblA
(TblA_ID,
mycasefield =
case
when mycasefield = 1 then 99861
when mycasefield = 2 then 99862
when mycasefield = 3 then 99863
when mycasefield = 4 then 99864
when mycasefield = 5 then 99865
when mycasefield = 6 then 99866
when mycasefield = 7 then 99867
when mycasefield = 8 then 99868
when mycasefield = 9 then 99855
when mycasefield = 10 then 99839
end,
alt_min,
alt_max,
longitude,
latitude
(
Select MTB.LocationID
MTB.model_ID
MTB.elevation, --alt min
null, --alt max
MTB.longitude, --longitude
MTB.latitude --latitude
from MyTblB MTB
);

The error I'm getting is:
Incorrect syntax near '='.

I have tried various versions of the case statement based on examples I have found but nothing works.
I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.

View Replies !

How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows

as per data in MS Sql

my tables are as follows
1)material
fields are -- material_id,project_type,project_id,qty, --

2)AB_Corporate_project
fields are-- ab_crp_id,custname,contract_no,field_no

3)Other_project
fields are -- other_proj_id,other_custname,po

for ex :
vales in table's are
AB_Corporate_project
=====================
ab_crp_id   custname contract_no field_no
    1                abc           234         66
    2             xyz             33            20

Other_project
============
other_proj_id     other_custname     po
      1        xxcx              111
      2        dsd                   222

material
=========
material_id         project_type     project_id          qty
    1                    AB Corporate 1              3
    2                     Other Project   2                7

i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project

sample query i write :--

select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id,
ab.custname ,op.other_proj_id,op.other_custname,op. po
case if m.project_type = 'AB Corporate' then
    select * from AB_Corporate_project where ab.ab_crp_id = m.project_id
else if m.project_type = 'Other Project' then
select * from Other_project where op.other_proj_id=m.project_id
end
from material m,AB_Corporate_project ab,Other_project op

but this query not work,also it gives errors

i want sql query to show data as follows

material_id      project_type       project_id      custname other_custname   qty
   1                   AB Corporate         1                        abc                        --                      3
   2                 Other Project            2                         --                         dsd                   7

so plz help me how can i write sql query for to show the output
plz send a sql query

View Replies !

Case Statement
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.

View Replies !

Case Statement In Sql Help
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!

View Replies !

What Is The Best SQL Statement For This Case ?
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!

View Replies !

Sql Case Statement Help
Hi all,
I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as
<code>
CASE WHEN (MyNum <> INT) then 0 end AS MyNum
</code>

This has to be included in the sql statement cause I need this field to get other data.
Any thoughts on how to achieve this would be greatly appreciated.

If Iâ€™m in the wrong thread section please advise of best one to get help in.

View Replies !

Case Statement
i am working on a sql statement that is kind of weird
i have a table but it only links to another table if that type of activity exists, for instance
i have an activity information in the CloseCall table if the call is closed, but if it is a followup activity it is in the followup table

i would like to display the close call notes from the closecall table if there is a close call activity in the activity table

T_Followup
T_activity<
T_CloseCall

here is the sql statement that i am trying to make work

SELECT t_Activity.ActivityDate, tlkup_ActivityType.ActivityTypeName, t_Activity.LocationID, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName,
tlkup_InterestLevel.InterestLevel, CASE activitytypeid WHEN 6 THEN
SELECT Closecallnotes
FROM t_closecall
WHERE t_Closecall.activityid = t_activity.activityid ELSE t_Activity.ActivityNotes END AS ActivityNotes
FROM t_Activity INNER JOIN
tlkup_ActivityType ON t_Activity.ActivityTypeID = tlkup_ActivityType.ActivityTypeID INNER JOIN
tlkup_Rep ON t_Activity.RepID = tlkup_Rep.RepID INNER JOIN
tlkup_InterestLevel ON t_Activity.InterestLevelID = tlkup_InterestLevel.InterestLevelID
WHERE (t_Activity.LocationID = 93)
ORDER BY t_Activity.ActivityDate DESC

is this possible this way if not how do i make it work, right now it is just telling me there is a syntax problem, which is why i thought i would post it here because i am not too sure on the syntax of a case command and how it can be used

View Replies !

Using LIKE In A CASE Statement
I have a unique situation. My data looks something like:Test 1Test 2Test 3Test 2Test 1Test 300Test 200Test 1Test 300Test 200I want to display all of the above like:Test 1Test 2Test 3Test 2MetalsTest 1Test 1Notice that I have everything displayed except for 'Test 200' and'Test 300'. This have been replaced with the word 'Metals' and I'monly displaying it one time, no matter how often Test 200 shows up, orTest 300.My code looks like the following. It works good, except if I have toadd 'Test 400' etc...I would have to hard code them and it will buildup real quick. I looked at GROUPs to see if that would help, but Idon't think it would because it has to be displayed with a differentname such as 'Metals' for all the tests...Test 100, 200, etc... Isthere a better way without having to add each number in it? I thinkthe best way is to use a LIKE statment where the WHEN is being usedbut I keep getting errors if I use the LIKE sytnax where the WHEN isbeing used.SELECT sampleFROM mysamplesWHERE (sample <'TEST 200') AND (sample <'TEST 300')UNION ALLSELECT DISTINCTSAMPLE = CASE Sample WHEN 'test 200' THEN 'Metals' WHEN 'Test 300'THEN 'Metals'ENDFROM MYSAMPLESWHERE (sample = 'TEST 200') OR(sample = 'TEST 300')Any help is appreciated...

View Replies !

SQL CASE Statement
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y)

Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help?

SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage
FROM [Company/Year]
GROUP BY CCode, BS_TOTAL_ASSETS

Thanks

View Replies !

CASE Statement Help
Hello everyone again, now I want to take my SP a step further. As in using a CASE towards my 'taxable' field. It's value is 1 if no tax, and null if taxed. I have two seperate formulas I want to run depending on the 'taxable' value.

Below is what i'm starting to chew on...

CASE when tbl_products.ptaxable = '1' then ((1+(.02))*((tbl_products.pprice)*(tbl_material_us ed.mqty))) else 0 end

How would I integrate my other formula for when the 'taxable' value is null? After the "else 0" in my CASE?

CASE when 'taxable' = 1 THEN run_formula1 ELSE run_formula2 ???

These CASE statments will go in my WHERE clause correct?
Thanks!!!

View Replies !

AVG(CASE) Statement - Help Please
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype.
I want to do a CASE statement to get all average scores for all calltypes.

Select Site, avg(totalscore) as [Avg Score]
FROM DB
WHERE calltype = 'A'
GROUP BY Site

Results

Site Avg Score (for A)
1 85
2 75.5
3 85.33

SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore
ELSE 0 END) AS [Avg Score For A]
FROM DB
GROUP BY Site

Results

Site Avg Score For A
1 i get 8.5
2 i get 37.75
3 i get 36.57
Why am I getting a difference?
Any help is greatly appreciated - thank you

View Replies !

Case Statement
Have a procedure called Collections_Cats. It runs fine but l want to modify it but l'm finding it difficult
to get the desired output. The case statement gives an example of what l'm trying to achieve. l would like to build it into my procedure.

l have a table called

paysheet

customer_no loan_no payday payfrequency etc
0000000000 000000 22 3
111111111122222212 4
3333333333 333333 21 2
5555555555 555555 6 1

payfrequency 1= daily
2= weekly
3= fortnightly
4= monthly

l would like to get all the records that satisfy my select criteria. Which is select all records and fields as
specified in my procedure that have a payday thats today only?

How can l build my case statement into the procedure?Is ther a better way of doing it?

l also want to do a count of all the records that have been processed by the procedure ?

Declare @Today int
Set @Today = (SELECT Day(GETDATE()))
SELECT
CASE
WHEN Pay_frequency = 4 And payday =@Today THEN @Today
WHEN Pay_frequency = 3 And payday =@Today THEN @Today
WHEN Pay_frequency = 2 And payday =@Today THEN @Today
WHEN Pay_frequency = 1 And payday =@Today THEN @Today
ELSE 0
END AS "Pay_day",Customer_No,Pay_Frequency
FROM Pay_Sheet
Order by Pay_frequency Desc

************************************************** *************

************************************************** *************

CREATE Procedure Collections_Cats
AS
BEGIN

Declare @today int
Set @today = (SELECT Day(GETDATE()))

DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment,
pay_sheet.payday AS payday
FROM Transaction_Record tr

INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_sheet.Payday = @today

OPEN Collections_Cats_Cursor

-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END

CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO

View Replies !

Case Statement
How do l use the case statement to cater for these updates.

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = ('AB')
WHERE AB_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + ' | SB'
WHERE SB_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'SB'
WHERE SB_CLIENT = 1 And entity is null
COMMIT
go

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | CI')
WHERE CI_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'CI'
WHERE CI_CLIENT = 1 And entity is null
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | GEMS')
WHERE GEMS_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'GEMS'
WHERE GEMS_CLIENT = 1 And entity is null
COMMIT
GO

View Replies !

IF Else Within A Case Statement
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row.
I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated.
Please help
Thanks

select
isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname
, case ProductID
WHEN 22 then count(S.Product)
Else 0
END AS Builders
, case ProductID
WHEN 23 then count(S.Product)
Else 0
END AS Associates
, case ProductID
WHEN 24 then count(S.Product)
Else 0
END AS Affiliates
FROM vwpersons p with (nolock)
join vwSubscriptions S with (nolock)
on S.RecipientID = P.ID
where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
group by P.Lastname, S.ProductID
having count(P.LastName)>=1
order by 1

Result set :

Firstname Builders AssociatesAffiliates

Abarca 010
Abascal200
Abelar 100
Abeyta100
Abeyta010
Abreu 100
Abreu 010
Acevedo100
Acevedo050

View Replies !

Case Statement
Hi
Can anybody tell me how to execute store procedure in the case statement.

Thanks

View Replies !

CASE Statement
I am trying determine if I can do something like the code below. I have done a left join on a table. In the select statement there are three possible values. Yes, No, or NULL. I could like to use a Case statement to determine if there is Null. If so, then output N/A in place of the Null. So then my possible valus are Yes, No, and N/A.

Any clues?

Thanks,
John

SELECT TOP 100
OfferDressRoomYN.yesno as OfferDressRoom
= CASE
WHEN offerDressRoomYN.yesno IS NULL THEN 'N/A'
END,
FROM dataquestionnaire dq
LEFT OUTER JOIN yesno OfferDressRoomYN ON dq.c3_1 = OfferDressRoomYN.yesnoid

View Replies !

CASE Statement
Hello All,

I have a condition for which I am trying to write a case statement as follows..

SELECT @Segment_Field = 'Acct_Status_' + CASE @Public_record_Type
WHEN NULL THEN ' '
WHEN 'BP' THEN 'BP' + ((CASE @Bankruptcy_Type WHEN NULL THEN ' ' ELSE '1' END) OR (CASE @Acct_Status WHEN NULL THEN '' ELSE '2' END))
END

But this is not working.The condition is actually tht I have to set the value for the variable @Segment_Field acc to the value of variables @Bankruptcy_Type and @Acct_Status. It should first check for @Bankruptcy_Type and if it is not null then the value should be Acct_Status_BP1.Then it should check for @Acct_Status and if it is not sull then the vlaue should be set to Acct_Status_BP2.

Can somebody please help..

Thanks

View Replies !

CASE Statement With IN/OR
Hello,

I'm trying to write a query with case statement.

the condition is

when project_ref =393 then select qtn_ref in (7070000,7060000))
and when project_ref =391 and select q.qtn_ref=8700000

I need this condition in 'WHERE' statement.

I can use 2 SELECT queries using 'IF ELSE', but I woud like to find out if there's any way to use CASE so I can write 1 query.

Tring to do something like this but it doesn't work.

SELECT *

FROM table

WHERE qtn_ref = case when project_ref =393 then 7070000 or 7060000
when project_ref =391 then 8700000

Anyone can help, please?

View Replies !

Case Statement
Hi!

      I need a case that returns the result of a select if it is not null, and -1 if it is null. I did it this way:

select

    case

       when(select column from table where conditions) is null then -1

       else(select column from table where conditions)

But it doesn't seem very clever to repeat the select statement. Is there any way I can do it without repeating the "select column from table where conditions"???

            Thank you!

View Replies !

SQL CASE Statement
Hi,

The below CASE statement should check if the @Period variable is set to a monday date.
If it is then I want to get data where r.ReceivedDate is set to a Friday date.

What is wrong with the below syntax?

Get this error...
Incorrect syntax near '='.

Code Snippet

WHERE (p.Name = 'test')
AND
CASE WHEN
LEFT(DATENAME(dw, @Period), 3) = "MON"
THEN
(CONVERT(VARCHAR(10), r.ReceivedDate, 103) = DATEADD(day, -3, @Period)
ELSE
(CONVERT(VARCHAR(10), r.ReceivedDate, 103) = @Period)

View Replies !

T-SQL - CASE Statement

I need to execute a simple Case Statement. Strangely, if I include a comparison operator I receive an error message showing a syntax error (Message 102, Level 15). But the same statement without the operator works jsut fine.

Works:
Select LastName, Group =
Case Age
When 15 Then '1'
When 18 Then '2'
Else '20'
From tblAgeGroup
End

Does Not Work:

Select LastName, Group =
Case Age
When <15 Then '1'
When <18 Then '2'
Else '20'
From tblAgeGroup
End

Also, I noticed that this does not work in any other combination of the "When.." clause.

Any help is appreciated.

Thanks

View Replies !

Is Case Statement The Only Way

Hi

I need to generate a SQL report like below,its basically calculating the count of students

For

District Level

then for

Region Level

then for

Each School under a Region

Like the Display Below

District summary

Total

Male

Femal

Indian

White

Asian

--

--

--

Type1

22

33

22

11

11

11

23

11

13

Type2

2

€¦6

€¦7

;;;;

13

14

Region 1

Region 2...................Region 8

Each School

School1....School 15

Do I have to have

case for each Type for each Race

and then

for Each Levels

of

District

Region

And 500 Schools??

Please Help

Thanks

View Replies !

Need Help Using Case Statement
I've these following table
SEL
Date_Taken | Main_ID | Time | Daily_Rainfall
---------------------------------------------------
...
...
...
4/3/2005 | 202 | 1015 | 2
4/3/2005 | 202 | 1045 | 2
4/3/2005 | 202 | 1215 | 7
4/3/2005 | 203 | 715 | 2
4/3/2005 | 203 | 1345 | 2
4/3/2005 | 203 | 1530 | 7
...
...
...
5/29/2005 | 203 | 1100 | 56
5/29/2005 | 203 | 1130 | 156
5/29/2005 | 203 | 1145 | 256
...
...
...

Station_Info
State | Main_ID
--------------------
SEL | 202
SEL | 203
SEL | 204
SEL | 205
SEL | 209

Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
------------------------------------------------------------------------------
202| 4/30/2005 | 525.8 | 683.11 | 356.33 | 754.33
203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34
204| 4/30/2005 | 265.53 | 453.21 | 543.66 | 753.24
205| 4/30/2005 | 251.38 | 754.33 | 478.34 | 785.22
209| 4/30/2005 | 259.5 | 356.34 | 894.33 | 354.78
202| 5/30/2005 | 565.8 | 383.11 | 756.33 | 254.33
203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34

This query only display Max(Daily_Rainfall) per day from SEL

SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL,
CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3
THEN t3.[3MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=6
THEN t3.[6MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=9
THEN t3.[9MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=12
THEN t3.[12MthCumRf]
END AS CumRf
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1 INNER JOIN dbo.LT t3 ON t3.STATION_ID =t2.STATION_ID
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
ANDt1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL'
ORDER BY MAIN_ID,DATE_TAKEN,TIME

In my query, i still can query if last3month using CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 THEN t3.[3MthCumRf]

How to adjust my CASE statement
if Main_ID=203, Date_Taken=5/5/2005,

i should pickup following rows
Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
-------------------------------------------------------------------
203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34

if Main_ID=203, Date_Taken=4/5/2005,
i should pickup following rows
Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34

Need someone help... plz

View Replies !

Case Statement
Im running the following sql statement but I dont see the
expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'.
There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values.

SELECT P1.id,
CASE
WHEN p1.acc!= p1.cl1 then 'ONE'
WHEN p1.mcc!= p1.cl2 then 'TWO'
when p1.ncr!= p1.cl3 then 'THREE'
Else NULL END
As NOnMatchingColumn
from
(select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1

View Replies !

Case Statement
I have a simple query with a case statement:
Select
Case
when HR.PREM_CUR_QUOTED_AM <> 0 and HR.PREM_LOST_AUTO_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0
THen 0
else 1
end Cur_check
from dbo.THIT_RATIO_DETL HR

In the where clause i want to to put
where cur_check = 1

but i'm getting an error that says :
Invalid column name 'Cur_check'

Can you use a case statement column in the where clause?

View Replies !

Case Statement
I am very new at SQL and I need some help if you can. A lot of my case statements have not been working for me. When I run my query the new names do post into the PRO_XTYP field. But when I want to add a criteria into my where clause it does not recognize the change. I have been trying to figure this out. My where clause would beâ€¦â€¦ Where Pro_XTYP = â€˜Ancilaryâ€™ (nothing shows), but when I put Pro_XTYP = â€˜ANCIâ€™ then data pops up. Why isnâ€™t my labels working? Do I need to write a IF Clause? Below is my case statement.

Pro_XTYP =
Case
When Pro_XTYP = 'PRIM' then 'Physician'
When Pro_XTYP = 'PROV' then 'Physician'
When Pro_XTYP = 'SPEC' then 'Physician'
When Pro_XTYP = 'ANCI' then 'Ancillary'
When Pro_XTYP = 'HOSP' then 'Hospital'
else Pro_XTYP
End,

View Replies !

Case Statement Help
select * into #t1
from pCodes

update #t1
set postcode = left(postcode,(charindex(' ', postcode)+1) + ' ' + right(postcode,3)) from #t1
where len(postcode) = 6

update #t1
set postcode = left(postcode,(4)-1) + ' ' + right(postcode,3) from #t1
where len(postcode) = 7

whats wrong with my case statment below cant seem to get it working!

SELECT
CASE POSTCODE WHEN LEN(POSTCODE) = 6 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3),
CASE POSTCODE WHEN LEN(POSTCODE) = 7 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3),
END
FROM #T1

View Replies !