Re: Pulling data that could be in one of 2 tables

Ok this may help. The query below works if the Orignal Order is located in
Table1 (even though my CASE statement seems backwards to me). If it was moved
to table2 then I just get a Null value. But I think this might give you a
better idea of what I need to do.

I left the original table names in the query:
Table1 = SOP10100
Table2 = SOP30200


SELECT SOP30200.SOPTYPE, SOP30200.SOPNUMBE, SOP30200.ORIGNUMB,
SOP30200.DOCID, SOP30200.DOCDATE, SOP30200.INVODATE,
SOP30200.MSTRNUMB, SOP30200.DOCAMNT, CASE WHEN
(SELECT DOCID
FROM SOP10100
WHERE SOP10100.SOPNUMBE = ORIGNUMB) IS NOT
NULL THEN SOP30200.DOCID ELSE SOP10100.DOCID END AS ORIGDOCID
FROM SOP30200 LEFT OUTER JOIN
SOP10100 ON SOP30200.ORIGNUMB = SOP10100.SOPNUMBE
WHERE (SOP30200.DOCDATE >= CONVERT(DATETIME, '2007-10-01 00:00:00',
102)) AND (SOP30200.SOPTYPE = '3')


"Russell Fields" wrote:

Matt,

I am having a little trouble following your narrative. (Sorry.) Sample
tables and sample data often help with this problem.

If I understand correctly, for anything you are querying there is always a
row in Table2 and sometimes a row in Table1. Only if Table2 does not have a
value in the OrderType column do you need to look in Table1. Is this more
what you are looking for?

Select t2.*,
CASE
WHEN t2.OrderType IS NOT NULL THEN
t2.OrderType
ELSE
t1.OrderType
END AS OrderType
From Table2 t2 LEFT OUTER JOIN Table1 t1
ON t2.OriginalOrderNum= t1.OriginalOrderNum
Where....


RLF

"Matt M" <MattM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7A051347-94E0-4F1E-A4A3-1E831575401A@xxxxxxxxxxxxxxxx
Sounds promising but I'm not sure if it will work.

The Invoices I am looking up are in Table2. What I need to do is pull all
invoices for let's say the month of September, and look up the
corresponding
Original Order with each Invoices Original Order Number in Table2 first.
If
it is not in Table2, I need to look for it in Table1. Once I find the
Order,
I need to be able grab the Order Type from the Order and the Invoice
Amount
which is on the Invoice. Will this still work.

Summary:
Invoices and Orders are in Table2
If Order has not fully shipped it may still be in Table1
Need to pull the Invoice Ammount from Invoice and the Order Type from the
Invoices original Order





"Russell Fields" wrote:

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" <MattM@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:84AF08D4-F845-4412-A7BC-A429110F736E@xxxxxxxxxxxxxxxx
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.






.

Relevant Pages

  • Re: Pulling data that could be in one of 2 tables
    ... value in the OrderType column do you need to look in Table1. ... Original Order with each Invoices Original Order Number in Table2 first. ... I need to be able grab the Order Type from the Order and the Invoice ...
    (microsoft.public.sqlserver.mseq)
  • Re: Pulling data that could be in one of 2 tables
    ... value in the OrderType column do you need to look in Table1. ... Original Order with each Invoices Original Order Number in Table2 first. ... I need to be able grab the Order Type from the Order and the Invoice ...
    (microsoft.public.sqlserver.mseq)
  • Re: Pulling data that could be in one of 2 tables
    ... Invoices from Table2 and find the corresponding DOCID for its Original Order. ... Invoice amount from table2 so I can add it up in SRS. ... value in the OrderType column do you need to look in Table1. ...
    (microsoft.public.sqlserver.mseq)
  • Re: Pulling data that could be in one of 2 tables
    ... Original Order with each Invoices Original Order Number in Table2 first. ... I need to be able grab the Order Type from the Order and the Invoice Amount ... If Order has not fully shipped it may still be in Table1 ...
    (microsoft.public.sqlserver.mseq)
  • Re: Tricky Visual Basic Code help...
    ... moves the current record into a new record in table2. ... this with a little VBA code and a couple custom queries. ... Set a "pushFlag" column in the record in table1 to a specific ... If a duplicate is not found, ...
    (microsoft.public.access.formscoding)