Monday, April 15, 2013

[T-SQL] CROSS APPLY - Logical Query Processing

[T-SQL] CROSS APPLY - Logical Query Processing

CROSS APPLY - Logical Query Processing

Posted: 15 Apr 2013 01:54 AM PDT

I have a question about when a CROSS APPLY is processed in a queryUsing the following code as an example:[code="other"]DECLARE @OC TABLE ( Batch VARCHAR(5), DateData DATETIME)INSERT INTO @OCSELECT 'ABCDH', '20130101' UNION ALLSELECT 'DFGHY', '20130102' UNION ALLSELECT 'DFGJU', '20130103' UNION ALLSELECT 'DFGHP', '20130104'DECLARE @PO TABLE ( Batch VARCHAR(5), DateData DATETIME)INSERT INTO @POSELECT 'ABCDH', '20130111' UNION ALLSELECT 'DFGHY', '20130112' UNION ALLSELECT 'DFGJU', '20130113'SELECT O.Batch, CA.LBat, CA1.POLBat, O.DateData, P.DateDataFROM @OC AS OCROSSAPPLY (SELECT LEFT(O.Batch,2) AS LBat) AS CACROSSAPPLY (SELECT LEFT(P.Batch,2) AS POLBat) AS CA1LEFTJOIN @PO AS P ON O.Batch = P.Batch[/code]This returns:[code="other"]Msg 4104, Level 16, State 1, Line 37The multi-part identifier "P.Batch" could not be bound.[/code]However, if you switch the second CROSS APPLY to after the JOIN it appears to work:[code="other"]SELECT O.Batch, CA.LBat, CA1.POLBat, O.DateData, P.DateDataFROM @OC AS OCROSSAPPLY (SELECT LEFT(O.Batch,2) AS LBat) AS CALEFTJOIN @PO AS P ON O.Batch = P.BatchCROSSAPPLY (SELECT LEFT(P.Batch,2) AS POLBat) AS CA1[/code]Would any joins not be processed before the CROSS APPLY?

Arithmetic overflow error - double join?

Posted: 15 Apr 2013 01:46 AM PDT

Guys,I've created a temp table which has a bunch of IDs and dates, something dead simple like this:[quote]SELECTPg.ID,pg.Name,COUNT(pa.Log_ID) as A,FROM tbla pgJOIN #log pa on pa.ID = pg.IDGROUP BY pg.id, pg.name[/quote]Works just fine, if I try to join to the same table again (with a different alias) and do another count it gives me an error:[quote]Msg 8115, Level 16, State 2, Line 1Arithmetic overflow error converting expression to data type int.[/quote][quote]SELECTPg.ID,pg.Name,COUNT(pa.Log_ID) as A,COUNT(pb.Log_ID) as B,FROM tbla pgJOIN #log pa on pa.ID = pg.IDJOIN #log pb on pb.ID = pg.IDGROUP BY pg.id, pg.name[/quote]My initial reason for this double join was thinking I could restrict based on date - so whilst my temp table has the data going back over 1 year I could join to it several times, restricting my data set on each join - e.g. once for the full year, once for 6 months, once for 3 months etc.I've not come across this before, does anyone know what might be causing it? I tried casting to a bigint but it just ran for ages (well over 10mins when the origina query takes seconds).Thanks!

Alternative UPDATE Syntax

Posted: 15 Apr 2013 01:11 AM PDT

