Nested While Loop

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

Nested Loop Function

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

Nested Loop In SQL Query

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

Nested Loop Join

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

Nested Loop Joins

Jan 26, 2006

View 1 Replies View Related

Nested Loop Join - Need Help :)

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

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 View Related

Whats Wrong With This Nested While Loop?

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

Looping In SQL 2000 (Can It Be A Nested Loop)

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

Variable Inside A Nested Loop

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

Nested Loop Vs. Hash Match?

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

Problem Of Inserting In A Nested WHILE Loop In SProc

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

Generate Result Using Nested Loop And Variables

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

SQL Server 2008 :: Difference Between FOR LOOP And FOREACH LOOP?

May 28, 2010

difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.

View 4 Replies View Related

Loop Though Table Using RowID, Not Cursor (was Loop)

Feb 22, 2006

I have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks

View 6 Replies View Related

Foreach Loop Doesn't Loop

Mar 3, 2006

I have a foreach loop that is supposed to loop through a recordset, however it doesn't loop. It just repeats the same row, row after row.

I would like to look into the recordset variable but I can't because it is a COM object and the ADODB namespace is not available in the script task.

Any solution to this? anyone experienced anything similar

View 1 Replies View Related

Fishing For A Clue. To Loop Or Not To Loop

Jul 8, 2006

I have a table called Tbltimes in an access database that consists of the following fields:

empnum, empname, Tin, Tout, Thrs

what I would like to do is populate a grid view the a select statement that does the following.

display each empname and empnum in a gridview returning only unique values. this part is easy enough. in addition to these values i would also like to count up all the Thrs for each empname and display that sum in the gridview as well. Below is a little better picture of what I��m trying to accomplish.

Tbltimes

|empnum | empname | Tin | Tout | Thrs |

| 1 | john | 2:00PM | 3:00PM |1hr |

| 1 | john | 2:00PM | 3:00PM | 1hr |

| 2 | joe | 1:00PM | 6:00PM | 5hr |

GridView1

| 1 | John | 2hrs |

| 2 | Joe | 5hrs |

im using VWD 2005 for this project and im at a loss as to how to accomplish these results. if someone could just point me in the right direction i could find some material and do the reading.

View 18 Replies View Related

ForEach Loop Or For Loop??

Feb 23, 2006

I have source and destination table names in the database(one table) and I need to read the source and destination tables one by one...

My Lookp table is like the following...

Srn srctable desttable

1 SRC1 DEST1

2 SRC2 DEST2

3 SRC3 DEST3

Now I want one package to load from source to destination.. how do I do it.. I dont know how to use....

How do I run the pacakge for each of the rows... ..............................

View 1 Replies View Related

Nested SQL(Nested SQL(Nested SQL(Nested SQL)))

Jan 3, 2007

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

Any help would be greatly appreciated - George

View 14 Replies View Related

Nested

Jul 20, 2005

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

View 3 Replies View Related

NESTED IF

Oct 15, 2007

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

View 4 Replies View Related

Help With Nested CTE

Apr 2, 2008

Hi gang,

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 anyone show me how to do this?

Thanks in advance for ANY insight/input!

Cheers,

Chris

View 5 Replies View Related

Nested IIF

Feb 14, 2008

Greetings -

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?

Tks & B/R

View 1 Replies View Related

SQL Nested IF-ELSE

Feb 2, 2008

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

RETURN -2

END
ELSE BEGIN
RETURN -1

END
END

Thanks!

View 3 Replies View Related

Sql Server Nested Set, How?

Nov 8, 2006

Hi, I'm not sure if this is a good place to ask sql questions, so please bear with me here...I have a table like soid, parentidWhat I'm trying to do is to write a self join where given a random ID, it'll give me the whole tree of its decendents. (I don't need its parent)so say I have data liek soid parentid1 null2 13 2when I specificy 1, it'll give me 2 and 3, even though 3 is indirectly related to 1. when I say 2, it'll just give me 3. Thanks a lot. GREATLY appreicate it.

View 1 Replies View Related

Nested Views

Oct 16, 2007

When running reports from data, is it faster using nested views approx 4 levels deep, or writing data to a temp tables then running the report?

View 3 Replies View Related

Nested Select

Apr 7, 2008

What's worng, please help? SELECT TTarea,personel,Date FROM person_table WHERE TTarea = (SELECT TTarea FROM TTarea_table WHERE Center='CENTER_office') I have many TTarea and I want to send back from inner SELECT statement but give an error that inner select statement don't return many result.I want to return many result and I display many TTarea in the CENTER_office

View 2 Replies View Related

How Do I Use @@Identity In A Nested SP????

Nov 12, 2003

Hi All,

Im inserting some data into a table and grabbing the new UserID with this statement

SELECT @UserID = @@IDENTITY

I would like to use the @UserID to Execute another SP within the same proc..
..something like this

Exec AnotherSP(@UserId)

But this doesnt seem to be working....Its seems to me that this is a much better approach performance wise rather than returning the UserID to the Business Logic Layer and calling another SP....Im I correct in that assumption....any assistance would be greatly appreciated.

Thanks in advance
AMRITZ

View 2 Replies View Related

