SQL & PL/SQL :: How To Use NOT NULL Inside DECODE
Jun 28, 2007DECODE(FIELD_1,NOT NULL,'working',NULL)
Is it possible to do such? else how to proceed so?
ADVERTISEMENT
SQL & PL/SQL :: Decode Functionality With Null
Jun 29, 2010Explain me the following behavior of DECODE?
SQL> select decode(null,null,'Matched','Not Matched') from dual;
DECODE(
-------
Matched
As expected, output should be 'Not Matched' instead of 'Matched'
SQL & PL/SQL :: Using NULL And DECODE In Dynamic Query
Sep 2, 2011small piece of PL SQL code. how to make this query.Requirement is that a concurrent program is run with parameters and one of them i_num_org_id is non mandatory so it can come as NULL...Now in an existing code which i have to change, it uses a query as
SELECT xyz
FROM abc_table
WHERE <various conditions>
AND DECODE(i_num_org_id,NULL,1,table.organization_id) = NVL(i_num_org_id,1);
Now with the above way, if the program is run with some value for i_num_org_id or run as normal query (with NULL as the value) inside a PLSQL procedure/package then it runs fine.This query if you run in Toad etc then too it will work fine but if it is made a dynamic SQL and then used as either EXECUTE IMMEDIATE or opened as a cursor then we get a "Missing expression". I created this small anonymous block to test this and this will go into missing expression error
declare
l_num_org_id NUMBER := NULL;
l_temp VARCHAR2(100);
l_sql varchar2(1000);
begin
l_sql := 'SELECT '||''''||'abcd'||''''||'
[code].....
how i can reformat this query so that even if NULL value comes for i_num_org_id then it is handled.I am aware about CASE but that cannot be used in WHERE clause i guess.
SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row
Aug 29, 2012I have the following query:
select col_1,col_9 from
book_temp b
where b.col_1 is not null
order by to_number(b.col_16)
;
What I want to add is the following:
COL_9
=====
NULL
A
B
NULL
C
D
E
F
NULL
G
I need to connect the NON-NULL rows to the preceding NULL row.
SQL & PL/SQL :: Counting NULL Vs NON-NULL In A GROUP BY Clause
Jun 21, 2010I am running a GROUP BY query on a few columns of enumerated data like:
select count(*), Condition, Size
group by Condition, Size;
COUNT(*) CONDITION SIZE
-------- ---------- --------
3 MINT L
2 FAIR L
4 FAIR M
1 MINT S
Well, let's say I also have a timestamp field in the database. I cannot run a group by with that involved because the time is recorded to the milisec and is unique for every record. Instead, I want to include this in my group by function based on whether or not it is NULL.
For example:
COUNT(*) CONDITION SIZE SOLDDATE
-------- ---------- -------- ----------
3 MINT L ISNULL
2 FAIR L NOTNULL
2 FAIR M NOTNULL
2 FAIR M ISNULL
1 MINT S ISNULL
SQL & PL/SQL :: Date Field - Not Null Column To NULL
Mar 16, 2011I have a table which has a not null column. the column is date field. I am trying to change it to Null. But it is giving a error.
I am using below query.
ALTER TABLE T_test
modify (paid_to_date null)
SQL & PL/SQL :: Unique Null And Multiple Non-null
Oct 24, 2013create table test
(
id int ,
dat date
)
/
I want to implement a business rule such as we have for each id at most 1 dat null. So, I've created this unique index on test.
create unique index x_only_one_dat_cess_null on test(id, case when dat_cess is null then 'NULL' else to_char(dat_cess, 'dd/mm/yyyy') end);
insert into test values (1, sysdate);
insert into test values (1, sysdate - 1);
insert into test values (1, null);
insert into test values (1, null);
-- -----
insert into test values (2, sysdate);
insert into test values (2, sysdate - 1);
insert into test values (2, null);
The 4th insert will cause an error and this is what I wanted to implement. OK. Now the problem is that for non-null values of dat, we can't have data like this
iddat
------------
124/10/2013
123/10/2013
123/10/2013
1
because of the unique index (the 2nd and the 3rd row are equal). So just for learning purposes, how could we allow at most one null value of dat and allow duplicates for non-null values of dat.
SQL & PL/SQL :: How To Modify Null Column To Not Null
Jan 9, 2012when i follow this steps mention on this website
[URL].........
to modify column from null to not null i got this error and on this website its show successful
my steps are
first i create a table
SQL> create table Stu_Table(Stu_Id varchar(2), Stu_Name varchar(10),
2 Stu_Class varchar(10));
Table created.
Then insert some rows into Stu_Table
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(1,'Komal');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(2,'Ajay');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(3,'Rakesh');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(4,'Bhanu');
1 row created.
SQL> insert into Stu_Table (Stu_Id, Stu_Name) values(5,'Santosh');
1 row created.
SQL> select * from Stu_Table;
ST STU_NAME STU_CLASS
-- ---------- ----------
1 Komal
2 Ajay
3 Rakesh
4 Bhanu
5 Santosh
Table Structure is like this
SQL> Describe Stu_Table
Name Null? Type
----------------------------------------- -------- ----------------------------
STU_ID VARCHAR2(2)
STU_NAME VARCHAR2(10)
STU_CLASS VARCHAR2(10)
now when i try to modify this Stu_id column to not null its give me error.
SQL>ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null;
ALTER TABLE Stu_Table MODIFY Stu_Id int(3)not null
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
and when i try to add new column with not null its also gives me error
SQL> ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL;
ALTER TABLE Stu_Table add C1_TEMP integer NOT NULL
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
SQL & PL/SQL :: Function In Oracle To Select Not-null Columns At Beginning And Null Columns At End?
Jul 12, 2012I 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
PL/SQL :: How To Update Null Data With Previous Not Null Data
Oct 25, 2012Initially i have inserted the data into table like
Date xxx yyyy
1/1/12 1 1
2/1/12 null null
3/1/12 null null
4/1/12 1 1
5/1/12 1 1
6/1/12 null null
in above example data is null for some date here my requirement is how can i copy before not null data(1/1/12) to *2/1/12, 3/1/12* .
How To Use Decode And Sum
Nov 16, 2009Which warehouses have pending orders for products, which are not in stock at the warehouse at the moment? Provide warehouse number, id of the product that is not in stock, number of orders issued for this product and total quantity ordered.
The tables I am using are
Warehouses:
Name Type
----------------------------------------
W_ID NUMBER(38)
CITY VARCHAR2(20)
W_SIZE NUMBER(38)
Inventories:
Name Type
-----------------------------------------
P_ID NUMBER(38)
W_ID NUMBER(38)
QUANTITY NUMBER(38)
Orders:
Name Type
-----------------------------------------
ORD_ID NUMBER(38)
SUPPLIER_ID NUMBER(38)
ISSUING_EMP_ID NUMBER(38)
ORDER_DATE DATE
ORDER_STATUS CHAR(1)
This is my code so far:
select w.w_id, i.p_id,
sum(decode(o.ord_id, ' ', i.p_id, 0)) Orders_issued,
select sum(i.quantity)
from
inventories i
orders o,
[code]...
but I get this error:
select sum(i.quantity)
*
ERROR at line 3:
ORA-00936: missing expression
SQL & PL/SQL :: What Does DECODE Do
Sep 7, 2011Just working with Unix for the first time and trying to understand this decode statement?
cursor l_cursor is select decode(type||'-'||to_char(line,'fm99999'),
'PACKAGE BODY-1','/'||chr(10), null) ||
decode(line,1,'create or replace ', '') ||
decode(type||'-'||to_char(line,'fm99999'),
'JAVA SOURCE-1','and compile ' || type || ' named "' ||
name || '"' || chr(10) || 'AS' || chr(10), '') ||
text text
from user_source
where upper(name) = upper(p_name)
order by type, line;
SQL & PL/SQL :: Use Of Decode
Feb 28, 2011In my Hard code i saw decode like this
DECODE(tablename.columnname,'k',text,NULL,text, NULL)
can I use decode like this.
Alternative To DECODE In Regards To Just SQL (not PL)
Jul 1, 2007im used to using PL SQL via oracle. but lately ive been doing a lot of mysql and PHP and i really need to use something similar to DECODE in my queries. or am i forced to do the checks via PHP.
View 6 Replies View RelatedSQL & PL/SQL :: Decode With Like Operator
Jul 12, 2013I need to get create_user_id for different sale_location_id.Also create_user_id field will be having different values.This is part of my big query.I need to add this stmt in that.So taken that part and figuring it out.
create table it(sale_location_id number,create_user_id varchar2(10));
table IT created.
insert into it values(1,'ISRA')
1 rows inserted.
insert into it values(2,'USFA')
1 rows inserted.
select a.sale_location_id,decode(a.sale_location_id,1,a.create_user_id like 'IS%',a.create_user_id like 'U%') create_user_id from it a
given error as:
ORA-00907: missing right parenthesis 00907. 00000 - "missing right parenthesis"
How to write this.
SQL & PL/SQL :: Decode In Where Clause
Aug 3, 2013I have a scenario where in report table I need to get
IF FLAG='Y' AND LOC=118443 THEN STATE='FIN'
IF FLAG='Y' AND LOC!=118443 THEN STATE SHOULD BE ('COMP','PART')
IF FLAG='N' IT SHOULD INCLUDE ALL STATES.
I tried using decode in WHERE CLAUSE , but not successful.
Create Table Report(Id Number,Loc Number,Flag Varchar2(3),State Varchar2(20));
table REPORT created.
Insert Into Report Values(1,1,'Y','COMP');
1 rows inserted.
Insert Into Report Values(2,118443,'Y','FIN');
1 rows inserted.
Insert Into Report Values(7,118443,'Y','COMP');
1 rows inserted.
[code]...
SQL & PL/SQL :: Case Vs Decode?
Feb 13, 2011How to use decode and case in "where" and "from" clause of a select statement.
I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition
SQL & PL/SQL :: Decode On One Line
Jul 28, 2010I tried writing a decode statement but it doesn't work as expected. The data I'm working has 4 different brand names with an associated code. I tried using decode to get the 4 brands to be on one line like this: Description, Inventory_Item, Product_Line, Product_Type, Brand_1, Brand_2, Brand_3, Brand_4..I still get 4 lines with the Brands displayed like a stair steps.
What do I have to do to get the data on one line? My code is as follows:
SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...
SQL & PL/SQL :: Decode With Between Clause
Aug 11, 2011I have a small doubt...Without any creation of tables as I feel it's not needed...(let me know if u need them) Error says "Missing Keyword"
select distinct record_number
from meta m, procedu b, control v, fact f, calendar dc, person p
where f.key = m.key and f.group_key = b.group_key
and b.proc_key = v.proc_key
and f.calendar_key = dc.calendar_key
and f.person_key = p.person_key
and VALUE BETWEEN DECODE((Select distinct operator_type
[code]....
But this gives an error...If the decode gives a "single"...the statement is working fine...but If the decode gives a "range"...the above statement gives an error saying "missing keyword"..Does the above code when mapped to "Range"...is it not producing "and" like " value between 100 and 1000"
SQL & PL/SQL :: How To Use Decode Function
Oct 31, 2011How can i use the decode function?
for example
I have the value of 1000 then the numbers 50-100 will be 'A' and 1-49 = 'B'?
SQL & PL/SQL :: Decode Function
Apr 4, 2013DECODE(:P_PERIOD_TYPE,'PJTD','PROJECT-TO-DATE','PTD','PERIOD-TO-DATE','YTD','YEAR-TO-DATE')
what does it mean..
PL/SQL :: Query For Emp With Decode
Dec 26, 2012How to get the required output with single query to acheive IF SAL>2500 and job='manager' then display job as 'seniormanager'.
CREATE TABLE EMP(EMPNO NUMBER(4) NOT NULL,ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7, 2),COMM NUMBER(7, 2),DEPTNO NUMBER(2));
INSERT INTO EMP VALUES(7521, 'WARD', 'SALESMAN', 7698,TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250, 500, 30);
INSERT INTO EMP VALUES(7566, 'JONES', 'MANAGER', 7839,TO_DATE('2-APR-1981', 'DD-MON-YYYY'), 2975, NULL, 20);
INSERT INTO EMP VALUES(7698, 'BLAKE', 'MANAGER', 7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'), 2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK', 'MANAGER', 7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'), 2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT', 'ANALYST', 7566,TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
[code]....
SQL & PL/SQL :: Use Decode Function
Aug 19, 2010select
DECODE(PAA.C_TYPE_DESC,'TXIS CASUAL LEAVE',NVL(PAA.ABSENCE_DAYS,0)) TCL,
DECODE(PAA.C_TYPE_DESC,'TXIS SICK LEAVE',NVL(PAA.ABSENCE_DAYS,0)) TSL,
DECODE(PAA.C_TYPE_DESC,'TXIS PRIVELEGE LEAVE',NVL(PAA.ABSENCE_DAYS,0)) TPL
from PER_ABSENCE_ATTENDANCES_V PAA
i want to use nvl function within decode function
means if C_TYPE_DESC is 'TXIS CAUSAL LEAVE' it return ABSENCE_DAYS but if ABSENCE_DAYS are null it will be return 0 else it should be return Absence_days
Using 2 Tables With Decode Function
Nov 8, 2009What I have to do is this:
List supplier�s name, id, number of pending orders, number of completed orders and number of all orders from this supplier. (my query doesn't include the last part yet).
My tables are as follows:
Suppliers:
S_ID
NAME
ADDRESS
PROVINCE
PHONE_NUMBER
Orders:
ORD_ID
SUPPLIER_ID
ISSUING_EMP_ID
ORDER_DATE
ORDER_STATUS
and this is what I have so far:
select name, s_id
sum(decode(order_status, 'P',1,0)) pending,
sum(decode(order_status, 'C',1,0)) completed
from suppliers
where s_id in
(select supplier_id from orders);
but I still get an error that says "sum(decode(order_status, 'P',1,0)) pending,
*
ERROR at line 2:
ORA-00923: FROM keyword not found where expected"
I assume this is because I'm not properly referencing the orders table.
Decode Based On Different Column
Jul 24, 2007Is it possible to decode based off a different column? I have a status column that i want to change the value of with a decode, but only if my date column has been populated.
So if status has a value of "New" and my date column is null, then i want it to stay new. If it is populated, i want my status to change to "released"
SQL & PL/SQL :: DECODE And CASE In WHERE Clause
May 7, 2010I am facing a problem while retrieving data from table using DECODE/CASE.
Table: PARAM_MSTR
MIN_VALMAX_VALPARAM_CODE DESCRIPTION DATATYPE
AB1000 HARD PARAMETERTEXT
CN1000 SOFT PARAMETERTEXT
0501001 CRYSTAL PARAMETERNUMBER
512001001 STONE PARAMETERNUMBER
Now I want to get the parameter description based upon the PARAM_CODE and a value passed which should be in range of MIN_VAL and MAX_VAL. Means when I pass PARAM_CODE=1000 and :parameter=A, then it should check the DATATYPE of the PARAM_CODE, in our case it is 'TEXT' so it should check the passed value between MIN_VAL and MAX_VAL like
:parameter BETWEEN MIN_VAL AND MAX_VAL and should return 'HARD PARAMETER'.
If I pass PARAM_CODE=1001, then the DATA_TYPE is 'NUMBER', so it will check the :parameter value as Number.
Like :parameter BETWEEN to_number(MIN_VAL) AND to_number(MAX_VAL)
For example:
PARAM_CODE :parametr Result
1000 A HARD PARAMETER
1000 C SOFT PARAMETER
1000 P NULL
1001 25 CRYSTAL PARAMETER
1001 99 STONE PARAMETER
1001 201 NULL
I have written a query using DECODE and CASE statement but it is not working properly.
SELECT * FROM param_mstr WHERE PARAM_CODE=1000 AND :parameter BETWEEN DECODE(DATATYPE,'NUMBER',CAST(MIN_VAL as NUMBER),MIN_VAL)AND DECODE(DATATYPE,'NUMBER',CAST(MAX_VAL as NUMBER),MAX_VAL)
SQL & PL/SQL :: Decode Using Multiple Values
Jan 23, 2013i am using the following within queries:
decode(qt_flag,'KT','Y',decode(qt_flag,'IT','Y','N'))
It works fine but when i use in ETL tool it is not working. Is there a way to use decode just once with multiple values like above.
SQL & PL/SQL :: DECODE And Date Format?
Jul 25, 2011While am working with Decode just found something, why it is returning like this.
I Ran this query where i have used all the date format as DD/MM/YYYY
SELECT DECODE(TO_DATE('25/04/2001','DD/MM/YYYY') ,
TO_DATE('01/01/1900','DD/MM/YYYY'), NULL ,
TO_DATE('25/04/2001','DD/MM/YYYY')) TEST
FROM DUAL
Quote:
TEST
-------------
25-APR-01
But the resule i got in DD-MON-YY
i tried searching this forum with searching strings Decode date format.. or similer i found nothing.
SQL & PL/SQL :: Decode Statement In Query
Aug 12, 2010I have used a decode statement in my query instead of using select * from <table_name> where value in <>
Instead I have used decode(value,<value1>, <value2>)
Which would be a better performance code?
SQL & PL/SQL :: How To Use Outer Join With Decode
Sep 19, 2012I need to apply an outer join on following statement.
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)=B.Fin_Wid;
I used following but error
Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)(+)=B.Fin_Wid;