where clause won't work against Oracle `ROWNUM` pseudocolumn

[klauern]

I have some Oracle tables that I'd like to query, but since LIMIT doesn't work the same way in Oracle, I'm using Oracle's ROWNUM Pseudocolumn:

user=> (select iunit (where {:rownum [< 10]}))
Failure to execute query with SQL:
SELECT * FROM iunit WHERE (iunit.rownum < ?)  ::  (10)
SQLSyntaxErrorException:
 Message: ORA-01747: invalid user.table.column, table.column, or column specification

 SQLState: 42000
 Error Code: 1747
nil

What confuses me is that I get a weird error with the <= predicate on it

user=> (sql-only (select iunit (where {:rownum [<= 10]})))
CompilerException java.lang.RuntimeException: No such var: korma.internal.sql/pred-<=, compiling:(NO_SOURCE_PATH:21)

[ibdknox]

0.3.0-alpha should have that predicate thing fixed. To do the rownum thing, the simplest solution is to use the (korma.internal.sql/generated) function:

(def rownum (generated "ROWNUM"))
(select iunit (where {rownum [<= 10}))

This will only work with 0.3.0-alpha though, since that function didn't exist before then. It looks like I may need a slightly nicer solution for inserting raw-sql like things though.

Cheers, Chris.

[klauern]

Hm.. That seems to cause more problems, because now I don't get anything for a simple select statement:

user=> (use 'korma.db)
nil
user=> (use 'korma.core)
nil
user=> ;; do db setup stuff
user=> (select iunit (where { :unit_key 93 }))
Failure to execute query with SQL:
SELECT "iunit".* FROM "iunit" WHERE ("iunit"."unit_key" = ?)  ::  (93)
SQLSyntaxErrorException:
 Message: ORA-00942: table or view does not exist

 SQLState: 42000
 Error Code: 942
nil
user=>

And this exact same thing will return a result in 0.2.1.

[klauern]

could it be that the " that is being done for the table causing the problem? I can't get that to work at all in Oracle with SQLDeveloper:

SELECT "iunit".* FROM "iunit" WHERE ("iunit"."unit_key" = 93)

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 1 Column: 22

[ibdknox]

Quoted identifiers are case sensitive, could it be that the table is not all lowercase?

[klauern]

Ah, I never expected it to be case-sensitive...seems to work this way:

user=> (defentity IUNIT)
#'user/IUNIT
user=> (select IUNIT (where {:UNIT_KEY 93}))
[{:BLAH BLAH BLAH IT WORKS{]

[klauern]

And it works. Excellent.