.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Inline table valued function, full outer join and cross apply

Posted By:      Posted Date: September 18, 2010    Points: 0   Category :Sql Server
Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

View Complete Post

More Related Resource Links

How to insert values into my table the data is coming from inline table valued function

Hi all,   i have table oi want insert values into my table the data is coming from inline table valued function how i can use the inline table valued function to insert datainto my table i have inline function which out puts 3 collumns of data how i can insert this data into my table i have the insert statment like below insert into tablename(c1,c2,c3,c4,c5) select ('abc',....................,'xyz') how i can use the out put put of my inline function to  to insert data into my columns c2,c3,c4. please help me out Thanks & Regards Sunil Yoganna

how to use table valued function with join on table in server 2000


Hi please any oone tell me how to call a table valued function


select * from get_date_time_from_gmt ('+17:45','1879-01-02 00:00:00.000','17:30')  when i call it then it will work properly. but now i have to use columns from cricket_master  

as mention below



* from dbo.get_date_time_from_gmt

repeating merge joins using full outer join - should be possible without sort, but cannot get the co

Hello,   I am trying to combine several source tables with Merge Join.  For the Moment I have three source tables all already sorted in the source query and the is sorted property with the correct keys set. (its an aggregate query with group by and sort by the key columns) Then I do a merge join between two of the tables - with a full outer join. Then I just want to do another merge join with the output of the next result. but as the merge join do not combine the key columns into one column I need to add a derived column which substitute null values on the left side of the join with values from the right side of the join. The second merge then says only the left columns of the first merge join is sorted, but not the comined columns. If i Replace with a derived column the left side primary key columsn with the combinded key columns the next merge join says the input is not sorrted? But the output must be sorted. So how do I get the next merge join working without the ssis sort transformation? the data is sorted, but the component does not recognize the sorted data? I do not want to use (can use) the sort because there are flowing >= 500 Mio. records through the pipeline. Any help appreciated.   Hanneshttp://www.hmayer.net/

Creating a table-valued function on a user defined type in SQL Server CLR

We would like to be able to create a table-valued function on a user defined type.  We would like the syntax in SQL to look like it works for the XML nodes function:   DECLARE @myXml XML = '<a><b>1</b><b>2</b><b>3</b></a>' ; SELECT  node.query('text()') FROM    @myXml.nodes('a/b') nodes (node) ;   In other words, in the FROM clause, we can access the "nodes" method of the xml variable and it returns a rowset. Thanks!

XQUERY using Cross Apply and Outer Apply

Trying to shred XML into relational table rows and can do it for one set of node values .. having difficulty creating a pair of columns. The relevant part of the XML looks like this ... <ns:Coverage>      <ns:CoverageCd>cvalue1</ns:CoverageCd>      <ns:Form>           <ns:FormNumber>fvalue1</ns:FormNumber>      </ns:Form> </ns:Coverage> I can successfully navigate through the XML and pull back a set of rows for all values of CoverageCd. Now I'm trying to add a column to each output row for the FormNumber value associated with the CoverageCd value. However; FormNumber is not always present. I found an example where using 'Outer Apply' can account for that, but it's not working for me. Here's my XQuery: WITH XMLNAMESPACES('http://www.mycompanyname.com/ACORD1.11.0/Policy_1.0/xml' AS "ns") select cov.i.value('.', 'varchar(30)')   [Coverage],          form.i.value('.', 'varchar(30)')  [FormNumber] from  dbo.mytablename        cross apply XMLcolumn.nodes('.//ns:Coverage/ns:CoverageCd') as cov(i)        outer apply cov.i.nodes('.//ns:Coverage/ns:Form/ns:FormNumber) as form(

table valued function problem

