How To Convert Iif To Case
Dec 10, 2004
Hi ,
I am facing problem for converting MS Access 2000 queries to SQL Server views.Specially in case of Iif statement
given below
IIf(Sum((Hour([out]-[in])+Minute([out]-[in])/60)/(Hour([deptend]-[deptstart])+Minute([deptend]-[deptstart])/60))<1,Round(Sum((Hour([out]-[in])+Minute([out]-[in])/60)/(Hour([deptend]-[deptstart])+Minute([deptend]-[deptstart])/60)),2),1) AS [Normal Attn]
what will be the 'CASE' statement of the above query.
I am waiitng for your kind reply
Best Regards
AKH
View 2 Replies
ADVERTISEMENT
Mar 11, 2014
How to convert an INT value from the registry to a VARCHAR but not sure how to convert from INT to VARCHAR within a CASE statement. Below is my code which current fails on "Conversion failed when converting the varchar value 'Automatic' to data type int".
DECLARE Var1 INT
EXECUTE master.dbo.xp_instance_regread
@rootkey = N'HKEY_LOCAL_MACHINE',
@key = N'SYSTEMCurrentControlSetServicesSQLServerAgent',
@value_name = N'Start',
@value = @Var1 OUTPUT
[Code] ....
View 2 Replies
View Related
Jul 23, 2005
I have created a SQL Stored Procedure that uses a Case statement todetermine the Order By. For one of the Case statements I am trying toturn a Char field into Datetime in for the Order By, however I can notget it to work. Can someone please take a look and my code below andtell me what I am doing wrong. Thank you.ORDER BYCASE WHEN @SortBy = 'dttm_stamp' THEN dttm_stamp End,CASE WHEN @SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),Event_Date1,101) as datetime) End,CASE WHEN @SortBy = 'FullName' THEN Emp_lastname + ', ' +Emp_firstname End,CASE WHEN @SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +Emp_firstname End DESC,CASE WHEN @SortBy = 'Emp_SSN' THEN Emp_SSN End
View 11 Replies
View Related
Apr 22, 2014
I have a table exam_setup
CREATE TABLE exam_setup
(
setup_id INT,
sub_id INT,
assignment decimal(4,1),
[Code] ....
I have to fetch the values like
assignment = 25
attendance = 15.5
INT_1 =0
INT_2 =0
if decimal point is 0 then have to return integer value only.. but the below query doesn't work, why?
[Code] ....
View 3 Replies
View Related
Nov 6, 2007
I am trying to write a Procedure in SQL 2005 that retreives a list of rows from the database. I pass to the procedure sorting parameters. This procedure works fine for all of the fields that I enter in the Order By, except for one.
The field that does not work is a varchar(500) field (named Description).
I am not sure what the problem is.
The error that I am getting is:
Msg 235, Level 16, State 0, Line 13
Cannot convert a char value to money. The char value has incorrect syntax.
Attached is the T-SQL Code. The declare and set statements in the beginning are for informational purposes.
Any help is appreciated.
------------------------------------------------------------------------------------------------------
declare @Match varchar(75)
declare @NumRows int
declare @StartPos int
declare @OrderBy varchar(25)
declare @OrderDir varchar(4)
set @Match = 'dog% AND bark%'
set @NumRows = 25
set @StartPos = 100
set @OrderBy = 'Description'
set @OrderDir = 'ASC'
WITH catitems AS
(
SELECT ci.ID, ci.SupplierCode, ci.Description, ci.AUDIO_LINK, ci.SoundLength, ci.HighCost, ci.Channels,
ROW_NUMBER() OVER (ORDER BY
CASE
WHEN @OrderBy='ID' AND @OrderDir='ASC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='ASC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='ASC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='ASC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='ASC'
THEN CAST(ci.Channels AS smallint)
END ASC,
CASE
WHEN @OrderBy='ID' AND @OrderDir='DESC'
THEN CAST(ci.ID AS int)
WHEN @OrderBy='SupplierCode' AND @OrderDir='DESC'
THEN CAST(ci.SupplierCode As int)
WHEN @OrderBy='Description' AND @OrderDir='DESC'
THEN CAST(ci.[Description] As varchar(500))
WHEN @OrderBy='HighCost' AND @OrderDir='DESC'
THEN CAST(ci.HighCost AS money)
WHEN @OrderBy='Channels' AND @OrderDir='DESC'
THEN CAST(ci.Channels AS smallint)
END DESC) As RowNo
FROM TableName As ci
WHERE CONTAINS (ci.Keywords, @Match) AND ShowOnWeb=1
)
SELECT catitems.ID,
catitems.SupplierCode,
catitems.Description,
catitems.AUDIO_LINK,
catitems.HighCost,
catitems.Channels
FROM catitems
WHERE RowNo BETWEEN @StartPos AND @StartPos + @NumRows -1
------------------------------------------------------------------------------------------------------
Thanks,
Aric
View 1 Replies
View Related
Jul 20, 2005
Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max
View 3 Replies
View Related
Jul 13, 2006
Our sql server 2005 database is receiving data from a third part program over which we have no control. We need to be able to automatically convert data entered in one column of one table to UPPER case only.
How can this be done in the table itself?
View 3 Replies
View Related
Aug 31, 2015
How can I change my T-SQL text editor from text sensitive to text insensitive?
View 2 Replies
View Related
Jan 6, 2005
Hello:
I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?
Any suggestions?
View 4 Replies
View Related
May 4, 2007
Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
thanks in advance.
View 3 Replies
View Related
Aug 17, 2005
We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????
View 4 Replies
View Related
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
May 4, 2015
I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?
View 7 Replies
View Related
Aug 19, 2007
I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI. Will there be any problems with this scenario? Thanks in advance!
View 2 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 15, 2006
Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks
View 4 Replies
View Related
Sep 6, 2007
I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.
View 2 Replies
View Related
Jul 20, 2005
Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?
View 2 Replies
View Related
Sep 26, 2007
select convert(varchar(16), getdate(), 101)+LEFT(REPLACE(convert(varchar, getdate(), 108), ':', ''),4)
From above query I get
mmddyyyyhhmm
but it' s yyyy and hour can not be separated
04/12/200702:05
How can I separated the year and hour ?
Thanks
Daniel
View 2 Replies
View Related
Aug 28, 2007
I just can't get the case to work for me in this view.
CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusThe error I get is:
An expression of non-boolean type specified in a context where a condition is expected, near 'THEN'.
ALTER VIEW [dbo].[AffiliationPayments]ASSELECT dbo.Affiliations.AffiliationType, dbo.Affiliations.AffiliationDescription, dbo.Affiliations.AffiliationEnd, dbo.Payments.PaymentDate, dbo.Payments.AmountPaid, dbo.Affiliations.Client_ID,CASE WHEN Isnull(dbo.Payments.AmountPaid,'No') THEN 'No' WHEN dbo.Payments.AmountPaid >0 THEN 'Yes' END AS Payment_StatusFROM dbo.Affiliations LEFT OUTER JOIN dbo.Payments ON dbo.Affiliations.Client_ID = dbo.Payments.Client_ID
View 2 Replies
View Related
May 8, 2006
I have a deadline that is set ahead of time and will not change. I need to insert this deadline for all new users, so I want to set in the DB and I have tried to insert the deadline into the db using a case(below)
Error -2147217900
Error -2147217900
Incorrect syntax near the keyword 'CASE'.
ALTER PROCEDURE prcStartWeeks @UserID VARCHAR(50) ASSET NOCOUNT ONDECLARE @Result INTDECLARE @ID INTDECLARE @Weeks INTDECLARE @DeadLine VARCHAR(8)SET @ID = 0SET @Weeks = 1SELECT @ID = ISNULL((SELECT peID from tblUserInfo where UserID = @UserID),0)IF @ID = 0 BEGIN SELECT @Result = -1 ENDELSE BEGIN WHILE @Weeks < 18 BEGIN CASE @Weeks WHEN '1' THEN @DeadLine = '5/9/2006' WHEN '2' THEN @DeadLine = '9/15/2006' WHEN '3' THEN @DeadLine = '9/22/2006' WHEN '4' THEN @DeadLine = '9/29/2006' WHEN '5' THEN @DeadLine = '10/6/2006' WHEN '6' THEN @DeadLine = '10/13/2006' WHEN '7' THEN @DeadLine = '10/20/2006' WHEN '8' THEN @DeadLine = '10/27/2006' WHEN '9' THEN @DeadLine = '11/3/2006' WHEN '10' THEN @DeadLine = '11/10/2006' WHEN '11' THEN @DeadLine = '11/17/2006' WHEN '12' THEN @DeadLine = '11/21/2006' WHEN '13' THEN @DeadLine = '11/28/2006' WHEN '14' THEN @DeadLine = '12/5/2006' WHEN '15' THEN @DeadLine = '12/12/2006' WHEN '16' THEN @DeadLine = '12/19/2006' WHEN '17' THEN @DeadLine = '12/28/2006' END INSERT INTO tblUserWeekly(PEID,WeekID,Points,DeadLine) VALUES(@ID,@Weeks,0,@DeadLine) SET @Weeks = @Weeks + 1 END SELECT @Result = 0 END
View 3 Replies
View Related
Aug 26, 2002
I want to use case in my date range to get last weeks range. I have a similar proc that uses variables, but my application UI will not allow this in production.
Error incorrect syntax near '=' on line 15
-- begin
select
Company
,Carrier
,Client
,DatePaid
,DateBilled
,cast(PremiumReceived as money)
,cast(PolicyAmount as money)
from view_billing
where
cast(datebilled as datetime) between
(cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-8
when datepart(dw,getdate()) = 3 then getdate()-9
when datepart(dw,getdate()) = 4 then getdate()-10
when datepart(dw,getdate()) = 5 then getdate()-11
when datepart(dw,getdate()) = 6 then getdate()-12
when datepart(dw,getdate()) = 7 then getdate()-13
end)
and
cast(datebilled as datetime) =
case
when datepart(dw,getdate()) = 2 then getdate()-1
when datepart(dw,getdate()) = 3 then getdate()-2
when datepart(dw,getdate()) = 4 then getdate()-3
when datepart(dw,getdate()) = 5 then getdate()-4
when datepart(dw,getdate()) = 6 then getdate()-5
when datepart(dw,getdate()) = 7 then getdate()-6
end
and and company like '%Comp%'
-- end
View 3 Replies
View Related
Apr 27, 2005
is it possible to test two fields in a case statement in SQL ?
View 3 Replies
View Related
Dec 14, 2004
I have a query using Transact-SQL and it is moderately complex. I was wondering if someone could tell me if it would be better to use IF...ELSE statements or CASE, WHEN...THEN statements.
What I have is something like this...
Code:
SELECT
something something
code = CASE
WHEN something IN (A list of things)
IF something else
THEN 4
I have just though of doing IF...ELSE statements to keep it simply, but this query needs to run in a relative quick time frame. I guess my question is, is IF...ELSE faster or is it faster to use a CASE statement?
View 6 Replies
View Related
Sep 30, 2005
Hi all,
I tried the following code and obviously doesnt work, is there a way to simulate a case within having?
CREATE TABLE LTG (Name VARCHAR(10), B1 INT, B2 INT, B3 INT);
INSERT INTO LTG VALUES
('Luis',1,0,0)
INSERT INTO LTG VALUES
('Hector',0,1,0)
INSERT INTO LTG VALUES
('Alejandro',0,0,1)
DECLARE @S INT
SET @S = 1
SELECTName,
SUM (B1),
SUM (B2),
SUM (B3)
FROMLTG
GROUP BY Name
HAVING
CASE @S
WHEN 1 THEN SUM (B1) != 0
WHEN 2 THEN SUM (B1) != 0
WHEN 3 THEN SUM (B1) != 0
END
Thanks for your help
Luis Torres
View 2 Replies
View Related
Nov 1, 2005
How do I get 1 sum out of the following varchar fileld?
Select 'USER_LEVEL' =
CASE
WHEN USER_LEVEL = 'Unlimited' THEN 0
ELSE USER_LEVEL
END
From Table1
View 1 Replies
View Related
Sep 4, 2007
Hello guys! i'm having problem with my stored procedure..can anybody please help me.
I have a stored procedure below that is successfully executed/saved/"Compiled"(whatever you called it) but when I try to use it by supplying value to its paramaters it throws an error (Please see the error message below). I suspected that the error occurs from line with the Bold Letters becuase "@SeacrhArg" variable is of type varchar while columns "Transac.Item_ID" and "Transac.Item_TransTicketNo" is of type Int. What you think guys?
ERROR:
Msg 245, Level 16, State 1, Procedure sp_Transaction_Search, Line 9
Syntax error converting the varchar value 'Manlagnit' to a column of data type int.
STORED PROCEDURE:
USE [RuslinCellPawnShoppeDB]
GO
/****** Object: StoredProcedure [dbo].[sp_Transaction_Search] Script Date: 09/04/2007 08:48:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_Transaction_Search]
@SeacrhArg varchar(20),
@SearchBy varchar(20),
@TransType varchar(20),
@FromDate datetime,
@Todate datetime
AS
BEGIN
SELECT Customer.Customer_LastName,Customer.Customer_Middl eInitial, Customer.Customer_FirstName, Customer.Customer_Address,
Items.Item_Description,Items.Item_Principal, Transac.ItemTrans_Date_Granted, Transac.ItemTrans_DateCreated,
Transac.ItemTrans_Status, Transac.Item_ID,Transac.Item_TransID,Transac.Item_ TransTicketNo
FROM RCPS_TF_ItemTransaction Transac
INNER JOIN RCPS_Customer Customer
ON Transac.CustomerID = Customer.CustomerID
INNER JOIN RCPS_Items Items
ON Items.ItemID = Transac.Item_ID
WHERE
CASE
WHEN @SearchBy = 'FirstName' THEN Customer.Customer_FirstName
WHEN @SearchBy = 'LastName' THEN Customer.Customer_LastName
WHEN @SearchBy = 'Item ID' THEN Transac.Item_ID
WHEN @SearchBy = 'Ticket No' THEN Transac.Item_TransTicketNo
END
LIKE @SeacrhArg AND
Transac.ItemTrans_DateCreated BETWEEN @FromDate AND dateadd(day,1,@Todate) AND
(
(@TransType = 'Pawned' AND Transac.ItemTrans_Status = 1) OR
(@TransType = 'Renewed' AND Transac.ItemTrans_Status = 2) OR
(@TransType = 'Redeemed' AND Transac.ItemTrans_Status = 3) OR
(@TransType = 'Sold' AND Transac.ItemTrans_Status = 5)
)
END
CALL STORED PROCEDURE
USE [RuslinCellPawnShoppeDB]
GO
DECLARE@return_value int
EXEC@return_value = [dbo].[sp_Transaction_Search]
@SeacrhArg = '%man%',
@SearchBy = 'LastName',
@TransType = 'Pawned',
@FromDate = N'9/01/2007 12:00:00 AM',
@Todate = N'9/6/2007 12:00:00 AM'
SELECT'Return Value' = @return_value
GO
View 4 Replies
View Related
Jan 21, 2004
I was just wondering if it is possible to have nested case statements (case within a case). If so, what would the syntax be? Here is what I have now:
case when isdate(my_date)=1 then convert(char(10),convert(datetime,cast(my_date as varchar(8))),101)
else null end "my_date"
I need to put another case outside this one. Any ideas?
Thanks.
View 14 Replies
View Related
Mar 4, 2015
I'm trying to put something like below within a where clause.I have a parameter @Units which can have one of the values on YES, NO or BOTH. The Idea is
if @Units = Yes I only want USER_DEFINED_DATA.CAD213 = 'on'
if @Units = No I only want USER_DEFINED_DATA.CAD214 = 'on'
IF @UNITS = 'Both' .... I want both
[code]....
View 2 Replies
View Related
Apr 8, 2008
I am trying to write the following using either an if/else or case, any help would be aprreciated.
Set the OPPORTUNITY.account_manager_override to false when the COMPANY.account_manager = 'xxx'. In all other cases, set the OPPORTUNITY.account_manager_override to true. Sounds simple enough....
This may help:
select opportunity_name, o.account_manager_id opp_manager, o.account_manager_override opp_override,
co.account_manager_id company_manager, co.account_manager_override company_override
from opportunity o
inner join company co on co.company_id = o.company_id
where co.account_manager_id = xxx
View 4 Replies
View Related
Apr 10, 2008
hi,
SELECT CASE WHEN data_Flag ='5' THEN Avg_Binaryvalue ELSE NULL END AS Avg1,
CASE WHEN data_Flag ='5' THEN Min_Binaryvalue ELSE NULL END Min1,
CASE WHEN data_Flag ='6' THEN Avg_Binaryvalue ELSE NULL END AS Avg2,
CASE WHEN data_Flag ='6' THEN Min_Binaryvalue ELSE NULL END AS Min2
FROM MeasurementData where sensor_serial_no='4' and month(date_time)='3'
this is my qrery.
i have 14 rows for data_flag =5 and 14 rows for data_flag=6
so if i use this query i am getting 26 rows.
my current output:
avg1 min1 avd2 min2
null null 10 20
23 24 null null
null null 54 57
43 12 null null
ets
my output should be like this:
avg1 min1 avd2 min2
23 24 10 20
43 12 54 57
so please give me sample query for this please
View 7 Replies
View Related
May 22, 2008
Hi guys,
Bit of background on this query... daily report that tells me each product we sell that we were unable to supply to our customers.
What I need to include is the earliest unfulfilled purchase order and the date that purchase order is due for delivery into our warehouse.
I've used a case statement with MIN to get the earliest unfulfilled PO (t9.docnum), but I have been unable to get the due date (t9.docduedate) to show correctly... it just gives me the due dates of every unfulfilled purchase order, and doesn't link the purchase order returned by the case statement. Any ideas? Thanks!
SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname ,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) AS 'PO'
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname
ORDER BY t2.u_vlgx_plc,
t6.cardname ,
t3.product
View 2 Replies
View Related
Jun 8, 2008
Hi,
am using case in sql, my query is below..
select eC_ID,
case eC_FormID
when '1' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'ITMGR')
when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')
when '3' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FCTL')
else 'None'
end
from eC_Master
My question is how do I use a case inside a case statement in sql. i.e., when case is '2' .,for below line.,
when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')
I need to check first EMPLOYEE_DIVISION..If its 'FIN',then i need to execute below query,
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FVP')else i need to execute,
when '2' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS')
So i have to use 2 tables..one is eC_Master and another Users. Can i use like below.
select A.eC_ID,
case A.eC_FormID
when '1' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'ITMGR')
when '2' then
case B.EMPLOYEE_DIVISION
WHEN 'FIN' THEN
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FVP') ELSE
(SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'DVP' AND EMPLOYEE_DIVISION = 'EIS') END
when '3' then (SELECT EMPLOYEE_NAME FROM Users where EMPLOYEE_TITLE = 'FCTL')
else 'None'
end
from eC_Master A inner join Users B on A.eC_Requestor_ID=B.EMPLOYEE_ID
THANKS TO HELP.
View 3 Replies
View Related
