Subquery In Case Statement
Jul 23, 2005
Hi all, first, let me preface this by saying that I am very new to sql
server, coming from oracle.
Here is my problem: I would like to have a case statement (similar to
decode in oracle) that will test a query for a null value. Here is my
statement:
SELECT
CASE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) WHEN NULL
THEN 0
ELSE
(SELECT MAX(SEQ) + 1
FROM [TRANSACTION]) END
It functions correctly if there is a value for MAX(SEQ) + 1, otherwise
it returns null. It's as if the test for null fails. I hope that
makes sense.
This will ultimately be in an insert statement; I have taken it to the
bare minimum to better understand where the problem is.
Please let me know if I am doing something dumb. Does this sort of
thing have to in a stored procedure?
Anyway, thanks for any help. have a great day!
Ryan
View 6 Replies
ADVERTISEMENT
Mar 27, 2007
I have the following in my WHERE clause for my Sql Server Reporting Services 2000 report (the user can use the parameter @MaxRevs to view all numbers (colNumber) or only the max of colNumbers when grouped by colParentNumber):
AND (tblMain.colNumber IN CASE @MaxRevs WHEN '' THEN '(SELECT colNumber FROM tblMain)' ELSE '(Select max(colNumber) From tblMain Group By [colParentNumber])' END))
I get the following error:
1. ADO error: Syntax error or ADO access error
I've used Case in the past, but it was for a "Like" portion in my WHERE clause.
The following doesn't seem to help me either in the SQL portion:
IF @MaxRevs = '' SELECT ....
ELSE
SELECT ....
With the above query, nothing is ever returned. I can even name the parameter to @MaxRevs55, which doesn't even exist, and the report just brings back a blank page w/o any errors.
Thanks!
View 2 Replies
View Related
Aug 10, 2005
I'm trying to create an aliased field on the fly in my sql string to use later in my datagrid, but having a tuff time coordinating the right CASE WHEN together with a subquery:
amtA = (select b.salesamount from b WHERE b.BoardDate = '" + day1 + "')
CASE WHEN b.salesamount > 0 THEN 'amtA' ELSE NULL END , the above will not give me an error, but it displays a blank datagrid when posting a date from a calendar to view a datagrid with information for that particular date.What I want to accomplish is making amtA an aliased field - the salesamount from the day before. the user will click on a calendar date from the page before and view data for that date, but 'amtA' will be the salesamount from the previous day (already configured in c#: DateTime day1;day1 = requestedday.AddDays( -1);)and i will want to display 'amtA' in a column in my datagrid (eventually will do a sum with day2, day3, etc for a weekly total).Just wondering the best way to parse yesterday's 'amtA' and use it as an aliased field namethanks in advancenetsports
View 4 Replies
View Related
Jun 8, 2008
Hello,
I am trying to do something like this:
select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA
But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.
How do I do this?
View 2 Replies
View Related
Nov 2, 2007
Hope someone can help;
I have a table with a list of payment information i have three other tables that store different types of commission rates that were active at a particular time.
Payments table – holds all payments received by customers
DirectRate table – holds the Direct rate active between start and end dates
ComRate table – holds the Commission rate active between start and end dates
FieldRate table – holds the Field rate active between start and end dates
Basically I am trying to get the total value of commission on all payments for all the different rates. To give you an example one payment can be of type Direct which would have to have the correct payment rate applied from the DirectRate table for the correct date range, this also applies for payments that are of type ComRate & FieldRate.
So I have the following SQL
SELECT CASE
WHEN dp.ReceivedByID = 1
THEN
dp.Amount * ((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL)))/100)
WHEN dp.ReceivedByID = 2
THEN
dp.Amount * ((Select tD.Rate From dbo.mTrackerDirectChange tD where tD.ClientID=d.ClientID and tD.ContractID=d.ContractID AND ((dp.PaymentOn >= tD.StartDate AND dp.PaymentOn <= tD.EndDate) or (dp.PaymentOn >= tD.StartDate AND tD.EndDate IS NULL)))/100)
ELSE
dp.Amount * (((Select tF.Rate From dbo.mTrackerFeeChange tF where tF.ClientID=d.ClientID and tF.ContractID=d.ContractID AND ((dp.PaymentOn >= tF.StartDate AND dp.PaymentOn <= tF.EndDate) or (dp.PaymentOn >= tF.StartDate AND tF.EndDate IS NULL))) + (Select tFe.Rate From dbo.mTrackerFieldChange tFe where tFe.ClientID=d.ClientID and tFe.ContractID=d.ContractID AND((dp.PaymentOn >= tFe.StartDate AND dp.PaymentOn <= tFe.EndDate) or (dp.PaymentOn >= tFe.StartDate AND tFe.EndDate IS NULL))))/100)
END
From
dbo.DebtPayment dp,
dbo.ImportBatchItem bi,
dbo.Debt d
where
d.DebtID=dp.DebtID
AND dp.DebtID=bi.ItemID
AND bi.ImportBatchID=2
I am using dp.ReceivedByID to assertain the payment type then depending upon that using the case statement to multiply the amount by the correct rate for the correct date range in the correct table. This sql works fine but it gives me a list of commision values, one for each payment. My problem is when I try to do a sum on this case statement I get an error
“Cannot perform an aggregate function on an expression containing an aggregate or a subquery�
any help most appriciated
p
View 7 Replies
View Related
Apr 12, 2008
I'm trying to select from a table with three columns. I want these columns to be spread out among multiple columns based on the values. I hope someone can shed some light on this. I might be able to use pivot, but don't know how the syntax would roll for this.
Here is the example of dummy values and the output I am trying to obtain.
drop table table1
create table table1
(Category int, Place int, Value int)
insert into table1 values
(1, 1, 20)
insert into table1 values
(1,2, 12)
insert into table1 values
(1,3, 30)
insert into table1 values
(2,1, 34)
insert into table1 values
(2,2, 15)
insert into table1 values
(2,3, 78)
select Category,
(select top 1 value from table1 where place = 1 and Category = t1.Category) as place1,
(select top 1 value from table1 where place = 2 and Category = t1.Category) as place2,
(select top 1 value from table1 where place = 3 and Category = t1.Category) as place3
from Table1 t1
group by Category
Thanks for the help.
View 5 Replies
View Related
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
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
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
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
Dec 7, 2000
Hi Everyone,
I want to insert a row in a table with 2 fields.
The data for the first column comes from a variable
and the data for the second column comes from a select query
can i insert the record in one sql statement like
************************************************** *************
declare @var1 int
set @var1 = 5
insert into mytable1 (field1,field2)
values
(@var1,select field1 from mytable2 where field2 = 10)
************************************************** ***************
where field2 in mytable2 is a primary key.
Awaiting a reply!
Thanks,
saad.
View 1 Replies
View Related
Apr 28, 2015
I have to write a delete statement that deletes all customers that have not put in an order I must use a subquery in the exist operator.
View 5 Replies
View Related
Nov 19, 2006
Hello!
I can use querys like these in Access:
SELECT Field1,
(SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1
SELECT Field1,
(SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1
But when I
try execute it with SQL Server Everywhere it says "Token in error
=
SELECT".
Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.
-Teemu
View 3 Replies
View Related
May 19, 2008
I am executing the following statement in SQL 2005 and it fails with:
Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
update #tmp_rpt
set [value] = (select sum([value]) from #tmp_rpt a where a.project_id = #tmp_rpt.project_id and a.sum_date < = #tmp_rpt.sum_date)
from #tmp_rpt
I am tryng to perform a running total.
This doesn't appear to be an aggregate in another aggregate or subquery.
Thanks,
Greg
View 2 Replies
View Related
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
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
Nov 28, 2006
Hi there,
I am pulling back records from the DB in this case to get Wheel information. I am pulling back based on user input, but also need to query a second table that contains the Price and model number from another table based on a field being pulled back in the original select.
I am not sure if this makes sense, here is a working copy of the SQL I have , but it's not pretty. There must be another way of stating this statement that i am missing, can anyone give me some suggestiosn?
SELECT tblMacPak2.*,
(SELECT ListPrice
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS ListPrice,
(SELECT PartNumber
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS PartNumber
FROM tblMacPak2
WHERE (Make = N'honda') AND (Model = N'civic') AND (SubModel = N'standard') AND (YearRange = N'2006') AND (Factory_Wheel_Diameter = N'15')
3 selects in one statement...that can't be right.
Thanks,
View 5 Replies
View Related
Apr 18, 2008
Hello
I am new to this forum and pretty new to running queries in SQL Server. I have been doing it for years on an iSeries platform and the following update statement would definitely work in SQL/400....but it does not in SQL Server 2000. Any help would be appreciated.
UPDATE TESTDTA.F0101 X
SET (ABAC07, ABAC12, ABAC28) =
(SELECT AIAC07, AIAC12, AIAC28 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)
WHERE EXISTS(SELECT AIAC07 FROM TESTDTA.F03012
WHERE AIAN8 = X.ABAN8)
...and here are the errors
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'X'.
Server: Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'WHERE'.
View 2 Replies
View Related
Oct 3, 2007
I am struggling with syntax for an update statement.
Goal: If a RelationID has invoice history then HasHistory=1
UPDATE [Tbl_Relations]
SET [HasHistory]= 1
FROM [Tbl_Relations] r INNER JOIN
(SELECT RelationID
FROM vw_Invoice_history
GROUP BY RelationID
) inv ON r.RelationID= inv.RelationID
Result: Subquery returned more than 1 value
What am I doing wrong?
Thanks in advance,
Frans
View 9 Replies
View Related
Apr 26, 2008
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...
View 1 Replies
View Related
Jul 20, 2005
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO
View 3 Replies
View Related
Nov 7, 2007
Consider the below code: I am trying to find a way so that my select statement (which will actually be used to insert records) can randomly place values in the Source and Type columns that it selects from a list which in this case is records in a table variable. I dont really want to perform the insert inside a loop since the production version will work with millions of records. Anyone have any suggestions of how to change the subqueries that constitute these columns so that they are randomized?
SET NOCOUNT ON
Declare @RandomRecordCount as int, @Counter as int
Select @RandomRecordCount = 1000
Declare @Type table (Name nvarchar(200) NOT NULL)
Declare @Source table (Name nvarchar(200) NOT NULL)
Declare @Users table (Name nvarchar(200) NOT NULL)
Declare @NumericBase table (Number int not null)
Set @Counter = 0
while @Counter < @RandomRecordCount
begin
Insert into @NumericBase(Number)Values(@Counter)
set @Counter = @Counter + 1
end
Insert into @Type(Name)
Select 'Type: Buick' UNION ALL
Select 'Type: Cadillac' UNION ALL
Select 'Type: Chevrolet' UNION ALL
Select 'Type: GMC'
Insert into @Source(Name)
Select 'Source: Japan' UNION ALL
Select 'Source: China' UNION ALL
Select 'Source: Spain' UNION ALL
Select 'Source: India' UNION ALL
Select 'Source: USA'
Insert into @Users(Name)
Select 'keith' UNION ALL
Select 'kevin' UNION ALL
Select 'chris' UNION ALL
Select 'chad' UNION ALL
Select 'brian'
select
1 ProviderId, -- static value
'' Identifier,
'' ClassificationCode,
(select TOP 1 Name from @Source order by newid()) Source,
(select TOP 1 Name from @Type order by newid()) Type
from @NumericBase
SET NOCOUNT OFF
View 14 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
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
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
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
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
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
Aug 27, 2001
Hi
Can anybody tell me how to execute store procedure in the case statement.
Thanks
View 1 Replies
View Related
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
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
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
