SQL & PL/SQL :: Selecting Value Of Column Into Different Variables Depending On Condition?
Aug 10, 2011
I need to select the value of a column into different variables depending on the condition.
As in, I have
FILE_TYPE_CODERETENTION_DAYS
CLR 5
SIZ 6
UOM 7
ADB 8
I need to get the Rention days into the 4 variables v_color_file_type_code, v_size_file_type_code, v_buyer_file_type_code AND v_uom_file_type_code Depending whether the FILE_TYPE_CODE is 'CLR' or 'SIZ' or 'ADB' or 'UOM'
View 3 Replies
ADVERTISEMENT
May 24, 2013
I was just wondering what people's thoughts and experiences are regarding performance of conditional expressions.
Tony blogs about it here
[URL}.........
And gives the example
1) Value of item/column in Expression 1 = Expression 2Expression 1: P1_JOB
Expression 2: SALESMAN
2) PL/SQL Expression
Expression 1: :P1_JOB = ‘SALESMAN’>
Recently I've been trying to eke every shortcut I can with an application and noticed this can be a contributing factor when done dozens of times within a page render.
View 1 Replies
View Related
Nov 4, 2013
I am having a table structure like below.
city amt1 tx_date
-----------------------------------------------------
Blr 10000 20050101
Delhi 25000 20050101
Blr 10000 20050102
Blr 2100 20050103
DELHI ...... 20050104
...... ....... ........
i have to place the data in following manner.
city 20050101 20050102 20050103 20050104 ...........etc
-------------------------------------------------------
Blr 10000 10000 2100
Delhi 25000 0 0
Depending on the no. of distinct dates in table 1 i have to make those many columns in table 2. I m trying to write a Query in SQL. If its not possible in SQL give me PL/SQL procedure.
View 6 Replies
View Related
Aug 1, 2011
My need is to calculate rows and write result into column:
c1 if current month is 1st in quarter,
c2 if current month is 2nd in quarter,
c3 if current month is 3rd in quarter.
How do I perform it?
View 22 Replies
View Related
Apr 15, 2013
I'm using Oracle Database 11g R2 need to upload Telecom CDRs to the database on daily basis , it's huge data and changeable , an example of my file in linux Redhat 5 server as below ,
INDRtotalduration = 00:00:00
origin_matrix = 4186603ec003ef01
triggering_key = 665000207
Start_Date_And_Time = 03/04/2013 09:24:10
IMSI = 418666651000207
[code]......
there is no problem with this i think i can use SQLLDR to upload this file , but the problem here the positions of the columns in the file could change depending on user behavior it could be the first row comes in the third row or any row and maybe more rows appears ,
locind = 0
origin_matrix = 4186603ec003ef01
Start_Date_And_Time = 03/04/2013 09:24:10
INDRtotalduration = 00:00:00
IMSI = 418666651000207
triggering_key = 665000207
[URL].......
this is sample of the file i could be more than 100 rows , and the position of the field and field names could be change every time depending on the Subscriber usage , is there any way to upload the file but after checking the field name in the file and matching to corresponding column name in the table .
View 5 Replies
View Related
May 31, 2013
I have a classical report with a column as "Affirmation Status". This column can take three value as "Affirmed, Rejected or NA". My requirement is I have to show all row in one color like red wherever it has value affirmed, and all row in color like green wherever it is rejected.
View 4 Replies
View Related
Mar 7, 2013
We have a master detail form with report that inserts the details in both region/ sub region.
However our requirement is that in sub region we have a LOV to chose the service, and each service has a price. How can we populate the Price column based on the selection of the Service? There are some similar posts but we cannot apply to our form.
Application Express 4.2.1.00.08
Oracle Database 11g Express Edition Release 11.2.0.2.0
View 2 Replies
View Related
Apr 24, 2012
I just want to know that "is it safe to select a columns using ROWID in a table?"
View 3 Replies
View Related
Jul 15, 2013
I have table TEST_REP with below data
DA SUMA
---------------------- ----------------------
2011 2
2011 3
2011 5
2012 2
2012 7
2014 2
2014 10
2015 2
2016 33
2015 26
2017 21
2017 2
2018 23
13 rows selected
I have used following query to get the below output:
select
br_mat MAT_YEAR,
sum(br_par) TOTAL
from (
(select to_char(da) br_mat,suma br_par from test_rep)
UNION ALL
[code].......
Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 23
2018 23
2019 0
2020 0
10 rows selected
Expected Output :
MAT_YEAR TOTAL
---------------------------------------- ----------------------
2011 10
2012 9
2013 0
2014 12
2015 28
2016 33
2017 and Greater 46
View 6 Replies
View Related
Feb 20, 2012
Is there a way of selecting the value of a previous column in a case statement, without having to duplicate. E.g.
SELECT prod_id as Product,
Some complex calculation... as Total,
CASE
[Code]....
I do not want to re-write the same calculation in the else statement but just want it to be be the value of Total column.
View 1 Replies
View Related
Apr 8, 2012
create table ptab(pid number);
create table ctab(aphone varchar2(20));
drop table ctable
create table xtab(pid number,phone varchar2(20), tel1 varchar2(20), tel2 varchar2(20), tel3 varchar2(20))
insert into ptab values(1);
insert into ptab values(2);
insert into ptab values(3);
insert into ptab values(4);
[code]..
i have 3 tables, xtab, ctab and ptab
join condition
ptab.pid = xtab.pid
and
--------------------------------------------------------------------------------
what I want the join between xtab and ctab is, aphone should match with phone, and then tel1, then tel2, and then tel3, (if phone, tel1, tel2, tel3 are not null in that order only), if aphone matches with any of these,then just print that particular pid, important point is, aphone should be checked against phone, tel1, tel2, tel3 that order only
so the results should simply print
pid
1
2
3
4
5 should not be printed here because for pid 5 in xtab, none of the phone numbers match with aphone of ctab
I tried this:
select
DECODE (ctab.aphone,
xtab.phone, 1,
xtab.tel1, 1,
xtab.tel2, 1,
[code]...
but i cannot join ctab and ptab, i dont want to use intersect etc, because we are looking at millions of rows here
View 3 Replies
View Related
Oct 20, 2010
I have a column transaction Number . It has the transaction number of goods sold.. Below is the sample.
1. PIT0120029015554492215851181828221018554492R06
2. XY1029201195J05
3. YJ1039201176J01
My Query :
I need to substr the transaction number which starts with PIT to susbst (trxno,1,12) .. and anything other than PIT i need full number without substr . But when i use the above code :" susbstr(trxno,1,12) ..": it will substr entire column,. Is there any way to substr only PIT and leave others,
DB:oracle 10g
View 7 Replies
View Related
Oct 5, 2010
Now am on a need to take sum of data from one table but three different condition with three column.I give example Lets consider I have a table called Sales in which it has three columns
1.Dept
2.sales_amt
3.status
There are 3 different flag for status A,B & C.I want display data in the following format in five columns.
Dept Status_A Status_B Status_C
1 30 50 20
1 10 60 30
total 40 110 50
But data is stored in the table in rows like
1 30 A
1 50 B
1 20 C like all rows.
For this I wrote union operation and sum.Am
creating a view for this with there columns dept,Sales_A,Sales_B & Sales C for sum of data.My view is
select dept,Sum(sales_amt),0,0 from sales where status='A' group by dept union
select dept,0,sum(sales_amt),0 from sales where status='B' group by dept union
select dept,0,0,sum(sales_amt) from sales where status='C' group by dept
Am assigning 0 for other two status of data because i want the data to be displayed on different columns based on row level condition.The view will return data like
Dept Sales_A Sales_B Sales_C
1 30 0 0
1 10 0 0
1 0 50 0
1 0 60 0
1 0 0 20
1 0 0 30
So finally am using sum function again and selection total sum of value for each status from this view.So by using union operation how can i display data from different condition of data into seperate columns.
View 2 Replies
View Related
Jan 9, 2013
I would like add an additional column to the data below:
create table test(
id number
cust_num varchar2(5));
[Code]....
Result:
001, AODER, 'Y'
001, BODER, 'Y'
001, CODER, 'Y'
001, DODER, 'Y'
001, 'NONE', 'Y'
001, 'NONE', 'Y'
[Code]...
I would like to add an additional column indicator (Y or N) to specify which ID's do not contain all records of 'NONE'. There can be an occurrence of 'NONE' as long as there is another cust_num different to 'NONE' These should be marked as 'Y' but in cases where all the ID's cust_num = 'NONE' only then these should be marked as 'N'.
View 5 Replies
View Related
Feb 5, 2013
while replicating form mssql 2005 it is entering space for null in oracle clob col.so i wanted to know.
1)can check constaint modify the content of column on which it is defined?
like i want to enter null in a column , if entered date is greater than current date else the entered date.i do not want to use triggers or client side script.
View 1 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
Apr 27, 2012
Which of the below is considered a bind variable. In example one proc. Test the parameter p1 is directly used in the query, so this can be considered as a bind variable.
Is that true about the second proc. where p1 is assigned to a local variable v1 , or this needs hard parsing because v1 is not a bind variable ?
Create or replace procedure test(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=p1;
END;
------------
Create or replace procedure test1(p1 IN VARCHAR2,p_refcursor OUT SYS_REFCURSOR) IS
v1 varchar2(100):=p1;
BEGIN
OPEN p_refcursor FOR select * from Test_tab WHERE item=v1;
END;
View 8 Replies
View Related
Aug 10, 2011
I want to create a SELECT, that shall give back only a special amount of rows, depending on the sum of one of the selected fields.
At first a code sample of the complete selection:
SELECT DISTINCT mnr, ktxt,
(SELECT Sum(meng_4)FROM reldb d1 WHERE d1.mnr=d.mnr)qty
FROM reldb d
WHERE mnr IN (SELECT mnr FROM relac WHERE Lower(rlnr) NOT LIKE 'platte geprägt%')
AND saext='M'
ORDER BY qty DESC,ktxt;
This selection produces some lines of output (in my case i.e. like 300). What I want to see is only that much lines that the condition 'sum of all items listed below meng_4<=sum of all items meng_4 of the whole selection * 0.9' is fulfilled.
So, if the whole selection produces a total of 10000 as sum for all items meng_4, I want to see only that amount of rows that sums a total of at least 9000 for all items meng_4.
I hope, this specification is exactly enough to understand my intent.
1. Can I do this in a query?
2. If yes, what would this query look like?
View 19 Replies
View Related
Nov 27, 2012
Is there any way to generate columns dynamically by depending on the rows in a table in 11G .
Ex: If the deptno in DEPT table is not constant,then how to generate the N numbers of columns based on the deptno. Below query is working when we hard coded the deptno (10,20,30,40).What else if we more number of departments and we don't know the departments also.
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
Connected as dbo
SQL> SELECT *
FROM (SELECT deptno, job, sum(sal) sal
FROM SCOTT.emp GROUP BY job, deptno) PIVOT(sum(sal) FOR deptno IN(10,
20,
30,
40));
[code].....
View 4 Replies
View Related
Jul 12, 2011
I have a database block that refreshes using a timer. In short, I list all open work pieces for a certain garage, displaying route, run, on_time, of_time, location grouped by garage, on_time, off_time
I need to highlight every garage with a different color. I am highlighting the on_time every time the garage changes.
The problem is, every time I move down the records, instead of keeping the previous garage color, and highlight the new garage on_time with a new color, the whole on_time column will be set to the current garage color.
I need to break down the block by color so the user can distinguish between the different garages, and the start and end time. Here is my code, I am putting it in when_new_record_instance of the work_block:
IF :work_bLock.current_garage = 9006 THEN
Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r191g223b191');
ELSIF :work_bLock.current_garage = 9002 THEN
Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r255g228b196');
ELSE
Set_Item_Property('work_block.on_time', BACKGROUND_COLOR, 'r255g250b250');
END IF;
I need the block to be 3 or more different colors depending on the garage.
View 2 Replies
View Related
Aug 22, 2010
I want to hide one frame and display the other depending on the parameter value . How could i make that ?
View 1 Replies
View Related
Oct 12, 2012
APEX 4.1
11g db
Internet explorer 8
I have a region with 4 fields - ID, name, address, telephone. A user can search on any one of theses fields, depending on which field they search on the query is different e.g. Searching on telephone is a different query to searching on address. The query will bring back the same columns, so I just need one report.
How do I create a report where depending on the field the user searches on, it uses the same report but different query.
View 6 Replies
View Related
Mar 28, 2011
I want to create a materialized view for the last 10 days with the enable query rewrite option.
e.g. i want to create a view with the list of employees who joined the company in the last 10 days.
create materialized view M_Employee
refresh fast on commit
enable query rewrite as
select joining_date , name
from
employee
where joining_datde < TRUNC(sysdate) - 10
I seem to get the error SQL Error: ORA-30353: expression not supported for query rewrite
30353. 00000 - "expression not supported for query rewrite"
*Cause: The select clause referenced UID, USER, ROWNUM, SYSDATE.
This error is self explanatory , but is there any work around to have a query liek this to list the employees based on sysdate.
View 23 Replies
View Related
Jul 24, 2013
How to color HTML region header depending on SQL query result ?
View 9 Replies
View Related
Nov 12, 2012
APEX 4.2 Theme 25 Oracle DB 11gr2 - I need to conditionally display Html5/Flash chart regions depending on browser compatibility, how to achieve this.
View 8 Replies
View Related
May 9, 2010
how to select the sixth highest earner in my employees table.how to select 6th lowest earner..
View 18 Replies
View Related
Jul 3, 2011
I am having trouble selecting the row that has max(pay_period) of 13. Here is some test data.
CREATE TABLE TESTME
(
SSN VARCHAR2(11 BYTE),
PAY_PERIOD VARCHAR2(3 BYTE),
PAY_YEAR NUMBER,
KRONOS_ID VARCHAR2(6 BYTE),
LAST_NAME VARCHAR2(15 BYTE),
FIRST_NAME VARCHAR2(14 BYTE),
ADJ_SALARY NUMBER
)
[code]....
View 17 Replies
View Related
Sep 7, 2007
Have a table like this:
ID1ID2DATEID2Value
1121/1/20066
1241/1/2006400
1246/1/2006410
1366/1/2006100
2121/1/20077
2246/1/2007350
2247/1/2007360
I need to return 1 row for each ID1 value - and only the ID2 value of 24 and only the most recently dated record for the multiple ID2 values - query would return:
1246/1/2006410
2247/1/2007360
I have worked and worked on this and I am still stumped (part of the problem may be I am also trying to make this work in Crystal Reports but that is for another day). I need to make this work in Oracle first.
View 3 Replies
View Related
Jun 18, 2013
I want to select data inserted in the table for that day only.
Table name -->ADJCOLUMNS
i want to select areAccount_no-->number datatype TRANSACT_DATE-- NOT NULL DATE I have written the query below .Is the below query correct.
select account_no,to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') T_date from adj
where to_char(TRANSACT_DATE,'DD-MON-YYYY HH24:MI:SS') between
to_char(TRUNC(sysdate),'DD-MON-YY hh24:mi:ss') AND
to_char(TRUNC(sysdate+1) - 1/86400,'DD-MON-YY hh24:mi:ss');
View 4 Replies
View Related
Jul 6, 2012
I need to find the top two values value for each ID Number:
CREATE TABLE TABLE_1
(ID number (8),
NUMBER_1 number (2),
NUMBER_2 number (2),
NUMBER_3 number (2),
NUMBER_4 number (2));
INSERT INTO TABLE_1
VALUES
('12345679','30','25','30','05');
INSERT INTO TABLE_1
VALUES
('99999999','30','25','15','05');
Desired Result:
ID Number 1st 2nd
12345679 30 30
99999999 30 25
View 7 Replies
View Related
