SQL & PL/SQL :: How To Get Data In Logic Order

SQL & PL/SQL :: Find Logic To Query Data Based On A Condition

Aug 18, 2013

find the logic to query data based on a condition like..I am having a table dummy

USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH

The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.

View 22 Replies View Related

SQL & PL/SQL :: Logic To Find Table Names And Data In Oracle?

Feb 1, 2013

I have a schema DEF and I have a column_name CREATE_DATE.

I wanted to write a procedure which will give me list of tables whose CREATE_DATE data is prior to year 2009.

View 5 Replies View Related

Performance Tuning :: Order Of Steps In Execution Path And Order Of Predicates In The Plan

Mar 20, 2012

Which step in the following plan is the first step of execution

I reckon it is "TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE"

Is that correct?

In the "Predicate Information (identified by operation id):"

section the predicates - access and filter for the step "TABLE ACCESS FULL | PYMNT_DUES" are displayed first

Isn't there any relation between the order of execution steps and the order in which predicates are displayed?

Execution Plan
----------------------------------------------------------
Plan hash value: 538700484
-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2364 | 15 (14)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 1 | 2364 | 15 (14)| 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 2364 | 14 (8)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 2313 | 13 (8)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 2281 | 12 (9)| 00:00:01 |
| 6 | NESTED LOOPS OUTER | | 1 | 2255 | 11 (10)| 00:00:01 |
|* 7 | HASH JOIN | | 1 | 175 | 6 (17)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | INDX_2 | 12 | 612 | 2 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | PYMNT_DUES | 43 | 5332 | 3 (0)| 00:00:01 |
| 10 | VIEW PUSHED PREDICATE | | 1 | 2080 | 5 (0)| 00:00:01 |
| 11 | NESTED LOOPS | | 1 | 154 | 5 (0)| 00:00:01 |
| 12 | NESTED LOOPS | | 1 | 103 | 4 (0)| 00:00:01 |
|* 13 | TABLE ACCESS BY INDEX ROWID| BANK_BATCH_STATE | 1 | 32 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | INDX_BBS_1 | 3 | | 1 (0)| 00:00:01 |
|* 15 | TABLE ACCESS BY INDEX ROWID| DAILY_CHECK | 1 | 71 | 2 (0)| 00:00:01 |
|* 16 | INDEX RANGE SCAN | INDX_SEARCH | 1 | | 1 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | INDX_IAM_SR_NO | 1 | 26 | 1 (0)| 00:00:01 |
|* 19 | INDEX RANGE SCAN | INDX_2 | 1 | 32 | 1 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN | INDX_2 | 1 | 51 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------

View 3 Replies View Related

SQL & PL/SQL :: Get The Data On Order By Wise

Nov 15, 2011

i am getting the data like bellow i need the data with out null values

date col1col2col3col4
16-Nov-1123
17-Nov-1112
18-Nov-11321
19-Nov-1123
20-Nov-11132
16-Nov-112321
17-Nov-11112
18-Nov-11211
19-Nov-11132
20-Nov-1112
16-Nov-1145
17-Nov-11465
18-Nov-112123
19-Nov-11132
20-Nov-1165
16-Nov-11456
17-Nov-11546
18-Nov-114656
19-Nov-11566
20-Nov-11564

need out like below

date col1col2col3col4
16-Nov-1123232145456
17-Nov-1112112465546
18-Nov-1132121121234656
19-Nov-1123132132566
20-Nov-111321265564

View 3 Replies View Related

How To Confirm Order Of Data In Results Of Two Sql Queries

Feb 9, 2009

I want to confirm the order of the data in results of two sql queries.

For example, in my table 5 columns are there.c1 to c5.

c1 c2 c3 c4 c5
--------------------------------------------------
test100 1 A 1-Jan-2006 2-Jan-2006
test100 2 B 3-jan-2006 4-Jan-2006
test101 1 A 01-Jan-2006 2-Jan-2006
test101 2 B 03-Jan-2006 4-Jan-2006
test101 3 c 05-Jan-2006 06-Jan-2006

In this table if you order by c2,

select c3
from tab1
where c1='test101'
order by c2;

c3
---------
A
B
C

If you order by c4,

select c1,c2,c3
from tab1
where c1='test101'
order by c4;

c3
------
A
B
C

Sometimes order of these data may change like. the result of the 2nd query may change (bcos of c4 date column changes)

C3
--
A
C
B

