How To Write Multiple Queries In Case Statement
Sep 19, 2013I have to do following scenario,
if 1st query Then 2nd Query
Else 'Msg'
How Can i do this using Case Statement??how can do this by Other way??
ADVERTISEMENT
How To Write Select Statement Inside CASE Statement ?
Jul 4, 2006Hello 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
Can I Write Multiple 'Select' Queries In Access? Please Help!
Jul 22, 2004I'm trying to code a query in Access that finds rows w/ duplicate "ContactKeys" then finds duplicate "AddressLine1s" out of the list of duplicate "ContactKeys." (I tried subqueries but it was really slow)
I am trying to create a new table with only duplicate ContactKey rows, and then I wanted to use that table to pick out the duplicate AddressLine1 rows.
Code:
SELECT *
INTO dupContactKeys
FROM Contacts
WHERE ContactKey IN (
SELECT ContactKey
FROM Contacts
GROUP BY ContactKey
HAVING COUNT(*) > 1)
SELECT *
FROM dupContactKeys
WHERE ContactKey IN (
SELECT AddressLine1, Zip
FROM Contacts
GROUP BY AddressLine1, Zip
HAVING COUNT(*) > 1)
ORDER BY ContactKey, TypeKey;
drop table dupContactKeys
This of course doesn't work. Please help, as I am going slightly mad!
How To Write Date Range On Case Statement
Oct 30, 2012I have a case statement as follows which I am using in the where clause to get today's data
(Case when (A.ID is null or B.ID = '' or C.ID= ' ') Then convert(varchar,(Case when A.Time is null then B.Time else A.Time end),103)
else convert(varchar,(Case when A.Time is null then ''" else B.Time end),103) end)
=convert(varchar,getDATE(),103)
However I want to know whether I can use a date range on a case statement
I.e. I want to be able to use BETWEEN statement to get a date range
BETWEEN convert(varchar,getDATE(),103) AND convert(varchar,getDATE()-1,103)
How do I achieve this on a CASE WHEN statement?
Multiple Clauses In A CASE Statement
Jan 12, 2008
Hi
I'm not sure if I have stated my subject line correctly for what I want to achieve, but I will attempt to explain it below.
In addition to what I have in my script below, I also need to include the following clauses:
1. where TransPerPaySequence.FinancialYTDCode like '2007', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72, it needs to be 35; and
2. where TransPerPaySequence.FinancialYTDCode like '2008', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72 or 35, it needs to be 31.
I would really appreciate any assistance that can be provided.
Thanks
SELECT DISTINCT
pc.PositionClassificationCode, pc.Description AS positionclass, pg.PositionGroupCode, pg.Description AS positiongroup, p.Description AS position,
e.PreferredName + ' ' + e.LastName AS employeename, SUM(ha.Quantity)
/ ((CASE p2.PositionGroupCode WHEN 'AC' THEN 72 WHEN 'AL' THEN 75 WHEN 'EX' THEN 80 WHEN 'MG' THEN 80 WHEN 'SM' THEN 80 END) *
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%'))) AS FTE,
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence AS TransPerPaySequence_1
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%')) AS payseq
FROM HistoricalAllowance AS ha LEFT OUTER JOIN
Position AS p ON ha.PositionCode = p.PositionCode LEFT OUTER JOIN
PositionGroup AS pg ON p.PositionGroupCode = pg.PositionGroupCode LEFT OUTER JOIN
PositionClassification AS pc ON p.PositionClassificationCode = pc.PositionClassificationCode LEFT OUTER JOIN
WAP ON ha.WAPCode = WAP.WAPCode LEFT OUTER JOIN
Employee AS e ON ha.EmployeeCode = e.EmployeeCode LEFT OUTER JOIN
Position AS p2 ON e.PositionCode = p2.PositionCode LEFT OUTER JOIN
TransPerPaySequence AS tpps ON ha.PaySequence = tpps.PaySequence
WHERE (e.EmployeeCode IN ('83', '739')) AND (ha.AllowanceCode IN ('005', '201', '203', '101')) AND (tpps.FinancialYTDCode LIKE '2007%')
GROUP BY pc.PositionClassificationCode, pg.PositionGroupCode, pc.Description, pg.Description, p.Description, e.PreferredName, e.LastName,
p2.PositionGroupCode
Update Multiple Columns In One Table With Case Statement
Nov 15, 2013I want to update multiple column in one table using with case statement. i need query pls..
stdidnamesubject result marks
1 arun chemistry pass 55
2 alias maths pass 70
3 babau history pass 55
4 basha hindi NULL NULL
5 hussain hindi NULL nULL
6 chandru chemistry NULLNULL
7 mani hindi NULLNULL
8 rajesh history NULLNULL
9 rama chemistry NULLNULL
10 laxman maths NULLNULL
How To Write Queries In C#
Nov 7, 2006hi,i am working on asp.net development server. i have created a web page on which i have put some option to enter the any user id. as soon as user id is entered on button click i want to search the first name, last name, age in my database where i have inserted the table with entries. then i wan to display it on the page.Please tell me how can i write the C# code on the clicking event so that i get results. i have connected the database to my virtual server.
View 1 Replies View RelatedHow To Write This Queries
Feb 12, 2008Hi.
I want create a queries that is
1. Item onhand > 0
2. For next 14 day this item does not have any Sales Order.
Because our product has shelf life issue, So I want to know which kind product that I have onhand and doesn't have any sales order for the next 2 weeks.
Item Table is T1 and Sales Order Table is T2.
Thanks
Write The Following Queries In SQL (was Very New To RDBMS)
Mar 9, 2005Trying to work on INNER and OUTER JOIN. Can someone help me to write to SQL statements.
Thanks
Sam
Consider the following schema:
suppliers(sid: integer, sname: string, address: string)
parts(pid: integer, pname: string, color: string)
catalog(sid: integer, pid: integer, cost: real)
Write the following queries in SQL:
1. Find the pname of parts for which there is some supplier.
2. Find the snames of suppliers who supply every part.
3. Find the snames of suppliers who supply every red part.
4. Find the pnames of parts supplied by Acme Widget Suppliers and no one else.
5. Find the sids of suppliers who charge more for some part than the average cost of that part.
6. For each part, find the sname of the supplier who charges the most for that part.
7. Find the sids of suppliers who supply only red parts.
8. Find the sids of suppliers who supply a red part and a green part.
9. Find the sids of suppliers who supply a red part or a green part.
10. For every supplier that only supplies green parts print the name of the supplier and the total number of parts that she supplies.
11. For every supplier that supplies a green part and a red part, print the name and price of the most expensive part that she supplies.
How To Write Hierarchy Queries
Feb 5, 2014I have one table which consists of data in the following manner
Child Parent
ZN1 NS
ZN2 NS
A1 ZN1
A3 Zn1
A2 Zn2
A4 Zn2
IT1 A4
It2 A3
It3 A2
It5 A1
Now i want to display the information in the follwing manner.
Zn1 NS
A1 Zn1
It5 A1
A3 Zn1
It2 A3
Zn2 NS
A2 Zn2
It3 A2
A4 Zn2
IT1 A4
How To Write Crosstab Queries
Aug 24, 2006I need to write Crosstab Queries just like this,
Qty + previous qty = TOT
QTY TOT
1 1
5 6
3 9
9 18
2 20
Problem Using Result From CASE In Another CASE Statement
Nov 5, 2007I 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
How To Write Sql Query For This Case?
Aug 31, 2005In one SQL server table table1 it contains following data
ID Date
CA01-001 01/01/2000
CA01-002 02/01/2000
CA01-003 01/15/2000
CA01-004 02/11/2000
CB02-001 01/01/2001
CB02-002 02/01/2001
ID is grouped by first four characters CA01 or CB02, then each record increased by 1 to be 001, 002 .. untill 999 in sequence
How do I write SQL query just to get each group largerest record such as for our case:
CA01-004 02/11/2000
CB02-002 02/01/2001
Thanks in advance!
How Should I Write This Query Wiht Case Statements
Oct 5, 2007Hi
I have a stored procedure and i am trying to add case statements to them.. but i am getting an Error. which is
Msg 125, Level 15, State 3, Procedure udf_EndDate, Line 34
Case expressions may only be nested to level 10.
And This is my sproc-- ================================================
-- Template generated from Template Explorer using:
-- Create Scalar Function (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date, ,>
-- Description:<Description, ,>
-- =============================================
Create FUNCTION [dbo].[udf_EndDate] (@PeriodId int)
RETURNS datetime
AS
BEGIN
DECLARE
@Month int,
@Year char(4)
SELECT
@Month = [Month],
@Year = Cast([Year] as char(4))
FROM
Period
WHERE
PeriodId = @PeriodId
RETURN
CASE @Month WHEN 1 THEN '1/31/' + @Year ELSE
CASE @Month WHEN 2 THEN '2/28/' + @Year ELSE
CASE @Month WHEN 3 THEN '3/31/' + @Year ELSE
CASE @Month WHEN 4 THEN '4/30/' + @Year ELSE
CASE @Month When 5 Then '5/31/' + @Year ELSE
CASE @Month When 6 Then '6/30/' + @Year ELSE
CASE @Month When 7 Then '7/31/' + @Year ELSE
CASE @Month When 8 Then '8/31/' + @Year ELSE
CASE @Month When 9 Then '9/30/' + @Year ELSE
CASE @Month When 10 Then '10/31/' + @Year ELSE
CASE @Month When 11 Then '11/30/' + @Year ELSE
CASE @Month When 12 Then '12/31/' + @Year ELSE null END
END
END
END
END
END
END
END
END
END
END
END
END
Any help will be appreciated.
Regards
Karen
Case Sensitive Queries
Jul 26, 2000Our database is configured as case insensitive. I need to run a query which is case sensitive. Is there a query option or function I can use to compare, taking upper/lower case into consideration?
Regards,
Gavin Patterson
Make SQL Queries Case Sensitive
Sep 13, 2007Hi,
I have an SQL Server 2000 database. There is a table for User Logins. I want the query to be executed as case sensitive. I mean if the database has a username like "USER1" and the user enters his username as "user1" then the login should fail.
So, Is there some way I can make SQL case sensitive? Please help
Regards
Vineed
SQL Server 2012 :: Update Statement With CASE Statement?
Aug 13, 2014i 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
Transact SQL :: Update Statement In Select Case Statement
May 5, 2015I 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'.
Case Statement Error In An Insert Statement
May 26, 2006Hi 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.
How To Show Records Using Sql Case Statement Or If Else Statement
Feb 20, 2008i 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
How Do I Write This SQL Statement?
Sep 11, 2007I have a table named "products", it has a column called "category", I have data such as "CategoryA", "CategoryB" and "CategoryC" etc.... now I want to do a select statement and choose "CategoryA" and "CategoryB" but I want to rename them as "A" and "B". how do I write such a statement... thanks!
How To Write SQL Statement
Feb 8, 2008I wanted to select few columns from the result of calling this store procedure and sort by a column.
{CALL FIQR.Portfolio.Loans_In_Portfolio('2007-09-07', 'FISYND', '', '%')}
How should I write it?(without changing the store procedure)
How To Write Sql Statement
Apr 29, 2005i have those columns: id, no, description.
View 3 Replies View RelatedHelp.. How To Write This Sql Statement?
Dec 14, 2006Hi all,
DECLARE @STA as char(50)
SET @STA = 'TT'
SELECT * FROM ABC_TBL where ABC_Filed in (@STA)
if i have multivalue in parameter @STA, like 'TT', 'DD', 'AA'
so how can i write my statement? i only need to pass one parameter with multilist, so do anybody know how to write the MS sql statement?
Example like
DECLARE @STA as char(50)
SET @STA = 'TT', 'DD'
SELECT * FROM ABC_TBL where ABC_Filed in (@STA)
Thank You.
Regards,
S3ng
Trying To Write A Select Statement.
Jul 26, 2007I want to write a select statement that will show only distinct Student IDs where every record in the table with that student id has a grade above 80?
Lets say for example the table looks like this:
Student_ID
Grade
11276
93
56879
81
11276
76
44327
92
11276
84
56879
97
56879
82 The select statement should only show students, 56879 & 44327 because student 11276 made a 76 in one class.Anyone know how to word this select statement?Thanks for any help!
How To Write A Sql Statement To Get The First Needed Row?
May 9, 2007I have a table with two columns. empnum and empname. there are some data with same empnum but different empname. I just want to get the first empname for the same empnum. Is there a way to write a sql stetment to do this?
Thanks
Is It Possible Write In 1 Query Statement?
Aug 20, 2007Hi guys
I'll appreciate any help with the following problem:
Table 1: TBSALESHEADER
PK: SALESNO
Table 2: TBSALESENTRY
FK: SALESNO
This query is return 2 rows.
SELECT PAYMTDID AS TYPE, CONVERT(CHAR(8),SALESDATE,112) AS DATE, SUM(SALESAMT) AS AMOUNT FROM TBSALESHEADER A, TBSALESENTRY B WHERE A.SALESNO = B.SALESNO AND CONVERT(CHAR(8),SALESDATE,112) = '20070701'
GROUP BY PAYMTDID,CONVERT(CHAR(8),SALESDATE,112)
TYPE DATE AMOUNT
----------------------------------------------------------
0 20070701 3041.29
1 20070701 1442.20
Issue:
Instead of retrieving 2 rows showing 2 type of records, I want to display the 2 type in 2 columns (need to return in 1 row).
Can I get this desired result as following in 1 query statement:
DATE AMOUNT (TYPE 1) AMOUNT (TYPE 2)
---------------------------------------------------------------------------------
20070701 3041.29 1442.20
Please help.
Thanks.
Is It Possible To Write If/Then Else Within SQL Select Statement For Vb.net?
Aug 1, 2007I'm running a select statement from within my VB.net code. However, if the software package name that I chose from a drop down list in my form is not available in the search (in the TableColumn called Software.Package_Name), I would like to be able to write the message "Record Not Found" to the Package_Name "ListBox" column.
Here's the select statement:
"SELECT n.Workstation_Name , s.install_status , s.install_date , s.name , s.version , s.build , s.Package_Name , n.city , n.state FROM software s , network n where ( s.mac=n.mac AND n.Workstation_Name='B001617CA709E' ) AND ( s.name='Adobe Reader' AND s.Install_status='1') ORDER BY n.Workstation_Name ASC"
My ListBox control is named lstQueryResults and uses a Structure that is one to one with each row of the lstQueryResults control.
i.e.
Public Class LIST_CTRL_DATA
Public WksName As String
Public InstallStatus As String
Public InstallDate As String
Public PackageName As String
Public AppName As String
Public Version As String
Public Build As String
Public City As String
Public State As String
Public Percent As UInteger
Finally, here's the While..End routine that plugs the data into the ListBox called lstQueryResults.
While sqlReader.Read()
currec += 1
Dim msg As THREAD_MESSAGE
msg.msgid = 101
Dim message As New LIST_CTRL_DATA
message.WksName = sqlReader("Workstation_Name").ToString
message.InstallStatus = sqlReader("Install_Status").ToString
message.InstallDate = sqlReader("Install_Date").ToString
message.AppName = sqlReader("Name").ToString
message.Version = sqlReader("Version").ToString
message.Build = sqlReader("Build").ToString
message.PackageName = sqlReader("Package_Name").ToString
message.City = sqlReader("City").ToString
message.State = sqlReader("State").ToString
message.Percent = (currec * 100) / maxrecs
msg.msgdata = message
m_ParentWindow.Invoke(m_NotifyMainWindow, msg)
End While
Any ideas on whether or not you can write an If..Then Else statement within the SQL Select Statement and if so how?
I'm still somewhat new to SQL. Any assistance would be great.
Regards,
NBK
How To Write The Select Statement?
Sep 26, 2007 I create a table that is named T1(C1,C2) and insert some data,as following:
C1 C2
1 1
4 NULL
NULL 5
7 NULL
NULL 9
12 12
14 NULL
NULL 16
I want to write a select statement to obtain the following result:
C1 C2
1 1
4 5
7 9
12 12
14 16
how to write the select statement?
How To Write If Else Statement With Variable For A Table Name
Apr 3, 2013I am running SQL Server 2000 and need to know how to write an IF else statement with a variable for a table name. I am constantly getting errors when I attempt this feat.
Code:
Use [TestDatabase]
Go
CREATE PROCEDURE UserInputAsTable
[code]....
How Do You Write Multiple DELETES In Same SP
May 23, 2007I want to use one stored procedure to delete rows in three different tables - how do I write this for Sql Server 2000 please?
PROCEDURE dbo.DeleteSubmission @C_ID intASDELETE *FROM tblCompaniesWHERE C_ID = @C_ID
DELETE *FROM tblStoresWHERE CS_ID = @C_ID
DELETE *FROM tblDistributorsWHERE CD_ID = @C_ID
RETURN
How To Write An Sql Across Multiple Databases
Dec 23, 2005so, I am trying to write an sql that requires information across two
different database. It's under the same sql server. However, the
location of the tables are from different database.
Any links I can read about that can show me how to write the queries?
Thanks in advance, and merry X'mas.
Run Both Queries In The Same Statement (was Help....with A Sql Qry)
Mar 2, 2006Hi i'm new to this just started using SQL 2000 have created two select statements which return data fine but now i need to run them both in the same statement HELP please
statement below
select count (*) Total, displayname0 AS 'Software', (prodid0) As 'Product ID', (Publisher0) AS ' Publisher', (version0) as 'Ver'
from v_gs_add_remove_programs arp
join v_cm_res_coll_OA10006b sap on arp.resourceid=sap.resourceid
where displayname0 = 'SAP front end'
group by displayname0, prodid0, publisher0, version0
select count (*) Total , (displayname0) AS 'Software Name', prodid0 As 'Product ID', Publisher0 AS ' Publisher', version0 as 'Ver '
from v_gs_add_remove_programs arp
join v_cm_res_coll_OA100073 mac on arp.resourceid=mac.resourceid
where displayname0 = 'mcafee virusscan'
group by displayname0, prodid0, publisher0, version0