SQL & PL/SQL :: Query Refinement Using Max Function

Jun 4, 2012

I have a table structure with test data.

desc tbl_female
Name Null Type
------------------- ---------- -----------------
ANI NOT NULL VARCHAR2(12)
CHAT_ID VARCHAR2(25)
AGE VARCHAR2(5)
RECORD_FILE VARCHAR2(25)
SUB_DATE_TIME DATE
STATUS VARCHAR2(15)
LOGIN_STATUS VARCHAR2(15)
CIRCLE_NAME VARCHAR2(10)
INTEREST VARCHAR2(10)
HOBBIES VARCHAR2(10)
OLD_ID VARCHAR2(25)
SORTED NUMBER
FAIL NUMBER
TIME NUMBER
FLAG NUMBER
POINT NUMBER
SF NUMBER
ACTIVE NUMBER

and Query depending on this table sometimes provide a row or sometimes not. Query is as follows which we are using

SELECT ANI INTO SUBANI FROM (SELECT ANI FROM TBL_FEMALE WHERE point=(select max(point) from tbl_female where circle_name in('pb','hr','hp','jk','pb','bir','dl','mp','mum','mh','gj') and sf=0 and login_status='LOGIN' and status in ('A','B') ) and circle_name in('pb','hr','hp','jk','pb','bir','dl','mum','mh') and sf=0 and login_status='LOGIN' and status in ('A','B') ORDER BY RANDOM.RAND_MAX(20)) WHERE ROWNUM = 1;

View 1 Replies


ADVERTISEMENT

SQL & PL/SQL :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

PL/SQL :: SUM Function In Query

Oct 16, 2012

Query:

select sum(2) from employees

How easy it is to tell the answer without running it in SQL Developer or SQL* Plus

I am finding it a bit hard to understand the logic behind allowing Lower(2.3) and Upper(69) etc queries

View 8 Replies View Related

Can Rewrite Following Query Without Using Row_number() Function

Jan 13, 2011

can I rewrite the following query without using the 'ROW_NUMBER() OVER ' part.The query is supposed to pull out the records whose CODE is not NULL and has most recent date for UPDATE_DATE . The reason I wanted to do this is, When I embed this query in between many other queries along with JOINs, My oracle server is unable to execute. So, I thought its better to supplant 'ROW_NUMBER() OVER ' logic with something else and try it. .

CURRENT QUERY:
SELECT a.* FROM
(SELECT b.*, ROW_NUMBER() OVER (PARTITION BY b.PIDM
ORDER BY b.UPDATE_DATE DESC) AS Rno
FROM
(
SELECT *
FROM SHYNCRO WHERE CODE IS NOT NULL
)b
)a
WHERE a.Rno = 1

View 4 Replies View Related

SQL & PL/SQL :: Create A Function Which Can Be Used In A Select Query?

Jun 11, 2008

I have a table structure like :-

Create table test(A varchar2(50),B NUMBER);

The data in that table is like that:-

A B
----------------------
2*3
2*4*5
4*5
column B contain no data.

I want to create a function which can be used in a select query,and the output should come like that :-

A B
----------------------
2*3 6
2*4*5 40
4*5 20

Means column B contains the resultant value of column A.And the above output should come through a select statement.You can use any function inside the select statement.

View 20 Replies View Related

SQL & PL/SQL :: Possible To Make Query With Analytic Function

Nov 4, 2011

Is it possible to make query with analytic function or somehow to represent output data for next example (sql: ???):

Table: Order
sql: select * from order
Sql output>
Item Barcode Qty
---- ------- ---
1 100100 2
2 100200 1
3 100300 3

Table: Order
sql: ???
Sql output>
Item Barcode Qty ElementarQty
---- ------- --- ---
1 100100 2 1
1 100100 2 1
2 100200 1 1
3 100300 3 1
3 100300 3 1
3 100300 3 1

View 2 Replies View Related

PL/SQL :: Using Function On Decode Or Case In Query

Mar 3, 2013

I am using oracle 11G database,I have to check length of name column value from employee table and if length(name) > 39 then value should be substr(name,0,39) else value should be name only. i tried below code

select CASE when length(name) > 39,substr(name,0,39)
else name
END

from employee but its not working ..can I do this using decode too ? ,,which one would be better or this is not a right way ?

