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
Upon form load, TList will be populated with predefined item. The behavior i am trying to achieve is to have a text item so user could entered specific text which will then filter the values in TList .
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 )
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
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
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:
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.
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.
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location, A.location_date, A.downloaded_date, A.downloaded_id, A.file_size, A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded from SDT_LOG A Inner Join ACTIVITY_LOG B On A.file_name = B.file_name and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record:
CODE Example of Name Type 1st record. ---- ------- -------------- FILE_NAME VARCHAR2(50) STLMK.txt FILE_DESC VARCHAR2(50) NON-RESIDENT FILE_LOCATION VARCHAR2(50) L:\NonResFiles YEAR NUMBER(4) 2008 LOCATION_DATE DATE 10/10/2007 DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM DOWNLOADED_ID VARCHAR2(50) Cindy FILE_SIZE CHAR(10) 16212 DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record:
CODE Example of Name Type 1st record ---- ------ ----------- FILE_NAME VARCHAR2(50) STLMK.txt ACT_CODE CHAR(2) D ACT_DATE DATE 10/10/2007 ACTIVITY_ID VARCHAR2(50) downloaded on DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
CODEFile_name Downloaded_ID Act_Code
STLMK.txt Cindy D STLMK.txt Cindy S STLMK.txt Jason D STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.
I have this SQL that returns the correct amount of rows which should be 2:
Select Distinct A.File_Name, A.File_Desc, A.file_location, A.location_date, A.downloaded_date, A.downloaded_id, A.file_size, A.days_to_request, B.File_Name, B.Act_Date, B.date_loaded from SDT_LOG A Inner Join ACTIVITY_LOG B On A.file_name = B.file_name and A.downloaded_date = B.date_loaded
I need to add another field in the Select query which is B.Act_Code. When I do, I get 2 extra rows. I do not know how to make these rows distinct.
The A table's structure is along with sample data for 1st record: Example of Name Type 1st record. ---- ------- -------------- FILE_NAME VARCHAR2(50) STLMK.txt FILE_DESC VARCHAR2(50) NON-RESIDENT FILE_LOCATION VARCHAR2(50) L:NonResFiles YEAR NUMBER(4) 2008 LOCATION_DATE DATE 10/10/2007 DOWNLOADED_DATE DATE 09/04/2008 9:17:00 AM DOWNLOADED_ID VARCHAR2(50) Cindy FILE_SIZE CHAR(10) 16212 DAYS_TO_REQUEST NUMBER(3) 60
The B table's structure is along with sample data for 1st record: Example of Name Type 1st record ---- ------ ----------- FILE_NAME VARCHAR2(50) STLMK.txt ACT_CODE CHAR(2) D ACT_DATE DATE 10/10/2007 ACTIVITY_ID VARCHAR2(50) downloaded on DATE_LOADED DATE 09/04/2008 9:17:00 AM
The second record of activity would all be the same except Cindy would be "Jason", act_code would be an "S", activity_id would be "sent on" and then of course the dates would be changed to whenever the new information was saved within the system.
I am getting something like this (shortened of course):
File_name Downloaded_ID Act_Code
STLMK.txt Cindy D STLMK.txt Cindy S STLMK.txt Jason D STLMK.txt Jason S
There should only be one row for Cindy with a D act_code and one row for Jason with an S act_code. For some reason, Cindy and Jason each get a row with the different act_code. I'm retrieving 4 rows instead of two when I use B.Act_Code in the SQL statement.
Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are. Cindy should have the D Act_Code because she downloaded that file name and Jason should have the S because he sent that file to someone else. Every time a file's activity changes, it is entered into the system so we can keep track of where the files are.
Also, I get the 2 extra rows when I add activity_id field to the select.I use Oracle 10.
trying to update a column in a table which has 3 columns of 16million rows from column in another table which has 1million rows, there is no relationship between the 2 tables.
Table A has 3 columns of 16million rows, the first two columns have 16million ID numbers, the 3rd colunm is currently NULL.
Table B has 1million Numbers, i need to somehow update column 3 in table A using the numbers in table B, it doesnt how many times each of the 1 million numbers are used but i dont want it to just update every row to the same value.
