Joining 2 databases (Full Version)

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



Message

xgd -> Joining 2 databases (5/1/2007 16:53:59)

What I have been trying to accomplish is the following.

1 DSN
2 Tables:
UPS_Invoices w/ fields (trackingID, date, Ref1, Ref2, Ref3)
ARNTracking w/ fields (trackingID, date, invoicenumber)

query by a 7 day daterange backwards with one date
return a result that shows all the UPS_Invoice.trackind not in ARNTracking
formated: ARNTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARNTracking.date



Any help would be greatly appreciated.



BeTheBall -> RE: Joining 2 databases (5/1/2007 19:06:13)

Welcome to Outfront. You will want to use a subquery in your WHERE clause. Something like this: 

SELECT ARNTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARNTracking.[date] FROM UPS_Invoices INNER JOIN ArnTracking ON ARNTracking.TrackingID=UPS_Invoices.TrackingID WHERE UPS_Invoice.trackingID NOT IN (SELECT trackingID FROM ARNTracking WHERE dateField <= dateProvided AND dateField >= DateDiff('d',dateProvided,7))


The items in bold will need to be changed to match your information. Note: date is a reserved word, hence the []



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

Thank you for the quick response!
I think I should have been a little more precise in how I am passing the date to this query. I am using a query populated dropdown list that gets distinct dates from UPS_Invoices 
<form method="POST" action="ups_invoice.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"
fp_sDefault="08192006"
fp_sNoRecords="No records returned."
fp_sDataConn="newmas"
fp_iMaxRecords=256
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
%>
<select NAME="Invoice_Date" SIZE="1">
<!--#include file="_fpclass/fpdbrgn1.inc"-->
	<option><%=FP_FieldHTML(fp_rs,"InvoiceDate")%></option>
<!--#include file="_fpclass/fpdbrgn2.inc"-->
</nobr><input type="submit" value="Submit" name="invoicedate"></p>
</form>

