RE: Joining 2 databases (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

BeTheBall -> RE: Joining 2 databases (5/15/2007 14:26:25)

Lost me a little there. So you are saying the following does work?

SELECT DISINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE AR_19InvoiceHistoryTracking.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')



xgd -> RE: Joining 2 databases (5/15/2007 14:29:55)

Sorry about that.
It does not work. It returns:
Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '.'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers

I was trying to say that I tested the subquery within that query, just to check again and it still works.



xgd -> RE: Joining 2 databases (5/15/2007 14:57:29)

Alright, after repeated "head to wall" contact, I see that DISTINCT was spelled DISINCT

Now: 
SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

Works, but returns no results.



BeTheBall -> RE: Joining 2 databases (5/15/2007 15:12:17)

I assume the following does work:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices



xgd -> RE: Joining 2 databases (5/15/2007 15:14:48)

Yes,
If I limit fp_iMaxRecords



BeTheBall -> RE: Joining 2 databases (5/15/2007 15:21:49)

What about this:


SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT EXISTS (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"' AND UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID)



xgd -> RE: Joining 2 databases (5/15/2007 15:28:02)

That errors with:
Incorrect syntax near the keyword 'EXISTS'

I believe the correct syntax would be 
SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE NOT EXISTS.....

But that returns all data.



BeTheBall -> RE: Joining 2 databases (5/15/2007 15:38:33)

As you can tell by the number of posts in this thread, I am not prone to giving up. However, given my inexperience with SQL it's probably best to get someone else involved. I would think the following would bring back the desired result:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

In deperation, I have posted this question in another forum I used to visit quite regularly:

http://www.access-programmers.co.uk/forums/showthread.php?p=591029#post591029



BeTheBall -> RE: Joining 2 databases (5/16/2007 9:52:28)

So far, no helpful answer in the other forum. Please do the following. Run this query:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices

Print the results.

Then, run this query:

SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'

Print the results.

Are there items in the first query that are NOT in the second query?



xgd -> RE: Joining 2 databases (5/16/2007 13:09:55)

Yes, there are 'trackingid' found in UPS_Invoices that are not in AR_19InvoiceHistoryTracking
I had to adjust the queries a little to limit the results.
Query 1: 
SELECT DISTINCT UPS_Invoices.TrackingID, UPS_Invoices.[transaction date] FROM UPS_Invoices WHERE UPS_Invoices.[transaction date] <= '"& myDate &"' AND UPS_Invoices.[transaction date] >= '"& myDateLess7 &"' ORDER BY trackingid
Returns 1297 Results

Query 2: 
SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'  ORDER BY trackingid
Returns 647 Results



BeTheBall -> RE: Joining 2 databases (5/16/2007 14:38:48)

OK, well if the field is transaction date and not InvoiceDate, did you try adjusting the following query for the correct name of the date field?

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

In other words, if InvoiceDate should be Transaction Date, change the above query and test it again.



xgd -> RE: Joining 2 databases (5/16/2007 15:12:03)

[transaction date] cannot be counted on since it will not always match to invoicedate.
I wish that was the case, but it can differ by +/- a few days



BeTheBall -> RE: Joining 2 databases (5/16/2007 16:59:50)

I see. I may have misunderstood one semi-critical piece of information. When you say you need to look for invoices in UPS_Invoices that are not in ARN_InvHistoryHeader for a certain date range, are the UPS_Invoices also limited by the same date range? In other words, supposing you are only looking at invoices for 4/30/2007, are you trying to get the UPS_Invoices dated 4/30/2007 that are not in the ARN_InvHistoryHeader invoices also dated 4/30/2007 OR are you looking for all UPS_Invoices with any date, that are not in ARN_InvHistoryHeader invoices dated 4/30/2007? See the difference? If the date matters to both, then we would need to add a date criteria at the end of the SQL besides at the end of the subquery. In other words, this:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

would need to be:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"') AND UPS_Invoices.InvoiceDate <= '"& myDate &"' AND UPS_Invoices.InvoiceDate >= '"& myDateLess7 &"'

Doubt that will mysteriously make the query suddenly start working, but it's worth testing and it will speed up the query once (if [&o]) it does start working.



xgd -> RE: Joining 2 databases (5/16/2007 17:15:07)

Results should be:

Any ups_invoices.trackingid NOT IN AR_19InvoiceHistoryTracking BY ARN_InvHistoryHeader.invoicedate (range provided by previous DRW)
ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'


DRW output would be
ARN_InvHistoryHeader.invoicedate , ARN_InvHistoryHeader.invoicenumber , ups_invoices.trackingid , ups_invoices.ref1 , ups_invoices.ref2 , ups_invoices.ref3

Seems easy enough..
I just can't get my head wrapped around it..



BeTheBall -> RE: Joining 2 databases (5/16/2007 17:40:06)

OK, that's how I originally understood it. I wonder if the query will be just too big. You see above that a query on distinct UPS_Invoices.TrackingIDs returned 1297 records when you had a date range on it. However, based on what you have said, we will need to look at a list of all unique invoice numbers in the UPS_Invoice table. How may are there?



xgd -> RE: Joining 2 databases (5/16/2007 17:52:52)

There are over 200K records in UPS_invoices and it grows every week.
But, The first DRW pulls all DISTINT UPS_Invoices.invoicedate (Which has nothing to do with any other dates) it is just the date the UPS invoice was complied (weekly, on Saturday, so there are only 50 distinct invoicedates to date) 
<form method="POST" action="ups3.asp">
	<p><nobr>
<!--#include file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %><%
fp_sQry="SELECT DISTINCT invoicedate FROM ups_invoices order by invoicedate desc"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="newmas"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice=""
fp_sMenuValue="InvoiceDate"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<font face="Verdana">Please select a date to view.</font>   
<select NAME="Invoice_Date" SIZE="1">
<!--#include file="_fpclass/fpdbrgn1.inc"-->

<option><%=FP_FieldHTML(fp_rs,"InvoiceDate")%></option> 

<!--#include file="_fpclass/fpdbrgn2.inc"-->
</select>
</nobr><input type="submit" value="Submit" name="invoicedate"></p>
</form>


We are then using that chosen [date] through [date-7] to query data on the other tables. (To get a weeks worth of data)
So, I would think that we would only need to query the UPS_Invoice table with the date chosen

Does that make sense??



BeTheBall -> RE: Joining 2 databases (5/16/2007 18:44:13)

So the invoice date in UPS_Invoices is always a Saturday, while the invoice date in ARN_InvHistoryHeader can be any day of the week?



xgd -> RE: Joining 2 databases (5/16/2007 18:48:06)

Correct



BeTheBall -> RE: Joining 2 databases (5/16/2007 21:10:56)

Given that, the SQL needed to simply get the TrackingIDs that exist in UPS_Invoices but not in AR_19InvoiceHistoryTracking would need to be something like:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Tracking.InvoiceDate = '"& myDate &"' AND UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')



xgd -> RE: Joining 2 databases (5/17/2007 9:52:31)

That gives me the same results as: 
SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'  ORDER BY TRACKINGID;



BeTheBall -> RE: Joining 2 databases (5/17/2007 12:38:09)

How many records does this return?

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Tracking.InvoiceDate = '"& myDate &"'

And how many does this return:

SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"'



xgd -> RE: Joining 2 databases (5/17/2007 13:12:38)

I would like to back up 2 post due to a mistake I made.
You asked:
quote:

Given that, the SQL needed to simply get the TrackingIDs that exist in UPS_Invoices but not in AR_19InvoiceHistoryTracking would need to be something like:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Tracking.InvoiceDate = '"& myDate &"' AND UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

I said I tried but returned the same results.
It doesn't. 1) UPS_Tracking.invoicedate doesn't exist and 2) I replaced it with UPS_Invoices.invoicedate and it returns no results.
Sorry.

The answers for your other question is:
1. After I replaced UPS_Tracking.invoicedate it returns 1344 records
2. 647 Records



BeTheBall -> RE: Joining 2 databases (5/17/2007 13:18:28)

quote:

I will never be able to apologize enough but I made a grave error.
I realized that I gave you incorrect information. Here is the correct info.

1 DSN
3 Tables:
UPS_Invoices w/ fields (TrackingID, invoicedate, Ref1, Ref2, Ref3)
ARN_InvHistoryHeader w/ fields (invoicenumber, invoicedate)
AR_19InvoiceHistoryTracking w/ fields (TrackingID, invoicenumber)


You say, UPS_Invoices.invoicedate doesn't exist, but in the criteria above, you show invoicedate as a field in UPS_Invoices. Please clarify the structure of the three tables involved.



xgd -> RE: Joining 2 databases (5/17/2007 13:23:21)

UPS_Tracking.invoicedate is not valid
UPS_Invoices.invoicedate is correct



BeTheBall -> RE: Joining 2 databases (5/17/2007 13:27:03)

OK, I see what you mean, I used the wrong table name in the WHERE clause. So, did you try:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE UPS_Invoices.InvoiceDate = '"& myDate &"' AND UPS_Invoices.TrackingID NOT IN (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')



xgd -> RE: Joining 2 databases (5/17/2007 13:30:27)

No results No errors
Although it does "think" about it longer then usual.



BeTheBall -> RE: Joining 2 databases (5/17/2007 13:36:36)

Just for kicks what happens if you run the above query changing NOT IN to just IN? Technically, that should also return 647 records.



xgd -> RE: Joining 2 databases (5/17/2007 14:01:13)

Oddly enough it returns 577

I pulled the db up in access to double check the counts.
Distinct in AR_19InvoiceHistoryTracking is 651 
SELECT DISTINCT dbo_AR_19InvoiceHistoryTracking.TrackingID, dbo_ARN_InvHistoryHeader.InvoiceDate
FROM dbo_AR_19InvoiceHistoryTracking INNER JOIN dbo_ARN_InvHistoryHeader ON dbo_AR_19InvoiceHistoryTracking.InvoiceNumber = dbo_ARN_InvHistoryHeader.InvoiceNumber
WHERE (((dbo_ARN_InvHistoryHeader.InvoiceDate) Between #8/12/2006# And #8/20/2006#));


Distinct in UPS_Invoices is 1344 
SELECT DISTINCT dbo_ups_invoices.TrackingID, dbo_ups_invoices.InvoiceDate
FROM dbo_ups_invoices
WHERE (((dbo_ups_invoices.InvoiceDate)=#8/19/2006#));



BeTheBall -> RE: Joining 2 databases (5/17/2007 14:53:01)

May have something to do with the dates. Anyway, I think I may have this figured out. See if this works:

SELECT DISTINCT UPS_Invoices.TrackingID FROM UPS_Invoices WHERE NOT EXISTS (SELECT DISTINCT AR_19InvoiceHistoryTracking.TrackingID FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.invoicenumber = AR_19InvoiceHistoryTracking.invoicenumber WHERE UPS_Invoices.TrackingID = AR_19InvoiceHistoryTracking.TrackingID AND ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')

By the way, if TrackingIDs are unique in UPS_Invoices, then you don't need to use the first DISTINCT



xgd -> RE: Joining 2 databases (5/17/2007 15:50:52)

Returns all records (I think), received buffer overload, set maxRecords to 2500 and it returned 2500



Page: <<   < prev  1 2 [3] 4   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.15625