I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
what I want my code to do is display the customers who live in either Georgia or Florida who recently placed an order totaling more than $80.
My code so far:
SELECT LastName, FirstName, SUM(Retail * Quantity) AS Total From CUSTOMERS, ORDERS, ORDERITEMS, BOOKS Where (State = 'FL' OR State = 'GA') Group by customers.customer# Having SUM(Retail * Quantity) > 80.00;
I have a query which is used to get the contact details based on the zip code
select * from contacts where primary_address_postalcode like '65084%' or primary_address_postalcode like '65011%' or primary_address_postalcode like '65034%' or primary_address_postalcode like '65078%' or primary_address_postalcode like '65050%' or primary_address_postalcode like '65037%' or primary_address_postalcode like '65329%' or primary_address_postalcode like '65072%' or primary_address_postalcode like '65081%' or primary_address_postalcode like '65038%'
how to display all the records in a table ,i am passing the table name as in param to the procedure/function suppose if i pass emp table name it will display 14 rec, if i pass dept it will display 4 records.
I want to display all records from table 1 (even the null values) that do not match records in Table 2. Below I am creating both tables and I am posting the result query I need.
------------------------------------ CREATE TABLE temp_table1 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
CREATE TABLE temp_table2 ( name VARCHAR2(12 BYTE), last_name VARCHAR2(12 BYTE), STATE VARCHAR2(2 BYTE), BIRTH_DATE DATE );
[code].....
The result query need to have 5 rows as shown below
NAME LAST_NAME STATE BIRTH_DATE ------------ ------------ ----- --------------------- john smith MA 12/1/1979 null null AZ null null null CT null null null MA null null null CT null
i want to display all the records of my database in a table with scrollingexample if i have 3records in my DB so a table must have 3records too !!!this is my code :
DECLARE Cursor cur IS SELECT numb_ph AS phone, name_emp AS name
[code]...
but the problem 's all records of my DB are display in one record (to view another record i use the scrolling ..
how to display the data which is shown below without duplicate records in compid and compname and all policy_id's should be there while excuting this query iam getting this data.
select distinct comp_id as compid, comp_disp_name as company, plcy_id as policyid,
19734 Save the Children 9013 GPA 19734 Save the Children 9012 GMC 20097 JMT 9486 GTL 10890 Steelco Gujarat Ltd. 9727 CAR 17330 Golden Jubilee Hotels Limited 8915 CGL 23117 NBHC 9093 GMC 17542 Heinz India 10693 Fire 19821 KSK Fabricators 10341 D&O 3769 Jones Lang Lasalle India 9199 WC 19821 KSK Fabricators 10340 WC
The select statement retrieves records well when executed at SQL promt.
But the same records are not displayed in this procedure by fetch from cursor.
The last message in the procedure 'completed' is also displayed.
create or replace procedure disp_rec(vid IN varchar2, vfeat IN number) is cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid; type vg is table of number; type vi is table of varchar2(3500);
I am running a query in our Clarity PPM database to return a list of all Support projects. This returns a simple list of project code and project name:
The query has the project resource tables associated with it, so I am able to list all resources allocated to the project. But for now i am only selecting a DISTINCT list of projects.
I have a separate query which returns a list of support resources.
select res.full_name, res.unique_name , dep.description from niku.srm_resources res, niku.pac_mnt_resources pac, niku.departments dep where res.unique_name = pac.resource_code and pac.departcode = dep.departcode and res.is_active = 1 and description like 'IMS%' and UPPER(dep.description) like '%SUP%'
What I need to be able to do in the first query, is return only projects that do NOT have a resource that appears in the resource list in the second query.
(the res.unique_name field in the second query can be linked to the same in the first query)
Logically, the process would be: 1. Identify Support Project 2. Identify Resources allocated to the project team 3. Compare with List of Support Resources 4. If any Resources in that list do NOT appear on the project, then return project.
I want added one new column in the below report which will shows only the items purchased on Cash basis but when i am running the query the column not showing any data.
if when you are querying a table in 11g and you use the order by clause and there is more than one occurrences with the same values in the order by, if the 11g default is different than from 10g.
For instance.
DECLARE MHBulk CURSOR FOR select invoice_nbr, customer_nbr, post_century, post_yymmdd, from CUSTOMERS where customer_nbr = 1234 order by post_century, post_yymmdd;
If you have more than one occurrence of the same customer_nbr, post_century, and post_yymmdd
it looks like the default order for how 11g retrieves the records is a bit different than the 10g default.
i hav the following records i need to display the records of financial year wise (i.e april year-1 to mar year) here there are no records for months 9,10(september and october)
what i want is 0 to be displayed over there in the below table
s.no key id yyyymm ce1 ce2 ce3 t ihu pf mi pwd F 920110600000156431372010043424541500065457709539XXXXXXXXXXU 10201106000001565313720100534245415000009539XXXXXXXXXXU 11201106000001566313720100634245415000009539XXXXXXXXXXU 12201106000001567313720100738555415000009539XXXXXXXXXXU 13201106000001568313720100836445415000009539XXXXXXXXXXU 1420110600000157131372010110000009539XXXXXXXXXXU 15201106000001572313720101276881082200001888009539XXXXXXXXXXU 1620110600000157331372011010000009539XXXXXXXXXXU
SELECT T1.COL1,T1.COL2, T1.COL3,T2.COL2, T3.COL1,T3.COL2, T4.COL1,T4.COL3, <CASE statements and calculations results - Some Amount1>, <CASE statements and calculations results - Some Amount2> [code]..........
First I need to display all the records from TAB_ONE which contains more than 10million records. If you see there are columns like AMOUNT_ONE,AMOUNT_TWO which has got some complex calculations and that is based on some other calculations and which in turn and goes on.... Like this I have some ten amount columns. Finally these records has to be inserted into a new table.
To get this I have written nested inline queries to calculate these AMOUNT columns but since considering the huge amount of records, it takes more than 8 hours for inserting this into a new table.
On first tab I am taking input (search criteria) on that input i am trying to display all records one by one as user click on next record button....
for that i used when-tab-page-change trigger for first record display and it display correctly.but for further record what should i need to write on next_record command button so all record i can see one by one.
Does ascending index ensures that query without order by will have the result set sorted?
E.g. the query is
select * from table_t where odm_type='I' and odm_uid>nvl(OpUidParm,-1); column odm_type has index created like this (default is ASC): create index ODM_UID_I on table_t (ODM_UID);
Will such a query always return the first record having the minimal odm_uid in all Oracle versions?
I have a requirement where I want to display amount what I have billed to my client and then how I received my amounts from that Bill. For eg.lets say there is bill no 001 amounting 10000 and after some days I received a part payment cheque/cash from client worth 2000 then he gave second part 2000 and then finally I received 2000. Still 4000 is remaining to be received.So in tabular format I want to display data which will have 3 records of 2000 and one record of balance 4000.
In attached file i have given few examples, where highlighted area is what i want to derive. There are formula's in Collection date, delay by and Int calc. Basically highlighted lines are the ones which should be shown in query result. And data shown above that is what i have right now.
Is there a way to find customers purchased only single product from the following table?
cusno Product Date ----- ------ ---- 121 ES 03/12 121 NT 30/12 131 ST 03/12 13 WT 04/12 150 ES 05/12 150 ES 06/12 150 ES 07/12 160 MN 05/12 160 ES 06/12 160 ES 07/12 162 NT 08/12
I need a query to display only 150 and 162 as they have purchased only one product.