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.
Hi,I'm curious about the computational complexity of a query I have. Thequery contains multiple nested self left joins, starting with a simpleselect, then doing a self left join with the results, then doing a selfleft join with those results, etc. What puzzles me is that the timerequired for the query seems to grow exponentially as I add additionalleft joins, which I didn't expect. I expected the inner select toreturn about 25 rows (it does), then I expected the self join to resultin about 25 rows (it does), etc. Each join just adds another column; itdoesn't add more rows. So the left part of the join is staying the samesize, and so is the right part of the join, since I'm always joiningwith the same table.So I would think the time for this query should be (time to join 25rows against the source table) * (num joins), but it seems to besomething like (num rows) ^ (num joins). Any ideas? I'm just trying tounderstand the system a little better. (But if you have any ideas aboutimproving the query, I'm always open to those, too.)The execution plan is what you'd expect: an index seek loop-joined withanother index seek, the results of which are merge-joined with anotherindex seek, the results of which are merge-joined with another indexseek, ad nauseum, until a final "compute scalar cost (39%)" and "select(0%)"For the brave and curious, I've pasted the query below.Thanksselect right(x.cp_yyyymm, 2)+'-'+left(x.cp_yyyymm, 4) as [Month],table0.cp_num_loans/1 as [AFCM9704], table1.cp_num_loans/1 as[AFC9104], table2.cp_num_loans/1 as [BFAT01C], table3.cp_num_loans/1 as[BFAT02B], table4.cp_num_loans/1 as [BFAT03D], table5.cp_num_loans/1 as[BFAT03E], table6.cp_num_loans/1 as [BFAT03F], table7.cp_num_loans/1 as[BFAT04A], table8.cp_num_loans/1 as [BFAT04C], table9.cp_num_loans/1 as[BFAT04D], table10.cp_num_loans/1 as [BFAT99C] from (((((((((((selectdistinct cp_yyyymm from cp_deal_history where cp_deal_id in('AFCM9704', 'AFC9104', 'BFAT01C', 'BFAT02B', 'BFAT03D', 'BFAT03E','BFAT03F', 'BFAT04A', 'BFAT04C', 'BFAT04D', 'BFAT99C') and cp_yyyymmbetween 200304 and 200504) as x left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFCM9704') astable0 on x.cp_yyyymm=table0.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='AFC9104') as table1on x.cp_yyyymm=table1.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT01C') as table2on x.cp_yyyymm=table2.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT02B') as table3on x.cp_yyyymm=table3.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03D') as table4on x.cp_yyyymm=table4.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03E') as table5on x.cp_yyyymm=table5.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT03F') as table6on x.cp_yyyymm=table6.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04A') as table7on x.cp_yyyymm=table7.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04C') as table8on x.cp_yyyymm=table8.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT04D') as table9on x.cp_yyyymm=table9.cp_yyyymm) left join (select cp_yyyymm,cp_num_loans from cp_deal_history where cp_deal_id='BFAT99C') astable10 on x.cp_yyyymm=table10.cp_yyyymm order by x.cp_yyyymm
Hiya, I have a need for a complex SQL statement to provide reporting information, but the SQL is way over my head and although I have some of the elements, I can't seem to pull them together to create a working SQL statement. My database structure is as outlined below:
and then for A2, etc (although missing out A3 as it is a text item, but I can deal with that via ASP code beforehand). I would expect to have to hit the DB for each of the IRVitems (so once for getting the data for A1, againfor A2, etc).
I was provided a great bit of code to use SUBSTRING and GROUP BY to get this data (thanks zuomin), but I didn't consider the possible text values or numbers >9 when defining what I was trying to do (to be fair, they're new requirements):
SELECT SUBSTRING(IRVvalues, 1, 1) AS Value, COUNT(ID) AS Count FROM tblIRV GROUP BY SUBSTRING(IRVvalues, 1, 1) ORDER BY value
which returns like this:
Value Count 1 187 2 163 3 2
Can I do a similar query to get all the info I need in one go? I saw the articale on a user-defined 'split' function (http://code.msdn.microsoft.com/SQLExamples/Wiki/View.aspx?title=StringArrayInput&referringTitle=Home) that can be used to split up the IRVvalues string to access the position I need in the array, but I'm a little clueless on how to then embed that into a SQl statement. Can anyone point me in the right direction please?
The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?
SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# , trans.TRID, trans.Batch_Code, trans.Last_Money, null as Shares, Settle_date as Process_Date, null as Closing_Price, trans.Dwnld_Date, trans.Acnt, null as Mktval, cast(Null as varchar(20)) as Cusip_#, ACT.dbo.account.account_key AS account_key FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15) UNION SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans INNER JOIN ACT_DATA.dbo.account ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5) INNER JOIN tbl_Accounts_TransactionalData ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt
Hi,I'm having problems constructing a nested join. It's quite complex, sohere's a simplfied example of the problem. Any thoughts on what I'mdoig wrong - or if I've got the whole approach wrong are welcome.I've two tables :-one is a contact table contacting name, addresses etc. Three of thefields represent users - 'created by', 'last modified by' and 'owner'.They contain usernames - eg. JDOE, BSMITH etc.The other table contants usernames and new ID codes.What I want to do is create a new dataset by joining the contacts tablewith the user table on all three fields - so the new dataset containsthe ids for the creator, last modifier and owner.I've tried things similar to:select c.*, u1.id, u2,id, u3.idfrom contact cleft outer join users u1left outer join users u2left outer join users u3on (u3.username = c.owner)on (u2.username = c.modified)on (u1.username = c.creator )But it compains that"The column prefix 'c' does not match with a table name or alias nameused in the query."The problem is referencing c (contact) through the whole set of joins.I would like to do this in some similar format as the query is within acursor and post-processing would be very long-winded.Thanks
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
Can anybody help me out in 1) implementing cursors in SSIS. I want to process each row at a time from a dataset. I was trying to use Foreachloop container but in vain. Can you please answer in detail.
my few other questions are: 1) Can i do nested inner join in SSIS. If yes, how? ( I have three table i need to join Tab1 to table 2 and get join the table 3 to get the respective data) 2) I have a resultsets. I want to split the data according to data in a col. Say for instance: Col1 Col2 A 1 A 2 B 3 C 4 C 5 i want to split the data according A, B and C . i.e., if Col1= A then do this, if Col1= B then do this..etc. How can i do this using conditional split task in SSIS
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!
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 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.
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 :-
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.
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
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.
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!
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.
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
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,
We find that a delete command on a table where the rows to be deleted involve an inner join between the table and a view formed with an outer join sometimes works, sometimes gives error 625.
If the delete is recoded to use the join key word instead of the = sign then it alway gives error 4425.
625 21 0 Could not retrieve row from logical page %S_PGID by RID because the entry in the offset table (%d) for that RID (%d) is less than or equal to 0. 1033 4425 16 0 Cannot specify outer join operators in a query containing joined tables. View '%.*ls' contains outer join operators. The delete with a correleted sub query instead of a join works.
Error 4425 text would imply that joins with view formed by outer joins should be avoided.
SQL Server 2000Howdy All.Is it going to be faster to join several tables together and thenselect what I need from the set or is it more efficient to select onlythose columns I need in each of the tables and then join them together?The joins are all Integer primary keys and the tables are all about thesame.I need the fastest most efficient method to extract the data as thisquery is one of the most used in the system.Thanks,Craig
Why is it that SQL joins (*=) run a little faster as opposed to ANSI joins(LEFT JOIN...)? Aren't they supposed to be almost identical?
The issue is this: we are promoting using ANSI syntax for the obvious reason (future versions of SQL Server may not support SQL Server syntax; portability, etc.)
However, the problem is the speed. What have others done about this? Do you use ANSI syntax or SQL syntax? HOw true is it that future SQL Server versions may discontinue support for the '*=" and "=*' join operators.
I have four tables which I want to return results for an advanced search function, the tables contain different data, but can be quite easily joined,
Table A Contains a Main Image, this image is displayed in the results Table B Contains an Icon, this image is displayed in the results Table C doesn't have an image in it but has a child table with a number of images associated to the table, in the UNION ALL statement I would Like to do a Join to get say the top Image from this child and print it for the row associated with table C.
Select title, description, image from tableA UNION ALL Select title, description, icon as image from tableB UNION ALL title, description, ( inner Join SELECT top(1) from imageTableC where imagetableC.FK = tableC.PK) as image from tableC
Could someone show me the syntax to do this, I have all the information printing to the screen, bar this table C image.
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.
Im having a problem with a statement i cannot seem to get 2 left joins working at the same time 1 works fine but when i try the second join i get this error:-
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx'.
My SQL statment is as follows :- SELECT children_tutorial.*,schools.schoolname,regions.rname FROM children_tutorial LEFT JOIN schools ON children_tutorial.school_id=schools.idx LEFT JOIN regions ON children_tutorial.region_id=region.idx
I am using an Access database i have tried all sorts to get it working and its driving me mad!! any help would be really appreciated.
When I add a constraint to the where part of the SQL statement below it will only show the nc_department.department where siteid equals it. How do I pull and display all of those departments regardless of where the siteid = the selected item? In other words doesnt the Left outer Join supposed to grab all contents from the left table regardless?
SQL = "SELECT nc_department.order_id, nc_department.department, Count(nonconformance.department_id) as 'events', ISNULL(SUM(nonconformance.nc_wafer_qty),0) as wafers FROM nc_department LEFT OUTER JOIN nonconformance ON nc_department.department_id = nonconformance.department_id WHERE nc_department.active = '1' AND nonconformance.site_id = '" & siteid.SelectedItem.Value & "' GROUP BY nc_department.department , nc_department.order_id"
Soory if this is a bit basic, but I'm chasing my tail: I have two select statements: SELECT MyRows from Table1 where X = 1 SELECT OtherRows From Table 2 Where y = 3 I want to produce a LEFT JOIN between the result of each query, and return the resultant rows I know this is not correct syntax:
(SELECT MyRows from Table1 where X = 1) LEFT JOIN (SELECT OtherRows From Table 2 Where y = 3) ON Tabel1.Row1 = Table2.Row2
but I think it illustrates what I want to do If I do the WHERE after the join I only get where there is a righthand table I cant get the syntax right, an example with the correct grammar would be very much appreciated
Instead of inserting into, how can I use a join of multiple tables to update a table. This is the join that inserts.
INSERT INTO [table] (MyID, material, dollars) SELECT l.MyID, material, dollars FROM Tab_Client_Input l left outer JOIN Tab_Special_tox r ON l.MyID = r.MyID
