derived table not filtering on one field

[toon10]

Does anybody know why my derived table completely ignores one particular where condition but accepts the rest?

SELECT A.`Part No`, A.`Selling Unit`, A.`Customer No`,
A.Description, A.`Bus Unit`, A.`Prod GP`, Format(`Cost Price`,
'Currency') AS `Cost Price`, Format(`Latest Price`, 'Currency')
AS `Latest Price`, A.`Prices Type`, A.`Product Manager`,
A.`Effective From Date`, A.`Creation Date`,
A.`Condition Type`
FROM tblPrice A INNER JOIN
(SELECT `Part No`, MAX(`Creation Date`)
AS `Creation Date`
FROM tblPrice
WHERE 1 = 1 AND
`Effective From Date` <= # 22 / 11 / 2005 # AND
`Prices Type` = 'International'
GROUP BY `Part No`) B ON A.`Part No` = B.`Part No` AND
A.`Creation Date` = B.`Creation Date`
ORDER BY A.`Part No` ASC

The "`Prices Type` = 'International'" returns records with other price types in but it filters on other fields OK (in this instance, it does filter on dates < = 21/11/05). The prices type field is a text field and if I do another SQL call on the tblPrice table without using the derived table and using "`Prices Type` = 'International'", that works fine.

Any help would be appreciated, am I missing something obvious here?
Thanks
Andrew

 

[ks2000]

Try this

SELECT A.`Part No`, A.`Selling Unit`, A.`Customer No`,
A.Description, A.`Bus Unit`, A.`Prod GP`, Format(`Cost Price`,
'Currency') AS `Cost Price`, Format(`Latest Price`, 'Currency')
AS `Latest Price`, A.`Prices Type`, A.`Product Manager`,
A.`Effective From Date`, A.`Creation Date`,
A.`Condition Type`
FROM tblPrice A INNER JOIN
(SELECT `Part No`, MAX(`Creation Date`)
AS `Creation Date`
FROM tblPrice
WHERE 1 = 1 AND
`Effective From Date` <= # 22 / 11 / 2005 #
GROUP BY `Part No`) B ON A.`Part No` = B.`Part No` AND
A.`Creation Date` = B.`Creation Date`
and B.`Prices Type` = 'International'
ORDER BY A.`Part No` ASC

Good Luck
Gopala Krishna Kakani

 

[toon10]

Ah, I see what you are doing.

It gives me a "join not supported message". I'll browse google for derived tables to get a better undertanding.

Thanks
Andrew

 

[druer]

You can't refer to TEXT fields in select clauses with any type of conditon. You need to do a CONVERT of any TEXT datatypes as following:

AND convert(varchar, b.[prices type]) = 'International'

or whatever the equivelent would be in Access.

 

[toon10]

The VB 6 designer doesn't like the convert keyword. The thing is,

SELECT `Part No`, `Selling Unit`, `Customer No`, Description, `Bus Unit`, `Prod GP`, Format(`Cost Price`, 'Currency') AS `Cost Price`, Format(`Latest Price`, 'Currency') AS `Latest Price`, `Prices Type`, `Product Manager`, `Effective From Date`, `Creation Date`, `Condition Type` FROM tblPrice WHERE 1 = 1 AND [Effective From Date] <= #22/11/2005# AND [Prices Type] = 'International' ORDER BY [Part No] ASC

...works perfectly. It's when I need to check on the latest creation date (where I need a derived table) that the results seem to ignore some or all of the where clause.

 

[druer]

Have you kept the existing clause and instead of trying to join simply done a sub-select clause like:

exactly what you have
AND CreationDate = (select max(creationdate) from tblPrice t2 where t2.PartNo = tblPrice.PartNo)

It won't perform as well, but it may get you around whatever bugaboo Access has with the join

 

[toon10]

I'll give that a try.

Thanks
Andrew