SubQuery Problem (1 reply)

This Comment will be submitted for moderation and will not be accessible to other users until it has been approved.
Submitted by programmer on Thu, 08/14/2008 - 23:47.

I'm having trouble with the following query, if only one result is returned it executes in a second or two, however if there are multiple reuslts it errors out. I also tried to us = ANY and = ALL and the query runs endlessly. Any suggestions?

SELECT t1.DATE, t1.REASON, t1.TITLE, Left( t1.REASON, 8 ) AS CLAIM_NO, Right( t1.REASON, 2 ) AS STAGE, t1.ID AS transatn_ID, t1.CLAIM, o1.ID AS ORDER_ID, o1.SUBREF, t1.SPOOL_ORD

FROM order_ln o1

INNER JOIN transatn t1 ON o1.ID = t1.TRANS_REF

WHERE

t1.ID =

( SELECT max( t1.ID ) AS ID

FROM order_ln o1

INNER JOIN transatn t1 ON o1.ID = t1.TRANS_REF

WHERE t1.DATA_TYPE = 'CM'

AND t1.`DATABASE` = 'CLAIM '

AND t1.REASON LIKE '%'

AND t1.TRANS_REF =2000563600

GROUP BY left( REASON, 8 )

)

Post Comment

  • You can enable syntax highlighting of source code with the following tags: <code>, <blockcode>, <c>, <cpp>, <drupal5>, <drupal6>, <java>, <javascript>, <php>, <python>, <ruby>. Beside the tag style "<foo>" it is also possible to use "[foo]". PHP source code can also be enclosed in <?php ... ?> or <% ... %>.