Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Any way to rerwirte this query? Expand / Collapse
Author
Message
Posted Tuesday, January 07, 2014 10:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Saturday, April 12, 2014 10:28 AM
Points: 313, Visits: 809
My udf needs to be the first statement in the query and then there is a left join to a table. How can I rewrite this?

I know if table is first statement and udf is a second statement in the join, we can simply pass the col from first table to the udf

But I am confused on this one.

Please help

select
From dbo.udf_Myfunction(a.col1) udf
LEFT JOIN mytable a on udf.col = a.col1
Post #1528573
Posted Tuesday, January 07, 2014 10:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 2,774, Visits: 5,949
It makes no sense that your udf needs to come before your table if it uses a column from your table.
This might help you:
SELECT *
FROM mytable a
OUTER APPLY dbo.udf_Myfunction(a.col1) udf




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1528580
Posted Tuesday, January 07, 2014 10:40 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 12,016, Visits: 11,046
I am guessing that what you really want is all the rows from the function and values from the table if there is a match?

This is where you can turn your join around to a right join.

select *
From mytable a
RIGHT JOIN dbo.udf_Myfunction(a.col1) udf on udf.col = a.col1



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1528583
Posted Tuesday, January 07, 2014 10:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 5:48 PM
Points: 2,774, Visits: 5,949
Sean Lange (1/7/2014)
I am guessing that what you really want is all the rows from the function and values from the table if there is a match?

This is where you can turn your join around to a right join.

select *
From mytable a
RIGHT JOIN dbo.udf_Myfunction(a.col1) udf on udf.col = a.col1


Hey Sean,
The parameter from the udf is a column from the table (I guess you didn't notice), so he would need to use APPLY. Even if a JOIN would be possible, RIGHT JOIN would make no sense as it shouldn't return any values not present on the table.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1528590
Posted Tuesday, January 07, 2014 10:55 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:49 PM
Points: 12,016, Visits: 11,046
Luis Cazares (1/7/2014)
Sean Lange (1/7/2014)
I am guessing that what you really want is all the rows from the function and values from the table if there is a match?

This is where you can turn your join around to a right join.

select *
From mytable a
RIGHT JOIN dbo.udf_Myfunction(a.col1) udf on udf.col = a.col1


Hey Sean,
The parameter from the udf is a column from the table (I guess you didn't notice), so he would need to use APPLY. Even if a JOIN would be possible, RIGHT JOIN would make no sense as it shouldn't return any values not present on the table.


/facepalm

I didn't notice the parameter was from the table.

I figured he wanted the values from the function and nulls when there was no match.

/me slithers away quietly hoping nobody will notice how awful his original suggestion was.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1528595
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse