Combine Tables With Union But Limit Number Of Records?
Sep 13, 2007
Updated to Add: In a last ditch search, I found my answer with ROWNUM <= 1 in the Where clause. It works and I can go from there with what I want to do.
I have a website that pulls similar information from multiple queries using a Union-based query. I want to only pull 1 record from one section, two from another, and 5 from the third. I've so far found LIMIT but haven't been able to get it to work in that way. Is it possible to limit each query in the union as I am looking to do?
The query is:
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
FROM (so_news p LEFT OUTER JOIN so_news_deptLevel c ON p.newsID = c.newsID) LEFT OUTER JOIN so_departments d ON d.deptID = c.deptID
WHERE p.storyType = 'alert' AND c.display = 'yes' AND c.startDate <= sysDate AND c.endDate >= sysDate
UNION All
SELECTc.priority, c.startDate, p.headline, p.newsID, p.kicker, p.webPath, p.makePopup, p.thumbnail, p.shortDesc, p.storyType, d.filePath
1. My detail form has part number as one of the fields and I want to make sure the user can enter a part number only once. How can I make sure user cannot enter it more than once?
2. I want to limit number of detail records for every parent record to 8. How can I accomplish this?
3. I have approval flags at both master and detail. If the user tries to approve the master with some/all detail records unapproved, system should display error message and make the user approve the detail before approving the master.
I need to display the data as range , i have created one table along with inserts as below , i need the desired output as mentioned below.The range will start from field STMH_PM_CODE , it will get the first and last stmh_pm_code and stmh_pm_desc which are in the same combination of STMH_NO,STMH_JOB_NO,STMH_batch_no and stmh_rev_no,
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1001','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002',FRAME','0'); ERROR: ORA-01756: quoted string not properly terminated
SQL> ED Wrote file afiedt.buf
1* INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1002','FRAME','0') SQL> /
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1003','COLUMN','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0002','1107040-1004','FRAME','0');
1 row created.
SQL> INSERT INTO OT_SHOP_TRANSMITTAL_TEST VALUES ('PR-1107-0001','1107040','0001','1107040-1005','FRAME','0');
1 row created.
SQL> COMMIT;
Commit complete.
-- i want it as below see the line in stmh_pm_desc the description gets added until there is a change in either stmh_batch_no or stmh_rev_no since we have stmh_pm_code 1107040-1004 with stmh_batch_no 0002 it goes to other line as a seperate new range and first one will stop with 1107-1003 as that is the last combination and new line or range will get added over again.
•Limit of number of sub programs in a package. •Limit of number of lines in a sub-program. •Limit of number of statements in a sub-program.
I searched on net and found below useful information.
The size limit for PL/SQL stored database objects such as subprograms, triggers, and packages is the size of the Descriptive Intermediate Attributed Notation for Ada (DIANA) code in the shared pool in bytes. The Linux and UNIX limit on the size of the flattened DIANA/code size is 64K but the limit might be 32K on desktop platforms.
The most closely related number that a user can access is the PARSED_SIZE in the static data dictionary view *_OBJECT_SIZE. That gives the size of the DIANA in bytes as stored in the SYS.IDL_xxx$ tables. This is not the size in the shared pool. The size of the DIANA part of PL/SQL code (used during compilation) is significantly larger in the shared pool than it is in the system table.
is there any recommendation on limit of number of subprograms should exist in a package exist, If there is any guidelines/recommendation exist for these.
I have to optimize a batch job which returns > 1 lakh records . I have a commit limit being passed . I am planning to divide the cursor records for processing as follows. If the cursor suppose returns 1000 rows and the commit limit passed is 200 , then i want to fetch 200 records first , bulk collect them into associative arrays and then bulk insert into target table.
After this is done, i will fetch the next 200 records from the cursor and repeat the processing. I would like to know how i can divide the cursor records, and fetch "limit" number of records at a time and also be able to go to the next 200 recs of the cursor next time.
What is the limit on number of partitions on a table.on many forums , 1024k-1 is given the maximum limit.But Exactly , I am not able to understand this 1024k-1.
I have a view that contain multiple tables with ( UNION all ) clause , is there any way that if i query from this view I can explicitly specify the table that i need the data from ?
Let say i have view that contain salaries of 2011
Create view sals_2011 as select * from sals_jan2011 union all sals_feb2011 ..... union all select * from sals_dec2011.
if i issued select * from sals_2011 where emp_id >500 and date < 01-feb-2011 the explain plan show me that full tables and indexes are in processing, while i know that i need only to scan sal_jan2011, and of course it is taking much longer time than selecting from the original table only.
,in 11g,is there a way I could limit the number of open application actions by some os user ?We have an application where users are executing the same thing while the last is not yet finished,so we have several same things runninng at the same time executed by the same user.
Can we restrict that somehow through the database or that needs to be done through application?
I have the following Union All query. It throws the following error in SQL plus
ERROR at line 27: ORA-01789: query block has incorrect number of result columns
After doing some google for the above error it suggests there are incorrect number of columns in the Union All query.I could not figure out the exact location well SQl Plus says error is on line 27 at the first opening bracket like
I have written the following PL/SQL procedure to delete the records and count the number of records has been deleted.
CREATE OR REPLACE PROCEDURE Del_emp IS del_records NUMBER:=0; BEGIN DELETE FROM candidate c WHERE empid in (select c.empid from employee e, candidate c where e.empid = c.empid and e.emp_stat = 'TERMINATED' ); [code]....
I have two sql queries. They run the one after another.
Query 1: select * from capital where member_status = 'MEMBER' AND rownum <= 25 order by price desc
Query 2: select * from capital where member_status = 'MEMBER' AND rownum > 26 order by price desc
Question is, in the query 2 I want records greater than row number 25. In query 2, I don't want the records that were fetched in Query 1. Is there any way to do this without using rownum?
Data from table 1 is printed first and after every X number of rows another set of data either from the same table or a different table needs to be printed.
I know how to use cursors to read all the records in a table.
But how can records that should be processed together be read in a loop, using cursors?
If there were, for instance persons from different nationalities, French, German, English, ..., I woluld like to read the first unknown number of persons from France and process that data, and then continue with the unknown number of germans and process that, and so on.
i have 100 records in table1,like as we have more 15 tables without data. the issue is how can i insert above table1 data(100 records) into different 15 tables in single sql command.
However, I need to have the total of no of records displayed for the sql statement and I need it in one sql statement with the one above so that I can display the total no of records along with the information for each field.
We have a package that processes(Insert and Update) set of records and runs in loop.
Package is working fine when we set number of records to 300 for each loop and commit. when we increase the number of records to 1000, package is getting stuck(Not ending and not coming out of loop).
Is there any DB parameter to be changed in my environment to process more number of records at at time?
I have a temporary table (with on commit preserve rows property) which is populated thru insert into command from a procedure. After which, i need to query the records from the populated temp table.
However, my query returns nothing. My procedure works fine cause i tried executing it to populate a regular table and it is ok. However, it shows no output in the temp table cause probably it is creating another session. How do i select the rows from the temp table after populating it from a procedure.
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 am executing a script that is deleting some parent records and the corresponding child records as I have used the "on delete cascade" with the Foreign key Constraint.
My question is that can I list the records that are being delete from all the tables i.e. both parent and child tables. Is some thing like spooling can work in this or do I have some other option with which I can see(select) all the deleted records.
I have a service that executes a pl/sql function (legacy app) to delete records from multiple tables. This function works fine in development, and has worked fine in production until about a week ago. I'm not a DB guy but the DB guys are trying to say this is an application issue. That may be, be the "insuff privileges" really leads me to believe otherwise.
What is causing this type of Oracle error? Permissions between dev and prod are the same, yet it works in dev but not prod.
ORA-29876: failed in the execution of the ODCIINDEXDELETE routine ORA-20000: Oracle Text error: DRG-50857: oracle error in textindexmethods.ODCIIndexDelete ORA-20000: Oracle Text error: DRG-10602: failed to queue DML change to column ABSTRACT for primary key AAAfBoAAEAABa62AAA DRG-50857: oracle error in drexrdml ORA-01031: insufficient privileges [code]....
In form i call a record from database table through execute_query. i have lov on ITEM_NO
i want to display on that form, no. of records of ITEM_NO which i select from the list. like if i have 10 records with item_no which i select from list is must show 1/10.
if i press the down arrow it should show next record of the same item_no.
