IBM Mainframe Forum

Hello,

Could anyone help me with a query please ?

I have to match 2 tables, something like this :

select *
from table1 A
where A.fieldA in (select B.fieldA
from table2 B
where A.id = B.id)

My problem is when I want to select a row in the Table2. I want to choose of the table2 the row that contains the biggest date and the biggest value of the field B.

Something like this

select *
from table1 A
where A.fieldA in (select B.fieldA
from table2 B
where A.id = B.id
order by B.date and B.FieldB
fetch first 1 row only)

but it doesn't work...

Thanks in advance !

Some (expletive deleted) person wrote:...but it doesn't work...

Then maybe you can be so smart to tell us What it doesn't work.

When I execute that spufi, I get this error :

SQLCODE = -199, ERROR: ILLEGAL USE OF KEYWORD ORDER. TOKEN ) UNION
EXCEPT WAS EXPECTED

Are you nervious ????

Thanks !

Are you nervious ????

nobody is nervous ...

but people who whine about something not working without telling anything more
just deserve that all their bytes lose their parity

Yes, you are right but it is not the correct way to answer a question like this "you can be so smart.."

You've been here long enough to know how it works, if you've been paying attention rather than just coming here when stuck.

You've previously asked, in the Cobol forum, about "3.2 for 300" which was a problem relating to your edit session for a file you had created under ISPF. You were lucky that time.

Since you can't let it drop, you've also been around long enough to use the Code tags.

Have you tried to question the error message, tell it the "correct" way to behave around your code?

Read the error message (is that exactly how it appeared?). Understand it in its fullness. Work out what it is telling you about your code. If you can't get it, come back and tell us what you understand by what you have learned.

"order by B.date and B.FieldB" is a syntax error, and at least you would need to order Descending.
Which version of DB2 are you using ?

Yes, you are right GuyC. I forgot to use DESC in the ORDER BY.

How could I get the db2 version ? I see "DB2 VERSION: V08NF" when I enter in Platinum.

Best regards