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 derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
-
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... -
query troubles
try: select t1.project_id, t2.project_name, t3.user_id, t1.user_history from table1 t1, table2 t3, table3 t3 where t1.project_id = t2.project_id AND t1.project_id = t3.project_id Based on the table structure you presented, the userhistory is tied to the project_id instead of the user_id If... -
Calculating differences between rows
try: select t2.id, t1.id, (t2.value-t1.value) from table t1, table t2 where (t2.id-1) = t1.id I left the id's in the select for clarity. This assumes that you are interested in the difference between two consecutive id's, i.e. (2-1), (3-2), etc. -
Possible SQL Statement
Simplest way to do this is like so: Note: This is generic SQL, please translate into the SQL for your DB SELECT t1.item1, table 2.column1, table3.column1 FROM tableA t1, tableB t2, tableB t3, tableB t4 WHERE t1.column1 = t2.column1 AND t1.column2 = t3.column1 AND t1.column3 = t4.column1... -
PreparedStatement/Parameter Error
Using the java.sql.* package, you have to create prepared statements with the '?' character From SQL*Plus (Oracle), you create bind variables by using the :variable notation. -
PreparedStatement & parameter Passing.
Query layout is important for working with complex queries but the most important step you can take is to write the query as a separate entity and try it out first via SQL*Plus or whatever SQL front end you use. Debugging a complex query embedded in a string is not an efficient process. Get...