This is my table-valued function Problem is it is returning only 1 record but it is supposed to return more than one i stuck with this problem plz replyALTER function [dbo].[funcqidata](@dst int,@mth int,@yer int) returns @cqi_rpt table ( hosp_name varchar(100) NULL, bedded_strength int NULL, out_patients int NULL, in_patients int NULL, minor_surgeries int NULL, major_surgeries int NULL, deliveries int NULL, radio int NULL, lab_test int NULL, tot_part1 int NULL, waste_man int NULL, swab_test int NULL, attendence int NULL, drug int NULL, charges_thismonth int NULL, charges_lastmonth int NULL, usr_charges_marks int NULL, tot_part2 int NULL --grand_tot int NULL ) as BEGIN DECLARE @hosp_name varchar(100), @bedded_strength int, @out_patients int, @in_patients int, @minor_surgeries int, @major_surgeries int, @deliveries int, @radio int, @lab_test int, @tot_part1 int, @opd_handled int, @opd_target int, --------- supporting variables declared for calculation ----------- @ipd_handled int, @ipd_target int, @minor_handled int, @minor_target int, @major_handled int, @major_target int, @delivery_handled int, @delivery_target int, @radio_handled int, @radio_target int, @lab_handled int, @lab_target int, -------- end of declaring variables ----------------------------- @check_charges int, @waste_man int, @swab_test int,

trying to use CASE within table-valued function

Hi, I am trying to create a table-valued function that takes a string argument and, depending upon it's value, returns one of a number of identically-structured tables (and before someone asks why multiple identically structured tables, NOT my idea!).  I am a novice with T-SQL, but I've been attempting something like this for the function: Case @Input   When 's1' then return table1   When 's1' then return table2 End ... but I keep getting errors.  Can someone help get the ball rolling and show me the basics on how to set up a function to do this?  I'm hoping since I'm just redirecting to existing tables that this function will have minimal performance hits.

Cross Join and error Two sets specified in the function have different dimensionality.