Nested Cursor

Jun 12, 2004

I think I am getting an endless loop here... anyone know how to fix it?

***********************

CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int

AS

Declare @RRID int
Declare @ITID int
Declare @FS2 int
Declare @FS1 int

Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID

Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID

while @@fetch_status = 0
select @FS1 = @@Fetch_Status

EXEC TrigSendNewIMAlertP2 @ITID

FETCH NEXT FROM crImpGrp
into
@ITID

close crImpGrp
deallocate crImpGrp

while @@Fetch_Status = 0
select @FS2 = @@Fetch_Status

FETCH NEXT FROM crReqRec
into
@RRID

close crReqRec
deallocate crReqRec
GO

View 1 Replies View Related

Nested SQL Problem

Apr 30, 2005

Hi,
Although I am quite familiar with MS Access-grade SQL, I am struggling a bit with proper grown up SQL Server. My usual approach to counting things in Access is to first create a query with the conditions on the data, then use this as the basis of a second query that does the actual counting of the presorted data. I believe the way to do this in SQL server is to use a nested query. I want to generate the top 10 highest counts for each pesticide detected (detection is level>0) for a client between two dates.
Currently I am using
<code>
SELECT top 10 Count(Pesticide) AS CountOfPesticide, Pesticide FROM (SELECT tblData.Pesticide, tblData.Level, tblData.Clast, tblData.Client FROM tblData WHERE (((tblData.Day>@sdate) AND (tblData.Day<@edate))) and (tbldata.level>0) and (tbldata.clast=@clast) and (tbldata.client=@client)) as monkeyboy GROUP BY Pesticide ORDER BY Count(Pesticide) ASC"
</code>
The results that the above SQL turns out though are not reliable. For example, if I set the dates to now and 14 days ago, it produces higher counts for some pesticides then if I set the dates to now and 30 days ago. Any pointers or general advice about nested sql is gratefully accepted!
thanks
Mike

View 3 Replies View Related

Need Help With An SQL Nested Query

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

Nested Cursors - Me Am Very Bad :(

Sep 26, 2005

Hi all,

I have a temp guy who is doing some work for us (seriously, I never wrote the query).

Now this is the scenario (hold tight). So we have a stored procedure as follows...

ALTER PROCEDURE createSegmentPoints AS

DECLARE @fLat INTEGER
DECLARE @fLon INTEGER
DECLARE @segId INTEGER

-- declare cursor to return the from lat and lon for all segments that do not have address point 109.
DECLARE c1 CURSOR FOR (SELECT From_Latitude, From_Longitude, id AS segment_id FROM Segments WHERE SegmentType != 109)
OPEN c1
FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
WHILE @@FETCH_STATUS = 0
BEGIN
    -- insert into table the segId, from lat, from lon and returned segment id from function.
    INSERT INTO test VALUES (@segId,@fLat,@fLon,dbo.points_test(@fLat,@fLon))

    FETCH NEXT FROM c1 INTO @fLat, @fLon, @segId
END

CLOSE c1
DEALLOCATE c1

As you can see here I am using a Cursor, which in turn calls a function
with each row that is processed in the recordset. The function that is
called is as follows...

ALTER FUNCTION points_test(@x INTEGER, @y INTEGER)
RETURNS INTEGER
AS
BEGIN
    -- function to find the closed segment point with address point 109 to the segment specified in procedure.
    DECLARE @tempDistance FLOAT(4)
    SET @tempDistance = 1000000
    DECLARE @id, @seg, lat, lon INTEGER
    DECLARE @distance, @xd, @yd FLOAT
    DECLARE c1 CURSOR FOR (SELECT from_latitude, from_longitude, id FROM segments WHERE segmenttype = 109)
    OPEN c1
    FETCH NEXT FROM c1 INTO @lat, @lon, @id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        -- calucations to get distance.
        SET @xd = (@lat-@x)
        SET @yd = (@lon-@y)
        SET @distance = SQRT((@xd*@xd) + (@yd*@yd))

        -- test if you have shortest distance.
        IF (@distance < @tempDistance)
        BEGIN
            SET @tempDistance = @distance
            SET @seg = @id
        END
        FETCH NEXT FROM c1 INTO @lat,@lon, @id
    END
    CLOSE c1;
    DEALLOCATE c1;
RETURN @seg
END

(This function works out an equation to get the shortest distance from
two parameters passed to the function calculated with data from each
row returned within the cursor)
As you can see here, this function contains ANOTHER cursor!! Ouch. The
fact that their is an SQL query in a function is a killer, but having
another embedded cursor there is also a killer - this has virtually
killer the application.

So, how best is it for me to correct this. Should I turn the function
into a stored procudure? But even if I do this, the nested cursor still
remains. I was thinking maybe to have the SQRT equations within the
SELECT expression and then wrapped in a MIN() to maybe get the lowest
value.

Any ideas would be of great help.

Thanks

Tryst

View 2 Replies View Related

Nested Roles

Mar 20, 2002

Guys,

Has anybody out there successfully nested roles?

SQL Server seems to let you do it, but then no permissions are carried up the hierarchy.

Regards,
ChrisH

View 2 Replies View Related