SQL & PL/SQL :: View With Column Based On Row Values Of Another Table

Apr 13, 2011

I have table with values :

PROV_IDMEASURE_IDPERCENTAGE
Z0000221P114 45
Z0000135P115 68

For the column all possible values are ( P102,P101,P103 etc toP124). I want to create a view ( if possible ) from the above with data output as :

PROV_ID P101 P102 P103 ............................P124
z000234 23 45 60 72

basically this view has columns based on the previous tables column ( MEASURE_ID) values and the values will be corresponding value in column Percentage.

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Inserts Based On Column Values

Aug 15, 2012

I have a data in one table with 6 columns where user may be updating values in all of these 6 columns or he may enter 3 or 4 columns based on that inserts should take place, this is similar to my previous thread , i am using if condition to check column for null if its not null then i will make a insert , but is there any other easier way to do this.

CREATE TABLE ot_inspect_head
(inh_sys NUMBER,i_txn_code VARCHAR2(12),i_no NUMBER,i_ref_txn VARCHAR2(20),i_ref_no NUMBER);
CREATE SEQUENCE inh_sys START WITH 1;
CREATE TABLE ot_inspect_item
(ii_inh_sys NUMBER , ii_pi_sys NUMBER,ii_sys NUMBER,ii_item_cd VARCHAR2(12),
ii_grade VARCHAR2(12),ii_qty number, ii_flex_01 VARCHAR2(12),ii_flex01_qty number,
ii_flex_02 VARCHAR2(12),ii_flex02_qty number,ii_flex_03 VARCHAR2(12),ii_flex03_qty number);
CREATE SEQUENCE ii_sys START WITH 1;

insert into ot_po values ('ss-po',1,ph_sys.nextval);
insert into ot_inspect_head values (inh_sys.nextval,'ss-ins',1,'ss-po',1);
commit;
select * from ot_inspect_item

II_INH_SYS II_PI_SYS II_SYS II_ITE II_GRADE II_QTY II_FLEX_01 II_FLEX01_QTY II_FLEX_02 II_FLEX02_QTY
---------- --------- ------ ------ -------- ------ ---------- ------------- ---------- -------------
2 1 2 HEA100 A 100

--Now if the inspection user issues the update statement , it will delete this row
--from ot_inspect_item and reinserts the values with values based on
--ii_flex_01,ii_flex_02,ii_flex_03
[code]...

View 5 Replies View Related

PL/SQL :: Create Reporting View Based On Date Column

Jul 30, 2012

My DBA gave me a table with only one date column say Table1.Date. Its in the format of Date and say it is = 7/23/2012.

Now i have to create my own Reporting View(which is used for reporting) based on that date column like below:

It should be a column with values in the following format =
2012-07
2012-06
2012-05 etc....upto
2010-01

So i started out my creating like this:

select
to_char(Table1.Date,'yyyy-mm')
from Table1
Union

[Code]....

.and so on till i get 2010-01.

there has to be a better way to do this.

View 7 Replies View Related

SQL & PL/SQL :: Create Materialized View With Clob Column Based On Varchar2?

Feb 9, 2011

I need to create a materialized view with a clob column based on a varchar2 column of a table.This is because in the mv the clob column data gets appended one after another.

View 2 Replies View Related

PL/SQL :: Select Records Based On First N Distinct Values Of Column

Sep 25, 2012

I need to write a query in plsql to select records for first 3 distinct values of a single column (below example, ID )and all the rows for next 3 distinct values of the column and so on till the end of count of distinct values of a column.

eg:
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10
7 lmn 10
.
.
.
so on... (till some count)
Result should be
Query 1 should result --->
ID name age
1 abc 10
1 def 20
2 ghi 10
2 jkl 20
2 mno 60
3 pqr 10

query 2 should result -->
4 rst 10
4 tuv 10
5 vwx 10
6 xyz 10
6 hij 10

query 3 should result -->
7 lmn 10
.
.
9 .. ..
so on..

How to write a query for this inside a loop.

View 5 Replies View Related

SQL & PL/SQL :: Dynamically Concatenating Column Values Based Upon Number Of Grouped Columns

Feb 17, 2011

My requirement is to concatenate two column values and place them in a new column.I have done it using self join but it limits the purpose,meaning when I have more than 2 values for grouped columns then it won't work.How to make this dynamic,so that for any number of columns grouped,I can concatenate.

SELECT a.co_nm, a.mnfst_nr, a.mnfst_qty,
a.mnfst_nr || ':' || a.mnfst_qty || ';' || b.mnfst_nr || ':'
|| b.mnfst_qty
FROM vw_acao_critical a JOIN vw_acao_critical b
ON a.co_nm = b.co_nm AND a.mnfst_nr = b.mnfst_nr
[code]......

What will be the case when I need to concatenate for more number of values.

like when co_nm has three bahs and manfst_nr and manfst_qty has 3 values for each for bah.and if three are having same_mnfst nr then I should use something dynamic.how to achieve this.

View 10 Replies View Related

Forms :: Create View Based On Values Selected In Forms

Jun 7, 2013

I am working on a simple form which will get the user to fetch required columns of employee master.

For that i have created a form which will display the column names and select option. When user clicks on query, the form will display the selected columns in a block. After going thru if required the user can download it to excel.

As of now I do not have filtering option. For this I thought of creating view based on the selection and then fetch records from the view and display it in form.

To achieve this I would like to know How I create or replace a view dynamically based on values selected. I remember doing this sort of thing long back, but could not recollect it.

View 3 Replies View Related

Reports & Discoverer :: Value In Table Column Based On Some Existing Column Value Automatically Without User Intervention

May 15, 2011

i have two questions.

(1) how can i fill some value in a table column based on some existing column value automatically without user intervention. my actual problem is i have 'expiry date' column and 'status'. the 'status' column should get filled automatically based on the current system date. ex: if expiry date is '25-Apr-2011' and current date is '14-May-2011', then status should be filled as 'EXPIRED'

(2)hOw can i build 'select' query in a report (report 6i) so that it will show me list of items 'EXPIRED' or 'NOT EXPIRED' or both expired and not expired separately in a single report based on user choice. 'EXPIRED' & 'NOT EXPIRED' can be taken from the above question no. 1.

View 3 Replies View Related

How To Subdivide 1 Large TABLE Based On The Output Of A VIEW?

Aug 15, 2012

I am searching for a decent method / example code to subdivide a large table (into a global temp table (GTT) for further processing) based on a list of numeric/alphanumeric which is the resultset from a view.

I am groping with the following strategy in PL/SQL:

1 -- set up cursor, execute the view (so I have the list of identifiers)

2 -- create a second cursor (or loop?) which: accepts each of the identifiers in turn executes a query (EXECUTE IMMEDIATE?) on the larger table INSERTs (or appends?) each resultset into the GTT

3 -- Then the GTT contains just the requires subset of the larger table for further processing and eventual import into iReport for reporting.

GTT is defined and ready to go, the larger table contains approx 40,000 rows and I need to extract a dozen subsets or so which add up to approx 1000 rows.

View 10 Replies View Related

SQL & PL/SQL :: How To Insert Values Into Another Column By Comparing Values Of Two Columns Of Same Table

Dec 23, 2010

My scenario is to insert values into 'out' column by comparing 's' and 'IP' columns of temp table.The exact situation is at first need to go to ip column,take a value and then go to source column and check for the same value of ip which is taken previously.Then after corresponding ip of that source column should be inserted back in previous source column.

The situation is marked clearly in file which i am attaching with '--' comments at respective places.I am also pasting the code which i tried out,unfortunately it is giving error as exact fetch returns more than requested number of rows since there are duplicates in the table.I tried it using nested for loops.Also implemented using rowid,but it didnt work.

fixing the errors or if there is any new logic that can be implemented.

DECLARE
i_e NUMBER(10);
BEGIN
FOR cur_1 IN(SELECT IP from temp where IP IS NOT NULL)
LOOP
FOR cur_2 IN(SELECT IP from temp where s=cur_1.IP)

[Code]...

View 9 Replies View Related

SQL & PL/SQL :: Partition Creation Based On Other Table Values

Feb 12, 2013

i am trying to create Procedure which will create the partitions based on the other table date values one per each day.

CREATE OR REPLACE PROCEDURE PARTITION_TEST(PART_DATE_TABLE IN VARCHAR2, TABLE_NAME IN VARCHAR2,SCHEMA_NAME IN VARCHAR2)
AS
V_PART_NM VARCHAR2(20);
V_PART_CNT NUMBER;
V_DATE DATE;
V_SCHEMA_NAME VARCHAR(15);

[Code]..

It is not creating the partitions and even not giving any errors.

View 4 Replies View Related

Query To Split Records Based On Values From Another Table?

Feb 20, 2012

I have a table with following values in a column

Table A

col1
10
35
20
25

I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.

Table B- 90 records (10 + 35 + 20 + 25)

Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,

with first group having start value -1 and end value -10

second group - start value -11 and end value-45

third group - start value -46 and end value -65

fourth group - start value - 66 and end value-90

one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.

note that the values in Table A is dynamically changing, so not able to hard code values.

View 2 Replies View Related

PL/SQL :: Update Vs Merge - Updating One Table Based On Values In Another

Jun 26, 2013

I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data 

MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN  UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT; 

I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?

View 4 Replies View Related

PL/SQL :: Updating CSV Values In Oracle Table Based On Condition

Aug 27, 2013

I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.

View 1 Replies View Related

SQL & PL/SQL :: Table Search Based On Column Value?

Aug 25, 2010

in one of the data base some of the column values are TE. i wanted to serch in what are the tables this TE values are present. so m running the below function

CREATE OR REPLACE FUNCTION find_in_schema(val VARCHAR2)
RETURN VARCHAR2 IS
v_old_table user_tab_columns.table_name%TYPE;
v_where VARCHAR2(4000);
v_first_col BOOLEAN := TRUE;

[code]....

but v_where := v_where || ' or ' || r.column_name || ' like ''%' || val || '%''' is giving me numaric or value error when i run as select find_in_schema('@TL') from dual; so how can i go ahed with the serch ?

View 2 Replies View Related

SQL & PL/SQL :: Find Out Table Name Based On Column Name

Jul 28, 2011

i need an sql query or procedure to find out table name based on the column name..

View 4 Replies View Related

Update A Column Based On Another In Same Table

Apr 1, 2012

I am trying to update a column based on another column in the same table (student table) and a column from another table (school table)

Code is:

update student_table
set student_code =
(select l.student_code
from school_table l, student_table n
where l.school = n.schoolname)

I get the following error ORA - 01427 Single-row subquery returns more than one row.

View 1 Replies View Related

PL/SQL :: Update Column Based On Sum Of Data From Another Table

Apr 17, 2013

We had two tables.

Table 1: matusetrans

ITEMNUM Location Quantity transdate
AM1324 AM1 2 12-4-12
AM1324 AM1 2 15-5-12
AM1324 AM1 3 10-6-12
AM1324 AM1 4 5-1-13

[Code]....

Table 2: Inventory

ITEMNUM STORELOC lastyear currentyear
AM1324 AM1 need sum(quantity) here need sum(quantity)
AM1324 AM2 need sum(quantity) here need sum(quantity)

We have to update the last year and current year columns with sum of quantities for each item from matusetrans table based on date at different location in Inventory table.

