|
| |
|
|
xgd
Posts: 59
Joined: 5/1/2007
Status: offline
 |
RE: Joining 2 databases - 5/14/2007 9:22:06
Not really sure where you are going with this one.
quote:
ARN_InvHistoryHeader.trackingID FROM AR_19InvoiceHistoryTracking
I did try:
SELECT ARN_InvHistoryHeader.invoicenumber FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '4/30/2007' AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd(d,45,'4/30/2007')
Which returned no results, even though I know there are within that date range.
|
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 9:39:32
Maybe the problem is in the DateAdd function. What if you hard code there as well, i.e.,
SELECT ARN_InvHistoryHeader.invoicenumber FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '4/30/2007' AND ARN_InvHistoryHeader.InvoiceDate >= '4/23/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/14/2007 9:51:32
Yes, that returns all invoice numbers between 4/23 and 4/30
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 10:13:37
OK. Try putting the single quotes around the d in the dateadd function:
SELECT ARN_InvHistoryHeader.invoicenumber FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '4/30/2007' AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd('d',45,'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/14/2007 10:22:51
Okay;
If I swap the <= & >= and remove the single quotes I get the date plus the days in dateadd
If I leave the <= & >= as is I get no results.
No results, no errors:
SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '1/30/2007' AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd(d,45,'1/30/2007') order by invoicedate;
Results of 1/30 + 45 days
SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate >= '1/30/2007' AND ARN_InvHistoryHeader.InvoiceDate <= DateAdd(d,45,'1/30/2007') order by invoicedate
I had to add a few things to sort what the results where....
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 12:31:04
Hmmm. You were looking for invoices that were equal to a date entered or within the seven days before the date entered, right? So we should want it to be equal to or less than the day entered or greater than or equal to the date entered minus 7 days. I messed it up a bit when trying to hard code the dates. Anyway, putting the date variable back in, instead of hard coding, I think this should be it:
SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '::Invoice_Date::' AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd(d,-7,'::Invoice_Date::') order by 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/14/2007 12:45:35
Your query works if I hard code the date inside the DateAdd
SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '::Invoice_Date::' AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd(d,-7,'1/6/2007') order by invoicedate
If I put '::invoice_date::' in the DateAdd, the error is:
Database Results Wizard Error
Unable to find operator in query string. Query string currently is SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= ? AND ARN_InvHistoryHeader.InvoiceDate >= DateAdd(d,-7,'::invoice_date::') order by invoicedate;
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 13:03:38
And you do have a form that submits to the results page with a field named invoice_date?
_____________________________
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/14/2007 13:06:00
Yes, the beginning part of that query has ::invoice_date:: and that part works.
Just if I put ::invoice_date:: with the DateAdd field it will not.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 13:15:43
Just for kicks, what happens if you do this:
SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= "& Request.Form("invoice_date") &" AND ARN_InvHistoryHeader.InvoiceDate >= "& DateAdd(d,-7,'Request.Form("invoice_date")') &" order by ARN_InvHistoryHeader.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/14/2007 13:18:48
Syntax error:
>= "& DateAdd(d,-7,'Request.Form("invoice_date")')
-------------------^
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 13:21:22
Remove the single quotes from around Request.Form("invoice_date")
_____________________________
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/14/2007 13:23:27
Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'DateAdd'
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 13:53:24
Wonder if it would be easier if we just put the dates into a variable and then used the variable in the SQL string. Before your SQL string line, put this:
If IsDate(Request.Form("invoice_date")) Then
myDate = Request.Form("invoice_date")
myDateLess7 = DateAdd("d",-7,Request.Form(invoice_date"))
Else
myDate = "1/1/1900"
myDateLess7 = "12/24/1899"
End If
Then change the SQL to:
SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"' order by ARN_InvHistoryHeader.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/14/2007 14:02:43
Where exactly do you want the: quote:
If IsDate(Request.Form("invoice_date")) Then
myDate = Request.Form("invoice_date")
myDateLess7 = DateAdd("d",-7,Request.Form(invoice_date"))
Else
myDate = "1/1/1900"
myDateLess7 = "12/24/1899"
End If
Below "<%" and above the "fp_sQry"
If so, then I get a http 500 error
Unterminated string constant
quote:
myDateLess7 = DateAdd("d",-7,Request.Form(invoice_date"))
---------------------------------------------------------^
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 14:05:11
Sorry, missed the opening double quote before invoice_date:
myDateLess7 = DateAdd("d",-7,Request.Form("invoice_date"))
_____________________________
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/14/2007 14:20:42
That did it
It returns data between date requested & date requested -7
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 14:35:43
Great. So now the subquery works. We now need to add the main query back in.
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.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"') order by ARN_InvHistoryHeader.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/14/2007 14:46:44
Database Results Wizard Error
Description: [Microsoft][ODBC SQL Server Driver][SQL Server]Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 15:15:56
OK, keep in mind I have never used MS SQL. However, it appears you would need to replace NOT IN with NOT EXISTS
_____________________________
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/14/2007 15:37:04
SELECT distinct 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 NOT EXISTS (SELECT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"') order by ARN_InvHistoryHeader.invoicedate
Returns no results. Altough there should be.
If I change it to WHERE EXISTS then everything is returned
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 15:51:49
I am really grasping at straws now. Go back to the query that used NOT IN, but include Distinct in the subquery:
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 DISTINCT ARN_InvHistoryHeader.invoicenumber, ARN_InvHistoryHeader.invoicedate FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"') order by ARN_InvHistoryHeader.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.
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 16:11:30
Ahh, wait a minute. I think I see the problem. We should only be selected one column in the subquery. We don't need or want the invoice_date in the subquery. 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 DISTINCT ARN_InvHistoryHeader.invoicenumber FROM ARN_InvHistoryHeader WHERE ARN_InvHistoryHeader.InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"') order by ARN_InvHistoryHeader.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/14/2007 16:46:15
No errors, but it is still returning all data
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 17:06:05
I see further problems in the above. I am still having trouble keeping the three tables straight. Anyway, above I am comparing trackingIDs to invoicenumbers so of course none of the tracking IDs are in the subquery and therefore, all records are returned. We need to join two tables in the subquery. Here is my latest (probably not last) attempt.
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 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 ARN_InvHistoryHeader.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/14/2007 17:12:01
No results
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/14/2007 17:59:37
It is probably best to take a step back and again determine if the subquery works since I changed it significantly by joining another table. So, test this:
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 &"'
_____________________________
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/15/2007 13:52:43
Sorry for the delay, had a meltdown here.
That query works. It returns all Distinct TrackingID within the date range requested. (execpt for a few, checking into that...)
| |
|
|
|
BeTheBall
Posts: 6336
Joined: 6/21/2002
From: West Point Utah USA
Status: offline
|
RE: Joining 2 databases - 5/15/2007 14:01:18
OK. So does the following give you all the tracking IDs not in ARN_InvHistoryHeader for the given period?
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 .InvoiceDate <= '"& myDate &"' AND ARN_InvHistoryHeader.InvoiceDate >= '"& myDateLess7 &"')
_____________________________
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/15/2007 14:12:05
Error:
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
Found the ".invoicedate" and replaced it with "ARN_InvHistoryHeader.InvoiceDate"
Tested the sub query from it again to verfiy and it still works, which leaves
SELECT DISINCT UPS_Invoices.TrackingID
FROM UPS_Invoices
WHERE UPS_Invoices.TrackingID
NOT IN
| |
|
 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 
