Hi,Please can somone help me with a nested SQL query. I have two tables please see belowTable 1CallIDEmployeeIDCallSummaryCallStatusTable 2CallHistoryIDCallIDDataAddedCallActionI would like to return the CallID, EmployeeID, CallSummary and CallStatus from Table 1, and also display the last CallAction from Table 2.This is a helpdesk database so a Call will have many CallActions i.e. Open, Held, Assigned Internal. How do I return the last CallAction Added against the selected CallID, I know I use the DateAdded but not sure about nested statements.The results I would like to return to the user would look like this:-Call ID: 1EmployeeID: 1Call Sumary: SQL ProblemCall Status: OpenCall Action (Last Action): Assigned Internal.
do i need to nest a query in RS if i want a calculated column to be compared against a multi value variable? It looks like coding WHERE calcd name in (@variable) violates SQL syntax. My select looked like
SELECT ... ,CASE enddate WHEN null then 1 else 0 END calcd name FROM... WHERE ... and calcd name in (@variable)
I want to write one query which will select multiple distinct records from one table For e:g Lets say in a table i have 3 fields name,tel_no,sex Now i want to list all the records which are distinct in each of these fields like distinct name,distinct address
Important Tables: Product (table of products) --ProductID --ProductName
ProductCategories (Associates a Product with one or more categories) --ProductID --CategoryID
Category (table of categories that a product may fall under) --CategoryID --CategoryName
Information:
Basically I have a product that falls into two categories. Therefore there are two records in the ProcuctCategories Table. I am trying to create a query that will find all products that are in categories 1 & 2.
Attempted Solution: SELECT * FROM Product WHERE (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =1)) AND (ProductID IN (SELECT CategoryID FROM ProductCategories WHERE CategoryID =2))
This returned zero records though it should have returned the product that is in categories 1&2.
HiI have 2 tables. The first has employee information and the second haspayroll information. I need to find out people who are not in thepayroll but in the employee table.Since the payroll has multiple instances i have to filter it and findout for each payroll.I don't think i have explained it very well so here is the data set.hope someone can help me with this.Thanks in advancepritTbl EmployeePlanIDSSN100111111111110012222222221001333333333TblPayrolldetailIDNumPlanID SSN11001111111111110012222222222100122222222221001333333333Required RESULT required(Missing employees from payroll)IDNumSSN13333333332111111111
Hello Everyone,I am trying to create a query for the purpose of a nested repeater relation. The information needs to be pulled from one table. I have shortened the columns to the ones that are required.table - PagesIDPageNameParentPageIDSo, take the following example:ID 14, PageName - Service A, ParentPage ID = 6ID 15, PageName - Service B, ParentPage ID = 6ID 36 PageName - Client 1, ParentPage ID = 14ID 37 PageName - Client 2, ParentPage ID = 14ID 38 PageName - Client 3, ParentPage ID = 15ID 39 PageName - Client 4, ParentPage ID = 15 So, I want to create a query that will get my nested repeater to display as follows:Service A Client 1 Client 2Service B Client 3 Client 4What I have come up with so far is:SELECT * from tbl_Pages WHERE ParentPageID IN (Select ID From tbl_Pages)SELECT p.ParentPageID, p.PageName, p.ID FROM tbl_Pages pThe relation would be based off ParentPageID. I keep getting errors that either there is no unique value or the relation is null. What am I am missing here?
Hi,I'm probably missing something obvious (either that or doing this totally wrong).I'm trying to use a nested loop to generate the following results:Unit Day1 Day2 Day3 Day4 Day5Name1 25 45 89 54 76Name2 48 54 81 74 98What I have so far is this:WHILE @FCount < @TotalFoodUnitsBEGINSELECT (SELECT Unit FROM tbl_acc_FoodVenues WHERE UnitID = (@FCount + 1)) AS Unit WHILE @FDCount < @Days BEGIN SELECT (SELECT FdRevenue_a FROM tbl_acc_aud_SportsAudits WHERE AudDate = DATEADD(day, @FDCount, @pdStartDate)) AS Rev SET @FDCount = @FDCount + 1 END SET @FCount = @FCount + 1ENDAny suggestions please
I am trying to write a query that does not use inner joins to see if it returns rows faster than a query using inner joins. My trouble is that I don't know how to write the syntax for the 3rd table that I am comparing against. Can I nest Exists in a Where clause? If so how is the 2nd exists statement added to the query? see my sample query below ================================================== === 'With 2 tables
SELECT DISTINCT s1.ID, s1.SITE FROM SITE_TBL s1 WHERE EXISTS (SELECT * FROM DEVICE_TBL d1 WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D') ORDER BY SITE
'with 3 tables this doesn't work
SELECT DISTINCT s1.ID, s1.SITE FROM SITE_TBL s1 WHERE EXISTS (SELECT * FROM DEVICE_TBL d1 WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D') AND (SELECT * FROM BIG_TBL b1 WHERE d1.fqdn = b1.xyz) ORDER BY SITE
================================================== === Thanks for the help Jim
Hi, I have the following scenario that I am not sure how to best tackle. Any advice or examples is appreciated.
I am creating a stored proc that requires a code to be passed to it. In return data gathered from 3 different tables will be returned. The big catch is that 1 of the tables resided in a differenct database.
So, here is the data layout.
Database 1, Table 1 contains the following fields: Job, CustID, ShipID, and ShipMethod.
So the first question is how should the stored proc look with an input parameter of "Job" and output of Job, CustID, ShipMethod, ShipID, Address, City, State, Zip, and CustomerName?
Secondly, which database should the stored proc reside?
Again, any advise, suggestions, pointers, etc. are appreciated.
Can anybody please tell me if a query such as this (Valid in MS Access) can work in SQL Server:
SELECT Description, Sum(Total) FROM ( SELECT Description, Total FROM Table_A UNION ALL SELECT Description, Total FROM Table_B UNION ALL SELECT Description, Total FROM Table_C ) GROUP BY Description
The group of unions work by themselves, but when I try to nest an outer query to do some a Summation(), I have syntax errors.
Any insight would be greatly appreciated. Thank you.
SELECT SUM (dbo.HOLDING.Shares_Par_Value_Qty * dbo.ASSET.Current_Prc) AS MarketValue FROM dbo.HOLDING INNER JOIN dbo.ASSET ON dbo.HOLDING.Property_Num = dbo.ASSET.Property_Num Group by dbo.HOLDING.Account_ID
Account_ID is the same in both the queries ie in both the tables Holding and Account.
I need the output like this
Select Account_Id, Branch_Cd, MarketValue from -------
But MarketValue should be calculated exactly in the above method. How do I combine these two queries. I need it asap. Help me out.
I have a database that contains a PERSONNEL table, a VISIT table, and a STARSHIP table. I am trying to generate a single column list of the personnel that are from Vulcan (PERSONNEL.PLANET) and all starships that have visited Vulcan (VISIT.PLANET). VISIT.SHIP and STARSHIP.REGISTRY columns contain the ships identifiers. How would I accomplish this? I am just beginning sql so please be nice ;)
I have the following result set #1 from the query below. As you can see, there are four different provider roles and four different physicians listed for the same case (acctnum). Is there a way I could add a nested select (or other method) which would allow me to list this case as one line item to appear in the manner of result set #2?
RESULT SET #1 (the results of the query I have now)
MRN ACCTNUM PTNAME AGE ADMDT DISCHDT LOS PROVIDER_CODE PROVIDER_ROLE PHYSNAME
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 10 ANE1 MILLER DR.
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 20 ADM MAY DR.
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 30 ATT SCHULTZ DR.
12345 11111117777 DOE, JANE 48 Nov 29 2006 Nov 30 2006 1 35 PRIN THOMAS DR.
RESULT SET #2 (this is how I desire the results to look)
MRN ACCTNUM PTNAME AGE ADMDT DISCHDT LOS ANE1PHYS ADMPHYS ATTPHYS PRINPHYS
12345 11111117777 DOE, JANE 48 Dec 13 2006 Dec 14 2006 1 MILLER DR. MAY DR. SCHULTZ DR. THOMAS DR.
Select
e.medrec_no,
e.account_number,
Isnull(ltrim(rtrim(pt.patient_lname)) + ', ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_fname)) + ' ' ,'')
+
Isnull(ltrim(rtrim(pt.patient_mname)) + ' ','')
+
Isnull(ltrim(rtrim(pt.patient_sname)), '')
AS SRM_PatientName,
pm.PatientAge,
left(e.admission_date,11) as Admit_Date,
left(e.episode_date,11) as Disch_Date,
(CASE WHEN DATEDIFF(DAY, e.admission_date,e.episode_date) = 0 Then 1
ELSE DATEDIFF(DAY, e.admission_date,e.episode_date) END) AS LOS,
epi.PROVIDER_CODE,
epi.PROVIDER_ROLE,
pe.PERSON_NAME as physician_name,
From srm.episodes e inner join
dbo.PtMstr pm on pm.accountnumber=e.account_number inner join
srm.ITEM_HEADER ih ON ih.ITEM_KEY = e.EPISODE_KEY INNER JOIN
srm.PATIENTS pt ON pt.PATIENT_KEY = ih.LOGICAL_PARENT_KEY inner join
srm.CDMAB_PROV_EPI epi on epi.episode_key=e.episode_key inner join
srm.providers p on p.provider_key = epi.provider_key inner join
srm.person_element pe on pe.item_key = p.provider_key
Where e.episode_date is not null and pm.AnyProc like '%4495%'
How to configure sqldatadapter with query like "select name ,id from tlb1 where id in (select id from tlb2 where dept=@dept)" Is the nested subquery is not allowed while configuring sqldaadapter? Swati
I need to create a query to list all the subfolders within a folder.
I have a database table that lists the usual properties of each of the folder.
I have another database table that has two columns
1. Parent folder 2. Child folder
But this table maintains the parent child relationship only to one level.
For example if i have a folder X that has a subfolder Y and Z. And Y has subfolders A and B. and B has subfolder C and D and C has subfolder E and F
The database table will look like
parentfolder child folder X Y X Z Y A Y B B C B D C E C F
I want to write a query which will take a folder name as the input and will provide me a list of all the folders and subfolders under it. The query should be based on the table (parent - child) and there should not be any restriction on the subfolder levels to search and report for.
I have been banging my head to do this but i have failed so far. Any help on this will be highly appreciated.
I'd appreciate some help with the issue below - my SQL is a bit rusty and was never that hot to be frank. I'm using SQL Server 2000 (although have a test box with 2005 Express also). I've trawled MSDN and a few forums but can't find the solution (maybe I don't know what I'mm looking for!), so any help would be marvellous...
I have a table with a field called 'IRV' containing a string of comma-separated values. I want to be able to query a point in that string and count the number of times a given value appears. So...as an example, I want to count how many times '1' appears at position 7 in the IRV. I can create SQL to do this as follows:
SELECT COUNT(X) AS is1 FROM myIRVtable WHERE (SUBSTRING(IRV, 7, 1) = '1')
So far so good. However, it is also possible that the value at position 7 in this string could be '2' (or '3', or '4', etc) - and rather than re-running the query again and again to get these values, I'd like to do it in one hit.
How can I combine all this together - anyone have any brilliant solutions?
I have a table that refers to itself, and I need to loop through it to get the total number of entries in the structure with an object_type of 40 this is the function I came up with to do this. The problem is that the nested function doesn't seem to get called.
Code Snippet using System; using System.Data; using System.Data.SqlClient; using System.Data.SqlTypes; using System.Collections.Generic; using Microsoft.SqlServer.Server;
public partial class StoredProcedures { private struct PerformanceCriteriaCounter { public PerformanceCriteriaCounter(int id, string objecttype) { Id = id; Object_Type = objecttype; } public int Id; public string Object_Type; }
[Microsoft.SqlServer.Server.SqlProcedure(Name = "usp_PerformanceCriteriaCounter")] public static int CountPerformanceCriteria(int ParentID, int SourcePosition) { using (SqlConnection conn = new SqlConnection("context connection=true")) { int Counter = 0; int Counter2 = 0; SqlContext.Pipe.Send("Called with ParentID=" + ParentID + " SourcePosition=" + SourcePosition); conn.Open(); using (SqlCommand myCommand = conn.CreateCommand()) { myCommand.CommandText = @"SELECT CompetenceLearningObject.Object_Type, CompetenceLearningObject.LearningObject_ID FROM CompetenceLearningObjectParent INNER JOIN CompetenceLearningObject ON CompetenceLearningObjectParent.LearningObject_ID = CompetenceLearningObject.LearningObject_ID WHERE (CompetenceLearningObjectParent.Approved = 1) AND (CompetenceLearningObject.Archived = 0 OR CompetenceLearningObject.Archived IS NULL) AND (CompetenceLearningObject.Approved_Status = 1) AND (CompetenceLearningObjectParent.Parent_ID = @ParentID) AND (CompetenceLearningObjectParent.Position_ID = @SourcePosition)"; myCommand.Parameters.AddWithValue("@ParentID", ParentID); myCommand.Parameters.AddWithValue("@SourcePosition", SourcePosition); List<PerformanceCriteriaCounter> loList = new List<PerformanceCriteriaCounter>(); SqlDataReader reader = myCommand.ExecuteReader(); while (reader.Read()) { SqlContext.Pipe.Send("Adding item"); loList.Add(new PerformanceCriteriaCounter(int.Parse(reader["LearningObject_ID"].ToString()), reader["Object_Type"].ToString())); } reader.Close();
I am trying to limit a result set by ROW_NUMBER. However, I am having problems getting it working.
The following query works fine, and I get a result set with PollID, AddedDate and RowNum columns.
SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM
( SELECT DISTINCT p.PollID, p.AddedDate
FROM vw_vs_PollsWithVoteCount p
JOIN vs_PollOptions o ON p.PollID = o.PollID
) AS Results
However, as soon as I add a WHERE condition:
SELECT *, ROW_NUMBER() OVER (ORDER BY Results.AddedDate DESC) AS RowNum FROM
( SELECT DISTINCT p.PollID, p.AddedDate
FROM vw_vs_PollsWithVoteCount p
JOIN vs_PollOptions o ON p.PollID = o.PollID
) AS Results
WHERE RowNum BETWEEN 1 AND 10
The query fails with an ' Invalid column name 'RowNum' ' error.
I have tried using 'Results.RowNum' but I get the same problem.
I don't understand what the issue is. The result set has a column headed 'RowNum' so why can't I apply a WHERE clause to this column? I can apply WHERE to the PollID column, for example, with no problem.
I have added some SQL to an Access form which updates the dbo_BM_Map table when the user hits the Apply button. There is a temp table with various fields, two being "Chapter_No" and "Initial_Mapping_Complete" which the update is based on.
I want this update to only apply to chapters that only have one name in the "Initial_Mapping_Complete" column. If a chapter has more than one then the update should ignore it. The attached screengrab shows you. The update should ignore chapter 19 as there are two people (Jim and James) in the Initial_Mapping_Complete field. Here is my code.
pdate dbo_BM_Map inner Join Temp_Progression_Populate on dbo_BM_Map.Product_ID = Temp_Progression_Populate.Product_ID Set dbo_BM_Map.Initial_Mapping_Complete = Temp_Progression_Populate.Initial_Mapping_Complete Where dbo_BM_Map.Chapter_No = Temp_Progression_Populate.Chapter_No And Temp_Progression_Populate.Initial_Mapping_Complete in (Select count(Initial_Mapping_Complete), Chapter_No from Temp_Progression_Populate Group by Chapter_No Having Count(Initial_Mapping_Complete) = 1)
My multiple level nested corelated query is not fetching correctresult. It work fine on small set of data, but fails on larger set ofdata. Any clue?Explaining data storing and discussing design would be tough for mehere, still to show you how complex I have created my life, here is thequery:select(SELECT Top 1 RowNSBranchID FROM AssoExtBranchToNSBranchMstM AM-- MMMWHERE AM.RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2 ANDExtSysCode = UM.SystemCode)))AND RowExtBranchID IN(SELECT RowID FROM ExternalBranchMstMWHERE ExtBranchID =(SELECT ExtBranchID FROM ExternalBranchMstMWHERE ROWID =(SELECT RowID FROM ExternalBranchMstMWHERE ROWID =(SELECT MAX(ROWID) FROM ExternalBranchMstM WHERE MCStatus = 2 ANDExtBranchCode = UM.UpBranchCodeAND RowExtSysID IN(SELECT RowID FROM ExternalSystemMstM WHERE ExtSysID =(SELECT ExtSysID FROM ExternalSystemMstM WHERE SF = 'Active' ANDRowID =(SELECT MAX(RowID) FROM ExternalSystemMstM WHERE MCStatus = 2AND ExtSysCode = UM.SystemCode))))AND (SF = 'Active'))))AND AM.SF = 'Active'order by AssoID desc,TrackID desc) nsbranchid, UM.*fromTmpInProcessData062005MstM UM
hey there, i'm trying to move one record from one table to the next,so i'm doing this by doing an insert into table, then delete from theprevious table. Only thin g is on the insert i want to include aparameter. Can't work how to do this. Do you think i need to doanother update query and insert the parameter that way, after theinsert is done?ALTER PROCEDURE dbo.ReturnLoan(@strBarcode varchar(100),@strLibrary varchar(100),@dateReturned datetime)ASINSERT INTO Loan_History(Barcode,UserID,Date_Borrowed,Library)Select Barcode, UserID, Date_Borrowed, Library FROM Loans WHEREBarcode = @strBarcodeAND Library = @strLibrary;DELETE FROM LoansWHERE Barcode = @strBarcodeAND Library = @strLibrary ;RETURN
I have a question about what is possible with a prediction query against a nested table. Say I have a basic customer-product case and nested table mining model like so:
Mining Model DT_CustProd ( [Id] , [Gender] , [Age] [Products] Predict ( [ProductName] , [Quantity] ) ) Using Microsoft_Decision_Trees
I can write a query to find the probability of product (and quantity) A like so:
SELECT (select * from Predict(Products,INCLUDE_STATISTICS) where ProductName = 'A' )
FROM DT_CustProd
NATURAL PREDICTION JOIN
(SELECT 'M' AS [Gender], 27 AS [AGE] ) AS t
What if I know that the query customer (M,27) in question has purchased product B, how can I use that in the prediction join to predict product A? The fact that product B was purchased might influence the prediction, right?
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)
( select min(cast(f.valuestr as float)) from bvfindings f where f.ObjectName = 'Hematocrit' and f.sessionid = s.sessionid and f.ValueTime > s.open_time)
the above returns me the 10.9
i modified the query
select min(cast(f.valuestr as float)), f.valuetime from bvfindings f where f.ObjectName = 'Hb'
I have the following query and where I need to use the t_PrevSession.DischargeTime which is in the nested query that is bolded below. How do i bring it up to the main select statement?
on executing the below query i am getting the following error
ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.
even though the order by clause is presenet in the nested query
SELECT t.[ProductId], Predict ([Association].[Product Basket],3) From [Association] PREDICTION JOIN SHAPE { OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} APPEND ({OPENQUERY([Adventure Works Cycle MSCRM], 'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')} RELATE [ProductId] To [ProductId] ) AS [Product] AS t ON [Association].[Product Id] = t.[ProductId] AND [Association].[Product Basket].[Product Id] = t.[Product].[ProductId]
I hope I'm posting this in the correct forum. If not I apologize. I have a nested select query that I imported from Oracle:
Oracle Version:
Code Snippetselect avg(days) as days from ( select dm_number, max(dm_closedate) - max(comment_closed_date) as days from dm_data where dm_type = 'prime' and dm_closedate <= '31-dec-2007' and dm_closedate >= '1-dec-2007' and program = 'aads' group by dm_number)
SQL Version:
select round(abs(avg(days)), 0) as days from (select dm.dm_number, abs(datediff(DAY,max(dm.dm_closedate), max(dm.comment_closed_date))) as days from dm_data dm, ProgramXref px where px.Program_Name = 'aads' and dm.Program_Id = px.Program_Id and dm.dm_type = 'prime' and dm.dm_closedate <= '31-dec-2007' and dm.dm_closedate >= '1-dec-2007' group by dm.dm_number)
In Oracle the query runs fine. In SQL I am getting a "Line 10: Incorrect syntax near ')'." error. If I run just the nested portion of the query, there are no errors. It only happens when the first query tries to query the nested query. Can anyone help me get the syntax correct?
OK heres the situation, I have a Categories table and a Products table, each Category can have one or many Products, but a product can only belong to one Category hence one-to-many relationship.
Now I want to do a SELECT query that outputs all of the Categories onto an ASP page, but also displays how many Products are in each category eg.
CatID | Name | Description | No. Products
0001 | Cars | Blah blah blah | 5
etc etc
At the moment I'm doing nesting in my application logic so that for each category that is displayed, another query is run that returns the number of products for that particular category. It works ok!
However, is there a way to write a SQL Statement that returns all the Categories AND number products from just the one SELECT statement, rather than with the method I'm using outlined above? The reason I'm asking is that I want to be able to order by the number of products for each category and my method doesn't allow me to do this.
I'm running Server 'A' and Server 'B' which are both on SQL 2005 SP2. Server B connects to Server A using the linked server functionality via the SQLNCLI provider. I am issuing an update statement from a web api in a nested transaction that uses a distrubted transaction.
However, I am receiving the following error :
Unable to start a nested transaction for OLE DB provider "SQLNCLI" for linked server "A". A nested transaction was required because the XACT_ABORT option was set to OFF. OLE DB provider "SQLNCLI" for linked server "A" returned message "Cannot start more transactions on this session.".
I've researched the issue and understand how XACT_ABORT works.
Also, I looked at the Linked Server provider options for SQLNCLI and came accross the Nested Queries option being unchecked. I checked the option and applied it to the Linked Server.
Okay, so after my long post my questions are: Do i need to restart SQL in order for this to take effect? If not, what do i need to do? I 've restarted IIS to no avail .
