RE: Joining 2 databases (Full Version)

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



Message

xgd -> 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 -> 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'



xgd -> RE: Joining 2 databases (5/14/2007 9:51:32)

Yes, that returns all invoice numbers between 4/23 and 4/30



BeTheBall -> 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')



xgd -> 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 -> 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



xgd -> 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 -> 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?



xgd -> 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 -> 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;



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

Syntax error: 
>= "& DateAdd(d,-7,'Request.Form("invoice_date")')
-------------------^



BeTheBall -> RE: Joining 2 databases (5/14/2007 13:21:22)

Remove the single quotes from around Request.Form("invoice_date")



xgd -> RE: Joining 2 databases (5/14/2007 13:23:27)

Microsoft VBScript runtime error '800a0005'
Invalid procedure call or argument: 'DateAdd'



BeTheBall -> 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;



xgd -> 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 -> 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"))



xgd -> RE: Joining 2 databases (5/14/2007 14:20:42)

That did it
It returns data between date requested & date requested -7



BeTheBall -> 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




xgd -> 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 -> 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



xgd -> 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 -> 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



BeTheBall -> 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



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

No errors, but it is still returning all data



BeTheBall -> 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



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

No results



BeTheBall -> 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 &"'



xgd -> 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 -> 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 &"')



xgd -> 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



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

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.09375