At 25/04/2001 22:49, you wrote:

>G'day,
> could someone put me out of my misery and explain why this cut-down
>SQL statement returns the same (first) value for CAT1NUM for every
>row of the table (even though they ARE different)?
>
>SELECT ITEMUSED_ID
> , JOBNO
> , ITEMNO
> , QTY
> , (SELECT CAT1NO FROM ITEMS WHERE ITEMS.ITEMNO=:ITEMNO)
> AS CAT1NUM
> , (SELECT CAT1 FROM CATEGORY1 WHERE CATEGORY1.CAT1NO=:CAT1NUM)
> AS CAT1
>FROM ITEMSUSED
>
>ITEMUSED_ID is in KeyLinks
>
>Can I use :ITEMNO like this?

No. Use ITEMSUSED.ITEMNO instead. I'm not sure about CAT1NUM though, but
don't use it like a parameter. If for CAT1NUM doesn't work, maybe use a
select with a join and use ITEMNO in the where clause.


Daniel Rail
Senior System Engineer
ACCRA Group Inc. (www.accra.ca)
ACCRA Med Software Inc. (www.filopto.com)
SFI MARKETING GROUP (www.ezinfocenter.com/531224)