I then use a chosen date by a query like: 
<% 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 ups_invoices.[InvoiceDate], ups_invoices.[AccountNumber], ups_invoices.[InvoiceNumber], ups_invoices.[InvoiceAmount], sum(ups_invoices.[InvoiceAmount]) AS Total_Invoice FROM ups_invoices WHERE (""InvoiceDate"" =  '::Invoice_Date::') group by ups_invoices.[accountnumber], ups_invoices.[invoiceamount], ups_invoices.[InvoiceNumber], ups_invoices.[InvoiceDate];"
fp_sDefault=""
fp_sNoRecords="<tr><td colspan=4 align=""LEFT"" width=""100%"">No records returned.</td></tr>"
fp_sDataConn="newmas"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&Version=200&Recipient Number=202&AccountNumber=202&Account Country=202&InvoiceDate=135&InvoiceNumber=202&Invoice Type Code=202&Invoice Type Detail Code=202&Account Tax ID=202&Invoice Currency Code=202&InvoiceAmount=5&Transaction Date=135&Pickup Record Number=202&Lead Shipment Number=202&World Ease Number=202&Shipment Reference Number 1=202&Shipment Reference Number 2=202&Bill Option Code=202&Package Quantity=5&Oversize Quantity=5&TrackingID=202&Ref1=202&Ref2=202&Ref3=202&Ref4=202&Ref5=202&Entered Weight=5&Entered Weight Unit of Measure=202&Billed Weight=5&Billed Weight Unit of Measure=202&Container Type=202&Billed Weight Type=202&Package Dimensions=202&Zone=5&Charge Category Code=202&Charge Category Detail Code=202&Charge Source=202&Type Code 1=5&Type Detail Code 1=202&Type Detail Value 1=202&Type Code 2=202&Type Detail Code 2=202&Type Detail Value 2=202&Charge Classification Code=202&Charge Description Code=202&Charge Description=202&Charged Unit Quantity=5&Basis Currency Code=202&Basis Value=5&Tax Indicator=5&Transaction Currency Code=202&Incentive Amount=5&Net Amount=5&Miscellaneous Currency Code=202&Miscellaneous Incentive Amount=5&Miscellaneous Net Amount=5&Alternate Invoicing Currency Code=202&Alternate Invoice Amount=5&Invoice Exchange Rate=5&Tax Variance Amount=5&Currency Variance Amount=5&Invoice Level Charge=5&Invoice Due Date=135&Alternate Invoice Number=202&Store Number=202&Customer Reference Number=202&Sender Name=202&Sender Company Name=202&Sender Address Line 1=202&Sender Address Line 2=202&Sender City=202&Sender State=202&Sender Postal=5&Sender Country=202&Receiver Name=202&Receiver Company Name=202&Receiver Address Line 1=202&Receiver Address Line 2=202&Receiver City=202&Receiver State=202&Receiver Postal=5&Receiver Country=202&Third Party Name=202&Third Party Company Name=202&Third Party Address Line 1=202&Third Party Address Line 2=202&Third Party City=202&Third Party State=202&Third Party Postal=202&Third Party Country=202&Sold To Name=202&Sold To Company Name=202&Sold To Address Line 1=202&Sold To Address Line 2=202&Sold To City=202&Sold To State=202&Sold To Postal=202&Sold To Country=202&Miscellaneous Address Qual 1=202&Miscellaneous Address 1 Name=202&Miscellaneous Address 1 Company Name=202&Miscellaneous Address 1 Address Line 1=202&Miscellaneous Address 1 Address Line 2=202&Miscellaneous Address 1 City=202&Miscellaneous Address 1 State=202&Miscellaneous Address 1 Postal=202&Miscellaneous Address 1 Country=202&Miscellaneous Address Qual 2=202&Miscellaneous Address 2 Name=202&Miscellaneous Address 2 Company Name=202&Miscellaneous Address 2 Address Line 1=202&Miscellaneous Address 2 Address Line 2=202&Miscellaneous Address 2 City=202&Miscellaneous Address 2 State=202&Miscellaneous Address 2 Postal=202&Miscellaneous Address 2 Country=202&Shipment Date=202&Shipment Export Date=202&Shipment Import Date=202&Entry Date=202&Direct Shipment Date=202&Shipment Delivery Date=202&Shipment Release Date=202&Cycle Date=202&EFT Date=202&Validation Date=202&Entry Port=202&Entry Number=202&Export Place=202&Shipment Value Amount=5&Shipment Description=202&Entered Currency Code=202&Customs Number=202&Exchange Rate=5&Master Air Waybill Number=202&EPU=202&Entry Type=202&CPC Code=202&Line Item Number=5&Goods Description=202&Entered Value=5&Duty Amount=5&Weight=202&Unit of Measure=202&Item Quantity=5&Item Quantity Unit of Measure=202&Import Tax ID=202&Declaration Number=202&Carrier Name=202&CCCD Number=202&Cycle Number=202&Foreign Trade Reference Number=202&Job Number=202&Transport Mode=202&Tax Type=202&Tariff Code=202&Tariff Rate=5&Tariff Treatment Number=202&Contact Name=202&Class Number=202&Document Type=202&Office Number=202&Document Number=202&Duty Value=5&Total Value for Duty=5&Excise Tax Amount=5&Excise Tax Rate=5&GST Amount=5&GST Rate=5&Order In Council=202&Origin Country=202&SIMA Access=5&Tax Value=5&Total Customs Amount=5&Miscellanous Line 1=202&Miscellanous Line 2=202&Miscellanous Line 3=202&Miscellanous Line 4=202&Miscellanous Line 5=202&Miscellanous Line 6=5&Miscellanous Line 7=202&Miscellanous Line 8=202&Miscellanous Line 9=202&Miscellanous Line 10=202&UPS Internal Use=202&"
fp_iDisplayCols=4
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>



<!--#include file="_fpclass/fpdbrgn1.inc"-->
		<tr>
			<td>
<%=FP_FieldVal(fp_rs,"AccountNumber")%></td>
			<td>
<%=FP_FieldVal(fp_rs,"InvoiceDate")%></td>
			<td>
