SQL & PL/SQL :: Add Null In Select Based On Certain Condition?

Oct 20, 2011

I got a requirement to check if a particular range of values are present , if few values missing then have to add null for the values. This has to be a SQL statement.

table 1 :

fiscal_month_id, value1, value2
2010100028482848
2010110025712709.5
2010120027452721.3
2011010026052692.25
2011020026382681.4
2011030030992751
2011040027662753.14
2011050029732780.625

This is the table. the fiscal month id denotes that the values is from month Oct(20101000) to May (20110500). The fiscal year cycle for me is from OCT to SEP. Hence when we select this table, the select should retreive the below data

fiscal_month_id, value1, value2
2010100028482848
2010110025712709.5
2010120027452721.3
2011010026052692.25

[code].....

How to do this in a select statement ?

This fiscal_month_ID is actual from another table where all time level information are kept.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: How To Make IS NOT NULL Condition To Use Index

Aug 23, 2013

The below query is not performing good.

SELECT * FROM wedb_master
WHERE c_group IS NOT NULL;

The table is having 200000 records.

I have index on c_group but not used. FTS is happening.

how improve the performance of IS NOT NULL predicate.

View 2 Replies View Related

SQL & PL/SQL :: Null Value On Field Use In Join Condition?

Aug 5, 2011

I have encountered a weird (or maybe not weird at all but unexplainable from my point of view) behavior from Oracle. I have simplified the example as much as possible

This query returns 2 rows as expected:

with edited as
(select F101, e_id from (select 'Test' F101, -1 e_id from dual
union all select 'Test1' F101, -2 e_id from dual) input_clob),
distinct_intermediate_edited as

[code]...

But this one (with only one row in input_clob) returns one row (as expected) but with null on e_id (why?):

with edited as
(select F101, e_id from (select 'Test' F101, -1 e_id from dual) input_clob),
distinct_intermediate_edited as
(select e.f101, e.e_id
from edited e

[code]...

If I change the join condition with and nvl(e.E_id,0) = nvl(e_id,0) both cases work as I expect (e_id = -1 for second query) but I simply want an explication for this behavior.

View 8 Replies View Related

SQL & PL/SQL :: How To Use Or Condition To Check If 6 Col Values In A Row Is NULL

Aug 23, 2010

How can I use OR condition in sql to check whether if the 6 col values in a row is NULL, I need something like below, but idk the syntax of checking if either of the mentioned cols is null using an or condition.

select a.mid
FROM table_a a
JOIN table_b b
ON a.dept = b.dept
JOIN table_c c
ON b.eid = c.eid
WHERE a.tid = :tid
AND (a.i1 or a.i2 or a.i3 or a.n1 or a.n2 or a.n3 or a.t1 or a.t2 or a.t3 ) IS NULL

View 1 Replies View Related

SQL & PL/SQL :: Grouping Based On Condition

Aug 4, 2013

i have a table where there are codes of length 6 or length 12 some times, i need to add the summary of amount based on two different types of codes, one is adding the distinct codes which are of 6 char long and other sum will be based on from substr(7) till last.

create table strings ( strings_var varchar2(12),strings_amt number);
insert into strings (strings_var,strings_amt) values ('02.01',10 );
insert into strings (strings_var,strings_amt) values ('02.01_A11111',15);
insert into strings (strings_var,strings_amt) values ('02.02_A11111',15);
insert into strings (strings_var,strings_amt) values ('03.01_B11111',15);
insert into strings (strings_var,strings_amt) values ('03.02_B11111',15);

the output which i want is as below.

string value
'02.01' 10
'A11111' 30
'B11111' 30

View 4 Replies View Related

PL/SQL :: Display Value Based On Condition

Dec 13, 2012

I have wells that can have multiple statuses (one record for each status). I need to create a column to display a Y or N based on whether or not a given well has only certain types statuses. If all records for a given well are Susp and/or Abd, then I want to display a Y. If the well records include Susp or Abd, but also have other statuses (or do not even have a status of susp or abd) then I want to display a N.

So:

Well           Status       ident
12345         SUSP          Y
12345         SUSP          Y
12345         ABD           Y
98765         SUSP          N
98765         PROD         N
98765         ABD           N
45678         SUSP          Y
45678         SUSP          Y
ASDFG         ABD           Y
ASDEG         ABD           Y
TTTTT         PROD       N
TTTTT         TEMP

View 4 Replies View Related

SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?

Jul 12, 2012

I have 8 columns. Some of them might be null.I want to display all 8 columns in my result. Not null columns will be first and null at the end.Here is a sample data :

Employee table :
Employee_id Emp_fname emp_lname emp_mname dept salary emp_height emp_weight
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

The expected result is :
result1 result2 result3 result4 result5 result6 result7 result8
1 aaa ddd d1 100 6 180
2 bbb ccc 120 169
3 dfe d2 5.9 223

View 8 Replies View Related

SQL & PL/SQL :: Aggregation Two Different Columns Based On Same Condition?

Apr 3, 2011

I have a result-set which has 4 columns like (Region, PaymentDate, DebitAmt, CreditAmt). This result-set will always have a maximum of one month's records in it.

Suppose, imagine i have data for a month (ex, Mar 1 to Mar 31)...

Now... to aggregate (sum) the amount columns (DebitAmt, CreditAmt) in my resultset based on different date ranges, i wrote a sql like below...

Quote:
SELECT
REGION,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN DebitAmt ELSE 0 END) AS Debit_H1,
SUM(CASE WHEN PAYMENTDATE BETWEEN MAR 1 AND MAR 15 THEN CreditAmt ELSE 0 END) AS Credit_H1,

