QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;
NAME DOJ QUAL EMPID
---------- --------------- ---------- -----
sam 21-mar-2012 mca
sony 03-jan-2000 mba 10610
maya 21-mar-2012 m.arch
sonna 18-mar-2012 mis
[code]...
i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?
I need to return the stored result in a record by ref cursor. In the below code I am displaying the result through DBMS_OUTPUT.PUT_LINE but i need to do it through ref cursor to display the result.
declare type r_cursor is REF CURSOR; c_emp r_cursor; type rec_emp is record ( name varchar2(20), sal number(6) [code].....
I have the below PL/SQL Block in which it iterates through the id stored in local table and passes this value to the select statement which runs on a remote DB.
In the loop the select statement is executed for each id in local table.Now i am trying to display the output of the execute statement which does not work.
let me know how can i show the output of this and store in a file or display on screen.
server output on DECLARE v_sql VARCHAR2(9000) := NULL; BEGIN
I am trying to display the results from 2 queries, one is supposed to display the count of the employees, per department, who win over the average of the entire company and the other one is supposed to display the count of the employees, per department, who win under the average of the entire company.
I used a UNION ALL, but all it does is merge the results from the ones that win over and under the average into one row, is there a way to separate them? I tried assigning names to each salary using AS but it only displays the first I put in.
sql Original - sql Code ( SELECT DE.DEPARTMENT_NAME, COUNT (EM.EMPLOYEE_ID) AS MAYORES FROM DEPARTMENTS DE, EMPLOYEES EM WHERE DE.DEPARTMENT_ID = EM.DEPARTMENT_ID AND EM.SALARY > (SELECT AVG(EM.SALARY) FROM EMPLOYEES EM) GROUP BY DE.DEPARTMENT_NAME [code].....
am facing a Problem in creating display items .First I create a display item and name it ITEM6This Display item will show Department name of employees .I create a Block Level Trigger to invoke this display item .
WHEN-VALIDATE-ITEM select dname into :ITEM6 FROM DEPT where :EMP.DEPTNO=DEPT.DEPTNO;
In Property Palette I edit following Data Type : char canvas :canvas4
Then I save ,compile and Run it successfully .But when i press Execute Query button . It shows me Following error message .
SELECT ROWID,ITEM6,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO FROM scott.emp
I have the following data (columns IDx, STARTx and DURATION) and want to create the column RESULT. Example:
create table zTEST ( IDx number, STARTx number, DURATION number, RESULT number ); insert into zTEST (IDx, STARTx, DURATION, RESULT) values ( 1 , null , null , null );
[code]...
The logic behind the table is like this: when there is a Start-ID and a Duration, the field Result shall show the Start-ID for the next records (Duration period). Even if there is a new Start-ID in row 9, it is ignored since threre is already a current signal from row 6 which is still running.
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
SELECT SUM(salary) FROM employees WHERE department_id =60 O/P is : 28800But when i use the above query in anonymous block it gives me 684400 DECLARE v_sum_sal NUMBER;
[code]....
The above output statements gives me 684400 as output.. But the expected is 28800
I have two tables T1 and T2. T1 is the original backup snapshot for changed records from overnight batch in a big table and T2 is the overnight batch changed records. Both tables have similar number of rows (T2 might have more for newly inserted rows) and you can find out the differences by comparing these two according to action column in T2 (C - Update, A - Insert and D - Delete)
how to compare these two tables to generate something like the following. I can join these two tables to generate the diff but it is one row per account.
client_nbr branch_cd, account_cd, action column, old_value, new_value 8888 123 45678 C account_clsfn_cd 004 005 8888 123 45678 C buy_cd 98 99 8888 012 34546 A sell_cd 12 8888 321 98765 D dividend_cd 1
I am using Oracle 10g so Unpivot cannot be used.
CREATE TABLE T1 ( CLIENT_NBR CHAR(4 BYTE) NOT NULL, BRANCH_CD CHAR(3 BYTE) NOT NULL, ACCOUNT_CD CHAR(5 BYTE) NOT NULL, ACCOUNT_CLSFN_CD CHAR(3 BYTE), SELL_CD CHAR(2 BYTE), BUY_CD CHAR(2 BYTE),
I have a table MESSAGE which has some billion entries. The columns are msg_id, vehicle_id, timestamp, data, etc.I have another table VEHICLE which holds static vehicle data (about 20k rows) such as vehicle_id, licenceplate, etc.
My first target was to partition the table via timestamp (by range) and subpartition by vehicle_id (by hash).So I could easily drop old data by dropping old partitions and tablespaces.
Now comes the new difficult 2nd target: the messages of some vehicles must be kept forever.My idea is to add a column KEEP_DATA to the table MESSAGE. I could try to partition by timestamp AND KEEP_DATA, subpartion by vehicle_id.The problem of this idea is that i have to update billions of rows.
It would be perfect if there is a possibility to add this KEEP_DATA-flag to the table vehicle.Is there any way to "link" this information to a column in MESSAGE table?
I mean something like this:
alter table MESSAGE add column (select keep_data from vehicle where VEHICLE.vehicle_id = MESSAGE.vehicle_id as keep_message) ;
Is there some possibility like that? Would the partitioning on this column / statement work?Would the value of the keep_message be calculated on runtime?
If so will the performance influence be noticeable?If so will the performance also sink if the application is querying all rows except the keep_message?
how to write a single query, Also i may add /delete school/class anytime. Query should support mesans everything dynamically should get the data.Even stored procedure will work for me.
As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.
1. This is a table 'spj' SID PID JID QTY ---------- ---------- ---------- ---------- 1 3 2 5 1 2 2 12 1 7 1 10 2 5 3 5
[code]...
I wish to count all the qty for each pid.This is what I have: SQL> select pid,sum(qty) from spj group by pid; PID SUM(QTY) ---------- ---------- 1 5 2 12 3 29 4 10 5 5 6 6 7 10
and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.
2. Another query I can't perform needs the table s SID SNAME STATUS CITY ---------- -------------------- -------------------- ------------------- 1 s1 y asansol 2 s2 y durgapur 3 s3 y durgapur 4 s4 y asansol 5 s5 y kolkata 6 s6 y asansol 7 s7 y tarakeshwar
the question says:
Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.
I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values
For example:
So when dept.deptno=10, I want to display these 2 columns values 1. dept.deptno 2. dept.dname
otherwise, display these 2 columns values 1. dept.loc 2. null
Can this be done with one case, decode or "other" type of structure going thru the table one time??
SELECT emp.empno, emp.ename, CASE WHEN dept.deptno = 10 THEN to_char(dept.deptno) [code].......
I have created a query that display the difference in the tables and column of two users. Some of the column in the new user have been added new which were not there in the old user and some columns from the old user still exits but their data_type is changed.
Now I want to select one more column in the select statement specifying whether the column is modified or has been added.
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH FROM DBA_TAB_COLUMNS WHERE OWNER = 'ABC' AND TABLE_NAME NOT IN (SELECT table_name FROM all_tables where owner = 'ABC' MINUS SELECT table_name FROM all_tables where owner = 'XYZ' ) MINUS SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH FROM DBA_TAB_COLUMNS WHERE OWNER = 'XYZ' [code]....
the above query fetches records as 1 testing D 2 upload H
i have to display my third column with full description which is stored in another table as select descr from app where code = application; I have to display this descr in the above result set instead of D,H.
TABLE_A ------------------------------ ID DEPT CRS ------------------------------ 1 CS CS_100 2 SCIENCE SCI_150 3 MATH MATH_400 4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
-------------------------------------------- NO DEPT CRS -------------------------------------------- 1 MATH MATH_400, MATH_550, MATH_230 --------------------------------------------
instead of., -------------------------- NO DEPT CRS --------------------------- 1 MATH MATH_400 2 MATH MATH_550 3 MATH MATH_230 ---------------------------
AREA (area_code, area_name) MONTHLY_SALES (area_code, isbn, book_name, qty, sales_amount)
I want to display the output of top 3 sales columnwise as below.
AREA_NAME Top 1st (ISBN) Top 1st (BOOK_NAME) Top 1st (QTY) Top 2nd (ISBN) Top 2nd (BOOK_NAME) Top 2nd (QTY) Top 3rd (ISBN) Top 3rd (BOOK_NAME) Top 3rd (QTY)
The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.
I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.
03-AUG-10> set pages 0 03-AUG-10> set heading on 03-AUG-10> select count(*) numberofrows 2 from iceberg_mig_acnts_stage2 s2, tvp109workorder t109 3 where s2.no_account = t109.no_account
How I can build a query with conditions and calculations? E.g. I've got this table
Start | End | Working Place | Mandatory ------------------------------------------------------------------------------------ 01-JAN-13 | 11-JAN-13 | Office | 1 14-JAN-13 | 25-JAN-13 | Home Office | 0 04-MRZ-13| 15-MRZ-13 | Office | 0 11-FEB-13 | 22-FEB-13 | Office | 1
Now if column working place=Office and column mandatory=0 the new column "price" has to calculate: (End-Start)* $25.00 and if working place=Office and column mandatory=1 the "price" column has to calculate: (End-Start)* $20.60 else $0.00
I tried it with the case statement but I didn't know how to calculate my values and display it to the virtual column "price".
Something like case when Working_Place = 'Office' and Mandatory=1 then ... else '0.00' end as PRICE ?????
I am trying to only display a column value one time for a record, not a static value. The value changes and there can be many values. I do not think grouping will work since the Date value is dynamic. I am using Oracle for Toad 10.5. There are 4 records with this test data.
select Date, Person, Language, Country from TableATableA Date Person Language Country 01/25/2013 James English 12/20/2012 James English US
