February 4, 2011 at 2:24 pm
I am trying to query two tables, one which has invoice information, the other which has price history info.
In a sample scenario the invoice table has CUSTOMER, INV_NO, ITEM, INV_DATE, and the price table has CUSTOMER, ITEM, START_DATE, END_DATE, PRICE.
Using a query like
SELECT INV.CUSTOMER, INV.INV_NO, INV.ITEM, INV.INV_DATE,
PRICE.PRICE
FROM
SCHEMA.INVOICES INV
LEFT OUTER JOIN SCHEMA.PRICING PRICE
ON INV.CUSTOMER=PRICE.CUSTOMER AND INV.ITEM=PRICE.ITEM
AND (INV.INV_DATE>=PRICE.START_DATE AND INV.INV_DATE<=PRICE.END_DATE)
works for most scenarios, but when a price has not been superseded, the PRICE.END_DATE is set to '0000-00-00', which is of course then not returned in my query.
e.g. INVOICES
CUSTA ITEM1 2011-01-08
CUSTA ITEM2 2011-01-08
PRICING table
CUSTA ITEM1 2011-01-01 2011-01-31
CUSTA ITEM1 2011-02-01 0000-00-00
CUSTA ITEM2 2011-01-01 0000-00-00
In this case, I am not seeing the ITEM2 returned with the query....How can I adjust the query to make sure this line is included?
Yes I know....the date should not be empty, but the query is actually on a linked server, which is an old MySql database of which I have no control over....so....can anyone help??
Thx
February 4, 2011 at 8:36 pm
Are these date columns defined as character or datetime data type?
February 4, 2011 at 8:39 pm
If actual datetime columns, you should either use null for the active record end date (many will argue against this) or use 9999-12-31 (which I think more would agree to). Based on what I see, they are not datetime columns.
February 5, 2011 at 1:38 am
They are actually date time columns.... MySQL allows storage of this in a date time column as a dummy value.
The problem just is how to query the data... :ermm:
February 5, 2011 at 9:45 am
Jason Coleman (2/5/2011)
They are actually date time columns.... MySQL allows storage of this in a date time column as a dummy value.The problem just is how to query the data... :ermm:
Assuming you have no invoice dates before (say) 1900, maybe changing
AND (INV.INV_DATE>=PRICE.START_DATE AND INV.INV_DATE<=PRICE.END_DATE)
to
AND (INV.INV_DATE>=PRICE.START_DATE AND (
INV.INV_DATE<=PRICE.END_DATE OR PRICE.END_DATE < '1900-01-01'))
will work.
Tom
February 7, 2011 at 3:53 am
thx for that, that did work. But (there always is a "but") I have checked further into the different scenarios, and found that there are other ways the data can be queried.
Basically here is the whole story....
I am trying to query two tables, one which has invoice information, the other which has price history info.
In a sample scenario the invoice table has CUSTOMER, INV_NO, ITEM, INV_DATE, and the price table has CUSTOMER, ITEM, START_DATE, END_DATE, PRICE. The price table does not necessarily have a value loaded in the "CUSTOMER" field
e.g. INVOICES
INV01 CUSTA ITEM1 2011-01-08
INV01 CUSTA ITEM2 2011-01-08
INV02 CUSTB ITEM2 2011-01-20
PRICING table
CUSTA ITEM1 2011-01-01 2011-01-31 14.00 EUR
CUSTA ITEM1 2011-02-01 0000-00-00 15.00 EUR
CUSTA ITEM1 2011-02-01 0000-00-00 10.00 GBP
CUSTA ITEM2 2011-01-01 0000-00-00 10.00 EUR
NULL ITEM2 2011-01-01 0000-00-00 12.50 EUR
NULL ITEM2 2011-01-01 0000-00-00 10.00 GBP
I need to query the invoice line items and return the correct line item price, but I am at a bit of a loss...
I have tried the following, but get the error that the subquery returns more than one row..
select inv.cust,inv.invoicenum,inv.item,inv.date,
(
select
(case
when price.cust=inv.cust AND price.from>=inv.date and price.to<=inv.date then price.value
when price.cust=inv.cust AND price.to='0000-00-00' then price.value
when price.to='0000-00-00' then price.value
else 0
end) as price
from schema.pricing price
)
from schema.invoices inv
left outer join schema.pricing price
on inv.item=price.item
February 7, 2011 at 5:15 am
There seem to be a number of different things happening here.
1) The cust field may not be matched (the code you posted doesn't address this, although the issue was mentioned in the text)
2) The item field may not be matched (the code you posted appears to try to cater for this, although it wasn't mentioned in the text)
3) By allowing non-matches as well as matches you get more than one value, where a single value is needed (which is the problem you are currently hitting).
As you are running the query on an old MySQL database, I'm not sure what features are available to address these issues. In T-SQL, I would generate some extra values to tell me which non-matches (if any) contributed to each row, use these extra values to allow me to number the rows partitioned by the invoice's cust and item so that the preferred one of each partition is the lowest numbered and then select the set of rows with row number 1. That needs the row_number over (partition, order) feature, and I don't know if MySQL supports it.
That goes something like this:
select select top 1 P.cust,P.invoicenum,P.item,P.date
from (
select inv.cust, inv.invoicenum, inv.item, inv.date
case when price.cust = inv.cust then 0 else 1 end as cmatch,
case when price.item = inv.item then price.value else 0 end as value,
case when price.item = inv.item then 0 else 1 end as imatch
ROW_NUMBER() over(
PARTITION BY inv.item, inv.cust ORDER BY imatch,cmatch
) as rownum
from schema.invoices inv left outer join schema.pricing price
on inv.cust = price.cust and inv.item = price.item
where price.from <= inv.date and (price.to >= inv.date or price.to = '0000-00-00')
) P
where P.rownum = 1
If I didn't have ROW_NUMBER (eg working in MS SQL 2000) I would mess about with quirky update, but I'm pretty sure MySQL doesn't support that.
Tom
February 7, 2011 at 5:49 am
That needs the row_number over (partition, order) feature, and I don't know if MySQL supports it.
Unfortunately it doesn't. This is really starting to get me stuck....Another option might be to import the data into a SQL server using SSIS and query the data that way....
February 7, 2011 at 6:16 am
Jason Coleman (2/7/2011)
That needs the row_number over (partition, order) feature, and I don't know if MySQL supports it.
Unfortunately it doesn't. This is really starting to get me stuck....Another option might be to import the data into a SQL server using SSIS and query the data that way....
I think that importing the data into SQL Server is probably the best approach.
Sorry not to be of more help.
Tom
February 7, 2011 at 7:21 am
Here is what would help, the DDL for the tables, some sample data, and expected results based on the sample data.
In you latest post I noticed that you now have pricing in multiple currencies (Euros and British Pounds), how does the pricing relate to the customers? The more information you provide up front, the better answers you will get. Please tell us everything we need to know, not just pieces here and there.
Help us help you.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply