Hi Rainer,
I get this exception when I try to use DBCommand skipRows method. Does this
mean my Oracle version 11g does not support skip rows?
Exception in thread "main" org.apache.empire.exceptions.NotSupportedException:
The function skipRows is not supported for type
org.apache.empire.db.oracle.DBCommandOracle.
at org.apache.empire.db.DBCommandExpr.skipRows(DBCommandExpr.java:463)
at
com.vzbi.persistent.process.CustomerDBReadProcess.getXMLCustImpactByID(CustomerDBReadProcess.java:568)
at
com.mcit.mastars2k.TSManagement.test.TestMisc.testImpact(TestMisc.java:78)
at
com.mcit.mastars2k.TSManagement.test.TestMisc.startTest(TestMisc.java:24)
at com.mcit.mastars2k.TSManagement.test.TestTsMgr.main(TestTsMgr.java:55)
This is my code.
DBCommand dbCmd = getFilteredCustImpactByIDQuery( typeID, ID,
userGarmLevel,userGSAMValue );
dbCmd.limitRows(2000);
dbCmd.skipRows(100);
I am using version 2.4.1 of empired-db.
Thank You,
Chieu Nguyen
[email protected]
From: Rainer Döbele [mailto:[email protected]]
Sent: Tuesday, June 24, 2014 1:24 AM
To: [email protected]
Subject: re: Building DBCommand
Hi Nguyen,
sorry that I have not been able to answer your request earlier.
I am not sure where you problem is.
The solution I recommended should do exactly what you want. The order of the
rows is correctly maintained and you get the statement same statement as you
have mentioned in your E-Mail.
I am using it myself. This is an example of what I get with the Oracle driver:
SELECT * FROM (SELECT row_.*, rownum rownum_ FROM (SELECT t1.LAST_NAME,
t1.FIRST_NAME, t1.ADDRESS, t1.ZIP, t1.CITY FROM CUSTOMERS t1
ORDER BY t1.LAST_NAME, t1.FIRST_NAME) row_ WHERE rownum<=40) WHERE rownum_>20
If you don't get this you might have an old version of Empire-db. Please make
sure you use the latest version.
Best regards
Rainer
Von: Nguyen, Chieu X [mailto:[email protected]]
Gesendet: Mittwoch, 18. Juni 2014 16:04
An: [email protected]<mailto:[email protected]>
Betreff: RE: Building DBCommand
Hi Rainer,
Thank you for responding to my email. I have try what you suggested and it
works. The only issue with that is Oracle randomly selected # of rows to
return. The reason I need to put in that format is because I want this
scenario.
Let say I have 400 rows in a table, and I want to select those 400 row order by
customer name. Now, I want to return that 1st 100 in that order. Next, I want
to run the same query but this time I want it to return rows 101-200, then,
rows 201-300, and finally rows 301-400.
I think the only I can achieved this is by running the query below.
select * from (
select a.*, ROWNUM rnum from (
<select statemenet with order by clause> // I know how to write this query.
) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW
Thank You,
Chieu Nguyen
[email protected]<mailto:[email protected]>
From: Rainer Döbele [mailto:[email protected]]
Sent: Wednesday, June 18, 2014 3:01 AM
To: [email protected]<mailto:[email protected]>
Subject: re: Building DBCommand
Hi Nhuyen,
this is Oracle Syntax and it is already implemented for the Oracle driver.
All you need to to is the following:
1. Make sure you are using de DBDatabaseDriverOracle.
2. Use cmd. limitRows(...) and cmd. skipRows(...)
then it should generate the desired statement for you.
There is no need to wrap the command inside another command.
Best regards
Rainer
Von: Nguyen, Chieu X [mailto:[email protected]]
Gesendet: Dienstag, 17. Juni 2014 22:43
An: [email protected]<mailto:[email protected]>
Betreff: Building DBCommand
Hi Rainer,
I was wondering how would I write the following SQL using EmpireDB.
select * from (
select a.*, ROWNUM rnum from (
<select statemenet with order by clause> // I know how to write this query.
) a where rownum <= MAX_ROW
) where rnum >= MIN_ROW
I would appreciate any suggestions.
Thank You,
Chieu Nguyen
[email protected]<mailto:[email protected]>
