.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

Outer Apply, within an Outer Apply

Posted By:      Posted Date: October 01, 2010    Points: 0   Category :Sql Server

Good morning, I have a from clause that uses an outer apply function to get the most recent sales order from a job order, how ever the sales order could be against one of several items. This is my current statement: I would appriciate any help in fixing this error.







View Complete Post

More Related Resource Links

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(

Inline table valued function, full outer join and cross apply

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

Nested Tracers - is there a way to only log the outer trace?



I have some nested tracers. The outer one has a category of 'PageTrace' and the inner ones have categories of 'DBTrace'. When I set the outer category source to 'All' I get both traces. When I switch on the inner trace, I just get the inner trace.

So, my question: is there any way to just log the outer trace events, i.e. the Page Traces.




Foundations: Easily Apply Transactions To Services


Managing state and error recovery using transactions is the topic of this month's installment of Foundations.

Juval Lowy

MSDN Magazine January 2009

Unit Testing: Apply Test-Driven Development to your Database Projects


Jamie Laflen extols the benefits of TDD when applied to database development-and supplies some useful techniques along the way.

Jamie Laflen

MSDN Magazine Launch 2008

Apply custom.master to "Site Settings" page?


I'm using a custom.master page, which is applied to almost all pages available to READ users.

However, I'd like to apply it to the Search, Site Settings, and View All Site Content pages.

I'm not sure where this would be applied.

Guidance much appreciated. Thanks!

.NET4, DataGrid doesn't apply EditingElementStyle with ValidatesOnExceptions when catching an excep

Hello everyone, I'm using WPF and the .NET 4 framework (4.3.30319), on the Windows 7 32-bit OS. I wanted to keep things simple with validation and my property setter just throws an ArgumentException when the data is not valid, instead of implementing a ValidationRule class. While this worked with a ListBox, it seems it doesn't with a DataGrid. Below is a simple example to reproduce the problem, check the XAML and C# code related to the 2nd column. The datagridTBStyle is meant to apply a specific style when an error occurs (since control templates are out of the question with DataGrid), but what I see is: it applies the correct style when editing the cell (blue text) it doesn't apply the part that is supposed to trigger on Validation.HasError (red on yellow background + tooltip) Without this style, it doesn't apply a red border around the cell as it should according to the documentation. However, it does put a part of red exclamation mark in the row header when an error occurs, and it catches the exception. Am I doing something wrong, or is this actually a bug? Link to the documentation. <Window x:Class="DataGridTest.MainWindow" xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation" xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml" Title="Test" Height="200" Width="350&q

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/

INSERT statement with OUTPUT clause, referencing outer query columns

I have a problem to solve and I have run into what appears to be a limitation of TSQL.  I have looked around and did not find much on this subject, so I apologize if this duplicates another post.  I am using SQL Server 2008. From what I have read on BOL, when you are performing a DELETE or UPDATE statement, you can reference unaffected columns from the outer query in the OUTPUT clause, but this is apparently not allowed in an INSERT statement. I am working on a process that will create new copies of existing records - essentially, the user can create a whole new copy of a set of records, and the process requires that I track both what the original PK values were and the corresponding PK values for the new rows. This example will hopefully spell out my problem.  This script shows two tables, [Primary_Object] and [Secondary_Object].  Not shown here are multiple tables that rely on [Secondary_Table], which is why I have to be able to track this info. This first script shows the setup of the tables involved and the data involved: /* create test data */ create table primary_object ( primary_object_id int identity(1,1), parent_object_id int, name char(1)) create table secondary_object ( secondary_object_id int identity, primary_object_id int, amount money) insert into primary_object (parent_object_id, name) select 0, 'A' insert into secon

how to apply validation controls on Gridview which is created dynamically

 hi, i created a gridview with two columns and 8 rows dynamically. All the rows are empty and editable at runtime. I am giving column names for this grid from some other .aspx page by passing column names using querystring.Because i am using this grid for three different pairs of column names.  i would like to know how to apply Required Field Validator, Regular Expression Validator  and Range Validator on Gridview textboxes. So that by using those validators i want to validate user inputs. And i would also like to know how to add rows at runtime for this grid. I am using ASP.net 2.0 and C#.net for this.(Visual Studio 2005) The code i am using to create gridview is below. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="LoadData.aspx.cs" Inherits="LoadData" %>  <form id="form1" runat="server">  <div> <asp:GridView ID="GrdDynamic" runat="server" AutoGenerateColumns="False" ForeColor="#333333" BorderColor="GradientInactiveCaption" BorderStyle="Solid" style="z-index: 108; left: 24px; position: absolute; top: 13px">  <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />  <RowStyle BackColor="#EFF3FB"

how to apply css to custom web part?

I've created a custom web part in visual studio 2005. i want to apply style sheet to this web part? how do i do this? where should i add reference for css in custom code?

How to I apply an XAML declared animation to multiple controls dynamically?

Hello, How to I apply an XAML declared animation to multiple controls dynamically? Here is the XAML declared animation: <Window.Resources> <Storyboard x:Key="TextBlock_SB"> <DoubleAnimation Storyboard.TargetName="TextBlock_1" Storyboard.TargetProperty="(TextBlock.Opacity)" From="1.0" To="0.75" Duration="0:0:0.5" AutoReverse="True" RepeatBehavior="Forever" /> </Storyboard> Here is the code that starts the animation for TextBlock_1: (this.Resources["TextBlock_SB"] as Storyboard).Begin(); I want to apply the same storyboard/animation to TextBlock_2 and TextBlock_3 and perhaps others.  How do I do that without defining more XAML storyboards? Regards, Robert  

Left Outer Join fails to return all records

I have a table tblDrop that contains 1067 records for 8/31/2010 and a table tblSoftcount that has 739 records, some with bad serial numbers. I need to match all of the records in tblDrop with those in tblSoftcount. I have a query using Left outer join that has worked for the duration of the application, but has recently stopped returing the full set of records from tblDrop. We have not changed the SQL versions on the server (compatibility level 2005). Here is the query: SELECT   v.SlotNum_ID, s.SlotNum_ID, v.PTNumber, s.PTNumber, v.DropBillAmount AS [Drop], v.ReportDate, v.DateValue     FROM tbl_Drop v LEFT OUTER JOIN tblSoftCount s ON v.SlotNum_ID = s.SlotNum_ID AND v.ReportDate = s.ReportDate WHERE   s.ReportDate = '8/31/2010' AND v.DropBillAmount <> 0 AND s.Slotnum_ID IS NULL;  

In SPCalendarView apply color to SPCalendarItem

Hi, For SPCalendarItem, BackgroundColorClassName value works only when IsAllDayEvent is set to true. How to apply the color when IsAllDayEvent is false. Thanks.

How to apply css to /_layouts/settings.aspx

Hello, I have designed custom menu with css. Its works fine across the websites. But, I browse www.ab.com/IT/_layouts/settings.aspx page i won't be able to see any css effect. At heading only menu come which doesn't looks good. Can any one suggest me that how can apply my css to across all websites at all pages. Thanks in advance.Thanks, Sam

Merge Replication, Push Subscription : The snapshot takes centuries to apply

Well, not centuries, except that the users are storming the gate. I'm trying to find how to get the snapshot moved to the subscriber and applied in a reasonable time. Last time was successful, but took 2 1/2 days to build the subscriber database from the snapshot. Hillary responded: Something is very wrong here. You should be able to generate your snapshot, copy it manually over to the subscriber - using the altsnapshotfolder parameter and then apply it there. So now I have my snapshot, a folder with lots of .cft .bcp .dri .prc .sch  and .trg files. Getting this to the subscriber computer shouldn't take long. Once I get it there, how do I use it to get the subscriber set up?  You can't be successful at this unless you're at least 1/2 a bubble off level.

How to apply skin to master pages

i have master pages set to each page, i want to use skins, but i cannot attach it to master pagedo i need to go in every page and set theme there ?how can i apply it to master page, so that it gets applied to all the pages
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