we had nearly 13,000 records(itemnum's with different location) in inventory table in DB we have to update entire records.

How to write an sql queries to update lastyear and currentyear columns with sum of quantities based on itemnum and location in Inventory table

Edit/Delete Message

View 6 Replies View Related

Fetching Table Names Based On Column Value

Mar 6, 2013

I have a schema in which i have 10000 tables and i want to fetch table names from the schema where org_id is not equal to 1,

Note: Here org_id is the column name .

Provide me sql code or procedure to achieve this.

View 2 Replies View Related

Application Express :: Assign Values In Many Rows Based On Search Values?

Jul 25, 2013

I used Region, Process by to search the report which appears as shown above. Then I use Choose Auditors column to select my Auditor and copy paste it into the report under To be Audited By col. Is there a way to automate the process. I am here using a tabular form in APEX. My main aim is to assign auditors based on Region, not equal to Processed by. 

View 4 Replies View Related

SQL & PL/SQL :: Column Access In Table Based On User Parameter Input

Nov 10, 2010

Need to access data in a table base on user parameter input where the data is stored like a spreadsheet with column headings JAN, FEB, MAR... and the rows are the years. Is there a way to create a generic SQL statement so that I don't have to have 12 if statements in the procedure?

View 5 Replies View Related

Get Values From Table Column Using PL/SQL Or Just Using SQL

Dec 3, 2008

I'm having a table which has a column which has values given inside square brackets.

[[[123412]]] ,[[[[werer34]]]],'[[qw2_w3rt]]

Now, the requirement is to get the values which are inside the innermost square brackets.Such as in

First case : 123412
Second one: werer34
Third : qw2_w3rt

Only the values inside are needed. Is there any way to achieve it using Pl-Sql or just using Sql ?

View 3 Replies View Related

Oracle SP For Comparing Column Values In Table Pairs

Aug 16, 2013

I have a SP for comparing 80 diff column values in 8 table pairs and it is taking a huge lot of time to process as I have to process around 10k records.

View 5 Replies View Related

SQL & PL/SQL :: Count Values From TOTAL_fault Column From Fact Table

Nov 22, 2012

I have two tables with below structures.

desc Fault_Dim
Name Null Type
------------------- -------- ------------------
KEY NOT NULL NUMBER(38)
SYSTEM_NAME VARCHAR2(3 CHAR)
PRIORITY VARCHAR2(40 CHAR)
SEVERITY VARCHAR2(40 CHAR)
TYPE VARCHAR2(40 CHAR)
STATUS VARCHAR2(40 CHAR)
CREATE_DT DATE
SUBTYPE VARCHAR2(255 CHAR)
[code]...

The Severity column has values as below 1,2,3, My requirement is to show results as in the attatched image.Where the count values are values from TOTAL_fault column from the fact table.

I believe this is possible in hierarchial queries and pivorting

View 1 Replies View Related

SQL & PL/SQL :: Dynamic Column Creation / Create Column Based On Number Of Child In Hierarchy

Oct 15, 2013

I have one hirarchical query which return the parent to child hirarch level data. it has 11 level child data. i want to create column based on number of child in hirarchy. though i know it is 11 but it can change also.Is there any way i can create the column dynamically

ORG_UNITCOST_CENTERORG_UNIT_NAMEPARENT_ORG_UNITLLSYS_CONNECT_BY_PATH(ORG_UNIT,'/')

500171960000022000Managing Director - LUL500169965/00000001/50000001/50017588/50016996/50017196
500018370000021241FSO500171966/00000001/50000001/50017588/50016996/50017196/50001837
502894940000021241Knowledge Management500018377/00000001/50000001/50017588/50016996/50017196/50001837/50289494
508014980000021241Finance500018377/00000001/50000001/50017588/50016996/50017196/50001837/50801498

View 1 Replies View Related

Can Partition A Table Based On Date If It Does Not Have A Date Column

Jun 21, 2012

How can we partition a table based on date if it does not have a date column.

Actually I have to compare two tables on daily basis and fetch few rows from those two tables and enter it to a third table.But both these tables does not have a date column.

I am confused if i need to alter those tables and add date column or if there is some way in which i can compare the data from the two tables for that particular day only and not the whole table data.

View 1 Replies View Related

SQL & PL/SQL :: Trigger Which Changes Content Of One Column Based On Information Within Another Column

Sep 10, 2010

I am trying to create a trigger which changes the content of one column based on the information within another column.

For Example, if the 'ITEMQUANTITY' field drops below 1 then I want the STATUS column to say 'Out Of Stock'.

View 23 Replies View Related

Forms :: View Based Form

May 7, 2013

i have a form based on view just to display. when i execute query,records are getting displayed more than once. make them get displayed just once.

View 12 Replies View Related

Replication :: Materialized View Based On 2 Views

Apr 1, 2009

I have a Materialized view based on 2 views. I modified one column in both the views.Do i need to refresh the Materialized view.

E.g. :

In view1 and view2 i changed column like this
nvl(test.no,'n/a') testdata --changed
nvl(test.id,'n/a') testdata --original

CREATE MATERIALIZED VIEW MV_test
NOCACHE
LOGGING
NOCOMPRESS
NOPARALLEL
BUILD IMMEDIATE
[code].........

View 3 Replies View Related

PL/SQL :: Materialized View Based On Stored Procedure Data

Sep 23, 2013

is it possible to base a Materialized View on results returned from a stored procedure?If not, do you see any other way except of filling a table with data from the stored procedure and then basing the MV on it? 

View 4 Replies View Related







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