Cross Apply problem

How do I get the desired result for the following:

SOURCE TABLE:  USSoilLyr

FIELDS: 	               TAXOR	PARTSIZE	            PCT_R
		Alfisols	ashy		60
		Alfisols	ashy		57
		Alfisols	ashy		38
		Alfisols	clayey		85
		Alfisols	clayey		79
		Andisols	medial		50
		Andisols	medial		36

DESIRED RESULT (TARGET TABLE):  T_Order

FIELDS:		TAXOR	PARTSIZE	                PCT_R
		Alfisols	ashy		AVG(60+57+38)
		Alfisols	clayey		Avg(85+79)
		Andisols	medial		Avg(50+36)


My T-SQL Code, as follows, returns wrong calculations when I manually check 
the results.

UPDATE T_Order 
SET PCT_R = CSL.PCT_R FROM T_Order TC
CROSS APPLY(SELECT AVG(PCT_R)[PCT_R]
FROM USSoilLyr CSL
WHERE TC.TAXOR = CSL.TAXOR AND TC.PARTSIZE = CSL.PARTSIZE
GROUP BY CSL.TAXOR, CSL.PARTSIZE) AS CSL

Thanks for any help.
0
Utf
5/19/2010 6:25:01 PM
sqlserver.programming 1873 articles. 0 followers. Follow

6 Replies
1286 Views

Similar Articles

[PageSpeed] 48

Marilyn (Marilyn@discussions.microsoft.com) writes:
> My T-SQL Code, as follows, returns wrong calculations when I manually
> check the results. 
> 
> UPDATE T_Order 
> SET PCT_R = CSL.PCT_R FROM T_Order TC
> CROSS APPLY(SELECT AVG(PCT_R)[PCT_R]
> FROM USSoilLyr CSL
> WHERE TC.TAXOR = CSL.TAXOR AND TC.PARTSIZE = CSL.PARTSIZE
> GROUP BY CSL.TAXOR, CSL.PARTSIZE) AS CSL
 
I think this is better:


 UPDATE T_Order 
 SET    PCT_R = CSL.PCT_R 
 FROM   T_Order TC
 JOIN   (SELECT AVG(PCT_R)[PCT_R]
         FROM USSoilLyr CSL
         GROUP BY CSL.TAXOR, CSL.PARTSIZE) AS CSL
     ON  TC.TAXOR = CSL.TAXOR AND TC.PARTSIZE = CSL.PARTSIZE

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

0
Erland
5/19/2010 10:13:44 PM
Hi,

Thanks once again for replying :)!

I tried your code but it's still giving me the same erroneous results.  
However, I've come up with the following and this gives the correct figures I 
need:

UPDATE T_Order
SET PCT_R = (SELECT AVG(PCT_R) FROM
(SELECT B.TAXOR, B.PARTSIZE, B.PCT_R FROM USSoilLyr CSL B
  WHERE B.TAXOR = T_Order.TAXOR AND B.PARTSIZE = T_Order.PARTSIZE
  GROUP BY B.TAXOR, B.TAXPARTSIZE, B.PCT_R)X)

Thanks for always coming to the rescue!

"Marilyn" wrote:

> How do I get the desired result for the following:
> 
> SOURCE TABLE:  USSoilLyr
> 
> FIELDS: 	               TAXOR	PARTSIZE	            PCT_R
> 		Alfisols	ashy		60
> 		Alfisols	ashy		57
> 		Alfisols	ashy		38
> 		Alfisols	clayey		85
> 		Alfisols	clayey		79
> 		Andisols	medial		50
> 		Andisols	medial		36
> 
> DESIRED RESULT (TARGET TABLE):  T_Order
> 
> FIELDS:		TAXOR	PARTSIZE	                PCT_R
> 		Alfisols	ashy		AVG(60+57+38)
> 		Alfisols	clayey		Avg(85+79)
> 		Andisols	medial		Avg(50+36)
> 
> 
> My T-SQL Code, as follows, returns wrong calculations when I manually check 
> the results.
> 
> UPDATE T_Order 
> SET PCT_R = CSL.PCT_R FROM T_Order TC
> CROSS APPLY(SELECT AVG(PCT_R)[PCT_R]
> FROM USSoilLyr CSL
> WHERE TC.TAXOR = CSL.TAXOR AND TC.PARTSIZE = CSL.PARTSIZE
> GROUP BY CSL.TAXOR, CSL.PARTSIZE) AS CSL
> 
> Thanks for any help.
0
Utf
5/20/2010 1:34:01 PM
Cleaning up the data element names and formatting, it sounds like you
need a simple VIEW that will always be right and not a base table. The
base table will constantly need updating, use disk space and access
time, etc.

CREATE VIEW T_Orders (taxor, part_size, r_pct_avg)
AS
SELECT taxor, part_size, AVG(r_pct)
  FROM US_Soil_L_yr
 GROUP BY taxor, part_size;

A virtual table is just as much a table as a base table. This is SQL
and NOT a tape file system or punch cards where things must be
materialized.  This is why I stress that fields and columns are
nothing alike.

If the T-Orders are a subset then you can add a WHERE clause in the
VIEW or filter in the invoking queries.
0
CELKO
5/20/2010 6:17:01 PM
Hi,

Thanks for your attention.  I'm "extracting" 5 soil taxonomic tables (order, 
suborder, group, subgroup, class) from one US-wide table that would need to 
be accessed by another software (ESRI ArcGIS).  I'm not sure if ArcGIS can 
"read/access" a SQL Server View table (would need to research this).  

These tables need to be permanent tables once extracted.  They would again 
be transformed into another format to be accessed by our modeling application 
(but that's another task way in the future, I hope).

What's causing the processing to take so long (I'm guessing) is the 
"compound" fields I'm specifying to search the US-wide table--each table's 
main field + soil particle-size (e.g., TAXOR + PARTSIZE).  The pair must go 
together in order to create a distinct row for each table.  

I'm still hoping there's a better way.

"--CELKO--" wrote:

> Cleaning up the data element names and formatting, it sounds like you
> need a simple VIEW that will always be right and not a base table. The
> base table will constantly need updating, use disk space and access
> time, etc.
> 
> CREATE VIEW T_Orders (taxor, part_size, r_pct_avg)
> AS
> SELECT taxor, part_size, AVG(r_pct)
>   FROM US_Soil_L_yr
>  GROUP BY taxor, part_size;
> 
> A virtual table is just as much a table as a base table. This is SQL
> and NOT a tape file system or punch cards where things must be
> materialized.  This is why I stress that fields and columns are
> nothing alike.
> 
> If the T-Orders are a subset then you can add a WHERE clause in the
> VIEW or filter in the invoking queries.
> .
> 
0
Utf
5/20/2010 9:04:01 PM
>> I'm "extracting" 5 soil taxonomic tables (order, suborder, group, subgro=
up, class) from one US-wide table that would need to be accessed by another=
 software (ESRI ArcGIS).  I'm not sure if ArcGIS can "read/access" a SQL Se=
rver View table (would need to research this).  <<

I almost got to play with ArcGIS last year for a Voter Registration
and redistricting project for the State of Texas. The important word
is "almost" -- buy me a beer and I will talk about it.

But I have the impression that you will need to move SQL data (for us,
it was Oracle) into ArcGIS to use it.  I think you can load ArcGIS
from SQL tables and VIEWs without much trouble.  They have a really
nice newsletter, by the way; subscribe to it.

>> What's causing the processing to take so long (I'm guessing) is the
"compound" fields [sic: columns are not fields] I'm specifying to
search the US-wide table -- each table's main field [sic] + soil
particle-size (e.g., (taxor, prt_size).  The pair must go together in
order to create a distinct row for each table.  <<

Compound keys are not a problem for SQL. It is just a screaming pain
to map a linear language like SQL into 2D and 3D "GIS-speak".

In my case, we had voting districts for schools, congress, local
elections, state elections, water, etc.  Now set up polygons that give
a covering of Texas (not always related to county and city lines, ZIP
codes or anything easy).  Concave, convex, and disjoint are possible.
Etc.

Good luck; at least your stuff is physical and not political (Can you
say "Gerrymander"? I knew you could!).

Google up "TEAM for Texas" and read the BASELINE article.

0
CELKO
5/21/2010 1:29:40 AM
Hi,

Well...I'm working for Texas A&M University (owned by the State).  Luckily, 
I'm in one of the University's research centers.  I'm processing lots of raw 
data for use in our land and water modeling.  As such, I've inherited legacy 
data (mostly in text files with very vague schemas).  I'm still trying to 
explain to my boss why a database is not the same as a dictionary--but I've 
given up on that-- and that I don't know how to program in FORTRAN,  and AML.

These tables I'm creating are the intermediate data for our ArcGIS 
application.  Anyway, thanks for your tips.  



"--CELKO--" wrote:

> >> I'm "extracting" 5 soil taxonomic tables (order, suborder, group, subgroup, class) from one US-wide table that would need to be accessed by another software (ESRI ArcGIS).  I'm not sure if ArcGIS can "read/access" a SQL Server View table (would need to research this).  <<
> 
> I almost got to play with ArcGIS last year for a Voter Registration
> and redistricting project for the State of Texas. The important word
> is "almost" -- buy me a beer and I will talk about it.
> 
> But I have the impression that you will need to move SQL data (for us,
> it was Oracle) into ArcGIS to use it.  I think you can load ArcGIS
> from SQL tables and VIEWs without much trouble.  They have a really
> nice newsletter, by the way; subscribe to it.
> 
> >> What's causing the processing to take so long (I'm guessing) is the
> "compound" fields [sic: columns are not fields] I'm specifying to
> search the US-wide table -- each table's main field [sic] + soil
> particle-size (e.g., (taxor, prt_size).  The pair must go together in
> order to create a distinct row for each table.  <<
> 
> Compound keys are not a problem for SQL. It is just a screaming pain
> to map a linear language like SQL into 2D and 3D "GIS-speak".
> 
> In my case, we had voting districts for schools, congress, local
> elections, state elections, water, etc.  Now set up polygons that give
> a covering of Texas (not always related to county and city lines, ZIP
> codes or anything easy).  Concave, convex, and disjoint are possible.
> Etc.
> 
> Good luck; at least your stuff is physical and not political (Can you
> say "Gerrymander"? I knew you could!).
> 
> Google up "TEAM for Texas" and read the BASELINE article.
> 
> .
> 
0
Utf
5/21/2010 1:00:06 PM
Reply:

Similar Artilces:

Problem with SelectClipRegion
Hi all... I have this instructions in DrawItem virtual function of an owner drawn control. rgn.CreateRectRgn(rcLeft.left, rcLeft.top, rcLeft.right, rcLeft.bottom); dc.SelectClipRgn(&rgn); dc.SetTextColor(RGB(255, 255, 255)); dc.DrawText(str, rcText, DT_CENTER | DT_VCENTER | DT_SINGLELINE); rgn.DeleteObject(); rgn.CreateRectRgn(rcRight.left, rcRight.top, rcRight.right, rcRight.bottom); dc.SelectClipRgn(&rgn); dc.SetTextColor(RGB(0,0,0)); dc.DrawText(str, rcText, DT_CENTER | DT_VCENTER | DT_SINGLELINE); rgn.DeleteObject(); dc.SelectClipRgn(NULL); rcLeft and rcRight are areas containe...

MFC Extension DLL problem
Hi, I created a DLL to make use of a preexisting MFC application. I want to use some dialogs, views and some other stuff from it. Except for some linker warning I got the dll compiled. If I now start using a dialog from the DLL I get an Debug Assertion Error, when exiting my client application. The Expression is: _CrtIsValidHeapPointer(pUserData) and the error was triggered in dbgheap.c . So, I assume I have a memory leak somewhere. Do anyone have an explanation for this? After researching a bit, it occured to me that I have two heaps, one for the DLL and one for my client application. I th...

Creating Cross-Project Links
Hi 1). What is the usage of Creating Cross-Project Links ? As I understand, "Cross-project links are useful when two or more projects have only a few tasks that affect one another. For example, a milestone within a development project might trigger a task in an advertising campaign project." 2). How it is implemented in Project 2007 ? When I copied the task from the source project into my project ( target project ) using "Paste Special", the new task I created become another task in my target project. My observation is both task ( ie. task in the source...

Problems with Worksheet_Change
I've got the following code in a sheet in my workbook: Private Sub Worksheet_Change(ByVal Target As Range) Dim lngRowNum As Long If Target.Column = 6 Then If Target.Value = "Closed" Then lngRowNum = Target.Row Target.Range("B" & lngRowNum & ":I" & lngRowNum).Interior.Color = vbYellow End If End If End Sub What it's supposed to do is, if you change the value held in column 6--'F'--on any row to 'Closed', it should colour the row from column 'B' to column 'I' in ...

Blank page at the end of report (no margins problem)
In an Access report I've created, the last page is always blank (so not every other page, only the last one). This is very strange considering the fact that I've set both page headers and footers and they don't appear on this page. Now I've checked all margins, both "can shrink" and "can expand" options and I really can't seem to find where this problem is coming from. Another strange fact is that in preview mode (and in report preview mode) I don't see this blank page, it's just there when I print the report. Can anyone tell me what I'm m...

Mechanism for applying shared and NTFS permissions
Hello All, Can someone point me to documentation explaining the mechanism for security decriptors/attributes when applying permissions via changing group memberships for users. I do not need to know what file permissions are and what needs to be set but rather the background mechanism on how it actually gets applied and when it takes effect. For example, suppose a user is mapped to a shared drive and has full control on sharing and file system permissions via group membership. I decide that this person no longer needs full but rather read access on file/folder, after making th...

Customer Cross Reference
2nd Request - Posted a few Months Ago, No Reply --------------------------------------------------------------------------- Has anyone out there used the Customer Part Number Cross Ref v8.0? I loaded a customer list the other day. When I entered the Customer Number on the Sales Order, it was instantly converted over to our number. Worked Great... I'm wondering if there is a way to automatically reference the original Customer Number on the Packing List and/Or Invoice or is that something that needs to be manually typed in. I tried adding notes, but I don't know if they carry down...

Picture Problem #2
Every picture that I have tried to insert into Publisher turns out to only be a blank white box. How can I fix this? spinach_is4losers wrote: > Every picture that I have tried to insert into Publisher turns out to > only be a blank white box. > How can I fix this? =========================== Have a look at the following site: (Read the third FAQ) Q: Why can I not see images/shapes/ lines when editing my publication? http://ed.mvps.org/Static.aspx?=Publisher/FAQs -- John Inzer MS Picture It! - Digital Image MVP Digital Image Highlights and FAQs http://tinyurl.c...

Graphics Problem
I have a computer that just freezes up when booted normally. I've installed latest drivers. I turn the computer on. Windows XP loads About a minute or 2 it freezes up with like horizontal bars, all the pixels are shifted or the monitor just turns off. I think it works fine in 8 and 16 bit color. But when I set it to 32, it froze. The graphics card is: GeForce4 MX 440 with AGP8X Has SP3. Works just fine in safe mode. Did you install the latest driver for that GeForce4 MX 440 with AGP8X card -- Peter Please Reply to Newsgroup for the benefit of others Requests ...

Do rules created in Outlook apply to external emails received?
We are posting a few jobs for our company. What I did was created 2 new accounts and email addresses and gave permissions to the Director who now can open their Inbox within her Outlook. I created a new rule for each mailbox that states email "Sent only to me" to apply this rule "Have server reply using a specific message". Basically the message is the auto response email stating thank you and we have recieve your resume. I've tested from my internal account and get the auto response with the message and our company logo just like I set it up. However I do not ge...

Public Folder problems
Situation is as follows: Server1 in parent domain has a single SMTP Connector for internet bound email and incoming email. Server1 is member of RoutingGroup1 Server2 in the child domain is member of RoutingGroup2. Both Routing Groups are connected by a Routing Group connector. Mail between all users mailboxes flows without a problem We have the following issues: Issue 1: Public Folder replication only works one-way: from Server1 to Server2 Issue 2: Users canot send emails to mail-enabled public folders in the other routing group. If they try this, the emails get stuck in the Local Delivery...

Problem with dates after conversion
I converted a large Lotus 123 document into excel, now whatever date I enter returns a 1/00/00 date, in whatever format I choose..Jan 0 1900..1-0-00 etc. the correct date is displayed up on the F line but in my sheet is says Jan zero of the year 1900...It has successfully driven me nuts. Help please... Go to the Tools menu, choose Options then the Transition tab. Uncheck "Transition Formula Evaluation" and "Transition Formula Entry". When Entry is checked, Excel will treat 3/9/2005 not as a date but as division, and the result is less that zero, hence when formatte...

Apply a new design to a page in my publication
On Wed, 1 Feb 2006 21:11:31 +0000, Christi wrote (in article <4C3E5380-289B-451D-A2CC-035E04C4D662@microsoft.com>): > Well go on then. What's stopping you? ...

Strange problem
I keep getting "System.Net.WebException: The request failed with HTTP status 401: Unauthorized" when I try to access Reports and Organizational Unit Settings from Web client. When I access these features from browser installed on CRM server everything works fine, but when I try same features on any other browser this error pops up. What can be wrong? Hello Damir, Exactly the same problem here - were you able to solve it ? Regards, Mav. "damir" wrote: > I keep getting "System.Net.WebException: The request failed with HTTP > status 401: Unauthorized&quo...

SBS 2k3 sp2 problem after power off and on
Had something happen today SBS2k3 sp2 box began acting funny after power went off and back on. rather quickly. All Equipment is on APC battery backups as well as all routers switches and our 2 external modems. HP ML 370 G4 server w/4 gb memory. 3 Raid 5 72gb drives. 2 Nic cards one external 192.168.0.2 internal 192.168.16.2 modems hooked to the serial ports using com 1 and 2. Problem is that now one modem is not working and the network is not working correctly. Server can see no one and no one can see the server. ran SBS best practice program and it has one major problem it stat...

cross-reference
I created a cross reference in word to a section of my document that has a heading of 'Heading 3' I used Heading for the reference type and for Insert reference to I used Heading Number (Full context). I was expecting to get something like this 4.8.2 but instead I just got 0. is there some setting somewhere that needs to be tweaked? Make sure that numbering is correctly applied to your headings. If you are using Word 2007, you can choose the "1 Heading 1 1.1 Heading 2 1.1.1 Heading 3" format at Home tab | Multilevel List. If you are using Word 2003, see http://www...

OLK 2k7
Outlook is behaving strangly with the "through the selected account" option. Each time I restart Outlook the rule fails. When I go in to check on the rules I get told that the rule is "invalid". and the "SELECTED" account is no longer selected. Each time the criteria the account needs to be selected by changes. For example with the following data Account Name Email Account mailserver.domain1.com user@domain1.com mailserver.domain2.com user@domain2.com One time I go in and it's asking me to select the account ...

Apply this patch
--tbruwlic Content-Type: multipart/related; boundary="acmimsjtfnvutth"; type="multipart/alternative" --acmimsjtfnvutth Content-Type: multipart/alternative; boundary="djdrppzukstra" --djdrppzukstra Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Consumer this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to maintain...

number/bullet list style scheme problem from Word 2003 to Word 200
I have a Word 2003 template with build-in and customized styles included paragraph, character, linked style, list/outline style and some macros. The template works well in 2003 to create a document with all the customized styles shown. However, when the template opens in Word 2007, (right click on the .dot file), I have one pop up message that Word detects errors in the file and able to repair with a list of numbered styles. However, I lost my entire customized numbered and bullets list scheme. In 2007, when I use my mouse hover the style pane on these specific numbered or bul...

Problems getting outlook 2002 to work with migrated Exchange 2007 (from 2003)
Hi there, I'm having a problem getting Outlook 2002 to work with a new Exchange 2007 server I've just migrated my users to from Exchange 2003 (SBS).. the damn thing keeps hanging on with a grip of iron to the old server!! My situation history (much simplified) is thus... Original situation: ------------------- 1 SBS 2003 server SBS1 with Exchange 2003 and SQL 2000 installed, 1 Terminal Server TS1. User's login to TS1 through Citrix and have access to Office 2002 using Exchange 2003 as their mail server. Current situation: ------------------ Ran SBS Transition Pack 2003 on ...

Weird problem
I am using SHGetSpecialFolder with CSIDL_PROGRAM_FILES to get an install directory. On my Win2000 system, this works fine. On my Win98 system, it fails with an "invalid argument" error. I tried setting up a loop and calling with a range of values. All appropriate values worked correctly except 0x0026 (CSIDL_PROGRAM_FILES). Any ideas? -- ------------------------------------------------------------------------- Free software - Baxter Codeworks www.baxcode.com ------------------------------------------------------------------------- "Baxter" <baxter.spamblock@ba...

sa problem
This is a multi-part message in MIME format. ------=_NextPart_000_00D7_01C4846D.1FDEE6E0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable this error just started this morning; has come up maybe 10 times so far. = already been through kb (279742, 300114) and it doesn't apply; nothing = on the network has changed Event Type: Error Event Source: MSExchangeSA Event Category: RFR Interface=20 Event ID: 9074 Date: 8/17/2004 Time: 2:20:08 PM User: N/A Computer: Description: The Directory Service Referral interface failed to service a clie...

Pivot Table Problem
I am working in an excel spreadsheet which another person created a pivot table in. I have to do a lot of cutting and pasting into another report. When I scroll my mouse over any cell in the sheet with data in it a bluebox appears with the information in that in cell. How do I turn that off? It is very annoying as it causes a delay in my trying to copy that cell. Hi, While in the pivot table choose PivotTable Tools, Options, Options, Display and turn off Show contextual tooltips. Cheers, Shane -- If this helps, please click the Yes button. Cheers, Shane Devenshire ...

OutLook Crashes after Applying Update Rollup 1
Outlook is crashing every 10 minutes or so for one of my users. Please help. If the patch is the problem, then please let me know how to remove it. It does not show up in Add Remove programs. And also how can we know if the patch is applied or not. The CRM Help about version remains the same after applying patch. "elmo" wrote: > Outlook is crashing every 10 minutes or so for one of my users. Please help. I dont know whether the patch is the reason for the crashes. But: The version number of the client, that is shown in CRM Help, isn't exactly the right one. In fact, ...

2005 Update Retirement Account Problem
I am having the same problem as discussed in a post on 10/5. Has anyone else seen this or know of a solution? And btw, I am not running XP SP2, just a fully updated SP1 system. I tried everything I could find in the KB to no avail as well. I am getting really frustrated with how many bugs there are in 2005. I have used MS Money for years and have never had as many problems as I have with 2005. I can't believe that this product made it out to the public with as many problems as there are. Any comments would be appreciated. ...