<%=FP_FieldVal(fp_rs,"InvoiceNumber")%></td>
			<td width="115">
			<p align="right">
			<%=formatcurrency(FP_FieldVal(fp_rs,"InvoiceAmount"),2)%>
			</td>
		</tr>



<!--#include file="_fpclass/fpdbrgn2.inc"-->

		</table>
</tbody>
</table>


I was hoping that I could use that same distinct date query to choose the date needed in my original post.
So, by choosing a date the query would return that date and the past 6 days.

Thanks again !



BeTheBall -> RE: Joining 2 databases (5/2/2007 10:08:47)

OK. Then:

SELECT ARNTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARNTracking.[date] FROM UPS_Invoices INNER JOIN ArnTracking ON ARNTracking.TrackingID=UPS_Invoices.TrackingID WHERE UPS_Invoices.trackingID NOT IN (SELECT trackingID FROM ARNTracking WHERE UPS_Invoices.InvoiceDate <= ::InvoiceDate:: AND UPS_Invoices.InvoiceDate >= DateAdd('d',7,::InvoiceDate::))

Notice, I changed DateDiff to DateAdd. I had a mental breakdown on the previous post. Now, I am not sure the DRW will allow you to put ::InvoiceDate:: inside the DateAdd function like I have done above, but give it a try. If it doesn't, we can find another way of doing it.



xgd -> RE: Joining 2 databases (5/2/2007 10:26:58)

Unable to find operator in query string.
Query string currently is SELECT ARN_InvHistoryHeader.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.[invoicedate] FROM UPS_Invoices INNER JOIN ARN_InvHistoryHeader ON ARN_InvHistoryHeader.TrackingID=UPS_Invoices.TrackingID WHERE UPS_Invoices.trackingID NOT IN (SELECT trackingID FROM ARN_InvHistoryHeader WHERE UPS_Invoices.InvoiceDate <= ? AND UPS_Invoices.InvoiceDate >= DateAdd('d',7,::InvoiceDate::))



xgd -> RE: Joining 2 databases (5/2/2007 11:08:57)

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)

Same query as mentioned before
return a result that shows all the UPS_Invoices.trackind not in AR_19InvoiceHistoryTracking
formated: AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate




BeTheBall -> RE: Joining 2 databases (5/8/2007 17:53:05)

Give this a whirl:

SELECT AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM (AR_19InvoiceHistoryTracking.invoicenumber INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber) INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID
WHERE UPS_Invoices.trackingID NOT IN (SELECT trackingID FROM ARNTracking WHERE
UPS_Invoices.InvoiceDate <= ::InvoiceDate:: AND UPS_Invoices.InvoiceDate >= DateAdd('d',7,::InvoiceDate::))

We will probably have to take out pieces and put them back in to get it up and going.



xgd -> RE: Joining 2 databases (5/10/2007 15:54:38)

Thanks for the reply.
The operator ::invoice_date:: is not beinhg recognized, so I went ahead and removed the WHERE statement.
So, what i am left with is: 
"SELECT AR_19InvoiceHistoryTracking.InvoiceNumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM (AR_19InvoiceHistoryTracking.invoicenumber INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber) INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID;"


Then I receive this result:

Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'AR_19InvoiceHistoryTracking.invoicenumber'.
Number: -2147217865 (0x80040E37)
Source: Microsoft OLE DB Provider for ODBC Drivers

Thoughts?

Thanks !



BeTheBall -> RE: Joining 2 databases (5/10/2007 15:59:42)

That would make me question the existence of a table named AR_19InvoiceHistoryTracking

or, if that table does exist, are you sure it contains a field named invoicenumber?



xgd -> RE: Joining 2 databases (5/10/2007 16:35:30)

That was my first thought as well. Did I have another 'typo'
So I placed a simple
SELECT * FROM AR_19InvoiceHistoryTracking
....
....
<%=FP_FieldVal(fp_rs,"InvoiceNumber")%>

And received results.



BeTheBall -> RE: Joining 2 databases (5/10/2007 16:40:39)

What, if anything, does this return:

"SELECT * FROM (AR_19InvoiceHistoryTracking.invoicenumber INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber) INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID;"

And this?

SELECT * FROM UPS_Invoices INNER JOIN ARN_InvHistoryHeader ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID;"



xgd -> RE: Joining 2 databases (5/10/2007 16:49:09)

1:
Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'AR_19InvoiceHistoryTracking.invoicenumber'.
Number: -2147217865 (0x80040E37)
Source: Microsoft OLE DB Provider for ODBC Drivers

2:
Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]The column prefix 'AR_19InvoiceHistoryTracking' does not match with a table name or alias name used in the query.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



BeTheBall -> RE: Joining 2 databases (5/10/2007 16:57:28)

Oops. That second one wasn't what I wanted. Try:

SELECT * FROM UPS_Invoices INNER JOIN AR_19InvoiceHistoryTracking ON AR_19InvoiceHistoryTracking.TrackingID = UPS_Invoices.TrackingID



xgd -> RE: Joining 2 databases (5/10/2007 17:13:28)

RESULTS !
All though it took a long time to run the query, results did come through.

Looks like we are 1/2 way there !



BeTheBall -> RE: Joining 2 databases (5/10/2007 18:05:48)

quote:

Looks like we are 1/2 way there !


Quite the optimist aren't we. Hard to say how close we are. What does this one do:

SELECT * FROM ARN_InvHistoryHeader INNER JOIN AR_19InvoiceHistoryTracking ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber



xgd -> RE: Joining 2 databases (5/10/2007 18:15:54)

Again, results.
I also tried this: 
fp_sQry="SELECT DISTINCT UPS_Invoices.trackingid, UPS_Invoices.ref1, UPS_Invoices.ref2, UPS_Invoices.ref3, UPS_Invoices.invoicedate FROM UPS_Invoices INNER JOIN AR_19InvoiceHistoryTracking ON AR_19InvoiceHistoryTracking.TrackingID = UPS_Invoices.TrackingID WHERE (""InvoiceDate"" =  '::Invoice_Date::');"
....
....
<%=FP_FieldVal(fp_rs,"trackingid")%>
<%=FP_FieldVal(fp_rs,"invoicedate")%>
<%=FP_FieldVal(fp_rs,"REF1")%>
<%=FP_FieldVal(fp_rs,"REF2")%>
<%=FP_FieldVal(fp_rs,"REF3")%>

Which works with the invoice date query. But it returns all records, Not just results that are in UPS_Invoice and not in AR_19InvoiceTrackingHistory



BeTheBall -> RE: Joining 2 databases (5/10/2007 18:33:31)

OK. Let's go back to my query a few posts back and remove the brackets in the JOINs.

SELECT AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM AR_19InvoiceHistoryTracking.invoicenumber INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID
WHERE UPS_Invoices.trackingID NOT IN (SELECT trackingID FROM ARNTracking WHERE
UPS_Invoices.InvoiceDate <= ::InvoiceDate:: AND UPS_Invoices.InvoiceDate >= DateAdd('d',7,::InvoiceDate::))



xgd -> RE: Joining 2 databases (5/11/2007 9:33:52)

It returned a "?" on the 1st instance of the operator ::invoice_date::

Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM AR_19InvoiceHistoryTracking.invoicenumber INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID WHERE UPS_Invoices.trackingID NOT IN (SELECT trackingID FROM ARN_InvHistoryHeader WHERE UPS_Invoices.InvoiceDate <= ? AND UPS_Invoices.InvoiceDate >= DateAdd('d',7,::Invoice_Date::)) ;



BeTheBall -> RE: Joining 2 databases (5/11/2007 11:09:11)

What happens if you hard code a date. For example:

Unable to find operator in query string. Query string currently is SELECT AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM AR_19InvoiceHistoryTracking.invoicenumber INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID WHERE UPS_Invoices.trackingID NOT IN (SELECT trackingID FROM ARN_InvHistoryHeader WHERE UPS_Invoices.InvoiceDate <= '4/30/2007' AND UPS_Invoices.InvoiceDate >= DateAdd('d',7,'4/30/2007')) ;




