Need help using Cross Apply together with Group By

Question

Post reply

Need help using Cross Apply together with Group By

Eric_Gagne

SSCrazy

Points: 2062
More actions
October 15, 2008 at 10:46 am

#199380

Hi Forum,
I have to create a view which will serve to link two systems together. Unfortunately rewriting the older system is not an option right now so I need this view which will match the old structures for it to continue working.
The problem is that is sums certain fields and some information come from Table-Valued Functions.
My code looks like this;
Create view vView as
Select a.afield, sum(b.bfield), sum(c.cfield), d.dfield
from
table1 a,
join table2 b on [condition],
join table3 c on [condition],
left outer join table4 d on [condition]
cross apply table_valued_function1(p1, p2) as vresult1
cross apply table_valued_function2(p1, p2) as vresult2
Obviously if I run this I get errors because I'm missing the group by clause. My problem is if I add "group by a.afield, d.dfield" it complains than table_valued_function1.whateverfieldname is not in the group by list.
How do I add the fields returned by a table_valued function to the group by clause ?
I saw an example on a web site where they put the group by clause before the cross apply statements but when I try that I get "Incorrect syntax near the word 'Cross'"
Anyone can help with that ?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Eric_Gagne SSCrazy Points: 2062 More actions · Answer 1

Nevermind.....I found my mistake....after 3 hours. I tried to delete the post but it's not working !

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

eric (10/15/2008)
Nevermind.....I found my mistake....after 3 hours. I tried to delete the post but it's not working !

Please do not delete posts. It just annoys everyone else.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

eric (10/15/2008)
Nevermind.....I found my mistake....after 3 hours. I tried to delete the post but it's not working !

Would you mind telling us what your mistake was? Might save someone else 3 hours...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric_Gagne SSCrazy Points: 2062 More actions · Answer 4

Jeff Moden (10/15/2008)
eric (10/15/2008)
Nevermind.....I found my mistake....after 3 hours. I tried to delete the post but it's not working !
Would you mind telling us what your mistake was? Might save someone else 3 hours...

You're right Jeff, I should have done that. My confusion came from the error message because SQL always uses the real name of the table and/or function, not the alias we give it.

So although my function was called as

cross apply table_valued_fuction(parms list) as xyz

The error message was saying "table_valued_function.fieldname" and not "xyz.fieldname" so I thought I had to use the function name in the group by but I found out later that I only had to use the alias as for any regular table.

Here's an example.

Select a.field1, sum(b.field1), sum(c.field1), d.field1, vresult1.field1

from

table1 a,

join table2 b on [condition],

join table3 c on [condition],

left outer join table4 d on [condition]

cross apply table_valued_function1(p1, p2) as vresult1

group by a.field1, d.field1, vresult1.field1

As simple as that

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 5

Thanks, Eric. The only thing I'd worry about now is the performance hit of using both a table valued function and a cross-apply to drive it home. If I new more about both the function and what you're trying to do, we might be able to avoid it... or not. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eric_Gagne SSCrazy Points: 2062 More actions · Answer 6

There's probably a better way of doing this but I'm new to SQL and I'm learning "on the spot" while making my old applications work with a new system my company bought recently.

My apps were using Pervasive SQL databases (bTrieve ISAM Files) and I have to make them work with the new MRP system which is built on SQL2005.

Our previous MRP was running on a Unix system (HP-9000), seating on a Informix IDS database. They had process that put data in Informix tables and I had a dedicated program running on a PC that would grab this data every 5 minutes and 'feed' my bTrieve files.

My apps will be completely rewritten in time but for the moment I have to "map" my current data structures with the tables of the new system. Since it's completely different the only way I can do this is to create views matching my current structures.

One of those apps is a packing system which uses 5 tables (OrderHeader, OrderDetail, OrderSummary, CartonHeader and CartonDetail). My OrderHeader structure contains "shipto" address fields (Name, Address1, Address2, City, Zip, Country). The "OrderHeader" table of the new system doesn't have those fields, it only has a "ShipTo" code which is used to lookup in other tables. That ShipTo can either be a Customer, a Store or a DC (Distribution Center). Their system has a value-tabled function to resolve this, it receives parameters and returns the correct values.

That is why I'm using this. I had never used a function in a select statement before and all I could find on the web is the "cross apply" method.

If I don't use this then I think the only other way is to join 3 more tables (Customers, Stores and DC), they would have to be "left outer join" because depending on the type of shipping (ToCustomer, ToDC or Direct to Store) the link to the 'Stores' table may return no record.

Then I would have to use a series of "Case/When" statements to get the correct value. I'm not sure it would really be any faster than using 'Cross Apply'