SQL & PL/SQL :: Write Query To Show Monthly Salary?
Jul 12, 2012i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
ADVERTISEMENT
SQL & PL/SQL :: Write A Query To See How Many (no) Employees Getting Salary 3000?
Dec 21, 2010write a query to see how many(no) employees getting salary 3000 & what are their names respectively.
View 2 Replies View RelatedSQL & PL/SQL :: Showing Monthly Data
Mar 6, 2013I am having the query
SELECT JOB,
decode(to_char(hiredate,'YY'),80,to_char(hiredate,'MON'),NULL) "80",
decode(to_char(hiredate,'YY'),81,to_char(hiredate,'MON'),NULL) "81",
decode(to_char(hiredate,'YY'),82,to_char(hiredate,'MON'),NULL) "82",
decode(to_char(hiredate,'YY'),83,to_char(hiredate,'MON'),NULL) "83",
count(*) total
FROM emp
WHERE JOB='CLERK'
GROUP BY JOB,
hiredate;
I want result as follows
job 80 81 82 83 TOTAL
CLERK DEC DEC 2
CLERK JAN JAN 2
That means if 80,81,82,83 if having some common month then it should show side by side.
SQL & PL/SQL :: How To Write Correlated Sub Query
Sep 13, 2010I have employee, location, city tables, I have written following code to use joins
select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';
The same can be written by using non- correlated sub query as follow..
select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))
I need to implement the same concept using correlated sub query...
SQL & PL/SQL :: How To Write Column To Row In A Query
Nov 17, 2005How to write column to row...in a SQL query?
For example..
SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40
The above output to be written in a single row like given below.
7369 20 10 40
1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other
2) It should be in SQL query only..Not in procedures or functions.
Forms :: How To Write Query For ADD Button
Jul 23, 2010Without using any templates, i created a customized sample form....now i want to add data thru that form....i dont know where to write code and what is code if i want to add data?
View 9 Replies View RelatedSQL & PL/SQL :: How To Write Query To Convert XML To Table
Oct 15, 2012I have table xx_xml_test m which have row single entry
<logentry
revision="3">
<author>MA111300</author>
<date>2012-10-03T12:42:40</date>
<paths>
<path
[code].......
i want convert the table like below
Revision author date kind action path
3 MA111300 2012-10-03 12:42:40 file A /root.txt
3 MA111300 2012-10-03 12:42:40 file A /sample2/test_2.txt
SQL & PL/SQL :: Monthly Running Total With Opening Balance
May 9, 2012I want to generate the gapless montly running balance for the whole year.Data in the table "A" is as below.
code Date amount remarks
0101 30-SEP-10 1000 Opening
0101 01-OCT-10 1500
0102 25-OCT-10 500
0102 10-DEC-10 750
0101 26-JAN-11 450
0102 03-MAR-11 3000
0101 30-SEP-11 6000
Required output is
Code Date amount Balance
0101 31-OCT-10 1500 2500
0101 30-NOV-10 0 2500
0101 31-DEC-10 0 2500
0101 31-JAN-11 450 2950
-- -- -- continue
0101 30-SEP-11 6000 8950
Same output for code 0102. I have taken only 02 code for excample only. There are about 250 code in table.
SQL & PL/SQL :: Create Procedure Run For Both Monthly As Well As Yearly Report
Aug 3, 2010I want to create procedure who should run for both montly as well as yearly report for monthly report i will pass Year and Month as argument for year i will pass Year only
for annual report i a counting all the user, new entry for item, new enty for customer, new enty for admin for montly report i am counting new item, new group.
SQL & PL/SQL :: Write A Query In Order To Receive A New Table?
Apr 23, 2012i need to write a query in order to receive a new table.
select table_name,col1, null col2
from
(SELECT b.table_name, b.col1 col1, a.CountPrimaryKeys
from
(SELECT user_constraints.table_name,
COUNT(user_cons_columns.column_name) CountPrimaryKeys
[code].......
SQL & PL/SQL :: Write A Single Query Showing Number Of Requests
Jan 15, 2011I need to write a single query showing the number of requests (job_no) in my organization sections (1A,3A,4C,9Z....etc) based on these conditions..Less than 2 months & between 2 and 6 & between 6 and 12month & more than 12 months in my organization sections.
--Table structure
SQL> desc job_transfer
Name
----------------------
JOB_NO (Request number)
IN_SECT (name of entered section)
IN_DATE (date entered the section)
OUT_SECT
OUT_DATE (date went out from section)
I tried using UNION ALL but it didnt work fine.
SQL & PL/SQL :: Write Select Query To Get Sum Of Quantity For Each Type Of Item?
May 26, 2010I want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
Client Tools :: Possible To Write Select Query O/p In HTML Tag?
Jan 24, 2011is it possible to write select query o/p in html tag?
View 6 Replies View RelatedSQL & PL/SQL :: Write A Query Which Shows Data Of One Table Not Present In Other?
Apr 3, 2011I am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code
I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.
SQL & PL/SQL :: Write Outer Query Which Will Bring Individual Records Which Don't Sum Up To Zero
May 9, 2011I have to write a sub query / build a logic for the below.
There are several accounts which should have a zero balance i.e sum of all the amoutns in that account should be zero. If they are non zero , i have to report which amounts make up non zero balance.
If i have amts as +20 , -20 , -30,-10 i.e the sum is -40 indicating a non zero amount. I need the entire details of the records which makes up non zero sum. So in above case details related to -30 aand -10.
I'm using a sum group clause on several fields at which sum is required to be checked ie. date , account , currency . query that will bring individual records that don't make the sum zero.
Is it possible to write a outer query which will bring individual records which don't sum up to zero.
Client Tools :: Write Sql Query O / P In HTML Page?
May 31, 2012is it possible to write sql query o/p in HTML page?
View 3 Replies View RelatedSQL & PL/SQL :: Write Single Row Sub-query Which Returns ID_2 Value Of Row With Del_flag As N
Feb 13, 2012In my main query, There is a single row sub query which returns row with del_flag as N. Now in my table, there are 2 rows getting added from the incoming feed with del_flag as Y & N.
Here is my Table:
ID_1 DEL_F ID_2
-------------------------------
16643162 Y 49696
16643162 N 16643162
16612344 Y 98888
So how can i write a single row sub-query which returns ID_2 value of a row with del_flag as N. If there is no row with DEL_F as N, it should return ID_2 value of row with DEL_F as Y.i have tried below query, but it showing the error.
select
(case
when min(del_f)='N' then to_number(ID_2)
when min(del_f)='Y' then to_number(ID_2)
end ) ID_2 from table where ID_1=?
Here, im passing ID_1 value from main query
PL/SQL :: Write Dynamic SQL To Query Unknown Number Of Columns
Jun 6, 2012I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....
However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....
SQL & PL/SQL :: Show Missing Rows In Query
Feb 7, 2012SQL> select * from t1;
NAME
----------
JACK
JOHN
JENN
SQL> select * from t2;
NAME
----------
JACK
PAUL
SQL> select a.name from t1 a, t2 b where a.name <> b.name
2 order by name;
NAME
----------
JACK
JENN
JENN
JOHN
JOHN
I would have expected to see the following:
name
-----
JOHN
JENN
PAUL
how to fix this query? In additon, is there a way to print the table name or some arrows (>>, <<) to show which table the values came from
I.e
name
=====
JOHN t1 or <<
JENN t1 or <<
PAUL t2 or >>
Performance Tuning :: How To Show Right IO Of Query
Jun 21, 2011I use oracle 11g r2
i have created a table for objects
create table table_t
(Record_no number,
t_no number,
position SDO_GEOMETRY
, occupation_time number);
and inserted many values (examples below )
INSERT INTO tra VALUES
(2,
20503,
SDO_GEOMETRY
(2001,
NULL,
SDO_POINT_TYPE (1387, 0, NULL),
NULL,
NULL),
23037
)
/
and( position) indexed as Rtree spatial index
now when i run spatial query such as
SELECT * FROM tra t WHERE
SDO_FILTER(t.position, MDSYS.SDO_GEOMETRY(2001,NULL,NULL, MDSYS.SDO_ELEM_INFO_ARRAY(1,1003,3),
MDSYS.SDO_ORDINATE_ARRAY(0,0,9000,0)), 'querytype=WINDOW') = 'TRUE' and t.position.sdo_point.X=1;
i do not know how many IO accrued ?
i tried set autotrace on
but the physical read is 0 , this is not possible because i have more than 100000 objects there and all indexed as R-tree
SQL & PL/SQL :: How To Show Unique Record Only In Query
Jun 20, 2012I am getting a following odd result where the same a Job could be linked to multiple enquiries. What I want it to show is just one job if its linked to multiple enquiries. I show you some sample result:I wrote the following SQL for it:
SELECT
'Enquiry' as Origin,
job.job_number,
enquiry.enquiry_number as Defect_or_Enquiry,
job.site_code,
central_site.site_name,
job.priority_code,
priority.priority_name,
job.target_comp_date,
'Target Completion Date required' as Due_Date
[code]....
Is there anything you would add so that it shows just a unique job number where the job has multiple enquiries linked to it? I tried putting SELECT DISTINCT but that did not work.
SQL & PL/SQL :: Query To Show Only Transactions For Today
Sep 12, 2010I need to create a report that will show all activity for the day that the report is run. The report will be run or auto-refreshed throughout the day. So, at 10am, 11am, 2pm, 4pm etc. I just need to display quantities received, shipped etc. for that same day. Since the report will not be run at a fixed time, I can't use sysdate - .5 for example.
View 6 Replies View RelatedSQL & PL/SQL :: Show Same Record Twice With A Single Query?
Sep 24, 2012I have a scenario for which I need to show same record twice.
i.e
SELECT EMPID, ENAME FROM EMP WHERE EMPID IN (101, 102, 101);
Result:
101 - Rahul
102 - Ravi
101 - Rahul
Is this possible with a single query.
Export/Import/SQL Loader :: How To Write The Query Option In Expdp
Jun 5, 2012how to write the query option in expdp ....in expdp by using query option...
where columnname between '05-May-12 02:57:00.000 AM' and '6-May-12 02:59:59.999 AM';
Write A Query Based On Inventory On Hand Packed Quantity
Jul 18, 2013which table do I have to use to get the Inventory On Hand Packed Quantity based on Oracle EBS Suite using backend?
View 2 Replies View RelatedSQL & PL/SQL :: How To Query To Show Schema About Metadata In OracleDB
Apr 23, 2010i got problem that i want to know Metadata in my Oracle DB 2 version there're OracleDB 9i and OracleDB 10g ,So i want to know about sql command to show all metadata of these Database, because i want to list My metadata and improve to Metadata's Standard(ISO/IEC 11179). How can i show all metadata of these database. is it the same sql command line with this 2 version Database to show all metadata?
View 1 Replies View RelatedSQL & PL/SQL :: Show Result Of Query Just Like Pivot Table?
Jul 20, 2011 me in building a query. I want to show the result of the query just like pivot table.
Test case
CREATE TABLE CPF_YEAR_PAYCODE
(
CPF_NO NUMBER(5),
INC_DATE DATE,
PAYCODE_TYPE CHAR(1 BYTE),
[code]...
I want that my query should look like the format as attached in the xls sheet.
PL/SQL :: Query Is Show All Keepers / Who Cares Of All Animals In Zoo
Jan 29, 2013I have such a database "ZOO" in oracle PL/SQL. URL.....The query is "Show all the keepers, who cares of all animals in the zoo.
View 14 Replies View RelatedApplication Express :: Query Builder Show Nothing?
Sep 16, 2013i am trying to use the query builder in my 11g xe database but vainly ,there are no results shown ?
View 6 Replies View RelatedSQL & PL/SQL :: Write Query To Delete Similar Records In Particular Fields (columns) In Different Tables
Jul 17, 2012write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies View Related