0

I'm having trouble getting this subquery to work? I've seen posts where they have exactly the same subquery as this one, but theirs works and mine does not.

Example :SELECT X, Y, Z FROM TABLE WHERE X = (SELECT MAX(X) FROM TABLE)

Could it be because X in my example was aquired using Row_Number() OVER(Partition By X Order by Y) as X

Is there a different DB2 specific issue regarding this type of subquery?

Or am I simply misunderstanding/using bad examples to create this subquery.

Here is the error in question An unexpected token "SELECT MAX(X) FROM TABLE" was found following HERE X =(". Expected Tokens may include: "<from_clause>".

Thank you!

AdamTheD
  • 19
  • 5
  • Is the table really named "TABLE", this is a reserved word, you'd better use another name or always use double quotes around. I think it conflicts with the keyword used to consider some function resultas as a table. – nfgl Mar 16 '21 at 13:38
  • No it's just example data. – AdamTheD Mar 16 '21 at 13:55
  • Does this answer your question? [SELECT field as mytest WHERE mytest IS NULL - field "mytest" not found?](https://stackoverflow.com/questions/28695028/select-field-as-mytest-where-mytest-is-null-field-mytest-not-found) – mustaccio Mar 16 '21 at 19:13
  • I decided to flip the list and did where row_num = '1' instead, since the first entry would be the last record, upside down. – AdamTheD Mar 16 '21 at 22:17

1 Answers1

0

I decided to flip the list and did where row_num = '1' instead, since the first entry would be the last record, upside down.

AdamTheD
  • 19
  • 5