How To Display Row Value Of Each Class As Column Value
May 27, 2008
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.
View 3 Replies
ADVERTISEMENT
Mar 21, 2013
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...
View 1 Replies
View Related
Oct 22, 2012
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]...
View 6 Replies
View Related
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
Mar 28, 2010
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.
View 5 Replies
View Related
Mar 28, 2012
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 ?
View 4 Replies
View Related
Apr 23, 2013
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].......
View 6 Replies
View Related
Sep 26, 2011
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]....
View 9 Replies
View Related
Jul 24, 2012
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?
View 1 Replies
View Related
Mar 31, 2009
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.
View 5 Replies
View Related
Mar 10, 2011
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
---------------------------
View 2 Replies
View Related
Dec 21, 2012
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)
View 5 Replies
View Related
Aug 5, 2010
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.
View 10 Replies
View Related
Aug 2, 2010
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]....
View 9 Replies
View Related
Mar 12, 2013
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?
View 5 Replies
View Related
Jan 7, 2013
I am trying to only display a column value one time for a record, not a static value. The value changes and there can be many values. I do not think grouping will work since the Date value is dynamic. I am using Oracle for Toad 10.5. There are 4 records with this test data.
select Date, Person, Language, Country
from TableATableA
Date Person Language Country
01/25/2013 James English
12/20/2012 James English
US
[code]....
View 3 Replies
View Related
Feb 13, 2012
the table is a view. But the column ROWID doesn't display in Toad. He just ignores that.
View 3 Replies
View Related
Jun 8, 2013
How to display table columns on oracle form?
View 7 Replies
View Related
Dec 13, 2012
I have table with the values as below.
C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ
I dont want to print the repeated value(NAME) of C1 multiple times as below.
C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ
I could do it using the below query using union with the rownum.
select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3
Is there any other way of displaying using a single sql query.
View 17 Replies
View Related
Oct 12, 2011
I have a blob column in one table. I need to show it in my form (6i). If i put an image file in the column, it works (because i show it in a Image item type) but if i put a text file in the blob column, it does n´t work.
View 4 Replies
View Related
Oct 27, 2011
I have a table named PRODUCT (ID Number , ProductImage BLOB). I have inserted some records in. I want to create a form for user to view this table.
When I create Data Block, in Data Block Wizard, if I select option 'Table or View' to retrieve data, it's ok (Data type of item ProductImage is Image, when I run form and query, it display image normally).BUT, I want to retrieve data from stored procedure in a package. This is my package
PACKAGE my_pkg IS
TYPE my_rec IS RECORD
(
myID PRODUCT.ID%TYPE,
myImage PRODUCT.ProductImage%TYPE
);
[code].....
I select option 'Stored Procedure' in Data Block Wizard, then select my stored procedure (my_pkg.Do_Query). Next I choose 2 columns from my stored procedure (ID, ProductImage). The wizard still applys data type of ProductImage as Image. I finish Data Block Wizard and Layout Wizard... run form, query and an error message display:
FRM-40364: The data type of item 'ProductImage' does not match the corresponding column in the stored procedure.
I try to change 'myImage PRODUCT.ProductImage%TYPE' -> 'myImage BLOB' in my package but still get that error.
View 4 Replies
View Related
Apr 18, 2012
Recently I am facing a problem while working with Oracle reports 2.5.
My requirement is:
there is a report in which there are 5 columns right now. Now what i want is, whenever a condition will satisfy , a new column should be also display in that report otherwise it must be stay as it is.
Previously : a b c d
Now if a=1 : a b z c d
else
a b c d
where a,b,c,d,z are columns.
View 1 Replies
View Related
May 13, 2013
I am having report region with different columns, in that one column heading needs to be start with lower case like "iST Status" but always it is showing "IST Status".
View 5 Replies
View Related
Mar 30, 2012
I have extracted data from table and write into one text via sqlplus utility in shell scripts. i got correct output. i am having two issues on the output file
1) Outfile file size is huge high compare then table segment data.
2) last column having extra space.
The output column is clob datatype. so i have added set long 50000 and set longchunksize 50000 parameter. after adding these only i got above issues. without two options, i am not getting this isssue but lines are wrapped.
#Set the scripts Path
SCRIPTS_PATH="/usr/local/ccms/gpa/svr/scripts"
echo $SCRIPTS_PATH
[Code]....
View 2 Replies
View Related
Jun 28, 2010
Now i have a problem which i am facing for the first time. The problem is:
I have created a table test1 with two column of number data type and the column witdth is 25. Then enter the data but when i select the recrod it does not display the column data as it was entered.
SQL> create table test1
2 (
3 startno number(25),
4 endno number(25)
5 );
Table created.
SQL> insert into test1
[code]...
View 5 Replies
View Related
Aug 24, 2008
I'm modifying a package which contains a function with different parameters (say cno, gno, pname etc) need to be passed though it and that function is called by the cursor later in the program. I need to create a cursor (select statement) for every class (a program set)...i.e. multiple select statements for a program set with different values. The parameters needed to be passed correspond to same columns in different tables...so do i have to refer those tables everytime i'm passing the parameters using a select statement? The Program accepts the parameters as an array (list of parameters) and returns a cursor and the program handles one program set (class) at a time. That parameter list will be in a loop..i.e. it will be repeated for every certain combination (say cno - pname combination). So ... How should i proceed and create a select statement with different parameters declared in the function of type string making it dynamic and returning them using ref cursor?
View 1 Replies
View Related
Jul 2, 2011
how to display check box selected records in one column of form when push button is pressed.
View 1 Replies
View Related
Jun 2, 2010
My technical environment is: Win xp, dev9i AS vers9.0.2, Jinit vers1.3.1.9, OracleDB9i.
I created a sound,java file using Oracle9i JDeveloper, I compile it and the result is:
" Compiling...
D:Dev9ijdevWorkspaceWorkspace5soundsrcoracleformsfdSound.java
Successful compilation: 0 errors, 0 warnings."
I deploy sound.java to sound,jar and the deployed process is completed Successfully. I also executed all the configuration steps required to use this class from "Orale9i form builder" and it's ok
The problem is:
When I tried to import "sound" class from "Oracle9i form builder" by clicking
Program----> Import Java Class-----> Oracle.forms.fd.sound
Then an error has occurred:
Importing Class oracle.forms.fd.Sound...
Exception occurred: java.lang.NoClassDefFoundError: oracle/forms/ui/VBean
View 8 Replies
View Related
Sep 24, 2013
I need to filter ticket prices according to the class; i.e.
CASE WHEN booking_class = 'Economy' THEN economy_saver ELSE 0 END AS ECONOMY_SAVER THEN economy_basic ELSE 0 END AS ECONOMY_BASIC THEN economy_basic_plus ELSE 0 END AS ECONOMY_BASIC_PLUSEND, CASE WHEN booking_class = 'Business' THEN business ELSE 0 END AS BUSINESS THEN business_flexible ELSE 0 END AS BUSINESS_FLEXIBLEEND.
..The objective is to show only the prices that belong to the selected class. I am not sure if my sql is correct at this point.
View 7 Replies
View Related
Mar 28, 2013
I have database 10.2.0.3 with one procedure that calls some java class loaded in database. Unfortunately I don't have source code of that java class or whole jar that was loaded into database.I would like to download it from database and try to decompile it.
how can I download some java classes from database? Everything I know is only the name of some java class that is used in one PL/SQL procedure (CREATE OR REPLACE PROCEDURE ..... AS LANGUAGE JAVA).
View 1 Replies
View Related