View 3 Replies View Related

PL/SQL :: Pipelined Function With Dynamic Query?

Nov 12, 2013

 Orcl Ver: 11g R2. I am leveraging the Oracle's pipelined table function.It's working fine for static SQL.  

create or replace package test13_pkg as type r_disc_req is record(disc_line_id number,              

req_id number);    type t_disc_req is table of r_disc_req;    function F_GetDiscReq return t_disc_req pipelined;     procedure P_ProcessDiscReq;end;  CREATE OR REPLACE PACKAGE BODY test13_pkgAS   FUNCTION F_GetDiscReq      RETURN t_disc_req      PIPELINED   IS      lo_disc_req   r_disc_req;   BEGIN      FOR r_row IN (SELECT disc_line_id, req_id                      FROM edms_disc_lines_stg                     WHERE ROWNUM < 10)      LOOP         lo_disc_req.disc_line_id := r_row.disc_line_id;         lo_disc_req.req_id := r_row.req_id;         PIPE ROW (lo_disc_req);  
   
[code]...

View 11 Replies View Related

SQL & PL/SQL :: Using Function Inside Query And Without Cursor?

Mar 17, 2013

To display highest marks,least marks,average marks,total marks of the student name entered.

desc stud;
Name Null? Type
----------------------------------------- -------- ----------------------------
SID NUMBER
NAME VARCHAR2(20)
M1 NUMBER
M2 NUMBER

How do I do that using PL/SQL and without Cursor.

View 4 Replies View Related

SQL & PL/SQL :: Function That Returns Source Tables From A Query

Oct 16, 2013

I am interested if there maybe exists any function that would return all source tables that are present in the given sql. For example function('select 'abc' from table_1, table2') would return a list containing 'table_1' and 'table_2'.

View 14 Replies View Related

SQL & PL/SQL :: Date Error In Execute Immediate Query Through Function?

Jun 25, 2012

I've used a date in execute immediate query in function, but at the time passing the date as input parameter and getting the result i'm getting following error.

CREATE TABLE MIS.TEMP
(
ID NUMBER(8),
STOCKDATE DATE,
STOCKQTY NUMBER(10,2)
);

[code]....

SQL> select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual;
select getstockqty(1,to_date('31/03/2012','dd/mm/yyyy')) from dual
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at "MIS.GETSTOCKQTY", line 11

View 12 Replies View Related

SQL & PL/SQL :: Can Use User Define Value In Nested Function Of A Query

Oct 14, 2010

can we use user define value (how to use '&') in nested function of a query ?

View 7 Replies View Related

PL/SQL :: Output Like Using Sql Query Without Using Dense-rank Function?

May 1, 2013

Here table - tac has

row1
-----
X
X
X
B

[code]...

I want ouptut like using sql query with out using dense_rank function,

row1 row2

X 1
X 1
X 1
B 2

[code]...

View 7 Replies View Related

Query Not Picking Function Based Index

Apr 3, 2012

One of our query is not using function based index, the required priv is granted to the user executing the query and also tables stats are gathered? what could be the reason for the query to not to pick the FBIndx? the table is a huge one having million of records, is it that CBO thinks that not picking FB indx is the best execution plan? let me know how can we make the query use the FB indx, also there is a restriction that we cannot force it using hints.

View 3 Replies View Related

SQL & PL/SQL :: How To Refer Result Of Inner Query (analytic Function) In Outer One

Feb 23, 2012

How 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].......

View 8 Replies View Related

SQL & PL/SQL :: Function Batch - Cannot Perform DML Operation Inside A Query

Oct 8, 2013

Am calling the Function Batch to insert an update statemtnt into Batch_statement table in the DOWNLOAD_FUNC .But its failing with the error

SQL Error : ORA-14551: cannot perform a DML operation inside a query

Below Is the

FUNCTION BATCH(numTABLE_ID IN NUMBER, varSTMT IN VARCHAR2) RETURN NUMBER IS
BEGIN
INSERT INTO BATCH_STATEMENT(QUEUE_ID,TABLE_ID,STATEMENT,QUEUE_SEQUENCE_ID)
VALUES (numQUEUE_ID,numTABLE_ID,varSTMT,1);
RETURN 1;

[code].....

View 27 Replies View Related

SQL & PL/SQL :: Pivot Query - ORA-00937 - Not A Single Group Function?

May 8, 2012

I am having the following pivot query but I am not able to run it is giving error msg

ORA-00937: not a single-group group function

SELECT DISTINCT
C.SHORT_NAME,
MAX (
COUNT (DECODE (TO_CHAR (a.reg_date_cur, 'MON'), 'JAN', A.CAR_NO))

[code]...

View 6 Replies View Related

Function To Find Business Days Between Two Dates - Query Tuning

Mar 17, 2011

I have a query that uses a function to find the business days between two dates.It sums the total number of days between two dates per employee to find the total days for the past 30, 90, or 365 days.

The problem is that the query takes 21 second to return the last 30 days.Over 70 second to return the last 90 days and over 140 second to return the last 365 days.Do you know how I could tune the query to return faster? Below is the query for the last 30 days:

select dwt_emp_id, SUM((SELECT GET_BDAYS(DWT_DATE,DWT_CREATE_DATE) FROM DUAL))
from dwt_dvt_work_time where dwt_create_date > sysdate - 30
and dwt_hours > 4 and dwt_usr_uid_created_by <> -1 group by dwt_emp_id order by dwt_emp_id

Here's the function:
CREATE FUNCTION get_bdays (d1 IN DATE, d2 IN DATE)
RETURN NUMBER
IS total_days NUMBER(11,2);
holiday_days NUMBER(11,2);
[code]....

View 1 Replies View Related

SQL & PL/SQL :: How To Obtain Data Returned By Function When Executed Using Dynamic Query

May 25, 2011

I'm attempting to use dynamic SQL to execute a function that returns a user-defined collection. The problem is that I don't know how to use dynamic SQL to handle user-defined types...or if this is even possible?

The code I have is as follows:

CREATE OR REPLACE PACKAGE qi_test IS

TYPE typ_qi_data IS RECORD(
iQIFlag NUMBER(1),
iIPFlag NUMBER(1),
iRiskIndicator NUMBER(1),
iDenominator NUMBER(8),
iNumerator NUMBER(8)
[code]........

I want to be able to execute the above function using dynamic SQL. Initially tried:

DECLARE
f2_data qi_test.typ_qi_data_tab;
BEGIN
EXECUTE IMMEDIATE 'begin :1 := qi_test.get_f2_data; end;'
USING OUT f2_data;
[code]......

...but this just produces "PLS-00457: expressions have to be of SQL types". So it looks like I can't do it this way if the returned data type is user defined. I know it would be easier in this instance to just use something like:

f2_data := qi_test.get_f2_data;

...rather than EXECUTE IMMEDIATE, but it's the principle that I need to get right as it forms part of a much bigger piece of work.

View 10 Replies View Related

SQL & PL/SQL :: How To Bypass Putting Select Inside Count Function In Query

Oct 21, 2012

I have 2 tables, ASSIGNMENT and RESEARCH_PAPER. For each research paper, I need to find out :

1. The number of assignments created from it (after a given constant assign date)

2. The number of assignments created from it that have been approved.

3. The number of unique users who have either created or approved an assignment from it

Test data :

create table research_paper (id int, name varchar2(100));
create table assignment (id int, r_paper_id int, assigner_id int, assignee_id int,
approver_id int, assign_date timestamp, approved_yn varchar2(10));
insert into research_paper values (1, 'A');
insert into research_paper values (2, 'B');

[code]....

Assignment :

id r_paper_id assigner_id assignee_id approver_id assign_date approved_yn
-----------------------------------------------------------------------------------------------------------
11 100 200 100 23-10-12 12:00:00.000000000 AMY
22 200 100 200 22-10-12 12:00:00.000000000 AMN
32 100 200 101 24-10-12 12:00:00.000000000 AMY

[code]....

Research_paper:

id name
----------
1A
2B

Expected result :

r_paper_id created approved unique_users
-----------------------------------------------
1 3 2 4
2 3 2 3

I wrote the following query for that :

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.

View 7 Replies View Related

Performance Tuning :: Removing Nvl Function Then Query Executing In 2 Min Condition

Aug 10, 2011

when am trying to use nvl for one condition it is taking lot of time to execute but when am removing nvl function then the query executing in 2 min. condition is given below

(HOI2.ORG_INFORMATION1)=nvl(TO_CHAR(:p_set_of_books_id) , HOI2.ORG_INFORMATION1)

but when am using the same condition as below the querry executing in 2 min

(HOI2.ORG_INFORMATION1)=TO_CHAR(:p_set_of_books_id)

my query given below

(SELECT cust.customer_number cust_no, cust.customer_name customer,
cnv.item_no, SUM(wd.shipped_quantity) shp_qty_nos,
0 rtn_qty_nos,
ROUND(SUM(cnv.cnf * wd.shipped_quantity), 3) shp_qty_tons,
0 rtn_qty_tons, 0 net_shp_qty_nos, 0 net_shp_qty_tons
[code]...

View 30 Replies View Related

Application Express :: Using Variables In PL/SQL Function Body Returning SQL Query?

Nov 20, 2012

I have this procedure

POSTCODE_TO_LAT_LNG_GM_API(postcode  IN  VARCHAR2, lat  OUT NUMBER,  p_long OUT NUMBER)

to convert a postcode into lat/long values. I then need to add them to the returned SQL statement so I used the string concat operator || with to_char but it comes up with this error when I try to apply the changes: Query cannot be parsed within the Builder. If you believe your query is syntactically correct, check the ''generic column'' checkbox below the region source to proceed without parsing.

ORA-00936: missing expressionh4.

h4. btw I'm using Oracle 11g release 11.2.0.3.0 and Apex version 4.1.1.00.23

DECLARE
l_lat NUMBER;
l_lng NUMBER;
l_SDO_GEOMETRY SDO_GEOMETRY;

[code]...

View 9 Replies View Related

SQL & PL/SQL :: Call Function Generates ORA-14551 /cannot Perform A DML Operation Inside Query

Aug 9, 2011

Calling function

select PACK.MAIN('blah') from dual

generates:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 756
ORA-06512: at "SYSADM.NEW_QUANTUM_PACK", line 245

Unfortunately the Body is not accessible to see.The spec of the function is:

FUNCTION MAIN (mvar IN varchar2) RETURN varchar2; I read somewhere that I can call it like:

var myVar VARCHAR2; call PACK.MAIN('blah') into :myVar

But this generates: ORA-01008: not all variables bound

View 3 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I have the following C code:

class Factorial {
  public:
  int getVal (int a);
};
[code]....

/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.

Just in case, listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What is advantage of Deterministic function over normal function?

What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?

View 4 Replies View Related

SQL & PL/SQL :: Union Query Need To Add Extra Dummy Field To One Side Of Query

Dec 8, 2005

I have inherited a query that union alls 2 select statements, I added a further field to one of the select statements ( a date field). However I need to add another dummy field to the 2nd select statement so the union query marries up I have tried to do this by simply adding a

select
'date_on'
to add a field called date on populated by 'date_on' (the name of the column in the first query)

however when I run the union query i get the error Ora-01790 expression must have same datatype as corresponding expression.

View 6 Replies View Related

Reports & Discoverer :: Excluding SQL Query Data When Linked With XML Query?

Apr 26, 2013

I have data in a table and another in XML file,I used SQL query to retrive the data placed on the table, and link this query with XML query that retrieves the data stored in the xml file. The data stored in the table and xml file sharing a key field, but the xml contents are less than what in the table.I want to show only the data shared between the two queries, how can I do that?

e.g.:

Table emp:

e_id | e_name | e_sal
023 | John | 6000
143 | Tom | 9000
876 | Chi | 4000
987 | Alen | 7800

XML File

<e_id>
143
876

So, I want the output to be:

e_id | e_name | e_sal | e_fee
143 | Tom | 9000 | 300
876 | Chi | 4000 | 100

View 2 Replies View Related

Storing Select Query Result Into Array And Using It In Another Query?

Aug 7, 2009

I am looking to simplify the below query,

DELETE FROM A WHERE A1 IN (SELECT ID FROM B WHERE BID=0) OR A2 IN (SELECT ID FROM B WHERE BID=0)

Since both the inner queries are same,I want to extract out to a local variable and then use it.

Say,

Array var = SELECT ID FROM B WHERE BID=0;

And then ,

DELETE FROM A WHERE A1 IN (var) OR A2 IN (var)

How to do this using SQLPLUS?

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved