I'm having trouble with some SQL code regarding count and an outer join.
Here is my code.
SELECT o.salespersonid, Count(*) from
salesperson s, Ord o
Where s.salespersonid(+) = o.salespersonid
Group By o.salespersonid;
Where salesperson is a salesperson table and ord is a table containing orders.
The orders table contains a FK to salespersonid in the salesperson table.
I want it to return all salespersons along with the amount of orders they are on. It works but does not show the ones that do not appear on any orders hence the outer join.
I'm trying to SUM the results of "*SELECT originator, COUNT(*) as Inductions*" but am having trouble finding a solution? Can this be done and cause it to create the SUM in a row below the created Inductions column?
We are trying insert records from a select query in to temporary table, some of the records is missing in the temporary table. The select statement is having multiple joins and union all which it little complex query. In simple terms the script contains 2 part 1st Part Insert in to temporary table 2nd part Select query with multiple joins, inline sub queries, unions and group by classes and conditions Eg. If we execute select statement alone it returns some count for example => 60000 After inserting into the temp table, in temp table the count is around 42000 why is the difference?
It is simple bulk inserts... insert in to temp table select * from xxx. also, there is no commit in between. The problem is all the records populated by the select statement are not inserted in to temp table. some records are not inserted.
Also, we had some other observation. It only happens in its 2nd execution and not its first run. Hope there might be some cache problem Even, we also did not believe that. We are wondering. In TOAD, we tested however at times it happens. In application jar file, after "insert in to temp select * from xxx" we take the i. record count of temp table and ii. record count of "select * from xxx" separately but both doesn't match. Match only at 1st time.
I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt, (SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty FROM reldb d WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprägt%') AND saext='M' ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4. I hope, this specification is exactly enough to understand my intent.
I want to count the batch records using BATCH_ID with CASE statement ,for that i am using below query but its not working ,
SELECT COUNT(*) FROM <TABLENAME> WHERE VNBATCH_ID=CASE WHEN #SDC <10 AND #PERIOD >=10 THEN 0||#SDC||#PERIOD||#BATCH_ID WHEN #SDC <10 AND #PERIOD <10 THEN 0||#SDC||0||#PERIOD||#BATCH_ID WHEN #SDC >=10 AND #PERIOD <10 THEN #SDC||0||#PERIOD||#BATCH_ID ELSE #SDC||#PERIOD||#BATCH_ID END
I'm trying to return the number of records in my link table that contains the excursion_id I pass in by counting them. It doesn't seem to like the select count(*) into my output variable.
create or replace PROCEDURE BOOK_PASSENGER( EXCURSION_ID IN excursion_booking.excursion_id%TYPE, PASSENGER_ID IN excursion_booking.passenger_id%TYPE, NUMBER_BOOKED OUT NUMBER) AS BEGIN INSERT INTO EXCURSION_BOOKING VALUES(EXCURSION_ID, PASSENGER_ID, NULL); SELECT COUNT(*) INTO NUMBER_BOOKED FROM EXCURSION_BOOKING WHERE EXCURSION_ID = EXCURSION_ID; END BOOK_PASSENGER;
SELECT rp.id r_paper_id, COUNT(*) created, COUNT( CASE WHEN a.approved_yn = 'Y'
[code]....
But it fails, saying that 'single-row subquery returns more than one row' when I introduce the 'unique_users' clause. The remaining fields of the output are correct.
When I use the below code in my perl script (it is oracle database):
$query = "select a.sub_id, b.name from subscribertable a, invitationBin b where a.subscriberid=b.subscriberid"; $sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
everything is ok... and when I try to add a third table...
$query = "select a.sub_id, b.name, c.phone from subscribertable a, invitationBin b, personalDet c where a.subscriberid=b.subscriberid and b.subIdx=c.subIdx"; $sth = $dbh->prepare($sub_query) or die "SELECT-Query failed";
this fails... it seems like it doesnt let me add 3 tables in the SELECT query through perl script. The strange is that when I test this query with Oracle SQL Developer, it works fine...!
I am working on the following SQL select and I am having a mental block on how to get it fixed. I have two tables that I need to match on the codes in each table. If there is a just one record in Table1 with the same code as one record in table2 and both the date and name match then dont output those two records. Output all records if there are more than 1 record with the same code in each table. Below is some example data that is representive of a sample in the two tables and how the output should look based on that data:
Table1 code date name aaaa 1/1/2003 billy bob bbbb 2/2/2004 louis lewis cccc 3/3/2005 joe crab dddd 4/4/2006 mary little eeee 5/5/2007 joe black
Table2 code date name aaaa 2/2/2004 larry cole aaaa 3/3/2005 nat king bbbb 2/2/2004 louis lewis cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson dddd 7/7/2009 missy muffet dddd 5/5/2007 joe black eeee 8/8/2010 elton rocket
desired output results from select
aaaa 1/1/2003 billy bob aaaa 2/2/2004 larry cole aaaa 1/1/2003 billy bob aaaa 3/3/2005 nat king cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab cccc 3/3/2005 joe crab cccc 6/6/2008 dennis jackson dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet dddd 4/4/2006 mary little dddd 7/7/2009 missy muffet eeee 5/5/2007 joe black eeee 8/8/2010 elton rocket
Here is the select that I have so far:
select table1.rowid, table1_code, table1_date, table1_name, table2.rowid, table2_code, table2_date, table2_name from table1, table2 where table1_code= table2_code order by table1_code;
The above select gives me all records just fine, but does not eliminate single records that match. I tried using the Count(table1_code) > 1 and table2 code but I get a message about inproper grouping.
I have had a google around and can't seem to find an answer as to how do do the following Select statement. i am wanting to Select the following fields from across multiple tables.
I created a user and granted connect,resource priviliges. I gave access to this user for only 5 tables. when i check it later, other tables are also given access. How can i avoid this and give access to selected tables.
How does one select * from one table without selecting * from other tables that are included in a query? For example, if in the query below I want to view all fields in some_table, but not the fields from other_table, how do it?
select * from some_table st, other_table ot where st.id = ot.id
User1 is having 10000 tables in his schema...How can i grant "select" on a all tables of a user1 to another schema(user2) so that in future when user1 will create tables , the user2 will have "select" access on those tables automatically.
I dont want user2 to have "select any table" privillege.
User2 should not have "drop" privillege on his own tables.
I have long select which operate on 5 tables and has a lot of conditions in where clause (many combinations of values of just a few columns). Does reducing of those conditions could improve performance or just has a small impact?
I think if I have a lot of conditions on the same column, it don't take a lot of time to check them because values are in memory.
i am trying to left join a selection of two or more tables. what i have found, and solved part of my problem, is that oracle left joins only the last table in the select statement ...
i.e : select * from A, B left join C on C.id = A.id wouldn't work because left join applies to B and not A.
but as my queries grow i need to make something as follows :
select * from A, B left join C on (C.ID_A = A.ID and C.ID_B = B.ID) [... evantually more left joins as the preceding one may go here]
this query works for DB2 but Oracle claims that "A"."ID" is an invalid identifier, while the B.ID is recognized since it's the last table stated before the "LEFT JOIN" keyword.
im trying to select columns from different tables dynamically in a function . The parameter for the function will be table name and column id's, In this number of columns may vary . Is it possible to have dynamic %rowtype to store the cursor value in it.
i have two tables test1 and test2. i want to update the column(DEPT_DSCR) of both the tables TEST1 and TEST2 using select for update and current of...using cursor.
I have a code written as follows :
DECLARE v_mydept1 TEST1.DEPT_CD%TYPE; v_mydept2 TEST2.DEPT_CD%TYPE; CURSOR C1 IS SELECT TEST1.DEPT_CD,TEST2.DEPT_CD FROM TEST1,TEST2 WHERE TEST1.DEPT_CD = TEST2.DEPT_CD AND TEST1.DEPT_CD = 'AA' FOR UPDATE OF TEST1.DEPT_DSCR,TEST2.DEPT_DSCR; [code].......
The above code when run says that it runs successfully. But it does not updates the desired columns[DEPT_DSCR].
It only works when we want to update single or multiple columns of same table...i.e. by providing these columns after "FOR UPDATE OF" I am not sure what is the exact problem when we want to update multiple columns of different tables.
