Complicated query

Hi,
I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.
I need to write a query which should give me records :
1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.
2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.
Please help me to resolve this complicated query.
Thanks
Nick--these 2 table need to have the same number of columns and datatypes
if you want to use union
--take the union out if they don't have the same number of columns and
datatypes
--you can also do this with 2 left or right joins or even a full outer
join
select *,'missing in Trade' as Missing
from Trade t
where not exists (select * from Cons c where c.Exc_Ref=t.Exc_Ref)
union all
select *,'missing in Cons' as Missing
from Cons c
where not exists (select * from Trade t where c.Exc_Ref=t.Exc_Ref)
--column mismatch
select * from Trade t
join Cons c on c.Exc_Ref=t.Exc_Ref
where c.Qty <> t.Qty
or c.Date <> t.Date
...
...
...
<rest of columns>
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx|||On Sep 6, 12:29 pm, Nick <nachiket.shirwal...@.gmail.com> wrote:
> Hi,
> I have two tables Trade table and Cons table. Records are inserted in
> both the tables independent of each other. There are fields like
> Exc_Ref, Qty, Date in both the tables.
> I need to write a query which should give me records :
> 1. Where there is missing Exc_Ref value in either of the table. i.e.
> If Trade table has a Exc_Ref value but missing in Cons table then that
> record should be displayed. Similarly if Cons has a Exc_Ref value
> which is not found in Trade table then that too should be displayed.
> 2. In case where both the tables have matching Exc_Ref data then it
> should display the record only when the remaining column does not
> match like Qty or Date.
> Please help me to resolve this complicated query.
> Thanks
> Nick
select * from Trade t
FOOL OUTER join Cons c on c.Exc_Ref=t.Exc_Ref
AND c.Qty = t.Qty
AND c.Date = t.Date|||"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1189110636.605076.236920@.r34g2000hsd.googlegroups.com...
>.
> select * from Trade t FOOL OUTER join Cons c ...
It is no easy task to personalize a query. Good job:)
www.beyondsql.blogspot.com

Complicated query

Hi,

I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.

I need to write a query which should give me records :

1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.

2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.

Please help me to resolve this complicated query.

Thanks
NickSomething along these lines should do it.