So,@ run time i want to check the order of these 2 results should match....if not,i need to display the corresponding c1 column.

View 3 Replies View Related

PL/SQL :: Retrieve Data By Using Both Group By And Order By Clauses?

Nov 16, 2012

I have a table name as angdata77 having attributes like asigno..i want to retrieve data from angdata77 by using both group by & order by clauses.. for total count..am using the query as

select asigno,count(*) from angdata77 group by asigno order by asigno;

Is there any other query for retrieving the data from angdata77

View 5 Replies View Related

SQL & PL/SQL :: Result Order In 2 Table Joins Without Order By

Sep 6, 2011

I have below tables,

describe rpthead

Name Null Type
--------------------------- -------- -------------
RPTNO NOT NULL NUMBER
RPTDATE NOT NULL DATE
RPTD_BY NOT NULL VARCHAR2(25)
PRODUCT_ID NOT NULL NUMBER

describe rptbody

Name Null Type
------------- -------- -------------
RPTNO NOT NULL NUMBER
LINENO NOT NULL NUMBER
COMMENTS VARCHAR2(240)
UPD_DATE DATE

The fact is that we store some header in RPTHEAD and store real data in RPTBODY, the question is that if I use below SQL to query all data for a 'PRODUCT_ID'.

SELECT t0.LINENO, t0.COMMENTS, t0.RPTNO, t0.UPD_DATE
FROM RPTBODY t0 , RPTHEAD rpthead
WHERE
(
t0.RPTNO = rpthead.RPTNO
AND
t0.UPD_DATE>=to_date('1970/01/01 00:00:00','YYYY/MM/DD hh24:mi:ss')
AND
rpthead.PRODUCT_ID IN ('4647')
)

I do not want to have 'ORDER by' clause since data set is too large, the sorting takes long time, is there any way to get the result rows in the order sorted by RPTNO? We have the index for RPTNO on RPTBODY.

View 5 Replies View Related

Forms :: Order By Control Block Data Using Oracle 10g

Apr 11, 2013

I have two block....both are the control block.. in first block i select the date and in second block the data of that date is populated.but the data is populated using cursor in when-button-pressed trigger of that first block button.in cursor the data is selected and placed in field of detail block using into clause.... each field and one item of detail block is srno which is create in post-query of detail block using :sysyem.trigger_record.

Now i want after populated the detail block the data is sorted desc one of the field of the detail block..Can this possible using set_block_property() of block although the block is control block if yes where i should do this?

View 7 Replies View Related

Forms :: Data Block Ignoring Order By Clause?

Mar 6, 2012

I have a real problem with form, specifically one of its data blocks. In the order by property of the block i specify it to sort on a branch in ascending order(i tried descending as well) but for some reason the form ignores that and sorts it on the ROWNUM. I even removed the where clause, the order by clause and changed the query data source type to FROM clause and changed the data source name to pre-query. I then created the query string in the block's pre-query trigger and set query_data_source_name property to that query string and still the data in the block is not being sorted on the branch number but instead on the ROWNUM.

View 6 Replies View Related

Forms :: Custom ORDER BY In Multi-record Data Block

Jun 15, 2011

I have data block that has hundreds of records. On a screen I display 20 records at a time. On that same screen I have "find badge" function where user types badge/employee id and presses a button to find the record in a data block. I have no problem finding the records in a list and pointing the curser to appropriate record. My question is (which is a user request) if the badge/employee id is found to make that record to be the first record in a list.

View 2 Replies View Related

Server Utilities :: Data Pump Import Order - Causing Constraint Violation

Nov 16, 2010

We are trying to import data into existing tables in a schema using data pump

However the foreign key tables are being imported first and then the master table data thus violating the constraints

Apparently it seems larger tables are being imported first regardless of referential integrity constraints thus causing constraint violation (contrary to my understanding)

Is it a normal behaviour during data pump import?

Is it possible that the keys being sequence generated are causing this?

As I understand import will commit after each table In that case can we defer commit at all at the expense of large undo, set constraints to deferrable and try the import?

View 3 Replies View Related

SQL & PL/SQL :: Combining New Logic Using Old And New Cursor

Aug 22, 2011

I have to implement a new logic such that , Old logic which pulls data will stay in place.now New logic pull data form SAP . So i have implemented the new logic in to the existing cursor as below.

i.e.

Cursor new_sap
old_query
Union
New_query;

I have 3 new cursor like dis. How to process these old and new cursors in FOR loop.. Ex:

