i am new to SQL Server DB.
i have a typical query problem.
master table contains Data in following format
id stype sid svalue
1 status 1 approved
2 status 2 pending
3 request 1 draft
4 request 2 waiting
etc
in my query i need to substitute the svalue in place of sid
e.g.
select requestid ,bstatus, brequest from request ;
should give
---------------------------------
R1122 approved draft
------------------------------
instead of
R1122 1 1
*****************
link field is column name (quite unusual )
like for status column , it is bstatus column name and it is stored in master table as status .
i need to do the following in 1 query only.
kindly suggest some nested query to do the same
kindly help
Smita
I am using SQL Server 2000. I have a somewhat large query and hope someone could help me with it. (Man, there needs to be a way to use colors...) The bolded parts of the query need to be replaced by the underlined part. I can't say 'b.HW' because of the scope of the inline query and that they are all on the same level. I've been told I need to change to a nested subquery, but can't for the life of me figure out how to do that. Can someone please show me? The current query: SELECT x.SSN,x.RealName,x.BudgetCode,x.TH,b.HW,x.HP,z.HL,x.NHW,x.FSLAOT,y.HHW AS AH,y.NHH,CASE WHEN x.TH < x.HP THEN 'XX' WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH WHEN x.TH <= x.HP THEN 0 END AS SOT,CASE WHEN x.TH > x.HP THEN x.NHW - x.HP - y.NHH + x.FSLAOT WHEN x.TH <= x.HP THEN 0 END AS AO FROM(SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS TH,CASE WHEN SUM(a.hours) > 40 THEN (SUM(a.hours) - 40) * 1.5 WHEN SUM(a.hours) <= 40 THEN 0 END AS FSLAOT,CASE WHEN SUM(a.hours) >= 40 THEN 40 WHEN SUM(a.hours) < 40 THEN SUM(a.hours) END AS NHW,32 AS HP FROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.ScheduleType = 1 AND a.EmployeeType = 2GROUP BY a.SSN, a.RealName, a.BudgetCode) x LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HHW,CASE WHEN SUM(a.Hours) >= 8 THEN 8 WHEN SUM(a.Hours) < 8 THEN SUM(a.Hours) END AS NHH FROM dbo.ActivitiesInCurrentFiscalYear a, dbo.Holidays hWHERE a.ItemDate = h.HolidayDate AND a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1GROUP BY a.SSN, a.RealName, a.BudgetCode) y ON x.SSN = y.SSN AND x.RealName = y.RealName AND x.BudgetCode = y.BudgetCode LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HLFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) z ON x.SSN = z.SSN AND x.RealName = z.RealName AND x.BudgetCode = z.BudgetCode LEFT OUTER JOIN (SELECT a.SSN,a.RealName,a.BudgetCode,SUM(a.Hours) AS HWFROM dbo.ActivitiesInCurrentFiscalYear aWHERE a.ItemDate BETWEEN startdate and enddate AND a.EmployeeType = 2 AND a.ScheduleType = 1 AND NOT (a.program_code = '0080' OR a.program_code = '0081')GROUP BY a.SSN, a.RealName, a.BudgetCode) b ON x.SSN = b.SSN AND x.RealName = b.RealName AND x.BudgetCode = b.BudgetCode)
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...
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
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
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')
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
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.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone) FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone) FROM tablename) + (SELECT COUNT (TimeZone) FROM tablename WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
Can you give a whole SQL statement an alias so you can use it later?
Eg.
SELECT * FROM Employees WHERE age < 19 -- Could I call the above statement something like 'statement1' to use below as shown
SELECT * FROM Employees WHERE age < 25 AND NOT IN (statement1)
Soin effect I get a nested statement. The reason I am asking about aliases is because this would need to be repeated for, E.g. age < 30 Then age < 35 and so on and so forth.
So basically, I just want to alias a qhole SQL statement
Hi all,I have a query that looks like so:SELECT GLDCT AS [Doc Type], GLDOC AS DocNumber, GLALID ASPerson_NameFROM F0911WHERE (GLAID = '00181913')However by stipulating that GLAID = GLAID I cannot get the person_nameas not all the GLALID fields are filled in. from my reading of thehelpdesk I have a felling that a nested query might be the way to goor a self-join but beyond this I am lost!?Many thanks for any pointers in advance.Sam
I am trying to do some nested IF ELSE conditions. I get an error saying 'Error near work Begin'. Below is teh query and the variables comes in thru cursor.
Can somebody advise me on this and also let me know the best practices and alternative to this if any.
IF (@CCTable = 'Claiminassoc') BEGIN IF ( @ClaimCenterField = 'ClaimID' AND @VALUE ='Claim') BEGIN UPDATE dbo.Table SET ColName = 'Y' WHERE ID = @ID AND CCTable = 'Claiminassoc' AND CCField = 'ClaimID' AND DWField = 'CatastropheDesignationFlag' END END
ELSE IF (@CCTable = 'EmploymentData') BEGIN IF (@VALUE ='TRUE') BEGIN UPDATE dbo.Table SET ColName = 'Y' WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont' END
ELSE IF (@VALUE ='FALSE') BEGIN UPDATE dbo.Table SET ColName = 'N' WHERE ID = @ID AND CCTable = 'Claim' AND CCField = 'WagePaymentCont' END END
I have a challenge, which seems like it is probably trivial, but SQL chops are not up to the task, and I am hoping one of you hot-shot DBAs can throw me a bone!
I have a query that populates an OLAP Time dimension table (basically one row per day of the year over several years). What I want to do is expand that table to include each hour of each day over the time span.
The CTE I am using for the day population is:
Code Snippet WITH dates(date) -- A recursive CTE that produce all dates between 2006 and 2057-12-31 AS ( SELECT cast('2006' AS dateTime) date -- SQL Server supports the ISO 8601 format so this is an unambigious shortcut for 1999-01-01 UNION ALL -- http://msdn2.microsoft.com/en-us/library/ms190977.aspx SELECT (date + 1) AS date FROM dates WHERE date < cast('2058' AS dateTime) -1 )
What I wanted to do was something like:
Code Snippet WITH hours(hr) AS ( SELECT (DATEPART(hh,date) hr UNION ALL SELECT (hr + 1) AS hr FROM hours WHERE hr < 24 )
inserted just after
Code Snippet FROM dates
in the initial CTE. But from what I have read, it seems as though nested CTEs are not allowed.
Can someone pls advise the maximum number of nested IIF statements allowed in a VS 2005 report builder layout textbox expression? I seem to be hitting a wall at about 10, but cannot find verification. In case the limitation is by characters, the full expression would run about 3,500. Any other limitations which may have a bearing?
Hi all! I have a problem with my stored procedure, What I'm trying to do here is whenever a user tries to register, the stored procedure will check if the username already exists, and if not it'll now check if the email has already been entered into the database then if not the stored procedure will go ahead and insert the values into the database. *If the username already exists it'll return -1, and if the email already exists then return -2.
SELECT Username FROM UserAccount WHERE Username = @UsernameIF @@ROWCOUNT = 0 SELECT Email FROM UserAccount WHERE Email = @Email IF @@ROWCOUNT = 0 BEGIN
INSERT INTO UserAccount (Username, Password, Email, FirstName, LastName, Gender, BirthDate, Country, State, Zip, AdditionalInfo) VALUES (@Username, @Password, @Email, @FirstName, @LastName, @Gender, @BirthDate, @Country, @State, @Zip, @AdditionalInfo) END ELSE BEGIN
Hi and thanks in advance for the help. Here's what I'm trying to do, I need to select all the rows from one table, and only 1 row from a related table. Table setup Table1: Field 1 = PK Ident Table2: Field1 = FK ident I need to select all the rows that exist in Table 1, and I need 1 row out of table2 where Field1 is equal to the Table1.Field1 value (multiple records in table2 will exist with that same value.) I need the top row using a SELECT TOP 1 I was trying to do this with a subquery, but SQL is throwing an error asking me for EXISTS statments.
I have the following as a subquery in a larger stored procedure: SELECT P.ProductId, P.ProductName, P.Category , (SELECT MAX(O.Orderdate) FROM dbo.[Orders] AS O WHERE O.ProductId=P.ProductId) As MostRecentOrder, ROW_NUMBER() OVER (ORDER BY MostRecentOrder DESC) AS RowNumber FROM dbo.[Products] AS P WHERE P.Category=@category @category is an input parameter I am getting an error pointing to the Order By clause stating that "MostRecentOrder" is an invalid column name. If I sort by P.ProductId or P.ProductName, it works fine. Any ideas?
Hi All, This Subquery is kicking my ***. Maybe you can help. I want to query a query.I have the user enter a phrase from a textbox, then I want to group the results by element_label. This is what i have so far, but its not working. SELECT Element_ID, Element_Label, Element_Name, Question_ID, Question_Label, Question_Level, Question_Text, RelatedSRR FROM qryforaspx WHERE ([Question_Text] LIKE '%' + ? + '%') IN SELECT Element_Label FROM Description Group by Element_label Thanks,
Has anyone seen where subqueries collapse into a sum??? I have code like the following, which has been running fine for over a year:UPDATE Reports..DataStats SET Vendors_Cnt = (SELECT COUNT(*) FROM vVendors__AllRecords), Vendors_Audit_Cnt = (SELECT COUNT(*) FROM vVendors_InvAudit), Vendors_Rpts_Cnt = (SELECT COUNT(*) FROM vVendors_Inv12mo), Vendors_InvUnused = (SELECT COUNT(*) FROM vVendors_InvUnused),Vendors_InvOne = (SELECT COUNT(*) FROM vVendors_InvOne), Vendors_InvMulti = (SELECT COUNT(*) FROM vVendors_InvMulti), Vendors_InvUnpaid = (SELECT COUNT(*) FROM vVendors_InvUnpaid), Vendors_InvNewer = (SELECT COUNT(*) FROM vVendors_InvNewer), Vendors_Inv12mo = (SELECT COUNT(*) FROM vVendors_Inv12mo), Vendors_InvPrior = (SELECT COUNT(*) FROM vVendors_InvPrior), Vendors_InvSkipYear = (SELECT COUNT(*) FROM vVendors_InvSkipYear), Vendors_Known = (SELECT COUNT(*) FROM vVendors_Known), Vendors_Orphaned = (SELECT COUNT(*) FROM vVendors_Orphaned), Vendors_Active = (SELECT COUNT(*) FROM vVendors_Active), Vendors_Inactive = (SELECT COUNT(*) FROM vVendors_Inactive), Vendors_Excluded = (SELECT COUNT(*) FROM vVendors_Excluded)WHERE (AuditName = @AuditName)But now it is generating overflows....and is not equivalent to (ignoring the obvious UPDATE vs. return differences for illustration):SELECT COUNT(*) FROM vVendors__AllRecordsSELECT COUNT(*) FROM vVendors_InvAuditSELECT COUNT(*) FROM vVendors_Inv12mo SELECT COUNT(*) FROM vVendors_InvUnusedSELECT COUNT(*) FROM vVendors_InvOneSELECT COUNT(*) FROM vVendors_InvMultiSELECT COUNT(*) FROM vVendors_InvUnpaid SELECT COUNT(*) FROM vVendors_InvNewer SELECT COUNT(*) FROM vVendors_Inv12mo SELECT COUNT(*) FROM vVendors_InvPrior SELECT COUNT(*) FROM vVendors_InvSkipYear SELECT COUNT(*) FROM vVendors_KnownSELECT COUNT(*) FROM vVendors_OrphanedSELECT COUNT(*) FROM vVendors_Active SELECT COUNT(*) FROM vVendors_Inactive SELECT COUNT(*) FROM vVendors_ExcludedThis appears to have started around the beginning of May. Anyone else suffer after patches?
i have a table which i`m having difficulty setting up a subquery on.
cmpcode code grpcode ------------ --------- ------------ CORP 96020 01ADMIN HON 96020 01ADMIN LON 96020 04FOREIGN LON 96020 01DIRECT LON 96020 03ELLIOTT LON 96020 02ACTIVE NEW 96020 02INACTIVE NEW 96020 01ADMIN NEW 96020 03HOLECEK SIN 96020 01ADMIN
what i would like to do is pull in only `codes` with a grpcode in (02active, 01direct). in the example above, i would only want the `lon` cmpcode to appear, since it`s both 01direct and 02active. since the grpcodes are on different lines, i`m not sure how to accomplish this. also, my key is cmpcode, code - not just code. here`s how i`ve been attempting to do it:
select cmpcode, code, grpcode from oas_grplist where elmlevel = 5 and grpcode = `02ACTIVE` and code in(select code from coda..oas_grplist where grpcode = `01direct`).
the problem with this is the subquery join is only based on joining code, and cmpcode needs to be included in the join.
I have a SELECT statement with a subquery. I use an alias as I add the results of the subquery to the dataset. I then try to use the alias in the WHERE clause of the SELECT statement. I get an “Invalid column name “ message with this code:
select i.id as itemid, (select top 1 ca2.itemid from itemassign ca2 inner join account a2 on ca2.accountid=a2.id where a2.customerid=c.id and ca2.itemid=i.id) as iaid from item i inner join customer c on i.customerid=c.id where i.customerid=1 and iaid is null order by i.id DESC
Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'iaid'.
If I run the statement without the and condition in the WHERE clause it returns a valid result. Any input on this will be very appreciated!
Transmitter | Signal_id | Signal_date A0001 trouble 2007-06-09 A0001 fail test 2007-06-10 A0001 test 2007-06-11 A0001 test 2007-06-12 A0002 test 2007-05-23 A0002 fail test 2007-05-30 A0002 fail test 2007-06-06 A0003 test 2007-06-05 A0003 fail test 2007-06-12 A0004 test 2007-01-18 A0004 fail test 2007-02-18 A0004 fail test 2007-03-18 A0004 fail test 2007-04-18 A0004 fail test 2007-05-18
I am trying to get a list of transmitters that have failed to send their scheduled communication test. I only want a list of the transmitters who have failed two communications tests since the last successful test. In the above data, the list would result with A0002 and A0004. A0001 passed it's most recent test, and A0003 has only failed one time since it's last successful test. The following query does not look correct to me, but it does give me the results that *look* correct. If it works, why does it work because I don't understand how the query on abmtransmitter is passing the value last_test_date to the subquery. Or is this just a fluke and my result set looks correct but may not be? '
Code:
Select Transmitter_id, Site_name from abmtransmitter where transmitter_id in (select transmitter from abmsignal where signal_id = 'fail test' and signal_date > last_test_date group by transmitter having count(transmitter) > 1) and last_failed_test_date > last_test_date Order by site_name
I have a subquery where I get 2 rows back which cause issues (since I can't return more then one). I am using this subquery in my function and I can't use a temp table in order to select the correct value. what other options do I have . My subquery returns 2 dates and I need to return from my function the max date.
Im using sql server 2000 and I have sql statement that needs to do a LIKE statement from values from another table. An example would be the below
select Name, PostCode from Customers where Post LIKE (select PartialPostCode + '%' from areas where area_arid = '123')
However if the above sub query returns more than one row then it will error. So I thought I would create a function to return a string such as the below and put it into vvariable
@strPostCodesLike = 'PostCode LIKE 'WS1 %' OR PostCode LIKE 'WS2 %'
And tried to execute the following SQL statement
select Name, PostCode from Customers WHERE @strPostCodesLike
However the above does not work, as I would need to use dynamic sql to get it to work. I cant use dynamic sql unfortunately.
I am new to sql and i was reading about subquery and i think its the right tool for what i want to achieve i have two tables Products table, OrdersLine table Products Table ProdSku ProdName QOH Cost ******** OrdersLine Table OrderNum ProdSku Qty
I want to get the product Sku, Name, QOH, Cost and the Sum(Qty) from OrdersLine this is what i have tried SELECT dbo.Products.ProdSku AS Sku, dbo.OrdersLine.Qty AS Expr1 FROM dbo.Products INNER JOIN dbo.OrdersLine ON dbo.Products.ProdSku = dbo.OrdersLine.ProdSku WHERE (dbo.Products.ProdSku = '122345') GROUP BY dbo.Products.ProdSku please help!
I am cofuse with subquery.HOW does subquery work. the inner or outer query exicute first. what are different type of sub query. plz give example. i am more confuse with exits subquery
I have this subquery that I'm trying to get info from the two seperate tables to show up, but I only get one or the other.
-- This report is for the DC. This report is to look at what items -- come in a kit and what the inventory levels looks like at the -- time the report is run. 07/18/06 mjg SELECT I.ItemID, D.ShortDesc, KI.ItemID, KD.ShortDesc FROM timKitCompList KL INNER JOIN timItem I ON KL.CompItemKey = I.ItemKey INNER JOIN timitemdescription D ON I.ItemKey = D.ItemKey WHERE EXISTS (SELECT KI.ItemID AS KitItemNo, KD.ShortDesc AS KitItemDesc FROM timKit K INNER JOIN timItem KI ON K.KitItemKey = KI.ItemKey INNER JOIN timitemdescription KD ON KI.ItemKey = KD.ItemKey)
Here are the error messages that I get: Server: Msg 107, Level 16, State 2, Line 4 The column prefix 'KI' does not match with a table name or alias name used in the query. Server: Msg 107, Level 16, State 1, Line 4 The column prefix 'KD' does not match with a table name or alias name used in the query.
I need help. I have two tables (customers & orders). I want to build a query that lists all the customers who haven't placed an order in over a year. Below are the simplified tables (i've removed other fields that I don't think are relevant to this query). Do I need to do a subquery or something? Please help!
For any record, I am trying to pull out only the values in 6 specific columns (M1-M6) which correspond to a variable. If the value in any column equals the varaible, it should be included, otherwise it shouldn't
For example:
If x = "B" and ID = 1
I want to pull the record for ID #1 and return the two columns M2 and M3, because Left(ColumnX,1) = x (which has the value of "B") for both those columns. Columns M1, M4, M5, M6 would not be returned
1. Assuming I have below table: Projectid,Dept,Budgeted,Approved,Status A1,Audit,Yes, No, Started B1,HR,No, Yes, Started C1,IT,Yes, Yes, Not Started D1,Audit,Yes, Yes, Dropped
2. Below are the 2 queries created select dept, count(projectid) from table where budgeted = 'yes' group by dept select dept, count(projectid) from table where budgeted = 'yes' and (approved = 'yes' or status = 'started') group by dept
3. How to join the above 2 queries for me to get the following result
Greetings, I am working on a SQL View - I previously posted in this forum and thought I had fixed my problem. Turns out that my view no longer works because I have multiple records where I want only one record.
I am working with 4 tables - here are the tables and my sample data:
USE YOURDATABASE GO CREATE TABLE ZTECSOP60100 ( SOPNUMBE char(21) NOT NULL, PONUMBER char(17) NOT NULL, ORD int NOT NULL, RCPTCOST numeric (19,5) NOT NULL, QTYONPO numeric (19,5) NOT NULL, QTYRECVD numeric (19,5) NOT NULL )
INSERT INTO ZTECSOP60100 values ('ORD001', 'PO2074', '16384', '0.00000', '0.00000', '1.00000') INSERT INTO ZTECSOP60100 values ('ORD1003', 'PO2079', '16384', '425.00000', '0.00000', '5.00000')
USE YOURDATABASE CREATE TABLE ZTECPOP10110 (PONUMBER char(17) NOT NULL, ITEMNMBR char (31) NOT NULL, ITEMDESC char (101) NOT NULL, ORD int NOT NULL) GO
INSERT INTO ZTECPOP10110 values ('PO2074', 'SERVICE1', 'Service item', '16384') INSERT INTO ZTECPOP10110 values ('PO2079', 'SERVICE1', 'Service item', '16384')
USE YOURDATABASE CREATE TABLE ZTECSOP30200 ( SOPNUMBE char(21) NOT NULL, ORIGNUMBE char (21) NOT NULL, SOPTYPE smallint NOT NULL, ) GO
INSERT INTO ZTECSOP30200 values ('ORD001', '', '2') INSERT INTO ZTECSOP30200 values ('INV001', 'ORD001', '3') INSERT INTO ZTECSOP30200 values ('ORD1003', '', '2') INSERT INTO ZTECSOP30200 values ('INV010', 'ORD1003', '3')
USE YOURDATABASE CREATE TABLE ZTEC30310 ( PONUMBER char (17) NOT NULL, UNITCOST numeric (19,5) NOT NULL, TRXSORCE char (13) NOT NULL, UMQTYINB numeric (19,5) NOT NULL, EXTDCOST numeric (19,5) NOT NULL ) GO
USE YOURDATABASE INSERT INTO ZTEC30310 values ('PO2074', '125', 'RECVG00000001', '1', '125') USE RELIA INSERT INTO ZTEC30310 values ('PO2074', '125', 'POIVC00000001', '1', '125') INSERT INTO ZTEC30310 values ('PO2079', '100', 'POIVC00000005', '1', '300') INSERT INTO ZTEC30310 values ('PO2079', '75', 'RECVG00000007', '1', '375') INSERT INTO ZTEC30310 values ('PO2079', '85', 'POIVC00000004', '1', '170')
Here is my VIEW -
CREATE view [dbo].[_tec_SOP_POP_link] as select ZTECSOP60100.SOPNUMBE as SOP_ORDER_NUMBER, ZTECSOP60100.PONUMBER as PONUMBER, ZTECSOP60100.QTYRECVD as QtyReceivedSoFar, ZTECSOP30200.sopnumbe as SOP_INV_NUMBER, ZTECPOP10110.ITEMNMBR as ITEM_NUMBER, ZTECPOP10110.ITEMDESC as itemdescription, CASE WHEN ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.TRXSORCE like 'RECVG%' then ZTEC30310.UNITCOST else '0' end as ReceivedCost, CASE WHEN ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.TRXSORCE like 'POIVC%' then ZTEC30310.UNITCOST else '0' end as VENDORINVOICE_Cost, Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.EXTDCOST/ZTEC30310.UNITCOST else 0 end) as QTYINVOICEDMATCHED, Sum (case when ZTEC30310.ponumber = ZTECsop60100.ponumber AND ZTEC30310.trxsorce like 'POIVC%' then ZTEC30310.extdcost else 0 end) as TOTALDOLLARSINVOICEDMATCHED from ZTECsop60100 left outer join ZTECsop30200 on ZTECsop60100.sopnumbe = ZTECsop30200.ORIGNUMBE AND ZTECsop30200.soptype = 3 left outer join ZTECPOP10110 on ZTECSOP60100.PONUMBER = ZTECPOP10110.PONUMBER left outer join ZTEC30310 on ZTECsop60100.ponumber = ZTEC30310.ponumber where ZTECSOP60100.ORD = ZTECPOP10110.ORD GROUP BY ZTECSOP60100.SOPNUMBE, ZTECSOP60100.QTYRECVD, ZTECSOP30200.sopnumbe, ZTECSOP60100.PONUMBER, ZTECSOP60100.QTYONPO, ZTEC30310.TRXSORCE, ZTECPOP10110.ITEMNMBR, ZTECPOP10110.ITEMDESC, ZTEC30310.UNITCOST, ZTEC30310.umqtyinb, ZTEC30310.ponumber GO
This View returns 5 Records when I want only 2 records.
I need 1 record for each SOP_ORDER_NUMBER AND PONUMBER combination.
The unanticpated dilemna was multiple records in ZTEC30310 for TRXSORCE of POIVC or RECVG - I basically need to sum the records and return one record.
I attempted to fix by adding sum within my case statements but I get the SQL error message 'Cannot perform an aggregate function on an expression containing an aggregate or a subquery'
Any assistance would be appreciated......many thanks in advance...
Hi, I've read through quite a few of the other new sql user questions and saw similar questions, but nothing that quite fits.
I use a transit database. The tables that I need to query for this question are Clients, Booking, Bookinglegs
I need to return results on trip information where the creation date was the same day as the trip date (ldate) However, I also need to show trips taken by the clients whose trips made the above criteria, but also had other trips the same day.
This query produces the results I need for the trips created the same day as the trip day:
From Clients C, Booking B, Bookinglegs PU, Bookinglegs DO
Where c.clientid=b.clientid AND b.bookingid=PU.bookingid AND PU.legnum=0 AND DO.legnum=1 AND b.credate=b.ldate
I tried writing a case expression so that the results of this query would return a value of 'SAME DAY' and then adding an identical union query to it, except with the AND b.credate<b.ldate with a case expression that returned a value of 'Prior Booking'...this worked, however, I get every single booking for that day from the second query. Is there any way to do a subquery or a different statement that would produce results that were only contained in the first statement? I tried outer joins as well, but I'm really at a loss...
SELECT Distinct a.group_code, a.group_name + '-'+ a.group_note As full_name , b.station_nbr, b.beg_eff_date, b.end_eff_date, Round( e.C02_pct,3)As C02_pct, e.sample_date, d.station_name from group_detail a Inner Join station_group b on a.group_code = b.group_code Inner Join station_type c on b.station_nbr = c.station_nbr Inner JOIN station d ON c.station_nbr = d.station_nbr Inner JOIN gas_analysis e ON d.station_nbr = e.station_nbr Where (a.group_type = 'NPN')and( b.end_eff_date >=@sampledateBegin)and ( (c.type_code = 'WHM')or ( c.type_code = 'CRP')) and (e.sample_date Between @sampledateBegin and @sampledateEnd ) and e.seq_nbr = (select max(x.seq_nbr) From gas_analysis x Where x.station_nbr = b.station_nbr and x.sample_date= e.sample_date ) and e.C02_pct = (Select max(x.C02_pct) From gas_analysis x Where x.station_nbr = b.station_nbr and x.sample_date= e.sample_date and x.seq_nbr= e.seq_nbr) order by a.group_code
I am trying to obtain the highest C02 in this case 2.49 but before getting the highest C02 it needs to meet the Select MAX seq_nbr within a given date. In other words I am trying to write a Subquery that first will obtain the MAX seq_nbr within a given date and based on the records pick the higest C02.
I appreciate the help. I hope my scope help understanding what I am trying to achieve.