WITH   SET   [InternetSET] AS {   CROSSJOIN ([APM Channel].[APM Channel Desc].&[Interactive] , {        [APM SubChannel].[APM Sub Channel Desc].&[Affiliates]       ,[APM SubChannel].[APM Sub Channel Desc].&[Partner]       ,[APM SubChannel].[APM Sub Channel Desc].&[Unreferred]       ,[APM SubChannel].[APM Sub Channel Desc].&[SMS]       ,[APM SubChannel].[APM Sub Channel Desc].&[Search]       ,[APM SubChannel].[APM Sub Channel Desc].&[Media]       ,[APM SubChannel].[APM Sub Channel Desc].&[E-mails] } ) }   SET   [Top1SET] AS {   TOPCOUNT([InternetSET],1,[Measures].[% Approve]) } MEMBER   [Top1]   AS   SUM([Top1SET],[Approved Apps]) / SUM([Top1SET],[Basic Apps]), Format_String='0.00%'   MEMBER   [Internet]   AS SUM([InternetSET],[Approved Apps]) / SUM([InternetSET],[Basic Apps]), Format_String='0.00%' select { // if I use the set below instead of the member below it I get // error : Two sets specified in the function have different dimensionality [Top1set] // if I use the member below instead of the set above the query works // but the name of the item is not dis

Merge Join: Full Outer Join - keep key values in case of no-match

I'm using the Merge Join to join several different incoming flows into one flow.  I've configured the joins to use a Full Outer Join because I need all records from all sources. In the case of a no-match, I want the component to keep the values of join key fields instead of setting them to NULL.  How can I achieve that?  (Activating the checkbox doesn't help, because that adds a new field to the output instead of re-using the existing one.)

Cannot call Table Valued Function from Dynamic stored procedure statement

Hello,  I have a table-valued function that splits string into a table column. I can easily call this function from a stored procedure within a regular SELECT statement: SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM [dbo].[fnSplitValues](@Code2String))   However when I try to use the same logic for a dynamic SELECT statement: ‘SELECT * FROM Table1 WHERE Code1 = '1' AND Code2 IN(SELECT * FROM ' + [dbo].[fnSplitValues](@Code2String) + ')’ I get an error ‘Cannot find either column "dbo" or the user-defined function or aggregate "dbo.fnSplitValues", or the name is ambiguous.’ Any idea what is wrong with my dynamic SQL?   Please help, Lana

Schema comparer mistake in table-valued function (Nullability in column definition)

Hello. In my database i have table valued function. In its definition nullability of column is not set directly. This column has a user-defined data type. So the question is: is this column nullable? As i can see here: http://msdn.microsoft.com/en-us/library/ms174979.aspx   When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. Column data type Rule Alias data type The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help. It should be not null, as user defined type is declared as not null. SMO says, that column is not null, but schema compare says it is nullable. Who is wrong and why?

Table valued function content


Hi there,

I have been scouring the web for hours with no success.

I am looking for a way to fetch a function's script/code content. I have a way of doing this with stored procedures (see below), I also need to do this with functions.



SELECT [sp].[name], [sc].[text], [sc].[ctext]
INNER JOIN SYS.OBJECTS AS [sp] ON [sc].[id] = [sp].[object_id]
WHERE [sp].[type] = 'p'


Any suggestions would be greatly appreciated.

Thanks in advance for all the trouble.





table valued function from column from othe table


Hi please any oone tell me how to call a table valued function


select * from get_date_time_from_gmt ('+17:45','1879-01-02 00:00:00.000','17:30')  when i call it then it will work properly. but now i have to use columns from cricket_master  

as mention below



* from dbo.get_date_time_from_gmt (a.gmt_offset

How do display null rows returned from my table valued function?

DECLARE @techNumLoop VARCHAR(25)
 Tech Varchar(25) 
,Item_Count Integer NULL 
,Expenses MONEY NULL

SELECT DISTINCT t.Case_Number Tech
FROM tblTechnology t
OPEN techCursor;
FETCH NEXT FROM techCursor INTO @techNumLoop;
INSERT @myTemp
FROM ufn_Find_Some_Stuff(@techNumLoop)


Table Valued Function in the select statement


Hi, I have created a table valued function

dbo.FnTaskGrp(@p1 int,@p2 datetime)..

I got the result by executing,

SELECT * FROM dbo.FnTaskGrp(1,'1-jan-2010')

But For this ,

select * from dbo.FnTaskGrp(tab.WflTask,tab.WflModDt)

                  select WflTask,WflModDt from WorkFlow


I got the Error,

Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'tab'.

Isn't Possible in SQLSERVER 2005?









Full Text search join runs really slowly when inserting results into a table.


We have a problem when inserting the results of a join on a full-text search into a table. I have had to anomalyse the query but the bottom line is that the insert into a table is the factor that makes the query run really slow.

The join itself works fine and returns the result set (2 rows in this case) in approx 100 ms:

    tablea WITH (NOLOCK) 
INNER JOIN CONTAINSTABLE (indexedviewa,*,'nnnn') AS kwsearch
    ON kwsearch.[key] = dbo.tablea.aaaa 
WHERE p = 1
AND q = 2
AND r = 0
AND s NOT IN (8,9)
AND t = 'zzzzz'

However, when the results of the join (2 rows in this case) are taken and inserted into a table (whether it be #temp or a permanent table), the query takes approx. 3500 ms:

    propertyId  int,
    agentId     int);

    tablea WITH (NOLOCK) 
INNER JOIN CONTAINSTABLE (indexedviewa,*,'nnnn') AS kwsearch
    ON kwsearch.[key] = dbo.tablea.aaaa
WHERE p = 1
AND q = 2
AND r = 0
AND s NOT IN (8,9)

Need help with mis-match full outer join


Hi, I'm sql 2005.  I have a sp that has 3 parts.  1st part selects from database A into a #tempA, 2nd part selects into a #tempB.  These 2 tables have the 6 common fields and only one field that is different so each table has 7 columns.
Part 3 of this sp full out join these 2 temp tables on the first 4 common fields and only include row if either  col5 and col6 subtraction difference is not equal to 0(meaning they should have the same value for both table of col5 and col6).  Select these rows into a 3rd temp table. then return the results of it.
The problem I have now is my 3rd table would record rows that should be a match but because of select order from each table ends uip with mis-matched the rows.
OR (A.COL6-B.COL6)<> 0


  col1        col2        col3        col4        col5        col6

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend