How To Fetch Data

Sep 19, 2011

Table structure:

table x
x1 x2 (Columns)
1 x
2 y
1 z

now i need to o/p like this

1 x,z
2 y

how to fetch data in above format.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Fetch Data Based On Max Data And Rowcount

Mar 7, 2010

I have the following data with me

AddId Salaries
10600
10 50
10 400
10 300
10 200
10600
20 200
30200
30 600
30 300

Required output should be

The average of the three highest salaries exceeds 100, only those rows should be visible...wrt each addid.

View 1 Replies View Related

Forms :: ORA-24374 / Define Not Done Before Fetch Or Execute And Fetch

Mar 24, 2011

FRM-40501: ORACLE error: unable to reserve record for update or delete.

ORA-24374: define not done before fetch or execute and fetch

My master-detail form has single canvas. For both blocks, master and detail, two tables joined together in each. One table to be updated, second table has some info for reference (query only).

I am getting these errors when in detail block the item from LOV is selected for existing record. This does not happen for new record inserted in detail block.

View 1 Replies View Related

OO4O And Wizards :: How To Fetch Columns In Cursor Using FETCH

Nov 1, 2012

create or replace PROCEDURE newprocedur(outname OUT VARCHAR2,outroll OUT NUMBER) AS

CURSOR c1 IS
select Name,Rollno,Section from emp;
BEGIN
Open c1;
fetch c1 into outname,outroll;

Here out of 3 columns in cursor is it possible to fetch only two columns using FETCH like i did above?

View 1 Replies View Related

SQL & PL/SQL :: Fetch Data From Table

May 11, 2011

I want to write a SELECT query on the data which are collected in a PLSQL table which is having 4 columns.

Looping through all the records in the PLSQL table will not get my requirement. Because I need to group the data based on two columns and need to fetch the count of groups.

Is there any way to query that PLSQL table?

View 1 Replies View Related

SQL & PL/SQL :: Using A Cursor To Fetch Data?

Sep 25, 2013

when i run this code i get a invalid number error

create or replace
PROCEDURE BULK_REJECT(System_name VARCHAR2)
as
MDM_run_id VARCHAR2(14);
V_sql clob;
cursor Job_metric is
select rowid_job,system_name, table_display_name, run_status

[code]....

View 17 Replies View Related

Forms :: Fetch Data Through The Cursor?

Aug 22, 2010

I want to fetch the data through the cursor and cursor is getting the value of group_code through the variable 'a'. but when i am writing the code like this it is not coming.

My code is like this :

declare
a varchar2(400):='';
cursor c1 is select ref_no,ref_code,company_id from stock_detail where company_id=:global.company_id

[Code]....

View 2 Replies View Related

SQL & PL/SQL :: How To Fetch Data After Deducting 1 Field With Another From Second Row

Apr 21, 2010

This is my data

ID TIME_IDVCL_IDIDLE_END_TIME START_AT

351601834326910110/29/2009 1:18 10/29/2009 1:18
351601837056910110/29/2009 1:51 10/29/2009 1:51
351601838106910110/29/2009 2:39 10/29/2009 2:39
351601840626910110/29/2009 3:04 10/29/2009 3:04
351601841466910110/29/2009 3:09 10/29/2009 3:09

I want to minus 1st row of START_AT from 2nd row of END_TIME means (10/29/2009 1:51 - 10/29/2009 1:18)

View 3 Replies View Related

PL/SQL :: Query With Group By - Fetch Data From Database

Jul 26, 2013

I am trying to write a proper query to fetch data from database. Scenario:

I need to retrieve employees who are not working in multiple departments. scott@TESTCRM> select * from emp1;     

EMPNO     DEPTNO
---------- ----------     
7654         30      7698         30      7788         20      7788         30      7876         20      7900         10      7900         30    7902     20    7934     10 

scott@TESTCRM>  

Ouput Expected is      

EMPNO     DEPTNO
---------- ---------- 
7654         30      7698         30      7876         20      7902         20      7934         10

View 9 Replies View Related

Forms :: Unable To Fetch Data Using Control-blocks

Dec 24, 2010

I have created two non base table text items(from date, to date) and if values are not entered from front end i.e. null then hadrcoded in pre_query trigger to default values.But even i enter values dynamically from front end and when ever i press fetch button ,it is taking default values i.e. it is not accepting values what i have entered.

View 6 Replies View Related

Reports & Discoverer :: How To Fetch Data Based On Given Criteria

Sep 1, 2012

I need to design a report out of the below data:

1. bprf_no will be my primary field

2. report parameters will be the bill_month & no_of_months

Based on above 2 parameters I need to scan through the data for BILL_MONTH <= '20-Jun-2012' and NO_OF_MONTHS <= 6 the other criteria being the AVG_IND in (1,2).

In brief the criteria will be to pick all BPRF_NO having AVG_ID in (1, 2) consecutively till a break (AVG_IND not in (1, 2) starting from the given BILL_MONTH and going below this period, that is BILL_MONTH <= '30-Jun-2012'.

For the below data, if my parameters are: BILL_MONTH <= '30-Jun-2012' and NO_OF_MONTHS <= 3, only the underscore added data should be picked (as they fulfill the criteria) and the report output will be like:

BPRF_NO BILL_MONTH NO_OF_TIMES
-------------------------------------------------
BP05 30-Jun-2012 6
BP06 30-Jun-2012 6
BP07 30-Jun-2012 6
BP08 30-Jun-2012 6

Here the NO_OF_TIMES is the count, that is no of times the BPRF_NO falls into the above mentioned criteria consecutively starting from the provided BILL_MONTH.

BPRF_NOBILL_MONTH VOID_STATUS AVG_IND
------- ----------- ------------ --------
BP0130-Jun-1200
BP0230-Jun-1200
BP0330-Jun-1201
BP0430-Jun-1201
_BP0530-Jun-1201_
_BP0630-Jun-1202_

[code]....

My below query is fetching me wrong data:

----- Query -----
select bprf_no, no_of_month--count(*)
from
(
select a.bprf_no, count(*) no_of_month

[code]....

Here BP03 & BP04 should not come into the listing itself.

View 1 Replies View Related

Join Two Oracle Tables For Fast Fetch Of Data From Table?

Jul 2, 2012

join two oracle tables for fast fetch of data from table.

View 1 Replies View Related

Windows / .NET :: Excel VBA Macro To Fetch Data From Oracle Database

Aug 1, 2012

we are using ERP System based on Oracle DB. I have an oracle client 10.2.0 installed on my machine (Win 7/64-Bit) and trying to create Excel(2010) VBA-Macro to get datas out of the Oracle DB using a SQL query:

On my old machine (Win XP/Excel 2007) following worked fine:

strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
"CONNECTSTRING=(DESCRIPTION=" & _
"(ADDRESS=(PROTOCOL=TCP)" & _
"(HOST=" & strHost & ")(PORT=1521))" & _
"(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
Set oConOracle = CreateObject("ADODB.Connection")
Set oRsOracle = CreateObject("ADODB.Recordset")
oConOracle.Open strConOracle
Set oRsOracle = oConOracle.Execute(strSQL)

All the datas in the variables coming from cells or userforms - I am 100% sure all this is correct, as as said, it works on any Win XP Machine with Excel 2007

But trying same with Win 7/64-Bit/Excel 2010 gives me following error: MS ODBC Driver for Oracle: ora-01019: unable to allocate memory in the user side.

View 0 Replies View Related

SQL & PL/SQL :: Fetch Into Object Type?

Oct 2, 2011

what ios wrong in the following code

create or replace type testobj as object(col1 number);
create or replace type tabtest as table of testobj;
create or replace procedure proc(a out tabtest) is
cursor c is

[code]..

View 5 Replies View Related

SQL & PL/SQL :: Fetch Information For A Foreign Key

Jul 13, 2010

the view from where we can fetch information for a foreign key viz. reference table, referenced columns etc.

View 3 Replies View Related

SQL & PL/SQL :: How To Fetch Name Of Column And Table Name

Nov 30, 2011

how to get name of coloumn and table name which is being modified in trigger

View 8 Replies View Related

SQL & PL/SQL :: Fetch Age From Birth-dates?

Jul 28, 2013

Im supposed to fetch first names, last names and age of some people based on their birthdate. In my code so far i manage to get the names and the birthdates but what i actually need is a name and lastname and age for example 65 years.

here is the code :

SET SERVEROUTPUT ON
DECLARE
CURSOR C_kundålder IS
SELECT FNAMN,ENAMN,PNR
FROM BILÄGARE;
v_fnamn bilägare.fnamn%TYPE;

[code].....

View 24 Replies View Related

PL/SQL :: How To Fetch A Row With Maximum Rownum

Jan 15, 2013

In my sql query, how can i fetch the row with max row count? the query has around 10 columns.

View 2 Replies View Related

PL/SQL :: Fetch 1 Lac Rows From Table

Dec 15, 2012

OS : widows 2003

Oracle 9i release 2

I need to Fetch 1 lakh rows from table (all rows) in fastest way.

How to do it?

View 6 Replies View Related

Fetch Multiple Value From A Column And Display It As A Row

Apr 19, 2011

I need to fetch the data from a particular column and display it as a row of data.

For example,

Table

Col1 col2 col3
1 6 10
1 7 11
1 8 12
2 6 18
2 7 null
2 8 19

Data should be diplayed using col 2 as partition as below

col1 col3 col3 col3
1 10 11 12
2 18 null 19

Note: I have also referred to this link [URL]....

View 1 Replies View Related

Forms :: How To Fetch Records When Tab Page Changes

Jun 13, 2011

I have created two tab pages.when i change tab then it should be display automatically all the records in that tab without pressing any.

View 1 Replies View Related

SQL & PL/SQL :: How To Fetch Column By Comparing Date

Oct 16, 2012

i am fetching one record based on date but not able to fetch the data.

in my table pdate column is there with date datatype and value is in that is:15-OCT-12 so i am fetching empid based on dates.

select empid from masterprocessdailydata where pdate=to_date('15/OCT/12','dd/MM/yy'); but not getting the empid.

View 11 Replies View Related

SQL & PL/SQL :: Fetch Records Based On 2 Status

Jan 25, 2013

create table test_exp (oid number, ioid number, status varchar2(20));

Below are the insert statements.

Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 100, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 101, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (1, 102, 'CANCELLED');
Insert into TEST_EXP (OID, IOID, STATUS) Values (2, 103, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (3, 104, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 105, 'DISCO');
Insert into TEST_EXP (OID, IOID, STATUS) Values (4, 106, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 107, 'NEW');
Insert into TEST_EXP (OID, IOID, STATUS) Values (5, 108, 'CANCELLED');
[code]...

Now my problem is we should fetch the data based on the below rules

If an OID contains 2 IOIDs for which there is a NEW and DISCO status attached, then fetch the 2 records
If an OID has only 1 of these status, then ignore the same
If an OID has none of the 2 status, then ignore the same.

The expected output will be like below:

OID|IOID|STATUS
1 |100 |NEW
1 |101 |DISCO
4 |105 |DISCO
4 |106 |NEW

View 4 Replies View Related

SQL & PL/SQL :: How To Fetch Value Which Column Has Timestamp Datatype

Oct 14, 2012

i am running one query which is here

INSERT INTO shiftsample (Empid, PPDate, Inpunch, outpunch)( SELECT Emp_ID, PDate, In_Punch, Out_Punch FROM ProcessDailyData WHERE PDate = to_char(2012-10-15,'yyyy-MM-dd') AND Emp_ID = '00000001' );

in this query pdate has timestamp datatype and in shift sample ppdate column has date type. so i am not able to insert value from process daily data table.

getting this error.

SQL Error: ORA-01481: invalid number format model
01481. 00000 - "invalid number format model"
*Cause: The user is attempting to either convert a number to a string
via TO_CHAR or a string to a number via TO_NUMBER and has
supplied an invalid number format model parameter.

View 19 Replies View Related

Forms :: Fetch Record In A Datablock?

Jun 1, 2012

declare
cursor c1 is select distinct edt, regno, name, desgn, form_no, form_status, recd_on
from ol_registration_vu
where RECD_ON between :control.REC_FROM and :control.REC_TO ;

[Code]....

this coding giving me only one record i.e the last record of the table, whereas the table has 50 records. how can i get all records from the table in the form datablock.

View 20 Replies View Related

SQL & PL/SQL :: Fetch All Rows Into Single Variable

Feb 21, 2012

i want to load all rows of single columns into one variable. After that, that variable will use in any side of the pl sql block.

SET serveroutput ON
DECLARE
CURSOR CUR_DATA
IS
SELECT DAS_SECURITY_CODE FROM SECURITY_TYPE WHERE ASSET_TYPE='DERIVATIVES';
rec_data CUR_DATA%rowtype;

[Code]....

how to resolve this. above query,variable holds last value which fetched from cursor.

View 9 Replies View Related

SQL & PL/SQL :: Cursor To Fetch Column Names

Jul 25, 2013

I have the following piece of

CREATE OR REPLACE PROCEDURE COMP_RECORDS
IS
l_query VARCHAR2 (10000) := '';

CURSOR TBL1
IS
SELECT TABLE_NAME, COLUMN_NAME FROM COLS_TO_COMP WHERE TABLE_NAME='ACE_HIST';
TBL1_REC APP.COLS_TO_COMP%rowtype;

[Code]..

However I am getting an ORA-00923 exception with message as "FROM keyword not found where expected". know if I can/cannot use a cursor to fetch column names for a table?

View 9 Replies View Related

SQL & PL/SQL :: Fetch File Path From A Folder

Oct 4, 2013

We upload a file on our library on weekly basis. I wanted to fetch the latest uploaded file path/URL from a folder through PL/SQL, but not sure if it's possible.

View 12 Replies View Related

SQL & PL/SQL :: Fetch System Date Using Function?

Oct 18, 2010

create or replace function get_date(nn date)
return date as age date;

BEGIN
age := sysdate;
END;

i want to return that value in front end using callable statament..

View 2 Replies View Related

Queries Will Fetch Second Highest Sal From Emp Table

Jan 19, 2012

go through both the given queries...

SELECT * FROM EMP A WHERE 1=(SELECT COUNT(DISTINCT(SAL)) FROM EMP B WHERE B.SAL>A.SAL);
SELECT * FROM EMP WHERE SAL=(SELECT MIN(SAL) FROM (SELECT DISTINCT(SAL) FROM EMP ORDER BY SAL DESC) WHERE ROWNUM<=2);

both queries will fetch second highest sal from emp table.which sorting is used by oracle in order by and group by clause.

View 1 Replies View Related







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