For c1 in C2(order_number)
if order_source is sap then ( dis filters old data which is not in SAP)
new query in cursor should process
else
old query in cursor should process
end if;

How will i do this programatically.

View 1 Replies View Related

SQL & PL/SQL :: Logic For Update And Delete?

Apr 28, 2012

i have three tables: ot_req, ot_po_breakup, and ot_po. when a row is inserted into the ot_po table reffering to ot_req,there is a trigger which creates a seperate reocrd in ot_po_breakup with the details of ot_req ot_req.ri_sys_id as pb_ri_sys_id and ot_req.ri_qty as pb_ri_qty ,pi_sys_id as pb_pi_sys_id .upto this part is okand when i insert also the logic is okay.

i have created a trigger to update the rows in the ot_po_breakup table after insert on ot_poin order of pb_ri_sys_id and pb_pi_sys_id and try to update the values in the columns pb_ves1q, pb_ves2q, and pb_ves3q order. i am trying to take the quantities in ot_po.pi_qty and insert them into the pb_ves1q, pb_ves2q, and pb_ves3q columns of ot_po_breakup where those columns are empty (0 or null) such that the sum of those three columns for that row does not exceed the pb_ri_qty in that row.

My problem is i need to mofify my trigger to do the update and delete , that is whenever the user is updating the column ofot_po.pi_qtythe qty should in ot_po_breakup should get updated accordingly and sum of pb_ves1q,pb_Ves2q and pb_ves3q should be equal to pb_ri_qty and do the same for delete as well

CREATE TABLE OT_REQ
(
RI_ITEM VARCHAR2(20 BYTE),
RI_SYS_ID NUMBER,
RI_QTY NUMBER

[code]...

View 14 Replies View Related

SQL & PL/SQL :: Decode Logic Not Working

Feb 19, 2013

what is the right syntax to use the below decode logic.

Decode(EXTRACT(MONTH FROM sysdate) > 2, ltrim_blanks(GLAMOUNTS.CR_AMOUNT_02) + ltrim_blanks(GLAMOUNTS.DB_AMOUNT_02), 0)

View 12 Replies View Related

SQL & PL/SQL :: Query Logic - Students Who Took ALL / ANY Courses

Mar 1, 2011

I need to run a query for students that took ALL the following courses: 6710, 6711, 1032, 1035 and ANY of the following courses: 3061,3065

here is my query:
=SELECT distinct student_number, last_name, first_name, gender, ethnicity, students.grade_level, lunchstatus, course_name, course_number ,termid
from students inner join storedgrades on students.id=storedgrades.studentid
where students.schoolid='0976111'
and (students.grade_level >'9' and students.grade_level <= '12')
and (course_number = all('6710', '6711', '1035')and course_number=any('1031','1032','3061','3065','3062', '3401', '3082'))
order by last_name, first_name, grade_level, course_number

The problem is that I'm not getting any results but I know for a fact that some students took the required courses. On a side note, if I change the ALL to ANY - I do get results - but it is not what I want because it would be hard to keep track of the students that met the requirements.

View 7 Replies View Related

SQL & PL/SQL :: Merge Statement Required With Logic

Jul 10, 2013

Table Name: F_SCENARIO
System : Dataware house
Oracle version : 11g
Record Count : 2 Million records

Correct scenario records

F_Key F_Bridge_key Record_type
1 1 1
2 1 2
3 1 3

Wrong scenario records

F_Key F_Bridge_key Record_type
1 1 1
2 -5 2
3 -6 3

I want to write a Merge statement to update the negative values into 1.

View 6 Replies View Related

SQL & PL/SQL :: Code To Use Instead Of CONTINUE But Have Same Logic Incorporated?

Oct 21, 2010

I have a BEGIN Block of the code where a loop is running .I also have a CONTINUE statement in the EXCEPTION Block of the code where I return the control to the BEGIN Block in case of any exception.Now as per ORACLE Company, CONTINUE has a old issue which is already registered by Oracle Company Bug 7306422.

The issue is where CONTINUE statement does not run properly when your code Optimization level is 2. However it will work properly if we have the Optimization Level as 0 and 1.are facing the same issue here and have a workaround.

Is there any code which I can permanently use instead of CONTINUE but have the same logic incorporated.i.e returning to the next iteration whenever I say to pass the control.

View 10 Replies View Related

Forms :: Web-logic 10.3.5 With Oracle Reports 11.1.2.1.0

Jul 29, 2013

i am trying to upgrade my existing system (6i) to 11g. I have installed Weblogic 10.3.5 with forms and reports 11.1.2.1.0 on windows 7 32 bit. I can connect to my database (10g) from Form builder.

But when I try to open a form/pll of 10g or 6i, it says PDE-PLI018 could not find library and it shows the path of my earlier folder, which now does not exist.

I have made changes in registry FORMS_PATH, FORMS_BUILDER_PATH, UI_ICON And in default.env file FORMS_PATH and CLASSPATH

I have added entry to tnsnames.ora, through which I can connect to DB. And have followed below site to configure weblogic and FM forms and reports;

[URL]

1) Do we have to connect (create a bridge) to DB from weblogic except tnsnames.ora.

2) For library error I think I am missing some settings to be done.

3) If I am able to rectify above error then can I directly use [URL] and access the system from another pc, or still there are some modifications needed.

View 2 Replies View Related

PL/SQL :: Unable To Create Logic Sequence?

Oct 23, 2012

create table t (a varchar2(20),b number(8));
insert into t values ('aa',4);
insert into t values ('ba',6);
insert into t values ('ca',7);
insert into t values ('da',8);

in place of 8 there can be any number between 1 to 100

if in place of 8 number is <10
insert into t values('ea',10); ---- this i need dynamic insert
if in place of the number between 11-19 then
insert into t values('ea',20); ---- this i need dynamic insert
and so on

i tried as below

select case when max(b) <10 then 10 when max(b) between 10 and 20 then 20 end from t;

but i cant write case for again and again upto nth

View 3 Replies View Related

PL/SQL :: Require Logic / Oracle 10g R2 On Windows

Sep 5, 2012

I have oracle 10g R2 on windows.I have two tables and say table A ant Table B. Both have column Total_amount. There are primary key's on Both tables.

Sample query
SELECT A.EMPNO,A.EMP_CODE,nvl(A.TOTAL_AMOUNT,0) "A_TOTAL_AMOUNT",
    nvl(B.TOTAL_AMOUNT,0) "B_TOTAL_AMOUNT",(nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)) "DIFFERENCE"
    FROM A, B
    WHERE A.EMPNO =B.EMPNO (+)
    AND A.EMP_CODE = B.EMP_CODE (+)
    AND round((nvl(A.TOTAL_AMOUNT,0) - nvl(B.TOTAL_AMOUNT,0)),3) <> 0Above query retrives only Non-matching rows.

I need to retrive :-

1)output of the query shown above and

2)Records which are present in B and not in A.

View 2 Replies View Related

SQL & PL/SQL :: Same Logic When AFTER INSERT OR UPDATE Trigger Is Written

Feb 19, 2011

SQL> CREATE OR REPLACE TRIGGER TRI_COMPL_FEATURES
2 AFTER INSERT OR UPDATE ON COMPLEMENTS FOR EACH ROW
3 DECLARE
4 v_fno NUMBER;
5 v_tab VARCHAR2(30);
6 v_unique_id VARCHAR2(40);
[code]....

Trigger created.When I am trying to insert into complements table it is throwing error as follows:

SQL> insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL);
insert into complements values(19,NULL,'5',6,7,NULL,'W2023648',NULL,NULL)
*
ERROR at line 1:
ORA-20010: ORA-04091: table TEEMNGWS.COMPLEMENTS is mutating, trigger/function
may not see it
ORA-06512: at "TEEMNGWS.TRI_COMPL_FEATURES", line 19
ORA-04088: error during execution of trigger 'TEEMNGWS.TRI_COMPL_FEATURES'

I can understand that I am trying to perform DML operation(i.e select) on table which trigger is fired.But how can I implement the same logic when AFTER INSERT OR UPDATE trigger is written.

View 1 Replies View Related

PL/SQL :: Logic To Compare Date With Standard Timestamp

Feb 3, 2013

I want to retrieve the data which is "n" moths old.

To compare that I want use only SYSDATE without timestamp. I want to use standard timestamp '23:59:59' along with SYSDATE.

The condition should be as below.

UPDATE_DATE <= ADD_MONTHS(15/01/2013 23:59:59,-3)
UPDATE_DATE <= ADD_MOTHS(30/01/2013 23:59:59,-4)

UPDATE_DATE<=ADD_MONTHS(sysdate||' '||'23:59:59',-3);

View 6 Replies View Related

Implement Custom Logic On Button In Oracle CRM On Demand

Jun 25, 2013