xgd -> RE: Joining 2 databases (5/11/2007 11:58:12)

First I received:
Invalid parameter 1 specified for dateadd
So I changed 'd' to d

Then I received:

Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'AR_19InvoiceHistoryTracking.invoicenumber'.
Number: -2147217865 (0x80040E37)
Source: Microsoft OLE DB Provider for ODBC Drivers



BeTheBall -> RE: Joining 2 databases (5/11/2007 12:12:37)

Oops. I had an extra .invoicenumber right after the FROM. Try this:

SELECT AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID WHERE UPS_Invoices.trackingID NOT IN (SELECT trackingID FROM ARN_InvHistoryHeader WHERE UPS_Invoices.InvoiceDate <= '4/30/2007' AND UPS_Invoices.InvoiceDate >= DateAdd(d,7,'4/30/2007'));




xgd -> RE: Joining 2 databases (5/11/2007 12:15:41)


Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Ambiguous column name 'TrackingID'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers



BeTheBall -> RE: Joining 2 databases (5/11/2007 13:07:47)

Maybe:

SELECT AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID WHERE UPS_Invoices.trackingID NOT IN (SELECT ARN_InvHistoryHeader.trackingID FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '4/30/2007' AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd(d,7,'4/30/2007'));



xgd -> RE: Joining 2 databases (5/11/2007 13:27:51)


Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'trackingID'.
Number: -2147217900 (0x80040E14)
Source: Microsoft OLE DB Provider for ODBC Drivers




BeTheBall -> RE: Joining 2 databases (5/11/2007 19:53:36)

Does this work?

SELECT trackingID FROM ARN_InvHistoryHeader WHERE InvoiceDate <= '4/30/2007' AND InvoiceDate >= DateAdd(d,7,'4/30/2007')



xgd -> RE: Joining 2 databases (5/11/2007 20:17:39)

That won't work because there is no trackingID in that table:
quote:


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)



BeTheBall -> RE: Joining 2 databases (5/11/2007 22:25:11)

quote:

ORIGINAL: xgd

That won't work because there is no trackingID in that table:


Hence the error we were getting. Sorry, I am having such trouble keeping the tables and field straight.

Where does this one get you?

SELECT AR_19InvoiceHistoryTracking.invoicenumber, UPS_Invoices.Ref1, UPS_Invoices.Ref2, UPS_Invoices.Ref3, ARN_InvHistoryHeader.invoicedate FROM AR_19InvoiceHistoryTracking INNER JOIN ARN_InvHistoryHeader ON AR_19InvoiceHistoryTracking.invoicenumber=ARN_InvHistoryHeader.invoicenumber INNER JOIN UPS_Invoices ON UPS_Invoices.TrackingID=AR_19InvoiceHistoryTracking.TrackingID WHERE UPS_Invoices.trackingID NOT IN (SELECT AR_19InvoiceHistoryTracking.trackingID FROM AR_19InvoiceHistoryTracking WHERE AR_19InvoiceHistoryTracking.InvoiceDate <= '4/30/2007' AND AR_19InvoiceHistoryTracking.InvoiceDate >= DateAdd(d,7,'4/30/2007'));



xgd -> RE: Joining 2 databases (5/11/2007 22:34:27)

No worries
This one has been confusing me for a while now
I will give a try first thing tomorrow
I do want to thank you again for all your help

Greg



xgd -> RE: Joining 2 databases (5/12/2007 12:08:36)

There is no AR_19InvoiceHistoryTracking.InvoiceDate
So I replaced that with ARN_InvHistoryHeader.invoicedate

It does not give an error, it does try to run the query and just sites there.
I also tied replacing the WHERE statement with
WHERE ARN_InvHistoryHeader.InvoiceDate = '4/30/2007'
to see if it would run the query, but ended up witht the same results.



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

Let's test just the subquery:

SELECT ARN_InvHistoryHeader.trackingID FROM AR_19InvoiceHistoryTracking WHERE ARN_InvHistoryHeader.InvoiceDate <= '4/30/2007' AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd(d,7,'4/30/2007')



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

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.1074219