SELECT COALESCE(A.Exc_Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
...
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9

This assumes that Exc_Ref is the unique key to both tables.

Roy Harvey
Beacon Falls, CT

On Thu, 06 Sep 2007 10:29:30 -0700, Nick
<nachiket.shirwalkar@.gmail.comwrote:

Quote:

Originally Posted by

>Hi,
>
>I have two tables Trade table and Cons table. Records are inserted in
>both the tables independent of each other. There are fields like
>Exc_Ref, Qty, Date in both the tables.
>
>I need to write a query which should give me records :
>
>1. Where there is missing Exc_Ref value in either of the table. i.e.
>If Trade table has a Exc_Ref value but missing in Cons table then that
>record should be displayed. Similarly if Cons has a Exc_Ref value
>which is not found in Trade table then that too should be displayed.
>
>2. In case where both the tables have matching Exc_Ref data then it
>should display the record only when the remaining column does not
>match like Qty or Date.
>
>Please help me to resolve this complicated query.
>
>Thanks
>Nick

|||On 6 Sep, 20:06, Roy Harvey <roy_har...@.snet.netwrote:

Quote:

Originally Posted by

Something along these lines should do it.
>
SELECT COALESCE(A.Exc_Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
...
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9
>
This assumes that Exc_Ref is the unique key to both tables.
>
Roy Harvey
Beacon Falls, CT
>
On Thu, 06 Sep 2007 10:29:30 -0700, Nick
>
>
>
<nachiket.shirwal...@.gmail.comwrote:

Quote:

Originally Posted by

Hi,

>

Quote:

Originally Posted by

I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.

>

Quote:

Originally Posted by

I need to write a query which should give me records :

>

Quote:

Originally Posted by

1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.

>

Quote:

Originally Posted by

2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.

>

Quote:

Originally Posted by

Please help me to resolve this complicated query.

>

Quote:

Originally Posted by

Thanks
Nick- Hide quoted text -

>
- Show quoted text -

Thanks Roy ! Your solution is too perfect.

Thanks

Comparing two tables

I have two tables of "trade" data, one comes from out accounting
system, the other from text files sent to us by the counterparties
(brokers). I attacked the problem by creating two views that map the
data into a common format. I then used both of these in a UNION
query...
SELECT *
FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity IS NULL OR c.quantity <> p.quantity
UNION
SELECT *
FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
The problem is that the UNION causes a temp table to be created. When
I try to add filters to reduce the number of rows that are returned,
the filter is applied to the temp table, not the views, so every
single query takes the same amount of time -- about 20 seconds, and
that's simply too long.
In the past I have had excellent results by replacing views with the
SQL that makes the view. So in this case, "vCounterpartyTrades as c"
is replaced by "( all of the SQL in that view ) as c". That way I can
apply any WHERE filters directly in the SQL by splicing in the WHERE
in VBA, and everything gets a lot faster.
But the problem here is that both views appear twice, on either side
of the UNION. That not only dramatically expands the resulting SQL
(both the views are pretty long as it is) but means both are run
twice. So to really speed this up I think I need to remove the UNION
and replace it with some sort of join...
SELECT *
FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity =
0)
OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
...is the obvious solution, but this eliminates anything where one
side or the other is null. I know there's a simple solution to this
using other join styles, but I have very little familiarity with them.
Can someone help me out?
Maury
p.s. Is anyone else having problems with MS's interface to these
groups? I can no longer post via their web site, because the post form
will not pop up.
> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
I don't fully understand your requirements but is it possible to use UNION
ALL instead of just UNION? I would expect that to reduce the amount of work
that needs to be performed. Another option is to use a FULL JOIN instead of
the UNION of the LEFT/RIGHT JOINs. Maybe something like:
SELECT *
FROM vCounterpartyTrades AS c
FULL JOIN vOurTrades AS p ON
c.portfolioId = p.portfolioId AND
c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE
p.quantity IS NULL OR
c.quantity <> p.quantity OR
(p.quantity <> 0 AND
(c.quantity IS NULL OR c.quantity = 0)
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:a7f0ed7f-982e-4c41-9123-671e1958e37b@.f10g2000hsf.googlegroups.com...
>I have two tables of "trade" data, one comes from out accounting
> system, the other from text files sent to us by the counterparties
> (brokers). I attacked the problem by creating two views that map the
> data into a common format. I then used both of these in a UNION
> query...
> SELECT *
> FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity IS NULL OR c.quantity <> p.quantity
> UNION
> SELECT *
> FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
> The problem is that the UNION causes a temp table to be created. When
> I try to add filters to reduce the number of rows that are returned,
> the filter is applied to the temp table, not the views, so every
> single query takes the same amount of time -- about 20 seconds, and
> that's simply too long.
> In the past I have had excellent results by replacing views with the
> SQL that makes the view. So in this case, "vCounterpartyTrades as c"
> is replaced by "( all of the SQL in that view ) as c". That way I can
> apply any WHERE filters directly in the SQL by splicing in the WHERE
> in VBA, and everything gets a lot faster.
> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
> SELECT *
> FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
> WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity =
> 0)
> OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
> ...is the obvious solution, but this eliminates anything where one
> side or the other is null. I know there's a simple solution to this
> using other join styles, but I have very little familiarity with them.
> Can someone help me out?
> Maury
> p.s. Is anyone else having problems with MS's interface to these
> groups? I can no longer post via their web site, because the post form
> will not pop up.

Comparing two tables

I have two tables of "trade" data, one comes from out accounting
system, the other from text files sent to us by the counterparties
(brokers). I attacked the problem by creating two views that map the
data into a common format. I then used both of these in a UNION
query...
SELECT *
FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity IS NULL OR c.quantity <> p.quantity
UNION
SELECT *
FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
The problem is that the UNION causes a temp table to be created. When
I try to add filters to reduce the number of rows that are returned,
the filter is applied to the temp table, not the views, so every
single query takes the same amount of time -- about 20 seconds, and
that's simply too long.
In the past I have had excellent results by replacing views with the
SQL that makes the view. So in this case, "vCounterpartyTrades as c"
is replaced by "( all of the SQL in that view ) as c". That way I can
apply any WHERE filters directly in the SQL by splicing in the WHERE
in VBA, and everything gets a lot faster.
But the problem here is that both views appear twice, on either side
of the UNION. That not only dramatically expands the resulting SQL
(both the views are pretty long as it is) but means both are run
twice. So to really speed this up I think I need to remove the UNION
and replace it with some sort of join...
SELECT *
FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
c.custodianId = p.custodianId
AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity = 0)
OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
...is the obvious solution, but this eliminates anything where one
side or the other is null. I know there's a simple solution to this
using other join styles, but I have very little familiarity with them.
Can someone help me out?
Maury
p.s. Is anyone else having problems with MS's interface to these
groups? I can no longer post via their web site, because the post form
will not pop up.> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
I don't fully understand your requirements but is it possible to use UNION
ALL instead of just UNION? I would expect that to reduce the amount of work
that needs to be performed. Another option is to use a FULL JOIN instead of
the UNION of the LEFT/RIGHT JOINs. Maybe something like:
SELECT *
FROM vCounterpartyTrades AS c
FULL JOIN vOurTrades AS p ON
c.portfolioId = p.portfolioId AND
c.cusip = p.cusip AND
c.custodianId = p.custodianId
WHERE
p.quantity IS NULL OR
c.quantity <> p.quantity OR
(p.quantity <> 0 AND
(c.quantity IS NULL OR c.quantity = 0)
)
Hope this helps.
Dan Guzman
SQL Server MVP
"Maury Markowitz" <maury.markowitz@.gmail.com> wrote in message
news:a7f0ed7f-982e-4c41-9123-671e1958e37b@.f10g2000hsf.googlegroups.com...
>I have two tables of "trade" data, one comes from out accounting
> system, the other from text files sent to us by the counterparties
> (brokers). I attacked the problem by creating two views that map the
> data into a common format. I then used both of these in a UNION
> query...
> SELECT *
> FROM vCounterpartyTrades AS c LEFT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity IS NULL OR c.quantity <> p.quantity
> UNION
> SELECT *
> FROM vCounterpartyTrades AS c RIGHT JOIN vOurTrades AS p
> ON c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> WHERE p.quantity <> 0 AND (c.quantity IS NULL OR c.quantity = 0)
> The problem is that the UNION causes a temp table to be created. When
> I try to add filters to reduce the number of rows that are returned,
> the filter is applied to the temp table, not the views, so every
> single query takes the same amount of time -- about 20 seconds, and
> that's simply too long.
> In the past I have had excellent results by replacing views with the
> SQL that makes the view. So in this case, "vCounterpartyTrades as c"
> is replaced by "( all of the SQL in that view ) as c". That way I can
> apply any WHERE filters directly in the SQL by splicing in the WHERE
> in VBA, and everything gets a lot faster.
> But the problem here is that both views appear twice, on either side
> of the UNION. That not only dramatically expands the resulting SQL
> (both the views are pretty long as it is) but means both are run
> twice. So to really speed this up I think I need to remove the UNION
> and replace it with some sort of join...
> SELECT *
> FROM *a whole bunch of SQL* AS c, *another whole bunch of SQL* AS p
> WHERE c.portfolioId = p.portfolioId AND c.cusip = p.cusip AND
> c.custodianId = p.custodianId
> AND (p.quantity IS NOT null AND (c.quantity IS NULL OR c.quantity => 0)
> OR ( p.quantity IS NULL OR c.quantity <> p.quantity))
> ...is the obvious solution, but this eliminates anything where one
> side or the other is null. I know there's a simple solution to this
> using other join styles, but I have very little familiarity with them.
> Can someone help me out?
> Maury
> p.s. Is anyone else having problems with MS's interface to these
> groups? I can no longer post via their web site, because the post form
> will not pop up.|||Ok, I actually found a great guide on all of this and got it working.
The key was to use "FULL OUTER JOIN" in the FROM. This makes all rows
from either side appear. After that there was a lot of tweaking of the
WHERE, but I did manage to get it working in the end. I still have to
use the two subqueries, but by moving the filters into their WHEREs
the performance is excellent, basically instant.
Maury