SQL & PL/SQL :: Row To Column - If Member_id Not Exists In Tables Return 0000 Value
Aug 1, 2012
CREATE TABLE ACXIOM_RD.SOLN_STAR_MATRIX_123456_TMP1
(
ATTRIBUTE_ID NUMBER(10),
FIELD VARCHAR2(30 BYTE),
ATTRIBUTE_NAME VARCHAR2(64 BYTE),
TABLE_NAME VARCHAR2(32 BYTE)
)
[Code]....
Just now framed the query as like...
select framedquery from ( SELECT CASE WHEN table_name='MT_MEMBERS_SCOPED'
THEN ' case when 1>0 then nvl (( select value from MT_MEMBERS_SCOPED where source_code=123499 and member_id= soln_test.member_id and attribute_id= '||attribute_id ||''||'),0) else ''00'' end ' || ATTRIBUTE_NAME ||', '
WHEN table_name='MT_MEMBERS'
[Code]....
My requirement is soln_test table contains 3 records ,for each record need to match member_id in the MT_MEMBERS_SCOPED for the four records in the table.. output should be 3 records in soln_test table + 4 records in SOLN_STAR_MATRIX_123456_TMP1 totally 7 records it means column to row...
if the member_id not exists in both tables it should return '0000' value.
View 9 Replies
ADVERTISEMENT
Oct 10, 2013
I am encountering error in this code.
WHILE EXISTS ( SELECT * FROM tblOrgChart WHERE fxOrgID = v_chrTempKeyDept )
LOOP
v_intDept := CAST(v_chrTempKeyDept AS NUMBER) + 1 ;
v_chrTempKeyDept := LPAD('',3 - LENGTH(CAST(v_intDept AS VARCHAR2)),'0') || CAST(v_intDept AS VARCHAR2) ;
END LOOP;
Error: PLS-00204: function or pseudo-column 'EXISTS' may be used inside a SQL statement only
View 1 Replies
View Related
Apr 28, 2011
I want to search a some values in oracle table and then return the corresponding column names.
View 1 Replies
View Related
Oct 11, 2011
I wonder if there is any way to return the columns of an select with its letters lowercase?
I have a piece of code that creates an script wich returns an SQL result to be confronted with some templates. My template have the column names in lowercase and because It is case sensitive the Uppercase returned by Oracle,
View 5 Replies
View Related
Feb 5, 2013
I want my query to return the rows of the table where a column contains a specific values first in a certain order, and then return the rest of the rows alphabetized.
For Example:
Country
ALBANIA
ARGENTINA
AUSTRALIA
....
CANADA
....
USA
....
Now i want USA and CANADA on top in that order and then other in alphabetized order.
View 5 Replies
View Related
Dec 22, 2011
I have a table with multiple rows for the KEY attribute(its not a primary key) and a Rank for each row.
I want a query which fetches one row per KEY attribute.The row with lesser Rank should be considered. But in-case if the value is null for any column the value for next Rank should be considered.
WITH TMP_TBL AS
(
SELECT * FROM (
SELECT 'A' DUN,'1' RNK,'A21' col1,NULL col2,'A41' col3,NULL col4 FROM dual
UNION ALL
SELECT 'A','2','A122','A23',NULL,NULL FROM dual
UNION ALL
SELECT 'A','3','A32','A33',NULL,'A35' FROM dual
[code].......
DUN is the KEY attribute . RNK is the Rank for each Row. COL1... COL4 are data attributes
The results I am expecting is
DUNCOL1 COL2 COL3 COL4
AA21 A23 A41 A35
BB12 B23 B15
CC12 C13 C33 C14
I want this to be done with SQL only. So I tried various ways but none were successful.Finally I created a Multi Row function row_nvl and it worked.
SELECT DUN,
row_nvl(rownvl_param_type(RNK,col1)),
row_nvl(rownvl_param_type(RNK,col2)),
row_nvl(rownvl_param_type(RNK,col3)),
row_nvl(rownvl_param_type(RNK,col4))
FROM TMP_TBL
GROUP BY DUN
But I don't think my manager will allow me to deploy a Multi Row function .
View 2 Replies
View Related
Dec 1, 2011
I have the following table intra_trades with t_id as the primary key. There is a trigger on that table that gets the next sequence and inserts it into the t_id column for every insert. I need to load data into that table using SqlLoader as chunks of 3000 rows and return the t_id back the script that Sqlload the data so that it can use that t_id's for the next process in the script.
intra_trades
t_id NUMBER(15) pk
t_name VARCHAR2(30)
t_loc VARCHAR2(40)
t_start TIMESTSTAMP
t_end TIMESTSTAMP
[code]....
The problem is that the only unique key on that table is the t_id which has a sequence on it and it is the pk. There can be duplicate rows in that table to meet the business needs for the company. So it is hard to associate the rest of the data in a row with t_id. The only thing I can think of is return the t_ids in the order it inserted so if the script keeps the order of rows in the memory it can associate the tid with the rest of the intra_trades info.How can I make the sqlloader return an array of t_ids that inserted? I need to return the t_ids's in the order it inserted so that the script can associate the t_id with the rest of the rest of the data in a row.
View 4 Replies
View Related
Oct 17, 2012
In Apex 4.2, the item validation of "Function Returning Boolean" and "Function Returning Error Text"; They seam to be backwards.
Is there a simple statement that can be used to fix this in the apex dictionary?
View 1 Replies
View Related
Aug 4, 2011
How do i list all tables from dba_tab_columns which contains both column name='id' and 'date'. I don't want to list the tables contains either 'id' or 'date'
View 8 Replies
View Related
Feb 13, 2012
I had face the problem regarding to database structure changes..
I had a old database which contain multiple column for 1 empno. which contain multiple subjects and its total marks.
Now the new structure is like all subjects and total marks as a row.
Eg. OLD data Structure
EMPNO SUNBJECT_CODE TOTAL_MARKS
1111 S1 45
1111 S2 21
1111 S3 55
1111 S4 41
NEW DATA STRUCTURE
EMPNO SUB1 MARK1 SUB2 MARK2 SUB3 MARK3 SUB4 MARK4
1111 S1 45 S2 21 S3 55 S4 41
I have more than 1.5 lakhs records in my old data structure..
Is there any Possibilities direct from Sql or i have to do it manually in excel sheet?
View 11 Replies
View Related
Jul 24, 2012
i want to add a column data of datatype number of all the tables at a time in a database..
i am trying with the all_tab_columns but i can get only the column info whether it is number or varchar2 or any other data type but i am unable to retrieve the column name and the sum of length of the data present in all the rows in that column...
View 39 Replies
View Related
Oct 15, 2012
is it possible to update a same column name in two tables.
I have two tables in same schema
(1)table name
pem.igp_parent
column name
igp_no.
igp_type
(2)table name
pem.igp_child
column name
igp_no.
igp_type
i want to update igp_no column in one query, how it would be possible.
View 2 Replies
View Related
May 29, 2011
I need to implement the foreign key on a column of a table from 2 tables. My requirement is in bellow.
drop table t1;
create table t1 (slno number, acc_no number);
drop table t2;
create table t2 (acc_no number primary key, acc_name varchar2(100));
drop table t3;
create table t3 (acc_no1 number primary key, acc_name1 varchar2(100));
[code]...
It is provided that the values of acc_no in t2 and acc_no1 in t3 are unique.Now it required that while inserting into t1 , the system will check either t2 or t3 tables.
View 7 Replies
View Related
Mar 29, 2010
I need to identify the dependencies of all the Tables on Packages at column level.
E.g. : XYZ is a package that uses ABC Table having E,F,G has a column, PQR - Table and its columns - R,S,T
The resultant query / code should return like this
PackageName TableName ColumnName
XYZ ABC E
XYZ ABC F
XYZ ABC G
XYZ PQR R
XYZ PQR S
XYZ PQR T
Identify the dependencies at column level.
View 5 Replies
View Related
Oct 2, 2012
We have to update a single column data in about 10 tables which has child/parent table relations, pk/fk constraints.. The column that we are updating is a part of primary key in half of the tables and part of foreign key in the other half tables.. I'm thinking of disabling all the foreign key constraints in the tables then update the column data then enable the foreign key constraints in these tables.
View 7 Replies
View Related
Aug 8, 2011
I am getting ORA-39726 error when a dropping a column on a non-compressed table.
SQL> select distinct TABLE_OWNER,COMPRESSION,COMPRESS_FOR from dba_tab_partitions where TABLE_NAME='STM_AE_STMT_HISTORY_DETAIL';
TABLE_OWNER COMPRESSION COMPRESS_FOR
------------------- ---------------------- -------------------
APP_SU DISABLED
SQL> ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR;
ALTER TABLE APP_SU.STM_AE_STMT_HISTORY_DETAIL DROP COLUMN RATE_DESCR
*
ERROR at line 1: ORA-39726: unsupported add/drop column operation on compressed tables
View 2 Replies
View Related
Aug 16, 2011
i want to select columns of 3 tables in such a way that period column should be in the group by function.
create view allocated_budgets_detail as
select ba.ba_fin_year, ba.ba_start_date, ba.ba_end_date, ba.ba_rev_no,
bh.bh_budget_code,
bd.bd_period,
bb.bb_entered_amount
from budget_header bh, budget_allocation ba, budget_distribution bd, budget_balance bb
where bh.bh_budget_id = ba.ba_budget_id
and ba.ba_line_id = bd.bd_budget_line_id
and ba.ba_line_id = bb.bb_budget_line_id
group by bd.bd_period
View 13 Replies
View Related
Jul 20, 2009
We are using Oracle Streams for replication.
Column datatypes in some of the tables on Source and Destination are different (Number on Source and Varchar2 on destination).
Do we have to create any rule or dml handler to handle this or Streams will automatically take care of it?We are oracle 10g.
View 1 Replies
View Related
Jan 11, 2012
my user is trying to drop columns, but she gets below error:
SQL Error: ORA-39726: unsupported add/drop column operation on compressed tables
39726. 00000 - "unsupported add/drop column operation on compressed tables"
i just checked whether table is compressed or not, it is not compressed it seems:
select owner, table_name,COMPRESSION,COMPRESS_FOR from dba_tables
2 where owner = 'EQUIPMENT' AND TABLE_NAME = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
EQUIPMENT ETHRNT_VRTL_CNXN_HRLY_AGG_SWP DISABLED
1 row selected. i am able to set one column as UNUSED. and then i am able to see the count accordingly from below view:
select * from DBA_UNUSED_COL_TABS
where owner ='EQUIPMENT' and table_name = 'ETHRNT_VRTL_CNXN_HRLY_AGG_SWP';
but not able to drop the unused columns. if i tried to drop a column directly from the table, that also giving above error.
View 7 Replies
View Related
Aug 29, 2012
I have a question i wanted to know that " Is it possible to write the Following Query Using NOT EXISTS
SELECT * FROM DEPT WHERE DEPTNO NOT IN (SELECT UNIQUE DEPTNO FROM EMP);
And one more doubt is there, can we use join to get same result.
View 9 Replies
View Related
Aug 3, 2009
i see in my alert.log this message:
Errors in file /oracle/BWP/saptrace/usertrace/bwp_ora_2728058.trc:
ORA-01114: IO error writing block to file 1030 (block # 602122)
ORA-27063: number of bytes read/written is incorrect
IBM AIX RISC System/6000 Error: 28: No space left on device
Additional information: -1
Additional information: 180224
But this file_id i don't have in my database, i am making these queries:
SQL> select FILE_ID from dba_temp_files order by FILE_ID;
FILE_ID
----------
1
2
3
4
5
6
7
8
9
[code]....
I don't have this file_id, why alert.log is showing me it? Of course, nobody has created this datafile and nobody has removed it too.
View 11 Replies
View Related
Jun 18, 2013
i have a procedure like create or replace procedure studrec( a in sid%rowtype)asi sid%rowtype;beginselect sid into ifrom students; i need to check whether sid is exist in the variable i or not
View 5 Replies
View Related
Oct 12, 2005
I have a script like this:
------------------------------------------------------------
DROP TABLE CON_TEST CASCADE CONSTRAINTS ;
CREATE TABLE CON_TEST (
IDI NUMBER(10,0),
USERID VARCHAR2(10),
PWD VARCHAR2(10),
NOTE VARCHAR(100)
)
----------------------
But i think if table CON_TEST doen't exist, an error message will appear. I know that in SQL Server we can check if table exists or not. So, i wonder if we can do that in Oracle?
By the way, is there any way to run a file script that contents TABLES, STORED, ... on a developed PC connect to oracle db server? (in case, i'm developing on PC, using Net Service Name to conect to Oracle DB Server)
View 9 Replies
View Related
Mar 30, 2012
following is a query which i find difficult to understand why EXISTS is failing. There are two scenarios where if i block LINE 30 and unblock line 31 of the code then one record is returned.
SELECT A.ENTITY_CODE,
A.VRNO,
A.VRDATE,
[Code]....
View 4 Replies
View Related
Jan 22, 2013
Which is the best way to check whether a record exists.
DECLARE
l_count NUMBER;
BEGIN
SELECT COUNT(*) INTO l_count FROM emp WHERE empno=7839;
IF l_count=1 THEN
dbms_output.put_line('exists');
ELSE
dbms_output.put_line('not exists');
END IF;
END;
[Code]...
View 7 Replies
View Related
Nov 20, 2010
how to write a function that returns top value if not exists then next top for combination of customer_id and hierarchy.For instance :
If I've got table
customer_id ,hierarchy, function_code
123 |1 | Z1
123 |2 |67
123 |3 |5B
678 |10 |S2
678 |11 |Z2
345 |2 |11
For the customer ID 123 I want to return Z1, for customer 678 I want to return S2 and for customer ID 345 I want 11
Problem is that I'm new to the concept of looping. I know how to write a function that accepts customer_id as a value write a cursor and then check IF hierarchy = 1 the return FUNCTION_CODE IF hierarchy - 2 THEN ...
but I need something more universal as some of the customers may have hierarchy function 1 and that would be the top one for him but others might have function of hierarchy 10 as top and checking all of the possibilities using if would be just stupid. So how to write something universal ? And of course if function did not find any customer_id then return null.
View 9 Replies
View Related
Jul 13, 2012
I have a list of values from a spreadsheet and want to know which values are NOT matched in columns of a table
here's the list (really 4000+ long)
1234,
2345,
3244,
and I want to find the values that are not in the table 'table_name' like this
....
where not exists (Select number_n from table_name
where number_n in ('1234', '2345', '3244', ...(the list above))
View 11 Replies
View Related
Jan 19, 2010
I have a table called TRANS, and a primary key field tran_id. How would i check if there is a record matching tran_id 'DUP7927' ?
View 25 Replies
View Related
Aug 14, 2012
As per the earlier post,I am able to parse now.
i have also another concern as per below xml file.
My requirement is to identify perticular node ,whose having PriorValue attribute present in <pi:the Actual_Comp_Change> tag,those record should return.
<?xml version="1.0" encoding="UTF-8"?>
<pi:Extract_Employees xmlns:pi="urn:com.workday/picof">
<pi:Employee>
<pi:Employee_ID>1100</pi:Employee_ID>
[Code]....
OUTPUT:
EmployeeID_ Name JobTitle_ Grade ActualComp_Change_
1100 Surana Intern - Master¿s A 500000
1000 roy Intern - Master¿s B 216000
1000 roy Intern - Master¿s 00 266000
But my requirement is to display only those employeeID ,where Actual_Comp_Change tag having PriorValue attribute.
The required OutPUT should be :
EmployeeID_ Name JobTitle_ Grade ActualComp_Change_
1100 Surana Intern - Master¿s A 500000
is there any possibility to use ExistNode() function to the above quer or is there any alternative solution.
View 5 Replies
View Related
Sep 17, 2013
I have this sample:
the column data1 is datetime datatype with
t as ( select 'SMITH' nom,to_date('21/09/2013 07:30:00') data1
from dual union all select 'ALLEN',to_date('21/09/2013 07:40:00')
from dual union all -- select 'WARD',to_date('21/09/2013 07:50:00')
from dual union all
select 'JONES',to_date('21/09/2013 08:00:00')
from dual union all
[Code]..
How can I write a select to check that If I input 10 minutes to nom 'ALLEN' it's ok because the time 07:40 + 10 minutes = 07:50 the row not exists, (the next)but If input 20 it exists because the sum = 08:00 and row isn't free , indeed, there is 'JONES'?
View 4 Replies
View Related
