Multiple exists not functioning as expected?

I have the following query.
declare @.p0 nvarchar(6)
declare @.p1 nvarchar(2)
set @.p0 = 'Trish%'
set @.p1 = 'SH'
SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
[ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
[TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
[DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
[CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
[ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
[ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
[DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
[CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
[t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
FROM (
SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
[DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
[t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
[DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
[CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
[TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
[t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
[CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
[DocumentId])) AS [value2]
FROM [dbo].[vDocument] AS [t0]
) AS [t1]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t2]
WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
[DocumentId])
)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t3]
WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
[DocumentId])
))
-- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
-- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.21022.8
The problem is that this query returns rows, and it doesn't seem that
it should (there are no DocumentContact rows that have a FirstName
like Trish and ContactType = 'SH').
I believe it's because the two Exists in the where clause.. I would
think that rows have to be returned from BOTH Exists clauses for any
rows to be returned... but it seems that if EITHER Exists returns
rows, the entire query returns rows.
Am I not understanding how EXISTS works?
Thanks
Andy
If I understand correctly, you only want to return the rows where at least
one row exists in vDocumentContact for that DocumentId where that one row
has both a FirstName like Trish and ContactType = 'SH'. Your query does not
do that. It returns the rows where at least one row exists in
vDocumentContact for that DocumentId which has a FirstName like Trish and at
least one row exists in vDocumentContact for that DocumentId which has
aContactType = 'SH', but, as you have written the query, they don't have to
be the same row. For example, suppose I have a table of orders,
Create Table #Orders (OrderID int, CustomerID int);
Insert #Orders (OrderID, CustomerID)
Select 1, 1
Union All Select 2, 20
Union All Select 3, 30;
and a table of order lines,
Create Table #OrderLines (OrderID int, ProductID int, Quantity int);
Insert #OrderLines (OrderID, ProductID, Quantity)
Select 1, 1, 5
Union All Select 1, 2, 20
Union All Select 2, 1, 15
Union All Select 2, 3, 10
Union All Select 1, 1, 3;
If I am looking for orders which have ordered more than 10 of ProductID 1,
the following query does not do what I want,
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1)
And Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ol.Quantity > 10);
That query returns both Order 1 and Order 2 because they both meet the
condition that there is a row in #Orderlines with ProductID 1 and there is a
row in #Orderlines with Quantity > 10. But for Order 1, these aren't the
same row, so I don't want that order returned. Instead, the query should be
written as
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1
And ol.Quantity > 10);
Which returns the desired result (only Order 2).
So if I understand what you want, you need to replace your two WHERE EXISTS
with one WHERE EXISTS which checks both conditions.
If that is not what you meant, please post sample tables and data and a
description of the results you want. See www.aspfaq.com/5006 for how to do
this or do something like I did above.
Tom
"Andy" <andyj@.med-associates.com> wrote in message
news:12be336f-baee-4501-90db-e1a05226fa9a@.d62g2000hsf.googlegroups.com...
>I have the following query.
> declare @.p0 nvarchar(6)
> declare @.p1 nvarchar(2)
> set @.p0 = 'Trish%'
> set @.p1 = 'SH'
> SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
> [ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
> AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
> [TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
> [DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
> [CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
> [ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
> [ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
> [DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
> [CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
> [t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
> FROM (
> SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
> [DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
> [t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
> [DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
> [CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
> [TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
> [t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
> [CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
> CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
> AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
> [DocumentId])) AS [value2]
> FROM [dbo].[vDocument] AS [t0]
> ) AS [t1]
> WHERE (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t2]
> WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
> [DocumentId])
> )) AND (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t3]
> WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
> [DocumentId])
> ))
> -- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
> -- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
> -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
> 3.5.21022.8
> The problem is that this query returns rows, and it doesn't seem that
> it should (there are no DocumentContact rows that have a FirstName
> like Trish and ContactType = 'SH').
> I believe it's because the two Exists in the where clause.. I would
> think that rows have to be returned from BOTH Exists clauses for any
> rows to be returned... but it seems that if EITHER Exists returns
> rows, the entire query returns rows.
> Am I not understanding how EXISTS works?
> Thanks
> Andy

Multiple exists not functioning as expected?

I have the following query.
declare @.p0 nvarchar(6)
declare @.p1 nvarchar(2)
set @.p0 = 'Trish%'
set @.p1 = 'SH'
SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
[ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
[TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
[DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
[CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
[ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
[ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
[DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
[CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
[t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
FROM (
SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
[DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
[t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
[DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
[CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
[TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
[t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
[CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
[DocumentId])) AS [value2]
FROM [dbo].[vDocument] AS [t0]
) AS [t1]
WHERE (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t2]
WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
[DocumentId])
)) AND (EXISTS(
SELECT NULL AS [EMPTY]
FROM [dbo].[vDocumentContact] AS [t3]
WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
[DocumentId])
))
-- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
-- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
3.5.21022.8
The problem is that this query returns rows, and it doesn't seem that
it should (there are no DocumentContact rows that have a FirstName
like Trish and ContactType = 'SH').
I believe it's because the two Exists in the where clause.. I would
think that rows have to be returned from BOTH Exists clauses for any
rows to be returned... but it seems that if EITHER Exists returns
rows, the entire query returns rows.
Am I not understanding how EXISTS works?
Thanks
AndyIf I understand correctly, you only want to return the rows where at least
one row exists in vDocumentContact for that DocumentId where that one row
has both a FirstName like Trish and ContactType = 'SH'. Your query does not
do that. It returns the rows where at least one row exists in
vDocumentContact for that DocumentId which has a FirstName like Trish and at
least one row exists in vDocumentContact for that DocumentId which has
aContactType = 'SH', but, as you have written the query, they don't have to
be the same row. For example, suppose I have a table of orders,
Create Table #Orders (OrderID int, CustomerID int);
Insert #Orders (OrderID, CustomerID)
Select 1, 1
Union All Select 2, 20
Union All Select 3, 30;
and a table of order lines,
Create Table #OrderLines (OrderID int, ProductID int, Quantity int);
Insert #OrderLines (OrderID, ProductID, Quantity)
Select 1, 1, 5
Union All Select 1, 2, 20
Union All Select 2, 1, 15
Union All Select 2, 3, 10
Union All Select 1, 1, 3;
If I am looking for orders which have ordered more than 10 of ProductID 1,
the following query does not do what I want,
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1)
And Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ol.Quantity > 10);
That query returns both Order 1 and Order 2 because they both meet the
condition that there is a row in #Orderlines with ProductID 1 and there is a
row in #Orderlines with Quantity > 10. But for Order 1, these aren't the
same row, so I don't want that order returned. Instead, the query should be
written as
Select o.OrderID, CustomerID
From #Orders o
Where Exists (Select * From #OrderLines ol
Where o.OrderID = ol.OrderID And ProductID = 1
And ol.Quantity > 10);
Which returns the desired result (only Order 2).
So if I understand what you want, you need to replace your two WHERE EXISTS
with one WHERE EXISTS which checks both conditions.
If that is not what you meant, please post sample tables and data and a
description of the results you want. See www.aspfaq.com/5006 for how to do
this or do something like I did above.
Tom
"Andy" <andyj@.med-associates.com> wrote in message
news:12be336f-baee-4501-90db-e1a05226fa9a@.d62g2000hsf.googlegroups.com...
>I have the following query.
> declare @.p0 nvarchar(6)
> declare @.p1 nvarchar(2)
> set @.p0 = 'Trish%'
> set @.p1 = 'SH'
> SELECT [t1].[DocumentId], [t1].[Version], [t1].[ClosedDate] AS
> [ClosedDateUTC], [t1].[DueDate] AS [DueDateUTC], [t1].[DueDateLatest]
> AS [DueDateLatestUTC], [t1].[PriceListId], [t1].[SoldToId], [t1].
> [TaxRate], [t1].[IsLocked], [t1].[ShippingCost], [t1].
> [DocumentNumber], [t1].[RevisionNumber], [t1].[CreatedDate] AS
> [CreatedDateUTC], [t1].[CreatedById], [t1].[ModifiedDate] AS
> [ModifiedDateUTC], [t1].[ModifiedById], [t1].[TermsId], [t1].
> [ShipMethodId], [t1].[FOBId], [t1].[DocumentStatusId], [t1].
> [DocumentType], [t1].[CustPO], [t1].[DeliveryWindowId], [t1].
> [CategoryId], [t1].[SubCategoryId], [t1].[ShipDate] AS [ShipDateUTC],
> [t1].[value] AS [Subtotal], [t1].[value2] AS [Tax]
> FROM (
> SELECT [t0].[DocumentId], [t0].[Version], [t0].[ClosedDate], [t0].
> [DueDate], [t0].[DueDateLatest], [t0].[PriceListId], [t0].[SoldToId],
> [t0].[TaxRate], [t0].[IsLocked], [t0].[ShippingCost], [t0].
> [DocumentNumber], [t0].[RevisionNumber], [t0].[CreatedDate], [t0].
> [CreatedById], [t0].[ModifiedDate], [t0].[ModifiedById], [t0].
> [TermsId], [t0].[ShipMethodId], [t0].[FOBId], [t0].[DocumentStatusId],
> [t0].[DocumentType], [t0].[CustPO], [t0].[DeliveryWindowId], [t0].
> [CategoryId], [t0].[SubCategoryId], [t0].[ShipDate],
> CONVERT(Decimal(29,4),[dbo].[GetDocumentSubTotal]([t0].[DocumentId]))
> AS [value], CONVERT(Decimal(29,4),[dbo].[GetDocumentTaxTotal]([t0].
> [DocumentId])) AS [value2]
> FROM [dbo].[vDocument] AS [t0]
> ) AS [t1]
> WHERE (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t2]
> WHERE ([t2].[FirstName] LIKE @.p0) AND ([t2].[DocumentId] = [t1].
> [DocumentId])
> )) AND (EXISTS(
> SELECT NULL AS [EMPTY]
> FROM [dbo].[vDocumentContact] AS [t3]
> WHERE ([t3].[ContactType] = @.p1) AND ([t3].[DocumentId] = [t1].
> [DocumentId])
> ))
> -- @.p0: Input NVarChar (Size = 6; Prec = 0; Scale = 0) [Trish%]
> -- @.p1: Input NVarChar (Size = 2; Prec = 0; Scale = 0) [SH]
> -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build:
> 3.5.21022.8
> The problem is that this query returns rows, and it doesn't seem that
> it should (there are no DocumentContact rows that have a FirstName
> like Trish and ContactType = 'SH').
> I believe it's because the two Exists in the where clause.. I would
> think that rows have to be returned from BOTH Exists clauses for any
> rows to be returned... but it seems that if EITHER Exists returns
> rows, the entire query returns rows.
> Am I not understanding how EXISTS works?
> Thanks
> Andysql

Multiple Detail Rows Rendering in CSV

I'm not sure if this is the expected feature, but when I render a
simple table that has two detail rows to CSV the second row is appended
to the end of the first row. This does not happen in PDF/HTML/EXCEL.
Here how the report is designed:
Table
Column1|Column2|Column3
TextBox1|TextBox2|TextBox3
TextBox4|TextBox5|TextBox6
In Excel, the output is:
1,2,3
a,b,c
4,5,6
a,b,c
In CSV, the output is
1,2,3,a,b,c
4,5,6,a,b,c
Is there any way to get the output to work in CSV as it does in Excel?
Thanks
scottSome additional research and testing shows that this happens regardless
of if the two detail rows are in one table or two. If you put two
"single detail row" tables in seperate list boxes and have the list
boxes group by the same field you will end up with the same "look and
feel" in HTML/PDF/Excel view (Excel will be slightly different).
However, when saving to CSV the second table's row will be appended to
the end of the first table's row just like in the example above.
The dirty workaround to this is to save as Excel and then save as CSV.
However, the Excel render is much slower since there is a lot of extra
formatting and it produces a much larger file.
Is there any chance this is a bug? Am I setting something up wrong?
It also happens regardless of the encoding method (ASCII vs Unicode).
Is this by design?
Thanks for the help.
scott bieker