Two tables named r2.
Or
Might be too complex, join of a subquery, in a subquery?
Tracy
-----Original Message-----
From: MB Software Solutions
Sent: Tuesday, July 24, 2007 11:04 AM
I get this query to work on its own (to get the latest, active rates for a
given labor record):
SELECT r1.nlufabid, r1.nrate, r1.iid ;
FROM eiwdb!luFabRates r1 ;
INNER JOIN (select nlufabid,MAX(iid) as imax FROM eiwdb!luFabRates
r2 GROUP BY 1) x1 ;
ON x1.imax = r1.iid ;
WHERE r1.dactive <= DATE()
I try to plug that into this view definition and it says "Syntax error"
yet I just don't see it:
SELECT Lufab.iid, ;
Lufab.cdescription, ;
Lufab.nfactor, ;
Lufab.cqtytype, ;
Lufab.ctotalqtytype, ;
Lufab.cfactortype, ;
Lufab.cType, ;
Lufab.cClass, ;
luFab.cWorkcenter, ;
Lufab.dlastupdate, ;
lufab.cSKU1, ;
lufab.cSKU2, ;
Lufab.lActive, ;
NVL(r2.nRate, 0000.00) as nRate;
FROM eiwdb!lufab ;
LEFT JOIN ;
(SELECT r1.nlufabid, r1.nrate, r1.iid ;
FROM eiwdb!luFabRates r1 ;
INNER JOIN (select nlufabid,MAX(iid) as imax FROM
eiwdb!luFabRates r2 GROUP BY 1) x1 ;
ON x1.imax = r1.iid ;
WHERE r1.dactive <= DATE() ) r2 ;
ORDER BY Lufab.cdescription
Anyone see the problem? These are the kind of buggers that are so
annoying, because Syntax Errors should be easiest to correct yet it's
not so obvious to me! I suppose I should get up and step away for a bit
and revisit it with fresh eyes in a few minutes. ;-)
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[EMAIL PROTECTED]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
