SQL & PL/SQL :: Connect By Not Working With Nested Queries In From Clause
Jan 11, 2012
Using Connect By on a query which has a nested from clause(The from clause fetches around 100k records) gives incorrect results but if the same nested queries are used to build a table and the Connect By is used on the table then the output is correct.
I put the nested queries in a 'WITH' clause and got the correct output also.
I am not sure how to give the code here as you would need dump to make them work. I am giving the a sample
--Non Working Code
SELECT con_item, prod_item, compsite, bcsite, ibrsite, res
FROM (SELECT con_item, prod_item, compsite, bcsite, ibrsite, res
FROM (SELECT bd.item AS con_item, bd.fromid AS compsite,
bd.toid AS bcsite, bd.toid AS ibrsite,
[Code]....
View 1 Replies
ADVERTISEMENT
Jun 21, 2010
I have 4 select queries.
Query1
Query2
Query3
Query4
First Step:Combine Query1 and Query2,but the requirement is Query2 should only use the Acct_ID and Bill_ID which are output from Query1.
Second Step:Combine Query3 and Query4,but the requirement is Query4 should only use the Acct_ID and Bill_ID which are output from Query3.
Third Step: Is to now combine both the data set from First Step and Second Step.
In order to achieve my First and Second Steps I used WITH clause.
With S1 as ( Query1 ),
S2 as ( Query1 Union Query2)
Select S2.* from S1,S2
where S1.ACCT_ID=S2.ACCT_ID
AND S1.BILL_ID=S2.BILL_ID
With S3 as ( Query3 ),
S4 as ( Query3 Union Query4)
Select S2.* from S3,S4
where S3.ACCT_ID=S4.ACCT_ID
AND S3.BILL_ID=S4.BILL_ID
1. Is that approach right for achieving my First and Second step requirements ?
2. How to achieve Third Step ?
View 3 Replies
View Related
Jul 31, 2012
I'd like to audit a table for any SELECT queries that are executed against it with no WHERE clause. I've read the documentation on DBMS_FGA carefully, and as close as I can tell, creating a policy with a NULL audit_condition causes all queries against the table to be audited, which isn't what I'm looking for.
What I'd like is something like this:
DBMS_FGA.ADD_POLICY (
object_schema => 'scott',
object_name => 'emp',
policy_name => 'mypolicy1',
audit_condition => 'WHERE CLAUSE IS ABSENT',
audit_column => 'comm,sal',
[code].......
SELECT * FROM EMP;but queries with conditions ('WHERE sal > 400', for instance) are not trapped.
I'm using 11gR2 (11.2.0.2) on OEL.
View 2 Replies
View Related
Aug 30, 2010
I have a query regarding nested tables while exporting. Are we not allowed to use Query clause during the export of nested table?
I am getting the error:
"EXP-00053: unable to execute QUERY on table NT_LP_COREBAL_MAINT because the table has inner nested tables
Export terminated successfully with warnings."
The exp command we are using is:
exp XXXX/XXXX@XXXX file=test1.dmp log=test1.log tables=nt_lp_corebal_maint query='where domain_id=10110307'.
View 1 Replies
View Related
Aug 31, 2012
I'm trying to use SYSTDATE in a WHERE clause of nested SELECTS..I want to select a range of info from two days back from today until today (or time it is being run). But when I run this, it says I have a missing expression...
SELECT XXXX
FROM XXXX
WHERE DATE BETWEEN TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') -2)
AND TO_DATE(SELECT TO_CHAR(SYSDATE, 'YYYYMMDD HH24:MI:SS'))
View 7 Replies
View Related
Sep 21, 2011
I have a query which returns a nested table as a result of split function. I used any method to unnest the data. But I couldn't. I try it with this query.Note: To run the following query I attached everything needed.
-- This query gives very strange results.
SELECT *
FROM
(
SELECT
[code]...
View 18 Replies
View Related
Mar 16, 2013
Is there a way to define a SELECT clause once and reuse it in many other queries?
I have many procedures with same SELECT statement and I'm trying to find a way to not have to write out the SELECT clause in every function or procedure. Same question applies to FROM, WHERE, etc clauses.
View 12 Replies
View Related
Sep 18, 2013
SELECT sno,mid,mname
FROM manage_date
WHERE mname IN
('KIRAN-KUMAR',
'RAHUL-RAJ',
'KAUSHAL-SONI');
IF I use this query directly in DB it's working fine. But when this query is calling in .net using parameter as below it's not giving any records for more than one value. IN (:p_mname)
If I pass one name 'KIRAN-KUMAR' from .net It's working.
If I pass multiple names from .net query not returning any records.
View 4 Replies
View Related
Nov 8, 2010
It seems some issue with With Clause along with Row Number. Output is strange when it comes with SELECT MIN()... But Looks fine Once you add "order by" or "where" clasue inside WITH Clause
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
[code]....
But Output looks fine once you add ORDER By clause inside WITH clause
SQL> WITH EM AS
2 (
3 SELECT EMPNO, ROWNUM RN
4 FROM SCOTT.EMP T ORDER BY ROWNUM
[code]....
why it happens ?
View 4 Replies
View Related
Mar 12, 2012
IN clause is not working for stored function.At same time, the LIKE conditon is working.
SQL> CREATE OR REPLACE FUNCTION GET_EMPLOYEES (in_asset_type in SECURITY_TYPE.asset_type%TYPE)
2 RETURN VARCHAR2
[Code].....
View 1 Replies
View Related
Oct 31, 2012
I have two columns Department and EmpName:
Department EmpName
____________________________________
Sales empname1
Sales empname2
Marketing empname3
Development empname4
Now I want to count the number of employees in every department. I want the output to be
Department Total
______________________
Sales 2
Marketing 1
Development 1
I am retrieving names of the department through a subquery
The query I am trying to execute is:
SELECT Department, Employee FROM
( SELECT ...query from other table) AS Department, count( A.EmpName) AS Employee
FROM Employer A, EmployeeInfo B
WHERE (A.EmpID = B.EmpID AND A.EmpCategory like 'Category2')
GROUP BY Department
I know that you cannot group by using aliases and hence a little work around, but still the query isn't working.
View 2 Replies
View Related
Jan 23, 2007
our system has always been running on mysql database and recently we have switched to oracle. As the current system is coded using mysql query syntax, when i run this program using oracle database, i got a error. The language that I'm using is JSP.
this is the error message:
The following query could not run on oracle. To convert these mysql queries to oracle compatible queries.
SELECT productID,productName FROM products order by productName;
select newsID,newsDate,newsHeadLine1 from news order by newsDate Desc limit 3
SELECT fuji_products.productID, productName_Display FROM products,products_availability where products_availability.productID=products.productID and (product_status='enabled' or product_status='all') AND category='12'
SELECT catID, catSub1 from category where catSub = '"+ prodCat +"' AND catSub1 is not null group by catSub1 order by catSub1
View 6 Replies
View Related
Apr 1, 2013
Below is the sample code working fine in 10g and not working now in 11g.
CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "PSTest" AS
import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.List;
[code]....
we got the below error: ORA-00932: inconsistent datatypes: expected an IN argument at position 1 that is an instance of an Oracle type convertible to an instance of a user defined Java class got an Oracle type that could not be converted to a java class
Current Oracle version is Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit and the version we are upgrading is Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
View 3 Replies
View Related
Mar 22, 2013
I have two tables : oa_membership_dtl(in this created_by field is varchar2(200 byte) ,oa_partner_usr_dtl(in this table partner_userid is number(8,0) i need to do join on above fields.
I am using following two queries:
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on to_char(partner_user.partner_userid,'9999')=membership.created_by
select * from oa_membership_dtl membership
join oa_partner_usr_dtl partner_user
on rtrim(ltrim(partner_user.partner_userid||' '))=rtrim(ltrim(membership.created_by))
by using first data is not fetched but 2nd is working fine , i am getting the matched records using 2nd query.
whats the diff between to_char and || symbol?
View 1 Replies
View Related
Jul 7, 2012
I have been troubling with this issue for the last two weeks...tried so much, but could not able to resolve....
sqlplus username/password@IPAddress:port/ServiceName
I am able to connect to DATABASE from SQLAssistant only when I provide string like ABOVE........If I dont mention the SERVICE, m unable to connect to DB.Now the biggest issue is with SQL Developer.....m not at all able to connect to DB using SQL Developer.
View 3 Replies
View Related
Aug 17, 2010
I have installed oracle developer 6i. in sqlplus i want to put my username and password but nothing in the connect string field.
View 1 Replies
View Related
Oct 6, 2010
I have the following computer/setup:
Acer laptop, Intel Processor w/ Windows 7 Professional (64-bit)
Delphi 2010 Professional
Oracle XE (+ the Oracle XE client that comes with it)
I cannot can't to the Oracle database from Delphi. The error I keep getting is:
Alias is not currently opened. [Oracle][ODBC][Ora]ORA-12154:TNS: could not resolve the connet identifier specified.
Here's what I've done:
1. Create an ODBC connection to my Oracle database
- run C:WindowsSysWOW64odbcad32.exe to open the ODBC administrator.
- Data source name: bt_user
- TNS Server Name: localhost/xe
- user bt_user
- Clicked "test connection" -- Success.
2. Open Delphi and drop a TDatabase component on my main form.
- Set the Alias Name property to "bt_user" (the alias I set up in ODBC)
- Set database name to "bt_user".
- Checked "connected".
Alternatively, I tried using the Delphi ADO components (the dbGo components).
- Drop TADOConnection component on my form.
- Select "connection string" and open the dialog.
- Click "build string".
- Select "Microsoft OLE DB Provider for ODBC Drivers" from the selection.
- In "use data source", select "bt_user" from the drop-down list (my ODBC connection from #1)
- Click "test connection".
Again the same error.
My questions are:
1. Can Delphi work with Oracle XE (Express Edition)?
2. If so, then do I need to install the "Instant Client"?
3. Will the instant client for Oracle 10g work with Oracle 10g XE?
4. Do I need to install any additional ODBC driver(s)?
5. Why was I successful in creating the ODBC connection in Windows, but it failed in Delphi?
6. Will Visual Studio.NET (C#) work with Oracle XE, and what are the steps for setting that up?
View 2 Replies
View Related
Apr 23, 2010
can we use something like this
"select ... order by emp from emp"
what is to be done? so that this qurey runs. no co-related subquery to be used.
View 6 Replies
View Related
Jan 9, 2013
a material related to collections with Examples and also how to declare table type values.
View 9 Replies
View Related
Mar 1, 2012
DROP TABLE BatchEnrollment;
CREATE TABLE BatchEnrollment (
servicepointid VARCHAR(100),
curtailprogramid VARCHAR(100)
)
[Code]....
------------
Question I am getting error.
View 2 Replies
View Related
Jul 24, 2007
I have here my SQL
-> INSERT into myTable (col1, col2, col3) values(SELECT table_seq.nextval from dual, 'value2', 'value3');
That's not working...
View 8 Replies
View Related
Feb 1, 2011
There's a table T with a nested table within it on the master database. I need to mantain a materialized view of the table T on a remote database.
I get this error: QUOTE ORA-22804: remote operations not permitted on object tables or user-defined type columnsIs there any recommended workaround of this problem? The remote data must have the same structure as the master one.
The data should be refreshed every day, the data changes moderately, there are more or less 500 records.
View 2 Replies
View Related
Jan 23, 2011
When i am trying to use another RECORD as a NESTED RECORD into another program, it shows the below error: "PLS-00201: identifier 'TIMEREC' must be declared"
Record 1:
---------
declare
type timerec is record ( seconds smallint);
begin
dbms_output.put_line('Hello');
end;
Record 2:
---------
declare
type days is record (day number(5),time timerec);
begin
dbms_output.put_line('Hello');
end;
View 5 Replies
View Related
Jul 13, 2010
I'm working on a nested case statement, can't seem to get it right.We have a table that has injury_codes. What I'm trying to do is come with a nested case statement that will put the codes in a specific_cat, and based on the specific_Cat, assign a Generic_cat.
Example.
I have codes 11,12,13,14,15, 15, 17, 18, 19, 20, 21, 22
Codes 11, 12, 13 have a specific_cat "Head Injury"
Codes 14, 15, 16 have a specific_cat "Spinal Injury"
Codes 17, 18 have a specific_cat "Burns".
All 3 of these specific_cat come under the generic_cat "Trauma"
Codes 19, 20 have a specific_Cat "High Risk Pregancy"
Codes 21, 22 have a specific_cat "Premature Birth".
All 2 of these specific_cat come under the generic cat "OBGYN"...So my case stement should return :
CODESSPECIFIC_CATGeneric_Cat
11Head InjuryTrauma
12Head InjuryTrauma
13 Head InjuryTrauma
14Spinal InjuryTrauma
15Spinal InjuryTrauma
16Spinal InjuryTrauma
[code]...
just a small sample of codes, specific_Cat and generic_cat. I hundreds of these codes I need to categorize.
View 8 Replies
View Related
May 17, 2011
what is the advantage of Nested table ?
View 1 Replies
View Related
Aug 20, 2010
In the example below I believe I have created a Nested Table of PL/SQL type and have tried various references to get the SET operation to work, line containing MEMBER OF. Taking the example below from the oracle documentation I have two questions.
1) As I understand it I should be able to use SET operations on Nested tables of PL/SQL types, (I am not using the CREATE OR REPLACE DDL statement prior to the DECLARE block.).
Is this correct?
2) I am assuming that I have to reference the record, can I reference by its type / row instance or can I only retrieve the record like a Cursor Fetch solution, (which would defeat the purpose.).
Is this a SQL to PL/SQL <> PL/SQL to SQL problem?
download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/collections.htm
Example 5-24 Comparing Nested Tables with Set Operator
SET serveroutput ON
DECLARE
answer BOOLEAN;
[code]...
View 1 Replies
View Related
Sep 5, 2013
collection1
============
SELECT o.object_id
BULK COLLECT INTO l_obj_info
FROM (SELECT n.node_id, n.object_id
FROM nodes n
START WITH n.node_id = 100
CONNECT BY PRIOR n.node_id = n.parent_node_id) n
INNER JOIN objects o ON n.object_id = o.object_id
WHERE o.object_type_id = 285;
collection2
============
SELECT *
BULK COLLECT INTO l_tab
FROM ((SELECT REGEXP_SUBSTR (i_l_text, '[^,]+', 1, LEVEL)
FROM DUAL
CONNECT BY REGEXP_SUBSTR (i_l_text, '[^,]+', 1, LEVEL) IS NOT NULL));
END;
collection3
============
SELECT o.object_id
BULK COLLECT INTO l_fin_tab
FROM objects o JOIN ATTRIBUTES att ON o.object_id = att.object_id
WHERE o.object_id = collection1.object_id
AND att.VALUE = collection2.val;
how to implement for loop in the collection3 to get the values from collection1 and collection2. i have tried in the below way
CREATE OR REPLACE TYPE LIST_OF_ATTRIBUTES_TYPE AS TABLE OF varchar2(4000);/
CREATE OR REPLACE TYPE LIST_OF_OBJECT_IDS_TYPE AS TABLE OF number(9);/
CREATE OR REPLACE FUNCTION f_get_objects_by_type_id ( i_object_type_id IN NUMBER, i_l_text IN VARCHAR2, i_scope_node_id NUMBER)
[Code]....
View 2 Replies
View Related
Sep 10, 2013
I am studing Multidimensional Nested table and have the below code:
DECLARE TYPE table_type1 IS TABLE OF INTEGER; TYPE table_type2 IS TABLE OF TABLE_TYPE1;
table_tab1 table_type1 := table_type1();
table_tab2 table_type2 := table_type2(table_tab1);
BEGIN FOR i IN 1 .. 2 LOOP table_tab2.extend;
table_tab2(i) := table_type1();
[Code]...
exception when others then dbms_output.put_line(sqlerrm);END; This code is working fine as of now.But,If i comment below code(table_tab2 is also extended latter):
table_tab2.extend;
table_tab2(i) := table_type1();
then it gives me error 'Subscription Beyond count'. I would like to know why i need to extend table_tab2 twice?!
View 7 Replies
View Related
Jul 11, 2011
I want to truncate table partition but I'm getting error:
CODEORA-02266: unique/primary keys in table referenced by enabled foreign keys
because of table has a nested table. Currently this column is not in use so I could drop it, but I want to avoid it (table is huge). Is there another posibility to do truncate partitions in this table? ALTER TABLE ... SET UNUSED doesn't resolve the problem.
View 2 Replies
View Related
Dec 2, 2010
I try to do this:
CREATE MATERIALIZED VIEW MV_NESTED_DATA
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
USING NO INDEX
REFRESH COMPLETE
ON DEMAND
START WITH ROUND(SYSDATE)
NEXT ROUND(SYSDATE) + 1
WITH ROWID
AS
select NESTED_TABLE_FIELD from MY_TABLE@Y_DB_LINK;
where NESTED_TABLE_FIELD is a nested table stored as T_NESTED_TABLE
And I get the error: ORA-12014: table 'T_NESTED_TABLE' does not contain a primary key constraint
Why should it if I try to create a MV with "WITH ROWID" refresh option and not "WITH PRIMARY KEY" one?
View 4 Replies
View Related
