Derived Table DB2

building semantic layer
1

Folks,

I am trying to create a derived table in a universe based on DB2 database

As my DB2 SQL skills are not the greatest, I have copied the SQL below generated by a Business Objects Report and tried to create a derived table based on this SQL. It won’t parse and gives the error in the attachment…

What am I missing?

regards

James

SELECT
  VEHICLE.VEHICLEID,
  sum(BID.AMOUNT) as "Highest Bid"
FROM
  VEHICLE LEFT OUTER JOIN VEHICLE_IN_PROCESS ON (VEHICLE.VEHICLEID=VEHICLE_IN_PROCESS.VEHICLEID)
   INNER JOIN VEHICLE_IN_SUBPHASE ON (VEHICLE_IN_PROCESS.VIPID=VEHICLE_IN_SUBPHASE.VIPID)
   INNER JOIN BID ON (VEHICLE_IN_SUBPHASE.VISID=BID.VISID)
  
WHERE
  ( 
  ( Upper(( BID.IS_HIGHEST_BID_PROCESS )) = 'Y'  )
  )
GROUP BY
  VEHICLE.VEHICLEID

[Edited, when posting code samples please use the code option for formatting. It will preserve any indenting or formatting that you may have done. Thank you, Andreas.]
Doc1.doc (30.0 KB)

jemstar :ireland: (BOB member since 2006-03-30)

2

Not sure what this message means (as you don’t even have this ‘TABLE’ in your query), but my guess would be the doublequote around your Column Alias. Is that nescessary/allowed in DB2?

jobjoris :netherlands: (BOB member since 2004-06-30)

3

looks ok to me, although you have superfluous brackets in your predicate.
[edit]I’m not sure if that “Highest Bid” is valid though, you’d make life easier by calling it highest_bid[/edit]

does the query run OK in DB2 (QMF/Spufi/DB2 command line etc)?

when busobj parses statements it sometimes does funny things to try and determine if it’s valid. try doing a trace on your sql from the db2 client and look at the actual statement it is sending to DB2, this will probably be where the mysterious “TABLE” is coming from.

RobinM :uk: (BOB member since 2003-02-25)

4

The SQL seems to be edited. Usually BO does not create an alias name on the column names like “Highest Bid”. The message means that the parser found an error, and at that point accepted or discarded some tokens from the SQL code, finding then a table name where not expected. No unusual for a parser. Do not get stuck in the message text by itself. You can simplify your query try to parse it again until it is OK, and then put back the parts you removed from the code. It can help you to narrow where the problem really is. As mentioned already, does the query parse OK on DB2?

MarcoR :us: (BOB member since 2006-03-29)