Pulling data that could be in one of 2 tables

External Since: Jul 27, 2006 Posts: 7

Note: I am using this to create a report with SRS

I need to pull some data that could possibly be sitting in one of two
tables. Table1 is the "Current" table that holds Orders that have not shipped
yet. Once all of items on the Order have shipped the Order moves to Table2
and an invoice is created. However, there are times where only part of the
order ships so there is an Invoice placed in Table2 but the Order still
remains in Table1. The invoice also has a field that always points to the
original Order number. And Table1 and Table2 have the same exact fields as
each other.

Here is what I need to do. I need to go through all of the Invoices in
Table2 for a certain time period, and Group them by Order Type (Which is
stored in the Order record). The problem is, if the order does not exist in
Table 2 yet, I need to get the Order type from Table1. I am sure how exactly
to do this. Is it something in the SQL query like an If or Case Statement?
Then I though about looking into Stored Procedures but I dont know much about
using them, especially in SRS so I would need some pointers.

Maybe something like:

Select *, If(Table1.OriginalOrderNum in Table2 then Table2.OrdType else
Table1.Ordtype)
From Table1
Where....

I know that is totally incorrect but I hope it clarifies things.

Once I have that squared away, I just need to sum up the Invoice amounts in
each Order Type Group, but that should be easy enough if I can figure out the
first part.

Was that clear enough? Let me know if you need any clarification.

External Since: Feb 21, 2007 Posts: 457

Matt,

I believe that you are looking for a UNION, such as:

SELECT OriginalOrderNum, Column1, Column2, Column3
FROM Table1
WHERE ...
UNION ALL
SELECT OriginalOrderNum, Column1, Column2, Column3
FROM Table2
WHERE OriginalOrderNum NOT IN
(SELECT OriginalOrderNum FROM Table1)
AND ...

Read about UNION and UNION ALL since they are not identical. The above
query can then be used with whatever other reporting criteria you have.

RLF

"Matt M" wrote in message

> Note: I am using this to create a report with SRS
>
> I need to pull some data that could possibly be sitting in one of two
> tables. Table1 is the "Current" table that holds Orders that have not
> shipped
> yet. Once all of items on the Order have shipped the Order moves to Table2
> and an invoice is created. However, there are times where only part of the
> order ships so there is an Invoice placed in Table2 but the Order still
> remains in Table1. The invoice also has a field that always points to the
> original Order number. And Table1 and Table2 have the same exact fields as
> each other.
>
> Here is what I need to do. I need to go through all of the Invoices in
> Table2 for a certain time period, and Group them by Order Type (Which is
> stored in the Order record). The problem is, if the order does not exist
> in
> Table 2 yet, I need to get the Order type from Table1. I am sure how
> exactly
> to do this. Is it something in the SQL query like an If or Case Statement?
> Then I though about looking into Stored Procedures but I dont know much
> about
> using them, especially in SRS so I would need some pointers.
>
> Maybe something like:
>
> Select *, If(Table1.OriginalOrderNum in Table2 then Table2.OrdType else
> Table1.Ordtype)
> From Table1
> Where....
>
> I know that is totally incorrect but I hope it clarifies things.
>
> Once I have that squared away, I just need to sum up the Invoice amounts
> in
> each Order Type Group, but that should be easy enough if I can figure out
> the
> first part.
>
> Was that clear enough? Let me know if you need any clarification.