Query Problem (w/ A Case Statement) In SS2000

Feb 15, 2008

In SS 2000 I want to create a sproc that returns the correct address block for a contact. I want to concatenate AddressLine1 and AddressLine2 if AddressLine2 has a value. When I run the following query I get an error (below):

SELECT vwICPContacts.PersonID, vwPersons.PreferredAddress, vwPersons.Email1,vwPersons.email2, vwPersons.email3,
CASE
WHEN vwPersons.preferredAddress='Home Address' then
case
when (isnull([vwpersons].[HomeAddressLine2],'') = '') then [vwPersons].[HomeAddressLine1]
when (isnull([vwpersons].[HomeAddressLine2],'') <> '') then [vwPersons].[HomeAddressLine1] + Char(13) & Char(10) & [vwPersons].[HomeAddressLine2]
END
ELSE
case
when (isnull([vwpersons].[AddressLine2],'') = '') then [vwPersons].[AddressLine1]
when (isnull([vwpersons].[AddressLine2],'') <> '') then [vwPersons].[AddressLine1] + Char(13) & Char(10) & [vwPersons].[AddressLine2]
END
END
AS MailingAddress
FROM (vwPersons INNER JOIN vwICPContacts ON vwPersons.ID = vwICPContacts.PersonID) LEFT JOIN vwCompanies ON vwPersons.CompanyID = vwCompanies.ID

Error Message:
Server: Msg 403, Level 16, State 1, Line 1
Invalid operator for data type. Operator equals boolean AND, type equals nvarchar.

I've done this before but am completely stumped. Any ideas?

Jane

View 4 Replies


ADVERTISEMENT

Using Query In CASE Statement

Mar 4, 2008

Why couldn't they make SQL syntax error mistakes a little less vague.

Anyway, I was wondering, is it possible to use a set in your case statement?

CASE
(
select distinct tbhtg.TrainingBlockHistoryTypeGroupingCd
from tblTrainingBlockHistory tbh
inner join tblTrainingBlockHistoryType tbht on tbh.TrainingBlockHistoryTypeCd = tbht.TrainingBlockHistoryTypeCd
inner join tblTrainingBlockHistoryTypeGrouping tbhtg on tbht.TrainingBlockHistoryTypeGroupingCd = tbhtg.TrainingBlockHistoryTypeGroupingCd
where (select dbo.fnTrainingBlockStatus( 1234, getdate())) = tbht.TrainingBlockHistoryTypeCd
)
WHEN 'S' then (COUNT(DISTINCT TRD.TrainingBlockHistoryId) = COUNT(DISTINCT SWT.TrainingBlockHistoryId)) end


This is giving me an error on the WHEN statement. The error is "Incorrect syntax near '='" Have no idea how to fix this.
But the select statement seems to work, and as far as I can tell, that is how you write a CASE statement.

Also, this CASE statement is inside the HAVING clause - is that going to be a problem?

View 3 Replies View Related

How Do You Hold The Value Of A CASE Statement For Further Use In The Query??

Feb 18, 2007