[code]...

My doubt is, in the above query, to aggregate two different columns based on same condition, i am checking the same condition twice...

View 2 Replies View Related

SQL & PL/SQL :: Substring Column Based On Condition

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

SQL & PL/SQL :: Dataset From Two Sets Of Tables Based On Condition

Nov 26, 2010

have two queries that will return same columns from two different set of tables ( column mapping has been taken care of). The return type is out ref cursor. (P_SUPPLY_REORDER )

Query 1-SO
-----------------------------------
select
so.SMO_NO,
so.SPLY_ORD_DT,
so.fk_CUST_ID as CUST_ID,
so.CUST_PO_NO,
so.ATTENTION_NAME,
[code].....

Query-2 Xcom
--------------------------------------
select
null as sMO_NO,
xso.created_date as SPLY_ORD_DT,
xso.fk_cust_id as cust_id,
cust.cust_po_no as cust_PO_NO
,(sta.SHIP_TO_ATTN_FIRST_NAME||''||sta.SHIP_TO_ATTN_LAST_NAME) as attention_name,
xsol.CARTONS_ORDERED as SPLY_ORD_QTY,
[code].......

Now the requirement is
One of four conditions are possible for each Supply Reorder Number:

. Both table queries return no records- Populate all the P_SUPPLY_REORDER output fields with nulls
. SUPPLY_ORDER returns a record, but XCOM_ORDER_HEADER returns no records
- Populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
. SUPPLY_ORDER returns no records, but XCOM_ORDER_HEADER returns one record
- Populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.
. SUPPLY_ORDER returns a record, and XCOM_ORDER_HEADER returns a record; find out the latest order by comapring max(SPLY_ORD_DT)
from SUPPLY_ORDER with max(CREATED_DATE) from XCOM_ORDER_HEADER.
- If the latest order is in SUPPLY_ORDER, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If order dates are equal from both join results, then populate output fields with values from the join of SUPPLY_ORDER and SUPPLY_ORDER_LINE.
- If the latest order is in XCOM_ORDER_HEADER, then populate output fields with values from the join of XCOM_ORDER_HEADER and XCOM_ORDER_LINES.

Question is how can we switch over the queries to pull respective dataset based on these conditions ( checking that which table join is going to return a row and then based upon latest order if both tables return a row) and all this logic as part of single SQL statement that is returned as OUT Ref Cursor.

View 7 Replies View Related

SQL & PL/SQL :: Count Number Of Record Based On Some Condition

Jun 28, 2013

i have two tables one as test_master and other one limit master.

test master table structure

ct_id ct_a ct_b ct_C ct_d ct_e...... etc
1 -- -- -- -- --
1 -- -- -- -- --
2 -- -- -- -- --
limit master structure

limit_id ct_a_limit ct_b_limit ct_c_limit ct_d_limit ct_e_limt ..... etc
1 -- -- -- -- -- -- -- --
2 -- -- -- -- -- -- -- --

ct_id and limit_id is match column used for mapping.

now i want the total count of no of records in test_master in which test_master column value not exceed limit master column limit value if any one column exceeds its limit then the same row will be in my count criteria.

how to achieve the same using sql/oracle query.

View 7 Replies View Related

SQL & PL/SQL :: Update Query Based On Join Condition

Jun 16, 2011

I have two tables. By joining these two tables, I need to update a field in table1.

UPDATE table1
SET table1.FLAG = 'Fixed'
where table2.lastname = table1.lastname
and table2.status in ('fulltime','parttime')

I keep getting error 'table1.lastname' is invalid identifier.

I can't understand the error message. I made sure that the fields exist.

View 5 Replies View Related

SQL & PL/SQL :: To Delete Data From Tables Based On A Condition

Feb 3, 2013

i have a list of 500 tables. I want to delete data from those tables based on a condition. (Data before 2008 year needs to be deleted). Each table has a column based on which data needs to be deleted. Provide a code which does this efficiently and fast. Bulk collect is preferable.

View 17 Replies View Related

SQL & PL/SQL :: Create Multiple Records Based On Condition

Oct 10, 2012

CREATE TABLE test1
(strt_num NUMBER ,
end_num NUMBER ,
des VARCHAR2(5),
CONSTRAINT pk_strt_num PRIMARY KEY (strt_num)
);
INSERT INTO test1 VALUES (5, 8, 'GC');
INSERT INTO test1 VALUES (10, 25, 'AB');
INSERT INTO test1 VALUES (12, 35, 'PC');
INSERT INTO test1 VALUES (22, 65, 'LJ');

SELECT * FROM test1

STRT_NUM END_NUM DES
-------------- ------------ -------------
5 8 GC
10 25 AB
12 35 PC
22 65 LJ

The requirement is the records should be split based on below conditions

1. Split only those records WHERE (end_num - strt_num) > 10
2. If TRUNC((end_num - strt_num)/10) = n, then n + 1 number of rows should be created for that record
3. While splitting the records,
-> For first record , START_NUM = Original STRT_NUM and END_NUM = START_NUM + 10
-> Second record , STRT_NUM = previous END_NUM + 1 and END_NUM = previous END_NUM + 10
And this should continue for all records except the last record
-> For last record, STRT_NUM = previous END_NUM + 1 AND END_NUM = Original END_NUM

This table has 5 million records. Only for 2000 records (end_num - strt_num) > 10.

Expected Output.

STRT_NUM END_NUM DES
------------- -------- ---------
5 8 GC -- No chage, END_NUM - STRT_NUM < 10

10 20 AB
21 25 AB

12 22 PC
23 32 PC
33 35 PC

22 32 LJ -- STRT_NUM = Original STRT_NUm, END_NUM = STRT_NUM + 10
33 42 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
43 52 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
53 62 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = previous END_NUM + 10
63 65 LJ -- STRT_NUM = Previous END_NUM + 1, END_NUM = Original END_NUM

View 7 Replies View Related

Forms :: How To Retrieve Only That Data Which Is Based On Given Condition

Jun 22, 2011

,i have a table called travel detail from which i have retrieved few rows based on 1 condition by using LOV and on next few rows modification is allowed. The rows are getting modified but it is also allowing me to enter in next blank row and insert any data in that row.how can i retrieve only that data which is based on the given condition. Further blank rows should not b displayed.

View 2 Replies View Related

XML DB :: Increment Counter In Loop Based On Condition?

Jan 24, 2013

I want to increment a counter in a loop based on a condition.

Here is how my xml looks like

<result>
<resultset id=1>
<value>

[Code]....

I need to look at each and every resultset and check if the value is >400 and if it is then display some text. Something like this

<outputvalue>
Yes, there is a value greater than 400
</outputvalue>

In my XSL, I declared a variable called count with value 0. I created a for-loop which goes through these results. Then inside the loop there is a condition to cehck if the value is > 400. If the value is >400, then the counter is incremented.

````````````````````
<outputvalue>
<xsl:variable name="Count" select="0"></xsl:variable>
<xsl:for-each select="/results/resultset">

[Code]....

There is a problem with the above code is that result is like this

<outputvalue>
*1*
Yes there is a resultset with greater than 400
</outputvalue>

Is there a way I can just remove the character 1 from the output? Or is there a better way to increment?

View 3 Replies View Related

PL/SQL :: How To Enter Value Based On Condition In Column Without Using Trigger

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

SQL & PL/SQL :: Find Logic To Query Data Based On A Condition

Aug 18, 2013

find the logic to query data based on a condition like..I am having a table dummy

USERNAME RESP_NAME FUNC_NAME MESSAGE
-------- --------- ------------------ ------------
TEST1 SYS_ADMIN CONCURRENT_PROGRAM AAAAAAAAAAAA
TEST1 SYS_ADMIN % BBBBBBBBBBBB
TEST1 % CONCURRENT_PROGRAM CCCCCCCCCCCC
TEST1 % % DDDDDDDDDDDD
% SYS_ADMIN CONCURRENT_PROGRAM EEEEEEEEEEEE
% SYS_ADMIN % FFFFFFFFFFFF
% % CONCURRENT_PROGRAM GGGGGGGGGGGG
% % % HHHHHHHHHHHH

The above 8 are the possible ways to key in the data using a form.The logic is like based on a particular condition the message should display in the report.If they specify a particular USER and RESP_NAME and FUNC_NAME then it should display AAAAAAAAAAA (This should take priority first). The priority is in order the default should be the last one.

View 22 Replies View Related

PL/SQL :: Assign Number To Row Based On Condition Analytic Function

Oct 24, 2013

Oracle 11g databaseidval1val2100a110b120c200a220b 
WITH input AS
(SELECT 1  id

[Code].....

input; Output:idval1val2assigned_number100a0110b0120c2200a0220b1 The dense numbering sequence should be assigned to each row based on id and val1 column. For a given Id, the numbering only starts after val1 > 1 till then the assigned_number will be zero.

View 1 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 :: Trigger To Update Table1 Based On Condition Of Values In Table2

Feb 20, 2012

I have two tables as

Table LEAVE
Column Type Null Description
APP_NO Number(6,0) Not Null PK Leave Application Number
ECN Number(6,0) Not Null FK Employee Code Number
APP_Date Date Not Null Date of Application
From_Date Date Not Null Date from which the leave starts
TO_Date Date Not Null Date upto which the current application leave remains i.e. end of leave applied for date
NO_OF_Days Number(2,0) Not Null Difference between TO_Date and From_date
LEAVE_TYPE VARCHAR2(3) Not Null Can be one of SL, CL, LWP or LTA
Status VARCHAR2(25) Not Null Can be one of Saved, Rejected or Approved
Remark VARCHAR2(100) Nullable Reason to be put if status is rejected
[code]....

What I really want to do is that when a record is inserted in the LEAVES table (an application for leave is submitted by any employee and if it is approved) then I want to update the _USED values of the corresponding LEAVE_TYPE in the LEAVEENTITLE table which holds values of types of leaves entitled to employee.

For example if 3 rows are inserted in the LEAVES table as
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(1,1234,'2012-01-01','2012-01-05','2012- 01-01',5,'SL','APPROVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(2,1235,'2012-01-01','2012-01-05','2012- 01-01',5,'CL','SAVED',null);
INSERT INTO LEAVES (APP_NO,ECN,FROM_DATE,TO_DATE,APP_DATE,NO_OF_DAYS,LEAVE_TYPE,STATUS,REMARK)
(3,1236,'2012-01-01','2012-01-05','2012- 01-01',5,'LTA','REJECTED','Clash with the annual meet, revise dates');

Then the value of SL_USED in the LEAVEENTITLE table of record corresponding to the ECN = 1234 should be updated with +5 and naturally the SL_ UNUSED value of the record should be updated as SL_ENTITLED - SL_USED. For the APP_NO 2 and 3 none of the values in LEAVEENTITLE should be updated as the STATUS is not 'APPROVED'

I tried with the following trigger, but is compiling with a warning (not showing what the warning is)

CREATE OR REPLACE TRIGGER leaveentitle
AFTER INSERT ON LEAVES
FOR EACH ROW
BEGIN
UPDATE LEAVEENTITLE LVE
SET LVE.SL_USED = SL_USED+(CASE
WHEN :NEW.LEAVE_TYPE = 'SL'&& NEW.STATUS='APPROVED'
THEN :NEW.NO_OF_DAYS
SL_UNUSED=SL_ENTITLED - SL_USED
ELSE 0
END),
[code]....

View 9 Replies View Related

SQL & PL/SQL :: Join For Bringing Master And Child Records Based On Condition?

Jul 30, 2013

The attachment contains the table info. The condition is that when there are childer for the master ,the master record shd be negated . Excuse me if this very easy. Example -DEL HAS children so the record with DEL and DEL SHD not be in the result query. Example -RAG HAS NO children. so the MASTER record should be taken.

Input

COL1COL2COL3COL4
MASTERDELDELP1
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1

My expected output
COL1COL2COL3COL4
MASTERDELJANP2
MASTERDELAGRP3
MASTERRAGRAGP1
MASTERCENAPTP2
MASTERRUGRUGP1

View 3 Replies View Related

Application Express :: Default Value Of Radio Button Based On A Condition?

Oct 1, 2013

i have used radio button under dynamic conditions for an interactive report.eg: radio button values are like a) front end b)middlware c)back end.so based on the selected value of the radio button, interactive report appears. and each fields in radio button have separate set of users. now my issue is,based on the user logging in, the default value should set up while the page is loaded...[i.e... if a front end user logs in, the default value of tat radio button shd be front end...] 

View 5 Replies View Related

Forms :: Fetch Single Record From Multiple Records Based On Condition

Jan 27, 2012

I have made a travel booking system which comprises of 3 forms

1)Travel Booking form
2)Reservation Form
3)Cancellation Form

Under one booking number i can add multiple users in which they can have there multiple travels.

Users can cancel there individual travels under a prescribe booking number which on doing the Cancel flag turns to 'Y'.

What i want is, If a user is cancelling his/her travel under any booking number then while retriving the records in Travel Booking form, the travels which are cancelled should not be in enable mode.

For one user there can be 4 travels out of which 2 are cancelled, how can i track only those records whoes cancel flag is set to Y. some logic to find it out. Else can i use :system.cursor_record. If yes, How to use it for this system.

View 9 Replies View Related

Server Utilities :: Export / Import Tables Based On Date Condition?

Jul 26, 2012

i'm trying to do an export/import process using command prompt and the idea is export a records based on the date condition. and the date will be the parameter. my code is like this:

exp <username>/<password>@<database> file=<table_name>.dmp tables=<source_table> query="where <date> between &start_date AND &end_date";

is it possible to do like this, that it should prompt you to enter the start and end date?

then my import script:

imp <username>/<password>@<database> dumpfile=<table_name>.dmp tables=<target_table>;

the idea is get only the records from ProdDB based on the date condition, and append it to the MISDB.

View 12 Replies View Related

Application Express :: Navigate From A Report To Different Updatable Forms Based On Condition?

Oct 11, 2013

navigate from interactive report to different forms.. 

View 10 Replies View Related

SQL & PL/SQL :: Best Way To Select Out Of Group Condition

Nov 21, 2011

I am asking about the best way in SQL for the following case:

CREATE TABLE TRY_A (A NUMBER, B NUMBER);

INSERT ALL
INTO TRY_A VALUES (1,0)
INTO TRY_A VALUES (1,1)
INTO TRY_A VALUES (1,2)
INTO TRY_A VALUES (2,0)
[code].......

I need to learn the best way to select all A values where B is never 0. So if the needed result for the above example is:

A
--------------
3
5

As column B was never 0 with column A = 3 or 5.

View 11 Replies View Related

SQL & PL/SQL :: Select Clause With Condition

Jul 22, 2010

Based on same salary i need to retrieve data like below whose hire date should be within a week (7days). The table may have 200000 records.

empno Hiredate salary
1234 03-JUN-10 3000
1354 21-MAY-10 3000
1834 01-JUL-10 3000
1954 08-JUL-10 3000

View 9 Replies View Related

SQL & PL/SQL :: Select Rows With Max Condition

Aug 13, 2010

I have been trying to find out how to write the following query:

Suppose the following table:

ID seq
24 1
24 2
24 3
67 1
67 2
67 3
67 4
67 5
13 1
13 2

I would like to retrieve the rows for every different ID with its max value. For one ID that would be:

select * from TABLE where seq in(
select max(seq) from TABLE where id=24)

How can i do this for all the rows??

View 2 Replies View Related

SQL & PL/SQL :: Select Null Value

Feb 18, 2011

I have a sql queries in that i saw one sql querie like this

select columnname,null columnname from tablename

what is the meaning of this which i marked with red (null columanmea)

View 2 Replies View Related







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