Hi all,I have a question about the UPDATE syntax.There are the following - fictive - tables:Table ParamValues:[code="other"]PV_ChangeDate PV_ChangeUser PV_ID PV_Item PV_PK_ID PV_IntValue----------------------- ------------- ----------- -------------------------------------------------- ---------- -----------2013-04-15 16:00:15.477 Peter 1 wall 1 9002013-01-01 00:00:00.000 Peter 2 house 2 2002013-01-02 00:00:00.000 Doris 3 lantern 1 1002013-04-15 16:00:56.130 Peter 4 wall 2 70[/code]Table ParamKeys:[code="other"]PK_ID PK_KeyName PK_Description----------- -------------------------------------------------- --------------------------------------------------1 height The Height2 width The Width[/code]Usually, I use the following syntax - a example:[code="other"]-- changing the IntValue...UPDATE ParamValuesSET PV_ChangeDate = GETDATE(), PV_ChangeUser = 'Peter', PV_IntValue = 1000WHERE PV_ID = 123;[/code]Now, I have to use an INNER JOIN. I've read about an alternative syntax (UPDATE ... SET ... FROM ... WHERE) where an alias for the tables is to be used.For example:[code="other"]-- changing the height of the wall to 1000 :)UPDATE pvSET pv.PV_ChangeDate = GETDATE(), pv.PV_ChangeUser = 'Peter', pv.PV_IntValue = 1000FROM ParamValues pvINNER JOIN ParamKeys pk ON pk.PK_ID = pv.PV_PK_ID AND pk.PK_KeyName = 'height'WHERE pv.PV_Item = 'wall'[/code]But... in my situation I would like to (have to) avoid the alias "pv" in the UPDATE syntax. I tried the following, and it worked for me. But I never read or heard about it. So I don't know if it is an "official allowed" syntax:[code="other"]UPDATE ParamValuesSET PV_ChangeDate = GETDATE(), PV_ChangeUser = 'Peter', PV_IntValue = 1000FROM ParamValuesINNER JOIN ParamKeys ON PK_ID = PV_PK_ID AND PK_KeyName = 'height'WHERE PV_Item = 'Wall'[/code]Is it secure to use this last syntax?Many thanks in advance for you replies!Greg

please help with cursor syntax

Posted: 29 Mar 2013 03:56 AM PDT

I have a simple cursor and I need help fixing syntax.(I know that in this scenario I am using cursor properly).The purpose of the cursor (copied below) is just to output the names of all databases on my server. Later I will use dynamic sql (not copied below) to display the respective metrics (user_scans, user_lookups) for each database from sys.dm_db_index_usage_stats.For the cursor part I'm getting the following error:Msg 16915, Level 16, State 1, Line 4A cursor with the name 'getDatabaseName' already exists.Msg 137, Level 15, State 2, Line 3Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 6Must declare the scalar variable "@DatabaseName".Msg 137, Level 15, State 2, Line 8Must declare the scalar variable "@DatabaseName".my cursor script[code="plain"]use MiguelsAppDECLARE @DatabaseName varchar(max)DECLARE getDatabaseName CURSOR FOR SELECT name, database_id, create_dateFROM sys.databases where name not in ('master', 'tempdb', 'model', 'msdb');GOOPEN getDatabaseNameFETCH NEXTFROM getDatabaseName INTO @DatabaseNameWHILE @@FETCH_STATUS = 0BEGINprint @DatabaseNameFETCH NEXT FROM getDatabaseNameINTO @DatabaseNameENDclose getDatabaseNamedeallocate getDatabaseName[/code]Will someone please tell me where my syntax is wrong so it will run?

insert recursive days data into table.

Posted: 14 Apr 2013 10:16 PM PDT

Hi,Please guide me on following scenario.In one of my application i am giviving provision to user select startdate and enddate data to insert records. This functrionality is working fine.Also user is asking to provide check boxes for slection of day like sun,mon,tue....sat. if user selects date range like 01/01/2013 to 01/31/2013 and check for Tue and Fri only, then i want to insert data for that renage belonging to tuesday and friday only. there is no day column in my database. how can i handle this in SP?thanksAbhas.

Dynamic Date Columns

Posted: 14 Apr 2013 06:35 PM PDT

Is it possible to create dynamic dates with SQL. I want to display all the dates for a particular date range as column headers? How can it be achieved?

How to call a batch file to execute from an SP

Posted: 20 Mar 2013 04:15 AM PDT

Hi All,Need your assistance please, I am not very good with scripting.I have created a draft of SP, and I need syntax to make a call to a batch file(.bat) from within the SP. Once I have that I can incorporate it in the code and begin testing.Can someone please provide sample script.Thanks,SueTons.

How To Split The Data...Using The Stored Procedure...

Posted: 14 Apr 2013 03:06 AM PDT

Hi..I Want to Spliting the Data for The Following Table. Using The Stored Procedure....[code="sql"]Create Table Source (Owner_Name varchar(100),SeatNo Varchar(100))insert into Source values ('A','10,11,12'),('B','20,21,22'),('C','30,31,32')select * from sourceA 10,11,12B 20,21,22C 30,31,32[/code]But My Requirement Structure Sholud like below Mentioned..[code="sql"]Name NoA 10A 11A 12B 20B 21B 22C 30C 31C 32[/code]Thanks & Regards,K.D.Saravanan
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog