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]....
I am using Oracle 11g
The procedure is compiled without any errors.
The select statement retrieves records well when executed at SQL promt.
But the same records are not displayed in this procedure by fetch from cursor.
The last message in the procedure 'completed' is also displayed.
create or replace procedure disp_rec(vid IN varchar2, vfeat IN number)
is
cursor c1 is select gid, listagg(vindex, ',') within group (order by tid) idx from ridrecords where idarpt=vid and feattype=vfeat group by gid;
type vg is table of number;
type vi is table of varchar2(3500);
[Code]....
I have 3 tables, Emp(Emp_id,emp_name),dept(dept_no,dept_name),emp_dept(emp_id,dept_no). Emp tabl ehas some 20 employes id who belongs to different departments.There are few employee who belongs to multiple departments as well. I want to fetch records of emp_id, emp_name, dept_no in the following format.
Name id dept_no
Ram 101 10
20
30
Ani 201 10
20
i am using apex4.1 and i am trying to fetch multiple value using Ajax code.i am using the following link
[URL]....
NOthis is based on select list . when i choose particular value in select list corresponding values are displayed in items.
my select list is too long. so i replace select list with pop lov of values item then this code do not work. what changes i have to do so that this code work.
I have made a travel booking system which comprises of 3 forms
1)Travel Booking form
2)Reservation Form
3)Cancellation Form
Under one booking number i can add multiple users in which they can have there multiple travels.
Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.
What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.
For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.
how to get name of coloumn and table name which is being modified in trigger
View 8 Replies View Relatedi 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.
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.
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?
i have a proc that is taking p_serial_number refsursor as in parameter. the structure of p_serial_number is required to be
mfg_prod_cdchar (3 byte)
mfg_prod_seq_no char (6 byte)
How do I need to define this ref cursor ? and when I use it in my Procedure how do I fetch the column values ?
have a column cstomer_ts from table customer, which contain records like:
"cstomer account"||"PRIVATE"||"foundation"
"cstomer account"||"PRIVATE"||"foundation of money"
"cstomer account"||"PRIVATE"||"Moneycost"now i want to fetch the third record from the column cstomer_ts which starts with 'Foundation'
i mean i want to fetch only 3rd postion frm the column cstomer_ts and tht should starts with 'Foundation'
I have created tabular form. I have set LOV for one column. Here my requirement is when i select the value from lov then it will fetch that value into page item.
I am using apex 4.1
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.
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?
CREATE TABLE TYPE
(
c1_type VARCHAR2 (10),
c2_type VARCHAR2 (10),
c3_type VARCHAR2 (10),
c4_type VARCHAR2 (10),
c5_type VARCHAR2 (10),
c6_type VARCHAR2 (10),
[code]......
actual output of the below query, but i want to display in different way
select * from type;
C1_TYPE C2_TYPE C3_TYPE C4_TYPE C5_TYPE C6_TYPE C7_TYPE C8_TYPE C9_TYPE
Region_D Region_E Region_F Region_D Region_E Region_D Region_M Region_D Region_E
The expected output should be like this below, how to write a query or which built in function used to get the below result,
Region_D
Region_D
Region_D
Region_D
Region_E
Region_E
Region_E
Region_F
Region_M
I have a table TableA containing 2 columns ( Name and Value). Here I know what are the values for column Name
TABLEA
=======
Name Parameter
-------------------------
Nexus 11
GPlay 21
Demo 31
I need a query which provides the below output
Desired Output:
======
First Second Third
11 21 31
I have tried the below query
SELECT
DECODE (name,'Nexus', parameter) First,
DECODE (name, 'GPlay', parameter) Second,
DECODE (name, 'Demo', parameter) Third
FROM (SELECT name, parameter FROM TableA where name in ('Nexus','GPlay','Demo'));
This gives me the output
First Second Third
11 <Empty> <empty>
<empty> 21 <empty?>
<empty?> <empty?> 31
Is there any way to get the output in single line.
I have a procedure in this i want add a column to display my procedure is
set serveroutput on;
create or replace PROCEDURE IdentifySparesInDSIGNALTables
AS
V_SQL VARCHAR2(1024);
cnt1 number;
[code]...
i wnat to add dlstation in this procedure to display output...
How to display row value of each class as a column value
IDNAMECLASSHOURSSCHOOLNAME
1AllenMaths12school1
2SamMaths12school1
4PamMaths10school1
3SamComp10school1
9SmithComp15school1
7SmithPhy20school1
5PamPhy12school1
[code]....
I wanted output like below for School1
NamemathsCompPhyAccountsBusiness
Allen120000
Sam1210000
Pam10012150
Smith01520120
Adams000030
I wanted output like below for School2
NameCompAccountsBusiness
Anthony2000
Chris102020
I wanted output like below for School3
NamemathsAccounts
Angelo2015
Robert1515
how to write a single query, Also i may add /delete school/class anytime. Query should support mesans everything dynamically should get the data.Even stored procedure will work for me.
get members's home and work and cell phone number if it's avaiable. if not just disply null.
Table name : Test
member entity_id Phone_id phone_code Phone_num
abc 400175 200201 HME 49.6171.59501
abc 400175 200202 CEL 491.170.9174054
abc 400175 200203 WRK 49.6142.7.76675
def 521985 199991 HME 555-555-5555
ghi 345634 188881 HME 222-345-2345
ghi 345634 188882 CEL 222-456-6565
[Code]...
As far as SQL is concerned, I am using oracle 9i and oracle sql *plus.
1.
This is a table 'spj'
SID PID JID QTY
---------- ---------- ---------- ----------
1 3 2 5
1 2 2 12
1 7 1 10
2 5 3 5
[code]...
I wish to count all the qty for each pid.This is what I have:
SQL> select pid,sum(qty) from spj group by pid;
PID SUM(QTY)
---------- ----------
1 5
2 12
3 29
4 10
5 5
6 6
7 10
and it seems to work fine. But I now I want to display another column in the table called 'pname' which is a primary key in another table 'p'. 'spj.pid' is the foreign key.
2. Another query I can't perform needs the table s
SID SNAME STATUS CITY
---------- -------------------- -------------------- -------------------
1 s1 y asansol
2 s2 y durgapur
3 s3 y durgapur
4 s4 y asansol
5 s5 y kolkata
6 s6 y asansol
7 s7 y tarakeshwar
the question says:
Quote: List supplier names(snames) who supply at least all parts supplied by supplier(sname) S2.
How may I achieve it.
QUOTE (thiyagusham @ Mar 27 2012, 11:40 PM) SQL> select * from samp;
NAME DOJ QUAL EMPID
---------- --------------- ---------- -----
sam 21-mar-2012 mca
sony 03-jan-2000 mba 10610
maya 21-mar-2012 m.arch
sonna 18-mar-2012 mis
[code]...
i want to display in result column 10610 in second row , because emp sony having id "10610 "should i use sub-query ? without sub-query how can i display ?
I'm using the Oracle Emp,Dept tables as my sample. I want to display certain table column values based on some criteria. If met, display those values otherwise display other column values
For example:
So when dept.deptno=10, I want to display these 2 columns values
1. dept.deptno
2. dept.dname
otherwise, display these 2 columns values
1. dept.loc
2. null
Can this be done with one case, decode or "other" type of structure going thru the table one time??
SELECT emp.empno,
emp.ename,
CASE
WHEN dept.deptno = 10 THEN
to_char(dept.deptno)
[code].......
I have created a query that display the difference in the tables and column of two users. Some of the column in the new user have been added new which were not there in the old user and some columns from the old user still exits but their data_type is changed.
Now I want to select one more column in the select statement specifying whether the column is modified or has been added.
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'ABC' AND
TABLE_NAME NOT IN (SELECT table_name FROM all_tables where owner = 'ABC'
MINUS
SELECT table_name FROM all_tables where owner = 'XYZ' )
MINUS
SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,DATA_LENGTH
FROM DBA_TAB_COLUMNS
WHERE OWNER = 'XYZ'
[code]....
I am having following table, and below are the out put and desired output.
CREATE TABLE tbl1
(
mon VARCHAR2(10)
, grp VARCHAR2(50)
, visits NUMBER
, redirect VARCHAR(50)
)
;
[Code]....
Query:
SELECT
mon
, grp
, SUM(visits)
FROM
tbl1
WHERE
redirect IS NOT NULL
GROUP BY mon, grp
;
Output:
May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
Desired Output:
May-12 Green 16
May-12 Blue 20
May-12 Yellow 13
May-12 Red 0
May-12 Orange 0
How can this be acheived?
My Query is :
select id,descr,application from release
the above query fetches records as
1 testing D
2 upload H
i have to display my third column with full description which is stored in another table as select descr from app where code = application; I have to display this descr in the above result set instead of D,H.
Consider the following (example)table.,
TABLE_A
------------------------------
ID DEPT CRS
------------------------------
1 CS CS_100
2 SCIENCE SCI_150
3 MATH MATH_400
4 HISTORY HIS_110
[Code]...
To display CRS from TABLE_A where DEPT = 'MATH' but in the following format.,
--------------------------------------------
NO DEPT CRS
--------------------------------------------
1 MATH MATH_400, MATH_550, MATH_230
--------------------------------------------
instead of.,
--------------------------
NO DEPT CRS
---------------------------
1 MATH MATH_400
2 MATH MATH_550
3 MATH MATH_230
---------------------------
I have two tables as below
AREA (area_code, area_name)
MONTHLY_SALES (area_code, isbn, book_name, qty, sales_amount)
I want to display the output of top 3 sales columnwise as below.
AREA_NAME Top 1st (ISBN) Top 1st (BOOK_NAME) Top 1st (QTY) Top 2nd (ISBN) Top 2nd (BOOK_NAME) Top 2nd (QTY) Top 3rd (ISBN) Top 3rd (BOOK_NAME) Top 3rd (QTY)
I has a table of structure of varchar2 datatype.
NO
----------
1-2
3-4
5-6
desired output is:
SQL>1
2
3
4
5
6
The table has single column & the values may differ,that is, they may have 1-2-3-...-n in a single row, but the desired output is to be in the rows as shown above.
I tried concepts of SQL up to my knowledge, but I failed. The query to be done only in SQL.complete this query.
how to dispaly the column heading.
I tried to do the below.
03-AUG-10> set pages 0
03-AUG-10> set heading on
03-AUG-10> select count(*) numberofrows
2 from iceberg_mig_acnts_stage2 s2, tvp109workorder t109
3 where s2.no_account = t109.no_account
[Code]....
How I can build a query with conditions and calculations? E.g. I've got this table
Start | End | Working Place | Mandatory
------------------------------------------------------------------------------------
01-JAN-13 | 11-JAN-13 | Office | 1
14-JAN-13 | 25-JAN-13 | Home Office | 0
04-MRZ-13| 15-MRZ-13 | Office | 0
11-FEB-13 | 22-FEB-13 | Office | 1
Now if column working place=Office and column mandatory=0
the new column "price" has to calculate: (End-Start)* $25.00
and if working place=Office and column mandatory=1
the "price" column has to calculate: (End-Start)* $20.60
else $0.00
I tried it with the case statement but I didn't know how to calculate my values and display it to the virtual column "price".
Something like
case
when Working_Place = 'Office' and Mandatory=1
then ...
else '0.00'
end as PRICE
?????
Or is it not possible?