|
| |
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
 |
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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 []
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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.
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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?
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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;"
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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::))
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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')) ;
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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'));
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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'));
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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')
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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'));
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 59
Joined: 5/1/2007
Status: offline
|
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
Posts: 6381
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
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')
_____________________________
Duane
Some people are like Slinkies . . . Not really good for anything . . . . . But they still bring a smile to your face when you push them down a flight of stairs.
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
