I have a beginner level at oracle, I can write queries and stored procedure but as in Java I like to know precisely the language and the system I use. In my mission I discovered some noob details about Oracle that I must admit, I did not know.
Select * Paradigm
When I worked with Sybase, SQL Server or MySQL and tried to extract multiple column, I have the habbit to put the most important columns at the beginning of my request such as
Initial request that show me the most important column at first position and all other information (JOB_NAME is only in one table) :
SELECT JOB_NAME, p.* FROM jobstatetreatments j, prismjobs p WHERE j.job_id = p.job_id AND j.job_id ...
With Oracle, this will not work :
ORA-00936: expression absente
00936. 00000 - “missing expression”
…. the solution is to prefix all data, by their table name or aliases
SELECT p.JOB_NAME, j.*, p.* FROM jobstatetreatments j, prismjobs p WHERE j.job_id = p.job_id ..
I really would like to ask a DBA why Oracle did not provide that easy way to query data.
ORDERBY / ROWNUM Observation
How can you select the highest value? Once again if you try to query Oracle as in regular SQL command you may try this.
Let’s say that we have those values in the table myTable
SELECT * FROM myTable WHERE ROWNUM <= 3 ORDER BY col1 DESC;
Oracle first treats the rownum criteria to extract values then it sorts the list. So the solution is to separate the order by criteria from the rownum criteria.
SELECT * FROM (SELECT * FROM myTabme ORDER BY col1 DESC) WHERE ROWNUM <= 3;