I tried creating the required view using 'CASE' statement and group by but its returning multiple rows.
select case when PropertyID=1 then VALUE end as Attrib1, case when PropertyID=2 then ValueID end as Attrib2, case when PropertyID=3 then ValueID end as Attrib3 case when PropertyID=4 then ValueID end as Attrib4 from ( select Phone, PropertyID, ValueID,Value from PropertyValue group by Phone, PropertyID, ValueID,Value )
SELECT a.objname, c.property1, c.value1 FROM datatable a, datatabledet c WHERE a.OBID=c.DATAOBID and a.CLASSNAME='Class1'; OBJNAME PROPERTY1 VALUE1 280-419-1994psCls1Attr3Attr1Value3 280-419-1994psCls1Attr4Attr1Value4 280-419-1994psCls1Attr5Attr1Value5
[code]....
After query output we put through front end code to make it in the following way i.e. convert rows into columns but with respective data value:
WITH table1 AS -- this table contain a list of column names ( SELECT 11 cid, 'TEK' group_nm, 'TYPE DESC' column_nm FROM dual UNION ALL
[Code].....
i have 3 tables, one that contains name of columns(table1), another one contain number of rows (table2) and the last table contain
the values for columns in table1 for each row in table2.
what i want to do is join all 3 tables and display the output as follow
TYPE DESC NAME P DATE TYPE_PCT ==================================================== Good John 8/21/2010 0.009 BAD Ken 4/11/2010 10.009 Medium Rob 8/1/2010 0.001
as you can see the columns names comes from table1, and the values comes from table3. i want to join these tables so that it display the output above
SELECT LOC_CD, TO_CHAR(DT,'fmMon RRRR'), SUM(QTY) , GROUPING_ID(LOC_CD) FROM WIP WHERE LOC_CD IN (1,2,3,4) GROUP BY ROLLUP(LOC_CD), TO_CHAR(DT,'fmMon RRRR'),TRUNC(DT,'MM') ORDER BY TRUNC(DT,'MM'),LOC_CD
This query result attached
The red coln is the total I want to place it in row-wise
Date loc_1 loc_2 loc_3 loc_4 Total May 2012 4,554 6,644 11,198 June 2012 4,986 5,838 777 11,601 22,799
CREATE TABLE T1 ( id NUMBER, START_date DATE, end_date DATE, end_date1 DATE, end_date2 DATE, end_date3 DATE, LEVEL1 number ) /
[Code]...
I have data in the first table as mentioned above I need to insert multiple rows into the second table for the same ID depends on the level, If it is level 1 then two rows for same ID first reocrd start_date as the start_date and end_date as end_date from the table t1 for second record start_date is end_date in t1 and end_date for this record is end_date1 column in table t1.
If the level is 3 then the table t2 should have four records for one id and the phase is the value for each record for one ID for example in level 3 we have 4 records for one id and phase should be 1,2,3,4.
I need a single select query which converts all the rows into a single value . Below is my requirement :
Create table email_tbl(emailid varchar2(30)); insert into email_tbl('1@y.com'); insert into email_tbl('2@y.com'); insert into email_tbl('3@y.com'); insert into email_tbl('4@y.com');
Now , I need a single select query which gives me the below results.
1@y.com,2@y.com,3@y.com,4@y.com
I have done the above by using a cursors in the pl/sql objects.But want to achieve this with a single sql/query.
select 1 as id, 'role1,role2,role3' as roles from dual union all select 2 as id, 'role1' as roles from dual
to
select 1 as id, 'role1' as roles from dual union all select 1 as id, 'role2' as roles from dual union all select 1 as id, 'role3' as roles from dual union all select 2 as id, 'role1' as roles from dual
?
I would prefer sql then plsql. Script for creating a test table:
create table CONVERT_LIST(id integer, roles varchar2(100)); insert into CONVERT_LIST values(1,'role1,role2,role3'); insert into CONVERT_LIST values(2,'role1');
I have job, working hours, employee id, employee name in test_emp table. The job name and employees are not fixed in this table and it varies from project to project. We don't know how many employees are there and needs to be fetching on runtime.
Here columns should be created based purchase dates dynamically with respect to quantity. Query out put will be like matrix format. So i feel that PIVOT & SYS_CONNECT_BY_PATH will not serve my requirement.
INSERT INTO CFL VALUES (11, 1, 'JAN-10', 10); INSERT INTO CFL VALUES (21, 1, 'FEB-10', 20); INSERT INTO CFL VALUES (31, 1, 'MAR-10', 10); and so on (12 records for a year with same quota_id, e.g. here it is 1) [code].......
Also this column values period is dynamic.. it can be for any year.
The select will return values like
Select per_id, PERIOD, amount from cfl where quota_id = 1
Basically 12 rows will be the output: per_id period amount 1 JAN-10 10 1 FEB-10 20 1 MAR-10 10 ..............and so on
We have a requirement where we need to publish only the delta/changes made to an existing materialized view. This MV is fast refreshed over a db link. Since, MV will have all the data, we won't
1] Created MV Log
CREATE MATERIALIZED VIEW LOG ON SHRTS_FLNG_PRCSD WITH PRIMARY KEY, SEQUENCE INCLUDING NEW VALUES;
2] Create MV
CREATE MATERIALIZED VIEW MV_SHRTS_FLNG_PRCSD (ITRTN_NB,UNQ_ID,XCHNG_ORG_ID,FLNG_ST,MAX_PRCSD_ITRTN_NB,SBMTD_SCRTS_AM,SBMTD_PSTN_AM) BUILD IMMEDIATE REFRESH FAST ON DEMAND WITH PRIMARY KEY AS
4) I have created a temp table to capture the diff between MV and newly inserted Deltas. I have added SEQ_NB column using Oracle sequence to capture the diff
CREATE TABLE MKT_CMPLC.TMP_SHRTS_FLNG_PRCSD ( ITRTN_NB NUMBER(2) NOT NULL, UNQ_ID CHAR(38 BYTE) NOT NULL, XCHNG_ORG_ID NUMBER(8) NOT NULL, FLNG_ST VARCHAR2(9 BYTE),
[code]....
5) I have refreshed the MV
exec dbms_mview.refresh('MV_SHRTS_FLNG_PRCSD');
6) In order to capture the new inserts, I am using following DML
INSERT INTO tmp_SHRTS_FLNG_PRCSD SELECT ITRTN_NB, UNQ_ID, XCHNG_ORG_ID, FLNG_ST, MAX_PRCSD_ITRTN_NB, SBMTD_SCRTS_AM,
[code]....
We need only delta records. Is there any way to extract actual rows from MV logs.
INSERT INTO TEST_TBL VALUES( 1000, 2000, 3000, 4000 ) ;
Expected result
A1 1000 A2 2000 A3 3000 A4 4000
A1, A2, A3, A4 are hard coded fixed values.
I could have done this but not a good idea in case table TEST_TBL is not a single row table but an inline query on 1,00,00,000 records with summary functions. In my table I've a summary query instead of single row table.
SELECT 'A1', COL1 FROM TEST_TBL UNION ALL SELECT 'A2', COL2 FROM TEST_TBL UNION ALL SELECT 'A3', COL3 FROM TEST_TBL UNION ALL SELECT 'A4', COL4 FROM TEST_TBL
I Want to make a query to select finished goods product in sales having product code greater than 280 but i have face a problem that order by is not working because products column have character code as well as number. how to sort that column.
