SQL & PL/SQL :: Sub Query Using Outer Parameter?
Apr 10, 2010How to give table1 column to subquery with table2 :
(select t1.*, sq.*
from table1 t1,
(select a,b,c from table2 where col1= t1.col1) sq
where ...
ADVERTISEMENT
SQL & PL/SQL :: Query Without Outer Join
Nov 2, 2012I have an attendance table in which we have empno, time_in, time_out..etc.
I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.
I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?
SQL & PL/SQL :: How To Use Outer Join Condition In A Query
Jun 27, 2013How to use outer join condition in my below query. In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more records in the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records.
I want all the records from APPS_JP.GEDIS_OFFER_HEADER goh including other conditions. I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result.
SELECT GOH.ORIG_SYSTEM,
gsp.USER_NAME,
goh.ORDER_NO,
goh.OMEGA_ORDER_NUMBER,
goh.ORDER_TYPE,
[code].......
SQL & PL/SQL :: How To Refer Result Of Inner Query (analytic Function) In Outer One
Feb 23, 2012How can I refer the result of inner query - max(t.col3) in the outer query. I tried the 'group by' but it is not performing well
select
t1.col5, t1.col6
from t1,
(select t.col1
t.col2
[code].......
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.
PL/SQL :: Code_combination_id Doesn't Exist In Outer Query Table
Aug 20, 2013I have query in which having some issues with outer join.When I run the inner query, I am getting 121 rows and when i put outer query and run it, I am getting 69 rows coz the code_combination_id does not exist in the outer query table.But even when I say ( + ) , its not giving 121 rows..
SELECT DISTINCT t1.* --hr3.NAME prj_org --, p.segment1 prj_no, p.org_id --pt.task_number, pt.task_name, --paei.expenditure_type, pacdl. acct_ currency_code functional_curr from ( SELECT DISTINCT gll.ledger_id, gljeh.period_name "Fiscal Period", gll.NAME "Company Name", gljeh.NAME "Journal Name ", gljeh.je_source "Accounting Document Source", gljeb.NAME "Journal batch name", gljel. accounted_ dr,glcc.code_ combination_ id,glcc.segment1,glcc.segment2,glcc.segment3,glcc.segment4,glcc.segment5,glcc.segment6,glcc.segment7,glcc.segment8,gljel. accounted_ cr FROM gl_je_headers gljeh, gl_je_batches gljeb, gl_je_lines gljel,gl_code_combinations glcc, gl_ledgers gll WHERE gll.ledger_id =
[code]....
SQL Server Query - Oracle Does Not Support OUTER APPLY Statement
Feb 1, 2012DECLARE @MainTable TABLE (UniqueID INTEGER, Category VARCHAR(200), WeekDate DATETIME, VALUE INTEGER)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/07/2011', 5000)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(124, 'Pants', '10/07/2011', 4000)
INSERT INTO @MainTable VALUES(125, 'Shorts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(126, 'Shoes', '10/21/2011', 9000);
--select * from @MainTable;
[code]...
The query works with all the CTEs up to the last select statement. Oracle does not support the OUTER APPLY statement, how should the last piece be written to make it work in Oracle?
SQL & PL/SQL :: Left Outer Join Versus Right Outer Join
Aug 14, 2009i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?
View 6 Replies View RelatedSQL & PL/SQL :: Lexical Parameter Slow Down Query?
Feb 16, 2010All my following query is running fine.
SELECT pd,
vr_date,
x.vr_no,
vr_sn,
cac,
ac,
nar,
[code]....
but problem is that when above query is used or oracle reports then and lexical parameter is used then i slows down more than 100 times
SELECT pd,
vr_date,
x.vr_no,
vr_sn,
cac,
[code]....
above query is copy of top query but differnce is that &P_Unt_Cls lexical parameter is used instead of and Unit in(29,34,35,36,37,38,44,45,60,70,71), I am unable to understand why it slow down query.
SQL & PL/SQL :: Concatenate Parameter With A Column In Query
Sep 30, 2010SELECT OM.ORG_NAME||' Unit - ':p_unit name
FROM org_mst om
In the above query I want to concatenate Unit number passed by a parameter.
Any Query Timeout Parameter In Oracle?
Oct 2, 2013Is there any timeout for sql queries in oracle, if so what is the timeout parameter.
View 3 Replies View RelatedPL/SQL :: How To Pass List Of Values In Where Clause Of Query Parameter
Sep 18, 2012I need to get multiple code values and put it into a variable which later need to pass into the where clause of an sql. But i am not getting any results even i pass those values in the variable of an where clause: below is my Procedure:
declare
TYPE crMain_record is RECORD (
v_code dummy.v_code%type,
n_no dummy.n_no%type,
[Code].....
END;"lv_character" is going to hold the multiple code values which i need to pass into whare clause of the above SQL: the totlal number of these mulitipe codes can be more then 50..
And lv_character values are commung from a setup table
lv_character varchar2(32767):= '('||''''||'COMMIS'||''''||' , '||''''||'AGY BUILDING BENS'||''''||')';
--And lv_character values are commung from a setup table.where "lv_character" holdes multipe code values...
And lv_character values are commung from a setup table and upper(d.v_code)in lv_characterif the
Export/Import/SQL Loader :: Query Parameter And Partitions
Nov 28, 2012Version : 11.2.0.3
EMP_DTL table is a subpartitioned table (Range partitioned by MonthID and subpartitioned by COUNTYR_CODE).
It has 40 million records. I just wanted to export 100,000 records altogeter from all partitions for testing purpose.
But when I ran the below expdp with QUERY , it was exporting 100,000 records from each subpartition of the table !!
expdp "'/ as sysdba'" tables = HRTB_CMBH.EMP_DTL dumpfile=EMP_DTL_BKP.dmp DIRECTORY= DATA_PMP1 QUERY=HRTB_CMBH.EMP_DTL:"where rownum < 100001" LOGFILE= exp-partitionedTable.log The log
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201205"."TCO_201205_IND" 38.48 MB 100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201206"."TCO_201206_IND" 42.51 MB 100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201205"."TCO_201205_HKG" 31.28 MB 100000 rows
. . exported "HRTB_CMBH"."EMP_DTL":"TCO_201206"."TCO_201206_HKG" 32.97 MB 100000 rows
[Code]....
This is not mentioned in the Utitilies document. Is this expected behaviour ?
Server Utilities :: Syntax Error In Using Query Parameter In Expdp
Aug 17, 2013I want to take an export of table MESSAGE, and filter it for the day of 17 JUL 2013 (just to limit the size). i used the following expdp command but its not working.
expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile=FA0001P_BG_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
But with select query i am able to retrieve the rows for the specific date.
select * from MESSAGE where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
Here is the command with syntax error.
[oracle@orcl log]$ expdp SYSTEM directory=DATA_PUMP_DIR dumpfile=DB_16_08_2013.dmp logfile= DB_16_08_2013.log TABLES=schema.MESSAGE QUERY=schema.MESSAGE:where created_on between to_date('17-July-13 00:00:00','DD-Mon-YY hh24:MI:SS') and to_date('17-July-13 23:59:00','DD-Mon-YY hh24:MI:SS')
-bash: syntax error near unexpected token `('
Application Express :: 4.2 - How To Pass Date Picker Parameter Into Query
Aug 19, 2013steps to achieve this report I need a Date Column on top of the page Depending on the selected date, Result should be displayed below for e.g.
suppose i select a date as 14-aug-2013
Enter Report Date ___________________ ( Date Picker) Location Name Report Date Quantity valueXYS 14/08/2013 5000 1200000ABCD 14/08/2013 2000 24000-------------------------------------------------------------------------------------------------TOTAL 7000 1224000
Server Utilities :: Query Parameter - Export Subset Of Table Using RowID
Aug 15, 2012I am exporting using query parameter. I am trying to export subset of table using rowid.
SQL> select rowid , name from tab1;
ROWID NAME
------------------ ---------------
AAAM0rAAEAAAAGMAAA sam
AAAM0rAAEAAAAGMAAB sona
AAAM0rAAEAAAAGMAAC rose
AAAM0rAAEAAAAGMAAD chris
AAAM0rAAEAAAAGMAAE san
.................. ....
.................. ....
command given as
exp sam/sam tables=tab1 file=exprwid.dmp query="where ROWID='AAAM0rAAEAAAAGMAAA'" log=log1.log
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table TAB1
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings.
how can i export this record ?
Application Express :: Dynamic Reports - Runtime Parameter Passing To Report Query
Jul 27, 2013I have developed a report using apex shared components (report query and report layout) along with BI Publisher for report printing in pdf format. the Report template is built using Template Builder for Word. I have configured my Apex4.1 env with BI Publisher 11. This report is working fine for hardecode ID ( like REQUEST_ID=10 ). to make report dynamic i have made following additions in the report
I have added where clause in report query as WHERE REQUEST_ID = :REQUEST_ID (:REQUEST_ID is the text field in my page).
Added a text field names :REQUEST_ID to get user input at run time.
Create a button and add Repot Query URL in its properties. When i run the report with some valid value in :REQUEST_ID text field i did not get expected result infect there is no record getting displayed in the PDF file. i think this is not the write way to do this but interestingly im not getting any error. how can i get this functionality in apex this way or the other ?
Export/Import/SQL Loader :: Expdp Command To Export Table By Specifying Query Parameter
Aug 16, 2013I am using expdp command to export the table by specifying Query parameter. But i am unable to export the table based on the condition.
Ex:EXPDP username/password dumpfile=employee.dmp logfile=emp.log directory=DATADIR_EXP TABLES=EMPLOYEE query=EMPLOYEE:"UPDATED_TIME >= '04-JUN-13' AND UPDATED_TIME >= '05-AUG-13'" Estimate in progress using BLOCKS method...Processing object type TABLE_ EXPORT/ TABLE/ TABLE_ DATATotal estimation using BLOCKS method: 3 GBORA-31693: Table data object "<username>"."EMPLOYEE" failed to load/unload and is being skipped due to error:ORA-00933: SQL command not properly endedMaster table "<username>"."EMPLOYEE" successfully loaded/unloaded...Dump file set for <username>.SYS_EXPORT_TABLE_01 is: E:IMPDPemployee.dmpJob "<username>"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 12:34:45 Oracle 11g,
SQL & PL/SQL :: Outer Join On One Column?
Jan 7, 2011I have two tables A and B
Table A
bill_id number,
bill_line_id number,
discountnumber,
month varchar2(12),
year varchar2(4)
Table B
bill_id number,
bill_line_id number,
total_amt number,
customer_idnumber,
sales_repvarchar2(25),
sales_begindate,
sales_enddate
For all the records in table B I am trying to get discount information from table A. Each bill_id has one or many billing_line ids.It is not mandatory that all the bill_line_id from table B should be present in table A.in other words: a.bill_id can be 100 and a.bill_line_id = 456 but in table B, for b.bill_id =100 there can be b.bill_line_id = 123 and bill_line_id = 456.
I am joining the 2 tables based on bill_id, bill_line_id, and the start and end date.I am not sure how the outer join is done...Basically for every bill_id i need all bill_line id from table A.I am not sure how to *outer join on only one column*. When i tried it did not fetch all the rows from A.
select *
from table B left outer join table A on
a.bill_line_id = b.bill_line_id
and a.month = TRIM (TO_CHAR (b.sale_begin, 'Month'))
and a.year = extract(year from b.sale_begin)
where a.bill_id = b.bill_id
This is what I need: for every bill_line_id in table B i need discount information from table A if that bill_line_id is not in table A, the discount can be null. But, the bill_line_id in A and B should be for the same bill_id a.bill_id = b.bill_id and the outer join ......
SQL & PL/SQL :: Inner And Outer Loop In Cursor?
Jul 9, 2013BEGIN
FOR i IN 1 .. 10
LOOP
[Code]....
Assume the above is my code; there is inner and outer block.
Currently if inner block is failed, the outer block also going to exception block
But, my requirement is if inner block is failed it should not go to outer block, still the loop should continue and print 1...10 rows
SQL & PL/SQL :: OUTER Join That Gives No Result
Aug 17, 2012i have the following problem
select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
I should have a result with this request but there's nothing and the problem come from the fact that when the following condition is not met, there are no resulty even if there's an outer join
and memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
This condition makes Oracle ignores the outer join..I have tried to add this:
select dem.NUM_PCE, memo.comment
from demand dem, dem_comment memo
where CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE) = memo.parentId (+)
and (memo.dateCreation = (select MAX(DEM_DATE_CREATION_MEMO)
FROM dem_comment memo
WHERE memo.parentId = CONCAT(dem.NUM_PCE,dem.NUM_DEMANDE))
or memo.dateCreation is null)
but there are too much records and the result is not consistent
SQL & PL/SQL :: Outer Join With Cut-off Condition
Mar 10, 2010Suppose I have two tables
Transaction
TXN_ID:integer
TXN_DATE:date
Return_Transaction
RET_TXN_ID:integer
TXN_ID:integer
RET_TXN_DATE:date
[code]....
Transaction may have returned transactions. We use outer join to join the tables using TXN_ID. We have a report that shows the following data...The report use the following basic query to check all transactions with returned transactions information...Quote:
SELECT t.txn_id,
t.txn_date,
rt.ret_txn_id
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
Result
TXN_ID, TXN_DATE, RET_TXN_ID
100, 2010/03/10, 500
102, 2010/03/11, 501
If user want to check all transactions with no returned transactions, a where clause is appended to the query
Quote:
SELECT t.txn_id,
t.txn_date
FROM TRANSACTION t,
return_transaction rt
WHERE t.txn_id = rt.txn_id (+)
AND Nvl((SELECT 1
FROM return_transaction rt
WHERE t.txn_id = rt.txn_id),0) = 0
[code].....
SQL & PL/SQL :: How To Use Outer Join With Decode
Sep 19, 2012I need to apply an outer join on following statement.
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)=B.Fin_Wid;
I used following but error
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)(+)=B.Fin_Wid;
SQL & PL/SQL :: Time Bucket Outer
Aug 16, 2012I need joining the below 2 tables. I had used "between and " for Joining the table by using Inner Bound and Outer bound Key Columns and i use (current date - due_dt) for day calculation
My requirement is to show all the time buckets if at all no days falls in the particular time bucket.as like Outer join on Time bucket table
Loan_Account_summary
SNo Due_dt AMT
-----------------------------
102-08-201250000
203-05-201245000
309-05-201230000
415-09-201225000
531-05-201215000
608-06-201235788
719-10-201255000
Time Bucket
TB_IDTB_DESCReserve%Innner Bound outer Bound
------------------------------------------------------------------
1011-72.25 1 7
1028-302.5 8 30
10331-603 31 60
10461-901.75 61 90
10591-1201.5 91 120
106121-1801.25 121 180
107180+0 181 99999
Expected Output
TB_DESC Reserve AMT
----------------------------
1-7500000
8-30154822
31-600
61-90750000
91-1200
120-1800
180+65842
SQL & PL/SQL :: Using Outer Join Outside To_char?
Apr 1, 2008I need to use an outer join outside a to_char
to_char(the_date_field,'YYYYWW')(+) = '200815'
This throws up the error ORA-00936 missing expression.
I know it will work if I put the (+) right after the date before the mask but it need to check the week rather than the date.
I have tried using a function based index without success.
SQL & PL/SQL :: Double Right Outer Join
Oct 12, 2011I have a query which does a double right outer join, and I need it to be rewritten using the newer syntax.
The previous query had the following clause:
WHERE
MOD_INFO.MODIFICATION_ID (+)= MOD.ID
AND MOD.PEPTIDE_ID (+)= PEPTIDE.ID
Or you can think of it generically as:
T1.T2_FK (+)= T2.ID AND
T2.T3_FK (+)= T3.ID
How would this be written using the newer syntax?
SQL & PL/SQL :: Full Outer Join?
Oct 19, 2011 I am trying to use Full Outer Join, here is the DB version and test case
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
create table tab1 (The_Date DATE, x_ID varchar2(4), x_CODE varchar2(10),
r_qty number(8))
create table tab2 (The_Date DATE, x_ID varchar2(4), x_CODE varchar2(10),
o_qty number(8))
tab1 can have multiple records for key fields The_Date + x_ID + x_CODE
insert into tab1
values (to_date(sysdate)-2, '001', 'ABC', 100)
insert into tab1
[code]...
tab2 should have unique record for key fields The_Date + x_ID + x_CODE
insert into tab2
values (to_date(sysdate)-2, '001', 'ABC', 1000)
insert into tab2
values (to_date(sysdate)-2, '001', 'XYZ', 1500)
[code]...
THE_DATE X_ID X_CODE R_QTY
--------- ---- ---------- ---------
17-OCT-11 001 ABC 100
17-OCT-11 001 ABC 100
17-OCT-11 001 ABC 100
17-OCT-11 001 XYZ 1000
18-OCT-11 001 ABC 200
18-OCT-1
[code]...
When I tried my SQL statement as below it is not showing what i expected, but where and what it is? this approach is correct or not
devtest@ Test.DB> select a.the_date,
2 a.x_ID,
3 a.x_code,
4 sum(a.o_qty) o_qty, sum(b.r_qty) r_qty
5 from tab2 a full outer join tab1 b
[code]///
I want output as below:
THE_DATE X_ID X_CODE O_QTY R_QTY
--------- ---- ---------- --------- ---------
17-OCT-11 001 ABC 1000 300 -- here O_QTY should be 1000
17-OCT-11 001 XYZ 1500 1000
18-OCT-11 001 ABC 500 600 -- here O_QTY should be 500
19-OCT-11 001 ABC 500
18-OCT-11 001 XYZ 2000 -- here The_Date, x_ID and x_CODE should appear
EDITED FOR:
devtest@ Test.DB> ed
Wrote file afiedt.buf
1 with data1 as (select the_date, x_ID, x_CODE, sum(o_qty) o_qty
2 from tab2
[code]...
now only the required is values of THE_DATE, X_ID and X_CODE of Last Record.Still asking about, is it correct approach ?
SQL & PL/SQL :: Insert And Outer Join
Aug 13, 2011I am working on a query that has outer joins. The result of this query has to be stored into a table. When i execute this query alone (without INSERT command) it works absolutely fine but when i put the INSERT statement it gives me the error message "ORA-01719: outer join operator (+) not allowed in operand of OR or IN". The thing is I dont have an IN or OR in my query. I am in the middle of something
Attached is the screen shot of both the result and the error message. Query mentioned below:
<INSERT into table query comes here..>
select * from (
SELECT yy.ACCOUNTID, yy.dump_date, yy.balance, yy.Last_30days_activity,zz.GSM_Balance, nvl(yy.SUM_GSM_REV, 0) from
(
[Code]....
SQL & PL/SQL :: Outer Join 3 Tables
Jan 23, 2013I'm wondering what part of this query is wrong because obviously I'm not getting the desired results.
select a.iss_id,C.ISSR_ID
from ft_t_isid a left outer join ft_t_issu b on a.INSTR_ID=b.INSTR_ID
left outer join ft_t_irid c on b.INSTR_ISSR_ID=c.INSTR_ISSR_ID
and a.ISS_ID in ('CA13606ZDD20',
'CA780086AP98',
[Code]...
I know that the problem is in the joins. I expect to get 9 rows of result but I get a lot more and they are not even what I'm looking for.
PL/SQL :: How To Use Outer Join Condition
Jun 27, 2013How to use outer join condition in my below query.In the table APPS_JP.GEDIS_OFFER_HEADER goh I have more recordsin the table APPS_JP.GEDIS_ORDER_BUILDS gob I have less number of records. I want all the records from
APPS_JP.GEDIS_OFFER_HEADER goh including other conditions.I have tried goh.OFFER_NO=gob.OFFER_NO(+) but same result. [code]SELECT GOH.ORIG_SYSTEM, gsp.USER_NAME, goh.ORDER_NO, goh.OMEGA_ORDER_NUMBER, goh.ORDER_TYPE, gc.CUSTOMER_ID, gc.OMEGA_CUSTOMER_NUMBER, CASE WHEN gc.PRIVATE = 'N' THEN gc.CUSTOMER_NAME ELSE ''
[code]...