we wanted to know the approach of how to implement our custom logic through button click (which is vanilla) in Oracle CRM on Demand

View 1 Replies View Related

Reports & Discoverer :: Middle Ware Web Logic Server

Aug 9, 2013

I installed web logic server and form & report on my linux system and gave configuration option later . so now web logic admin server is running on different port and wls_reports server is running to different port in cluster. How its running on cluster and when i run

localhost:9002/reports/rwservlet/getserverinfo?server="report_server_name" i am getting rep-51002 error.

View 3 Replies View Related

Forms :: Null Values - Logic Error During Runtime?

Feb 28, 2012

I'm having a problem with null values. I want to display old student in a display item if subjects text item has records. Otherwise, display new student if it has null values. This is the code that i tried so far..

IF :block2.subjects IS NULL THEN
:block3.type := 'NEW STUDENT';
ELSE
:block3.type := 'OLD STUDENT';
END IF;

But i got logic error during runtime.

View 8 Replies View Related

SQL & PL/SQL :: Testing Nested Blocks But Logic Is Apparently Not Correct?

Apr 9, 2013

I am testing nested blocks, but my logic is apparently not correct.

I am running this script:

DECLARE
v_one number(1) := 1;
v_two number(1) := 2;

[Code]....

I receive the output from only one of the nested blocks:

bad
PL/SQL procedure successfully completed.

SQL>

I understand that I don't need nested blocks for the example above, but this was just a condensed version of what I'm trying to do. I think nesting blocks will be easier to read and maintain, instead of having a huge CASE statement.

How can I execute only the nested block for which the condition is true and ignore the nested blocks that follow?

Are nested blocks not the correct answer here? Should I be looking at invoking procedures/functions instead?

View 8 Replies View Related

SQL & PL/SQL :: Oracle 9i - Block Logic To Fetch Records From Table And Compare

Dec 26, 2011

the following proble.The emp table is having 14 records.

SELECT * FROM emp ORDER BY empno;

EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030

[code]...

The emp table is having 10 records.

SELECT * FROM emp_10 ORDER BY empno;

EMPNOENAMESALDEPTNO
7369SMITH80020
7499ALLEN160030
7521WARD125030
7566JONES200020

[code]...

I have written the following PL/SQL block logic tofetch the records from the emp table and compare the records with emp_10 table to perform insert if the records are newelse to perform update the existed records in the emp_10 table.

DECLARE
CURSOR tranche_balance_cur
IS
SELECT empno,
ename,
sal,

[code]...

Execution scenario 1:

I have commented insert and update statements in that case I got the following out put.

Inserted Records4
Updated Records10

As per the logic it's giving the correct output because the cursor is fetching 14 records in that already 10 records are existed in emp_10 tableand 4 are new records.so that it's showing the count for inserted records as 10 and updated records as 4.

Execution scenario 2:
I have uncommented insert and update statements in that case I got the following out put.

Inserted Records13
Updated Records1

As per the logic it's not giving the correct output.

I tried with using TRIM function in the comparision logic to avoid spaces.

TRIM(emp_10.empno) = TRIM(tranche_balance_rec.empno)
AND TRIM(emp_10.ename) = TRIM(tranche_balance_rec.ename)
AND TRIM(emp_10.sal) = TRIM(tranche_balance_rec.sal)
AND TRIM(emp_10.deptno) = TRIM(tranche_balance_rec.deptno)

View 10 Replies View Related

SQL & PL/SQL :: Order By With IN

May 24, 2010

Its a very small query

SELECT * from EMPLOY
WHERE fk_dept_id IN ( select id
from DEPARTMENT
ORDER BY END_DT DESC)

I cant use IN.

NOTE: Select * should be done from Employ only no joins and all.

View 10 Replies View Related

SQL & PL/SQL :: ORDER BY In A Particular Order

Jul 17, 2012

oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"

I have data like:-

event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2011081CC
171350 2012041WA
171350 2012031WW
171350 2011081WW
171350 2011081CR
171350 2011081CA

The possible last two characters of the iss_nbr can be:-

WW, WR,WA,CR,CA,CC

And I want it to be ORDER BY as follows.

WR
WA
CR
CA
WW
CC

So for example, in above case, it should be

event_idiss_nbr
171350 2012051WR
171350 2012041WR
171350 2012041WA
171350 2012031WW
171350 2011081CR
171350 2011081CA
171350 2011081WW
171350 2011081CC

How can I do it while loading the table?

View 11 Replies View Related