Problem using rownum

[rkishore999]

I'm trying to use the following query in Teiid:

select * from <table_name> where ROWNUM < 5;

I'm getting an error:

com.metamatrix.jdbc.MMSQLException: Element "ROWNUM" is not defined by any relevant group.

Database is Oracle.

How can I use rownum in Teiid?

Regards

Ram

[shawkins]

Teiid does not yet have direct support for a rownum construct. If you are using rownum for limit or offset, then you should use the limit clause instead:

select * from <table_name> where ROWNUM < 5

would become

select * from <table_name> limit 4

see the reference for more on the limit clause. If you are using the rownum in a way not expressable by a limit/offset and are using Teiid Designer, you could add rownum column to your source table.

[rkishore999]

Thanks Steve. Another issue:

I couldn't see any decimals being returned for real values from Oracle. Values are being rounded to floor.

Regards

Ram

[shawkins]

What are the runtime types of the source table columns? If they are exact integral types (byte, short, integer, long, biginteger) then they will not have decimal values.

[rkishore999]

Steve,

The runtime types of the columns are created as bigintegers. The actual type of the column in the source is NUMBER which could return decimals.

If the change the runtime type of the column from biginteger to bigdecimal and import into designer then it works fine.

Don't you think the columns with INTEGER type in the source should be able to return decimals? usually when we query Oracle it returns the the value with decimals if it is a decimal value and returns just integer if it don't have a decimal value. I suppose behaviour should be same when we query the VDB as well right??

Regards

Ram

[shawkins]

>The actual type of the column in the source is NUMBER which could return decimals.

Do you mean that the scale is non-zero, such as NUMBER(10, 2). In which case a modeled type of biginteger would be a bug. Or does it have zero scale. In which case it should import as an integral type (that is wide enough to hold the precision).

> Don't you think the columns with INTEGER type in the source should be able to return decimals?

It depends on how they are being used. If the source is an INTEGER, imported as an INTEGER, and simply selected from the source, then fractional parts do not come into play. If you use the value in a math function, such as intvalue * doublevalue - then you would expect the result to be typed as double and have fractional values. There are however times when the Teiid type system and the source type system will differ, such as with intvalue / intvalue. The result to Teiid will still be typed as int. I think the result for Oracle is allowed to have up to a scale of 19. If you want the oracle behavior, the workaround - just as you have done in your post - is to use the bigdecimal type for all oracle number types.