Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Recent content by crufty
-
SQL*PLUS Editor question...retrieving previous select statements
My editor is, basically, an emacs emulator (with extra goodies) for Windows. I am able to run a dos shell from within a process in the editor and ,then, I launch SQL*Plus. Epsilon can be purchased for <100$ at : http://www.lugaru.com/ I have no affiliation with the company, just a happy...- crufty
- Post #9
- Forum: Oracle: All versions
-
SQL*PLUS Editor question...retrieving previous select statements
I run SQL*Plus as a process inside my editor (Epsilon for Windows)which gives me full cut and paste, complete buffer listing, etc. Very useful setup.- crufty
- Post #6
- Forum: Oracle: All versions
-
SQL*PLUS Editor question...retrieving previous select statements
Actually, you can get the contents from the SQL*Plus buffer by using the L[IST] command in windows. The buffer will not hold all queries executed during the session, rather, the last query or procedure executed (I believe) Just for extraneous information, / executes the contents of the...- crufty
- Post #3
- Forum: Oracle: All versions
-
Trying to get a top 20 count of users from SQL
To get the top N without using the top function (say you're working with Oracle 8i) you have to first order the resultset in the manner you want (i.e in this example, by counter) then select the first 20 rows of the ordered resultset. For oracle, use ROWNUM to limit the resultset for the first... -
Number of days between two dates.
I think this a database to database issue. In Oracle, you can just subtract the two dates and the answer returned is the number of days. Example: select to_date('1-sep-02') - to_date('4-feb-02') from dual ; returns the number 209 If the date values are timestamps (i.e. containt hours... -
Top 10 in Oracle SQL
Actually, rownum < 11 returns the first 10 rows returned by the query NOT the top 10 rows. You have to wrap the ordered query in a 'select ... where rownum < N'. Example: select t0.* from (select * from prepaid.rating_report order by startdate desc) t0 where rownum < 11 ; -
SIMPLE =(+)
Nice solution, angiole. The select needs a slight edit in order to give the output in the format that they wante: select stat, NVL( tcount, 0 ) . . . (NOTE: NVL is an Oracle specific function) This will give (using your example numbers from your post) STAT TCOUNT ----------... -
Rolling sum
I believe the straight sql version can be done like so: select date, count, (select count from table t1 where t1.date < t0.date) from table t0 order by date; NOTE: I didn't test this thouroghly and it is pretty early in the morning so be careful ;^) If your RDBMS has a function that... -
correlated subquery
The correlated subquery calculates the number of rows where the value represented by w1.number_of_beds is less than all the values in w2.number_of_beds. The query then goes on to see if this value is less than 4, if it is, select everything from the row containing w1.number_of_beds. Example... -
Constants
Another way you could also make your constants accessible to multiple cllasses is by implementing an interface that contains all of your constants. By definition, all constants in an interface are public static final and are accessible by all classes that implement the interface. -
count distinct
Wrap a query that returns all distinct records of stage and clone_id in a 'select count(*) from ( )' statement: SELECT count(*) FROM ( select distinct clone_id, stage from t_expression ) This will return a single number that is the number of distinct clone_id, stage rows. -
Help writing SQL query using JOINS?
This observation is based upon the data structure you presented and doesn't ultimately solve your problem but should get you going in the correct direction: There isn't a connection between building and room, i.e. person.personid -> personroom.personid personroom.personroomid -> room.roomid... -
Query to display table structure
Just to confirm: Oracle uses 'describe' to give basic table information describe table (or, if you want to save 3 letters of typing in SQLPlus, descr table) obscure useless Oracle trivia: You do NOT need to end the line with a semicolon for the describe command in Or.acle -
list indexes
In Oracle: select * from ind; This will list all indeces and a ton of information about each index. -
Displaying a count of zero.
More than one way to write a query. The query below uses some Oracle specific functions: (+) -> is used for the inner join nvl(column, n) -> if the column returns a NULL, substitute in the number n else use the value in column select t0.country, nvl(t1.number, 0) from (select distinct country...
