Imitating Nested For Each Loop In SQL Query
May 25, 2007
Dear All,
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.
View 3 Replies
ADVERTISEMENT
Nov 1, 2005
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
View 3 Replies
View Related
Dec 10, 2004
Hi there. I want to avoid a cursur using a quite basing nested while loop. The problem is, that the outer index-variable (i) won't increment at all while the inner loop works perfectly.
This one should be quite easy to solve I guess, I'd be very happy if someone could give me a hint what I should try, though because I don't know what to try. The manual didn't help me much either, using CONTINUE and BREAKs didn't solve this problem for me.
My code:
DECLARE @i INTEGER
DECLARE @j INTEGER
SET @i = 1
SET @j = 0
WHILE(@i<= 10) BEGIN
WHILE(@j <= 100) BEGIN
SELECT @i, @j, COUNT(*) as anz FROM mytable WHERE dim1 = @i AND dim2 = @j
SET @j = @j + 1
END
SET @i = @i + 1
END
Thanks a lot for your help :)
Bernhard
corrected typo...
View 4 Replies
View Related
Jul 30, 2007
I want to know how to create a recursive loop/function in SQL, I can’t seem to figure out how to do it.
The database table I am working with is simply the following:
SeedID, ThisParentSeedID
1, 0
2, 1
3, 1
4, 2
5, 4
6, 5
7, 6
8, 7
9, 7
10, 7
11, 10
12, 0
13, 0
14, 0
The example table above shows that SeedID 1 = the parent level of the data. SeedID 2 and 3 are children of SeedID 1, 4 is child of 2, 5 is child of 4... 12 13 and 14 are also parent levels (they are not children of anything).
I want to know how to create a SQL script that is “object oriented� in that I will not have to create as many levels of nested scripts as there are nested “children� in the data.
What I am wanting to figure out is, with a single script, “which sub-children are assigned to [@SeedID]�? So if this script was called, and @SeedID = 1, it would return (2,3,4,5,6,7,8,9,10,11). If @SeedID = 12, it would return null. If @SeedID = 7, it would return (8,9,10,11)
I have tried to keep my question and data as simple as possible for the sake of getting some feedback or help. If you want me to clarify or explain better, please ask me to!
View 2 Replies
View Related
Jan 26, 2006
Hi,
I have came across a situation -
When there are no indices on the tables and if we force SQL server to use the "Nested Loop" joins, the query becomes very slow. Since there are no indices then Nested loop join should not be used.
The background for this problem is -
Analysis services is sending some query to SQL server while doing the cube processing. SQL server is using Nested loop joins even though there are no indices on any of the tables. Is there any way by which we can force the SQL server/Analysis services not to use Nested loop joins since there are no indices in any of the tables.
regards,
datta.
View 1 Replies
View Related
Jan 26, 2006
Hi,
I have came across a situation -
When there are no indices on the tables and if we force SQL server to use the "Nested Loop" joins, the query becomes very slow. Since there are no indices then Nested loop join should not be used.
The background for this problem is -
Analysis services is sending some query to SQL server while doing the cube processing. SQL server is using Nested loop joins even though there are no indices on any of the tables. Is there any way by which we can force the SQL server/Analysis services not to use Nested loop joins since there are no indices in any of the tables.
regards,
datta.
View 1 Replies
View Related
Mar 31, 2006
i'm running the following code on Ms SQL Server 2000, Query Analyzer to analyze the result of Nested Loop Join.
SET STATISTICS PROFILE ON
GO
SELECT pdN.ProductID, pdN.ProductName,
spN.CompanyName, spN.ContactName
FROM dbo.ProductsNew pdN
INNER JOIN dbo.SuppliersNew spN
ON pdN.SupplierId = spN.SupplierId
GO
but the execution plan give me the following result :-
http://i31.photobucket.com/albums/c366/i3lu3fun/executionplan.jpg
instead of using nested, why does it using hash join? is there anything wrong with my code?
View 8 Replies
View Related
Jul 20, 2005
Im getting way too many rows retured..what its trying to do is inserta 0 for revenue for months 7 - 12 (aka July through December) for eachof these cost centers for each payor type..Im getting a lot of repeatsand the concatenation field date always comes back as January 2003instead of the month and date its supposed to--Fiscal Yeardeclare @year smallintset @year = 2004--Month number the Fiscal year starts and endsdeclare @month smallintset @month = 7--Place holder for number of costcentersdeclare @cccounter smallint--loop counter for cost centersdeclare @ccount smallintset @ccount = 1--Place holder for number of payor typesdeclare @ptcounter smallint--loop counter for payor typesdeclare @pcount smallintset @pcount = 1--Temp table to store the blank values for all cost centers/payortypes for the fiscal yeardeclare @Recorded_Revenue_tmp table(Revenue money default 0,[Date] varchar(15),monthn smallint,yearn smallint,[CostCenter] varchar(50),[PayorType] varchar(50))--Temp table to store the values of the cost centersdeclare @costcenter_tmp table(ccid int IDENTITY (1,1),ccname varchar(50))--Inserts cost centers and code into the @costcenter_tmp temp tableinsert into @costcenter_tmp (ccname) select costcenter.fullname + ' '+ costcenter.code from costcenter, agency_cost_centerwhere costcenter.oid = agency_cost_center.cost_center_moniker--Sets the @cccounter variable to the number of cost centersselect @cccounter = count(*) from @costcenter_tmp--Temp table to store the values of the payor typesdeclare @payor_type_tmp table(ptid int identity (1,1),ptname varchar(50))--Inserts payor types into the @payor_type_tmp temp tableInsert into @payor_type_tmp(ptname)select fullname from payor_type,payorwhere payor_type.oid = payor.payor_type_moniker--Sets the @ptcounter variable to the number of payor typesselect @ptcounter = count(*) from @payor_type_tmp--Loop that gets the first part of the fiscal yearWhile (@month <13)begin--Loop that gets the value of the cost center to insertWhile (@ccount <= @cccounter)begin--Loop that inserts values for the first part of the fiscal year intothe @Recorded_Revenue_tmp temp tablewhile (@pcount <= @ptcounter)beginInsert into @Recorded_Revenue_tmp(Revenue, [Date], monthn,yearn,[CostCenter],[PayorType])select 0, datename(month, @month)+ ' ' + cast(@year -1 as varchar(4)),@month, @year -1, ccname, ptnamefrom @costcenter_tmp ct,@payor_type_tmp pt where ct.ccid = @ccount andpt.ptid = @pcountset @pcount = @pcount + 1endset @pcount = 1set @ccount = @ccount + 1endset @ccount = 1set @month = @month + 1endselect * from @Recorded_Revenue_tmpsample return data:(returns 16008 rows!!!)..0000January 200372003Genesis Assertive Community Treatment Team250SAGA..0000January 200372003Genesis Assertive Community Treatment Team250Self Pay..0000January 200372003Genesis Assertive Community Treatment Team250ABH..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Managed Medicaid..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercial..0000January 200372003Genesis Assertive Community Treatment Team250Commercialthanks -Jim
View 2 Replies
View Related
Jul 20, 2005
I have to automate a process that assigns sales leads to sales people.For example:Every day we buy a list of sales leads, it ranges in size from 50 -100 records.We have a team of sales people that also can range from 5 - 8 people.I need to take the new records and divide them evenly among the salespeople.If i get 50 records, and have 5 sales people, then each sales persongets 10 leads.--So, im guessing that I may need to have a nested loop inside this. Ihave tried it several different ways, but cant seem to get it quiteright.DECLARE @TotalRecordCount int, @TotalSalesPeopleCount int,@AmountForEach int, @LooperSalesPeoplerecords int,@LooperNewSalesLeadsRecords int, @SalesPersonID intSELECT @TotalSalesPeopleCount = COUNT(UserId)FROM SalesPeopleWHERE Active = 1--SELECT @TotalRecordCount = COUNT(*)FROM NewSalesLeads--SELECT @AmountForEach = (@TotalRecordCount/@TotalSalesPeopleCount)--SELECT @LooperSalesPeoplerecords = 1SELECT @LooperNewSalesLeadsRecords = 1--WHILE @LooperSalesPeoplerecords <= @TotalSalesPeopleCountBEGINWHILE @LooperNewSalesLeadsRecords <= @TotalRecordCountBEGINSELECT @SalesPersonID = (SELECT UserIDFROM SalesPeopleWHERE UniqueId = @LooperSalesPeoplerecords)SELECT @LooperSalesPeoplerecords =(@LooperSalesPeoplerecords + 1)UPDATE SalesLeadsSET SalesPerson_ID = @SalesPersonIDWHERE UNIQUEID = @LooperSalesPeoplerecordsSELECT @LooperSalesPeoplerecords =(@LooperSalesPeoplerecords + 1)ENDEND----Table structuresCREATE TABLE [dbo].[SalesPeople] ([SalesPerson_ID] [int] NOT NULL ,[FirstName] [varchar](20)NOT NULL) ON [PRIMARY]--INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (26, 'Bill')INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (28, 'Bob')INSERT INTO SalesPeople (SalesPerson_ID,FirstName) VALUES (37,'Chris')------------------------------------------------CREATE TABLE [dbo].[SalesLeads] ([SalesLeadID] [int]NOT NULL ,[SalesPerson_ID] [int]NOT NULL) ON [PRIMARY]--INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1001,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1002,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1003,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1004,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1005,0)INSERT INTO SalesLeads (SalesLeadID,SalesPerson_ID) VALUES (1006,0)------------------------------------------------So in this case, all 3 salespeople should receive 2 salesleads each.I dummied this down quite a bit. It actually ends up being more like15 sales people, and about 400,000 sales leads. But it should work onany level.Thanks for any help you might shed on this.
View 6 Replies
View Related
Jul 20, 2005
I am trying to write a utility/query to get a report from a table. Belowis the some values in the table:table name: dba_daily_resource_usage_v1conn|loginame|dbname|cum_cpu|cum_io|cum_mem|last_b atch------------------------------------------------------------80 |farmds_w|Farm_R|4311 |88 |5305 |11/15/2004 11:3080 |abcdes_w|efgh_R|5000 |88 |4000 |11/15/2004 12:3045 |dcp_webu|DCP |5967 |75 |669 |11/16/2004 11:3095 |dcp_webu|XYZ |5967 |75 |669 |11/17/2004 11:30I need to write a query which for a given date (say 11/15/2004),generate a resource usage report for a given duration (say 3 days).Here is my query:************************************set quoted_identifier offdeclare @var1 intset @var1=0--BEGIN OUTER LOOPwhile @var1<=3 --INPUT runs the report for 3 daysbegindeclare @vstartdate char (10) --INPUT starting dateset @vstartdate='11/15/2004'--builds a range of datedeclare @var2 datetimeset @var2=(select distinct (dateadd(day,@var1,convert(varchar(10),last_batch,101)))--set @var2=(select distinct (dateadd(day,@var1,last_batch))from dba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@vstartdate)set @var1=@var1+1 --increments a daydeclare @var5 varchar (12)--set dateformat mdy--converts the date into 11/15/2004 format from @var2set @var5="'"+(convert(varchar(10),@var2,101))+"'"--print @var5 produces '11/15/2004' as resultdeclare @vloginame varchar (50)declare @vdbname varchar (50)--BEGIN INNER LOOPdeclare cur1 cursor read_only forselect distinct loginame,dbname fromdba_daily_resource_usage_v1where convert(varchar (10),last_batch,101)=@var5--??????PROBLEM AREA ABOVE STATEMENT??????--print @var5 produces '11/15/2004' as result--however cursor is not being built and hence it exits the--inner loop (cursor)open cur1fetch next from cur1 into @vloginame, @vdbnamewhile @@fetch_status=0begin--print @var5 produces '11/15/2004' as resultdeclare @vl varchar (50)set @vl="'"+rtrim(@vloginame)+"'"declare @vd varchar (50)set @vd="'"+@vdbname+"'"--processes the cursorsdeclare @scr varchar (200)set @scr=("select max(cum_cpu) from dba_daily_resource_usage_v1 whereloginame="+@vl+" and dbname="+@vd+" and "+"convert(varchar(10),last_batch,101)="+@var5)--set @var3 =(select max(cum_cpu) from dba_daily_resource_usage_v1where--loginame=@vloginame and dbname=@vdbname--and convert(varchar (10),last_batch,101)=@var5)print @scr--exec @scrfetch next from cur1 into @vloginame, @vdbnameend--END INNER LOOPselect @var2 as "For date"deallocate cur1end--END OUTER LOOP************************************PROBLEM:Even though variable @var5 is being passed as '11/15/2004' inside thecursor fetch (see print @var5 inside the fetch), the value is not beingused to build the cursor. Hence, the cursor has no row set.Basically, the variable @var5 is not being processed/passed correctlyfrom outside the cursor to inside the cursor.Any help please.Thanks*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 3 Replies
View Related
Dec 20, 2007
I have two queries that seem to be the same, but perform very differently. The first query runs very fast (7000+ records returned in <1 sec.). The execution plan shows that it uses a nested loop with index seeks on both tables.
select *
from t_loadbasic
where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between '11/1/07' and '11/05/07')
The second query is almost the same, save the fact that it uses date variables instead of hard dates. The execution plan shows that it uses a hash match instead of a nested loop with an index scan on the main table (t_loadbasic). This query takes about 12 seconds to run.
declare @startdate datetime
,@enddate datetime
set @startdate = '11/1/07'
set @enddate = '11/5/07'
select *
from t_loadbasic
where ld_nbr in (select ld_nbr from t_tripcombined where comp_date between @startdate and @enddate)
I'm trying to figure out why the database executes these two statements so differently. BTW, I've tried switching the order of the tables. I've tried using joins instead of a subquery. The execution plan seems completely dependant on the use of variables. I can attach the execution plans if necessary.
I apologize if this is too simple a question, but I couldn't find an answer on any forums, web searches or BOL. Thanks in adavance.
View 1 Replies
View Related
Jan 17, 2008
Hi friends, I've been stumped on this for almost a week now. Everything works in the stored procedure code below except for the 'INSERT INTO @Uppdates' block of code. I have a SQL Analyzer test driver and when the code gets to the SELECT statement below the INSERT INTO @Updates line the value of the select line is displayed on the screen and nothing gets written to @Updates. I hope I'm being clear about this. Any ideas? IF @Edit=1 AND LEN(@Changes) > 0
BEGIN
--Split and parse changes
DECLARE @curRec int, @nxtRec int, @Record varchar(8000), @TNum int, @TNam varchar(50), @PDesc varchar(512), @PChk varchar(8), @SNum varchar(12), @NScr varchar(10), @OScr varchar(10),
@curField int, @nxtField int, @curSRec int, @nxtSRec int, @subRec varchar(8000), @curSField int, @nxtSField int
DECLARE @NewProj table (
ProjectID int,
SchoolNumber varchar(20),
ArtTeacherNumber int,
TeacherNumber int,
TeacherName varchar(40),
ProjectDescription varchar(512) NULL,
[Checksum] varchar(20) NULL)
DECLARE @Updates table (
ProjectID int,
StudentNumber varchar(12),
NewScore varchar(10) NULL,
OldScore varchar(10) NULL)
SET @curRec = 1
WHILE @curRec IS NOT NULL
BEGIN
SET @nxtRec = NULLIF(CHARINDEX(CHAR(1), @Changes, @curRec), 0)
SET @Record = SUBSTRING(@Changes, @curRec, ISNULL(@nxtRec,8000)-@curRec) --Extract a class record
SET @curField = 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNum = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Number
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNam = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Name
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @PDesc = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Project Description
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)-- Step over existing checksum
SET @PChk = RIGHT('0000000' + dbo.int2base(Checksum(@PDesc),16),8)-- Calculate new checksum based on project description that may have been changed.
SET @curField = @nxtField + 1
INSERT INTO @NewProj (ProjectID, SchoolNumber, ArtTeacherNumber, TeacherNumber, TeacherName, ProjectDescription, [Checksum])
SELECT DISTINCT Students.ProjectID, @SchoolNumber, @ArtTeacherNumber, @TNum, @TNam, @PDesc, @PChk
FROM @Students Students
WHERE Students.SchoolNumber=@SchoolNumber AND Students.TeacherNumber=@TNum
SET @curSRec = 1
WHILE @curSRec IS NOT NULL
BEGIN
SET @nxtSRec = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)
SET @subRec = SUBSTRING(@Record, @curField, ISNULL(@nxtSRec,8000)-@curField) -- Extract a score sub record. Consists of Student Number, new Score, old Score.
SET @curSField = 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @SNum = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract Student Number
SET @curSField = @nxtSField + 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @NScr = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract new Score
SET @curSField = @nxtSField + 1
IF @curSField > LEN(@subRec)
SET @Oscr = NULL-- If no Old Score specified
ELSE
BEGIN
SET @nxtSField = LEN(@subRec) + 1
SET @OScr = SUBSTRING(@subRec, @CurSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract old Score
END
-- Check for errors
IF ISNUMERIC(@SNum) = 0 OR @NScr IS NULL OR LEN(ISNULL(@PChk,0)) <> 8
BEGIN
SET @UpdateErr = 1
BREAK
END
-- Update the updates table and find ProjectID from existing data table
INSERT INTO @Updates (ProjectID, StudentNumber, NewScore, OldScore)
SELECT DISTINCT Students.ProjectID, @SNum, @NScr, @OScr
FROM @Students Students
WHERE Students.StudentNumber=@SNum
SET @curField = @nxtSRec + 1
SET @curSRec = @nxtSRec + 1
select * from @Updates
END
IF @UpdateErr = 1
BEGIN
BREAK
END
SET @curRec = @nxtRec + 1
END
Thanks in advance for looking at this,
View 3 Replies
View Related
Dec 3, 2013
How can I generate the following result using nested loop and variables :
col1col2
15
16
17
25
26
27
35
36
37
45
46
47
View 5 Replies
View Related
Nov 15, 2007
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)
View 1 Replies
View Related
Sep 13, 2005
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.
View 2 Replies
View Related
Oct 4, 1999
Hi,
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
IS IT POSSIBLE and if yes HOW
Thanks
Ashish
View 1 Replies
View Related
Mar 2, 2004
I am having trouble with the following query.
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.
I would appreciate any help available.
Thank you,
-Patrick
View 2 Replies
View Related
Jul 20, 2005
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
View 1 Replies
View Related
Mar 11, 2007
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?
View 5 Replies
View Related
Nov 15, 2001
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
View 1 Replies
View Related
Aug 4, 2001
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.
Database 2, Table 1 contains CustID, ShipID, Address, City, State, Zip,
etc...
Database 2, Table 2 contains CustID, CustomerName.
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.
View 1 Replies
View Related
Jan 26, 2006
Hi All,
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.
View 2 Replies
View Related
Feb 17, 2004
Folks
I have two queries
Select Account_Id , Branch_Cd from Accounts
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.
Thanks
View 2 Replies
View Related
Feb 8, 2007
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 ;)
View 1 Replies
View Related
Nov 7, 2007
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%'
View 7 Replies
View Related
Mar 17, 2008
I guess I need help in understanding how to do the nested query option and see what works here and what doesn't.
Lets go with some psudo code
SELECT Col1, Col2, COUNT(*) AS Expr1 FROM Table1 INNER JOIN Table2 ON Table1.Col2 = Table2.Col1
Why doesn't the following work, is there a work around?
SELECT Col1, Col2, COUNT(*) AS Expr1, (SELECT COUNT(*) FROM Table2) As Expr2 FROM Table1 INNER JOIN Table2 ON Table1.Col2 = Table2.Col1
View 6 Replies
View Related
Aug 14, 2007
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
View 1 Replies
View Related
Jun 6, 2004
Use Pubs
SELECT pub_id, type, SUM(price) as Total_price
FROM titles
GROUP BY pub_id, type
The above query returns the following resultset:
0736business 2.9900
1389business 51.9300
0877mod_cook 22.9800
1389popular_comp42.9500
0736psychology 45.9300
0877psychology 21.5900
0877trad_cook 47.8900
0877UNDECIDED NULL
Now I want to add another "Group By" on type, so I tried:
Select type, sum(Total_Price) from
(SELECT pub_id, type, SUM(price) as Total_Price
FROM titles
GROUP BY pub_id, type)
Group By type
But I got error: Incorrect syntax near the keyword 'Group'. How can I write such a nested group by query.
Thanks in advance for any help.
View 5 Replies
View Related
Jan 12, 2015
I need to know how many widgets are located at each factory.
I have a table called "Widgets". The pertinent column(s) are:
Factory UID
By using only this table I can group the results by the FactoryUID to get the answer. However, this table does not tell me the factory name.
I have a table called "Factories". The pertinent column(s) are:
FactoryUID
FactoryName
I can join these two tables by the FactoryUID. But I don't know how to write this query so that my results will look like the following table:
FactoryName Widgets
Factory1 100
Factory2 200
Factory3 300
View 6 Replies
View Related
May 12, 2008
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?
View 4 Replies
View Related
Jan 31, 2008
I'm having a trouble with a nested scalar query
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();
foreach (PerformanceCriteriaCounter lo in loList)
{
SqlContext.Pipe.Send("Testing item, ObjectType =" + lo.Object_Type);
if (lo.Object_Type == "40")
{
SqlContext.Pipe.Send("Incrementing counter");
Counter++;
}
else
{
SqlContext.Pipe.Send("Calling procedure with ParentID=" + lo.Id + " and Position=" + SourcePosition);
using (SqlCommand myCommand3 = conn.CreateCommand())
{
myCommand3.CommandType = CommandType.StoredProcedure;
myCommand3.CommandText = "usp_PerformanceCriteriaCounter";
myCommand3.Parameters.AddWithValue("@ParentID", lo.Id);
myCommand3.Parameters.AddWithValue("@SourcePosition", SourcePosition);
object objValue = myCommand3.ExecuteScalar();
if (objValue != DBNull.Value)
{
Counter2 = Convert.ToInt32(objValue);
SqlContext.Pipe.Send("It's not null value=" + Counter2.ToString());
Counter = Counter + Counter2;
}
}
}
}
}
return Counter;
}
}
}
This is the output from SQL Server when I call the function.
Code Snippet
Called with ParentID=1 SourcePosition=89
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Adding item
Testing item, ObjectType =30
Calling procedure with ParentID=2 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=3 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=4 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=5 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=6 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=7 and Position=89
It's not null value=0
Testing item, ObjectType =30
Calling procedure with ParentID=8 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=9 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=10 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=11 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=12 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=13 and Position=89
It's not null value=0
Testing item, ObjectType =20
Calling procedure with ParentID=14 and Position=89
It's not null value=0
You can see it adding and checking the values and attempting to call itself, but it never seems to get re-called, any ideas?
Cheers
Jon
View 3 Replies
View Related
Sep 11, 2007
Hi,
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.
Any help very much appreciated.
Thanks...
View 7 Replies
View Related
Oct 8, 2012
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)
View 2 Replies
View Related