I have a select statement and I would like to know which when clause(1st set of whens) was fired and the value of the then for further use in my query.  I am wondering if this is possibly, and how it would be done.  If i try and set a @variable at the beginning of the case statament i get an error, here is my query         SELECT PIE.productID, PIE.quantity, CEILING((PIE.width/12.0)/0.5)*.5 as width, CEILING((PIE.length/12.0)/0.5)*.5 as length,                 ***throws an error**** @sqft = CASE  --- but the then clause of this CASE is the value i want for later use in this same query                                 WHEN CEILING((PIE.width/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.length/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.width/12.0)/0.5)*.5) * 1.5) *                                     CEILING((PIE.length/12.0)/0.5)*.5)/1.0)*1.0                                 WHEN CEILING((PIE.length/12.0)/0.5)*.5 > (2.0 * (CEILING((PIE.width/12.0)/0.5)*.5)) THEN CEILING((((CEILING((PIE.length/12.0)/0.5)*.5) * 1.5) *                                     CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0                                 ELSE CEILING((CEILING((PIE.length/12.0)/0.5)*.5) * (CEILING((PIE.width/12.0)/0.5)*.5)/1.0)*1.0                         END AS sqft,                         CASE                                 WHEN @custMarkup = 1 THEN (SELECT PML1 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 2 THEN (SELECT PML2 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 3 THEN (SELECT PML3 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 4 THEN (SELECT PML4 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                                 WHEN @custMarkup = 5 THEN (SELECT PML5 FROM ProductMarkup PM WHERE PIE.productID = PM.productID)                         END AS markup         FROM ProductsInEstimate PIE         WHERE estID = @estid

View 1 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

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

Help With Query (count With Case Statement)

Mar 29, 2008

Hi,

I have the following query, that returns the proper count value I am looking for. I would like to modify it a little bit, but can't remember exactly how to do it.

select count(messageFromID) FROM tblMessage WHERE messageFromID = 1000) as OutBoundMessages

Basically now, it returns the "OutBoundMessages" column

I would like it to return "OutboundMessages_unChecked" and "OutboundMessages_checked" as well as "OutboundMessages_total" (I guess I could determine this value by adding the two values in the front end too. I definatley dont want to do a lookup to determine the total )

I determine if the column is "checked" or "unChecked" by a column in
tblMessage

For example

tblMessage.checked = 1 = ("checked")
tblMessage.checked = 0 = ("unChecked")


any help much appreciated..

thanks!
mike123

View 7 Replies View Related

T-SQL CASE Statement.. NOT In A SELECT Query - ??

Feb 4, 2008

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 5 Replies View Related

Using CASE Statement In A Prediction Query

Aug 5, 2006

Hi,

Can i use a CASE statement in a prediction query.
the following query is throwing me an error

SELECT
CASE [Sales Forecast Time Series].[City Code]
when 'LA' then 'Los Angeles'
WHEN 'CA' THEN 'California'
ELSE 'OTHERS'
END,
PredictTimeSeries([Sales Forecast Time Series].[Sales Value],5)
From
[Sales Forecast Time Series]

ERROR:
Parser: The statement dialect could not be resolved due to ambiguity.

Also

Is it possible to discretize the
Sales Value column using a the CASE statement, the output column of
PredictTimeSeries function.



Is there a link that can give me a
comprehensive info on what can be achieved and what cant be using DMX queries

View 3 Replies View Related

Need Help Converting A Select Query Into A Case Statement

Nov 24, 2004

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 6 Replies View Related

Adding Case Statement To Existing Query

Apr 3, 2008

I was asked to add an additional column to an existing query. I'm using Microsoft Query with a MS SQL 2000 server, and don't have much knowledge of SQL in general. Here's the existing query:



SELECT A.COMPANYCODE,
A.INVOICENUMBER,
A.LINENUMBER,
A.SONUMBER,
A.CUSTOMERCODE,
A.SHIPPERNUMBER,
A.INVOICEDATE,
A.ITEMCODE,
A.QUANTITYINVOICED,
A.UNITPRICE AS 'InvPrice',
A.QUANTITYINVOICED * A.UNITPRICE AS 'ExtInvPrice',
INVENTORY.UNITPRICE AS 'StdPrice',
INVENTORY.STANDARDCOST,
A.QUANTITYINVOICED * INVENTORY.STANDARDCOST AS 'ExtCost',
(A.QUANTITYINVOICED * A.UNITPRICE) - (A.QUANTITYINVOICED * INVENTORY.STANDARDCOST) AS 'GM$',
(INVENTORY.UNITPRICE - A.UNITPRICE) * -1 AS 'PriceListDiff'
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY
WHERE INVENTORY.COMPANYCODE = A.COMPANYCODE
AND INVENTORY.ITEMCODE = A.ITEMCODE
AND ((A.COMPANYCODE = 'csp')
AND (A.QUANTITYINVOICED <>$ 0)
AND (A.INVOICEDATE BETWEEN '03/1/08' AND '03/31/08'))
ORDER BY A.INVOICEDATE,
A.ITEMCODE



They want a column added to the current query where if A.Unitprice is greater than or equal to Inventory.UnitPrice then populate the column with A.QuantityInvoiced*A.UnitPrice. I posted on another forum, and the advice I got was to add this:


SELECT NewColumn = CASE
WHEN A.Unitprice >= Inventory.UnitPrice THEN A.QuantityInvoiced * A.Unitprice
ELSE 'null'
END,
FROM ABW.DBO.SALESANALYSISHISTORY A,
ABW.DBO.INVENTORY INVENTORY

I'm not sure how to integrate it to the current query, so I've tried running it by itself, and I get the error: Didn't expect 'A' after the SELECT column list.

Any help would be greatly appreciated to modify the current query to display the new column.

View 2 Replies View Related

Transact SQL :: Using Case Statement With Update Query

Jun 22, 2015

I have a table A  and lookup table B.

table A:
| ID | FRUIT | VEGETABLE | GOOD |
--------------------------------------------
|  1  | orange | cabbage     |  no   |
|  1  | apple  | lettuce        |  yes   |
|  1  | kiwi     | broccoli      |  no   |
|  1  | pear    | kale           |  yes   |

table B:
| ID | FRUIT | VEGETABLE |
-------------------------------
| 1  | apple  |  lettuce       |
| 2  | pear    |   kale         |

If the fruit and vegetable in table A is found in table B, then set the GOOD column = yes, else no.

This is what I have so far.

update tableA
set GOOD =
(case when tableA.id = C.id then 'yes'
else 'no'
end
)
from
(select tableA.id as id
from tableA A
left join tableB B on B.fruit = A.fruit
and B.vegetable = A.vegetable) C

View 6 Replies View Related

SQL Server 2012 :: Filtering Query Using CASE Statement Within WHERE Clause

Aug 21, 2014

How I am using a CASE statement within a WHERE clause to filter data:

CREATE PROCEDURE dbo.GetSomeStuff
@filter1 varchar(100) = '',
@filter2 varchar(100) = ''
AS
BEGIN
SELECT

[Code] .

What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.

Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?

View 5 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

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 1 Replies View Related

SQL Server 2012 :: Update Statement With CASE Statement?

Aug 13, 2014

i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause

the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]

i was thinking of doing

Update [tablename]
SET [No] =
CASE
WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa'
ELSE 'Null'
END

What is the best way to script this

View 1 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

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 7 Replies View Related

Transact SQL :: Update Statement In Select Case Statement

May 5, 2015

I am attempting to run update statements within a SELECT CASE statement.

Select case x.field
WHEN 'XXX' THEN
  UPDATE TABLE1
   SET TABLE1.FIELD2 = 1
  ELSE
   UPDATE TABLE2
   SET TABLE2.FIELD1 = 2
END
FROM OuterTable x

I get incorrect syntax near the keyword 'update'.

View 7 Replies View Related

Case Statement Error In An Insert Statement

May 26, 2006

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 10 Replies View Related

How To Show Records Using Sql Case Statement Or If Else Statement

Feb 20, 2008

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 8 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

Case Statement In Sql Help

Jan 18, 2008

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 7 Replies View Related

Case Statement

Apr 22, 2008

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 9 Replies View Related

Sql Case Statement Help

Jun 2, 2005

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 1 Replies View Related

What Is The Best SQL Statement For This Case ?

Sep 17, 2005

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 2 Replies View Related

CASE Statement

Jan 23, 2002

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 2 Replies View Related

Case Statement

Aug 27, 2001

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

Thanks

View 1 Replies View Related

Case Statement

Sep 23, 2002

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 1 Replies View Related

IF Else Within A Case Statement

Feb 14, 2006

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 2 Replies View Related

AVG(CASE) Statement - Help Please

Aug 20, 2007

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 1 Replies View Related

SQL CASE Statement

Aug 18, 2004

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 3 Replies View Related

Case Statement

Apr 11, 2008

i ahve one fucniton:
create function fntotalcountcustclas

( @campaign varchar(50), @startdate datetime, @enddate datetime)
RETURNS TABLE
AS
RETURN
( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount

from
(
select
vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas
from vwcustnmbr vc
join vwitemnbmr vi
on vi.sopnumbe=vc.sopnumbe
Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)

group by vi.itemnmbr,vc.custclas, vc.custclasdescription
) as t
)
when i m executing it:
select * from fntotalcountcustclas('copd','1/1/2008','4/11/2008')
order by totalcount desc

i m getting results like:
itemnmbr,custclas,custclasdescription,totalcount
------------------------------------------------
06-5841 STANDARD Standard(web) 31
06-5840 STANDARD Standard(web) 30
kr-014 STANDARD Standard(web) 72
06-5841 INDPATIENT Patient 12
06-5840 INDPATIENT Patient 9
06-5845 INDPATIENT Patient 6
06-5841 PROGRAM Program 6
06-5841 INST-HOSPITAL Hospital 11
...................

Basically, i ahve to use one condition to get corrrect output related to inputs:

like - i have to input @category varchar(50), @category_value varchar(50)
and if category = 'campaign' then category_value = ''
then output should be itemnmbr sum(totalcount) [whatever should be custclas or custclasdesscription]
itemnmbr sumcount
-----------------
06-5840 52
06-541 101
06-452 26
kr-045 252

and if categroy = 'item' then category_value = any itemnmbrs(06-5840,06-5845,06-5841 etc..)
then output should be
itemnmbr custclas custclasdescription totalcount
-----------------------------------------------------
06-5840 STANDARD Standard(web) 31
06-5840 INDPATIENT Patient 9
06-5840 PROGRAM Program 6
06-5840 INS-HOSPITAL Hospital 17

like that..

can anyone help me to write case statement.
thanks a lot!!

create function fntotalcountcustclas

( @campaign varchar(50), @startdate datetime, @enddate datetime,
@category varchar(50), @category_value varchar(50))
RETURNS TABLE
AS
RETURN
( Select t.itemnmbr,t.custclas, t.custclasdescription, t.totalcustclas as totalcount,
case when category

from
(
select
vi.itemnmbr, replace(vc.custclas,'','Unspecified') as custclas, vc.custclasdescription, count(vc.custclas) as totalcustclas
from vwcustnmbr vc
join vwitemnbmr vi
on vi.sopnumbe=vc.sopnumbe
Where vi.Campaign = @Campaign and (vc.docdate between @startdate and @enddate)

group by vi.itemnmbr,vc.custclas, vc.custclasdescription
) as t
)

View 10 Replies View Related

Case Statement

May 5, 2008

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 5 Replies View Related

Help With Case Statement

Jun 6, 2008

I'm not sure if I am doing this the right way. In my table I have project ids. Each project id has several activities associated with it. Each project has a starting balance. Each activity posts an expense to the total balance of the project. If the project has enough money to handle the charges made by the activities, all the activity expenses can be "posted". If there isn't enough money, I want to loop through the activities, check to see if there is enough of a balance to "post" the first one, If there is, then I want to re-adjust the balance and check the second activity. I want to scroll through each project/activity to see what can be "posted". Here is what I have so far, but I cannot work out how to change the total balance amount. Hopefully what I am trying to do makes sense!



declare @testId nchar(6)
declare @RowNum int
declare @newBalance int
select top 1 @testId=projID from #ProjIds
set @RowNum = 0

WHILE @RowNum <= (Select Max(temp_id) from #ProjIds)
BEGIN
set @RowNum = @RowNum + 1
set @newBalance = (select top 1 Bal_2300
from #RevRecData
where @testId=projId
order by projID, activity)

select projId, activity, postCr, Bal_2300,
'New_Status' = Case when (postCr <= Bal_2300) then 'Can Clear' else 'Still Check' END,
'New_Balance' = Case when (postCr <= @newBalance) then (@newBalance - postCr) else @newBalance End
from #RevRecData
where @testId=projId
order by projID, activity

select top 1 @testId=projId from #ProjIds
where projId > @testID
END

View 12 Replies View Related







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