SQL & PL/SQL :: Decode Statement In Query

Aug 12, 2010

I 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?

View 5 Replies


ADVERTISEMENT

PL/SQL :: Case When Statement In Decode

Dec 14, 2012

Here is one an example with CASE WHEN statement.

SELECT empno,ename,sal,
CASE
WHEN sal BETWEEN 800 AND 1200
THEN 'Lowest Pay'

[Code]...

even DECODE can be a replacement for this scenario, but it cannot have as flexibility as CASE can have.

Now, I want Equivalent code for DECODE..

View 11 Replies View Related

PL/SQL :: Decode Statement Within Inner Join?

Apr 22, 2013

I'm having some trouble writing this query using a decode statement within a join. First, lets say I have two tables. Table A has the fields 'Baseline', 'NAD', 'Null', and 'Harn'. Table B has the fields 'Null', 'NAD', and 'Harn'. I want to join these tables together by these fields to return their ID's. Because table B doesn't have 'Baseline' to even have a match between the two tables, in Table A, we want to treat 'Baseline' as 'Harn', hence a decode statement. My query is below

Select
...
...
...
From
...
inner join oracle_srid_xref B on A.horizontal_datum = B.horizontal_datum and
decode(A.horizontal_epoch, 'BASELINE', 'HARN', loc.horizontal_epoch ) = B.horizontal_epoch

I'm not getting the ID's where the horizontal_epoch was initially 'Baseline'

View 5 Replies View Related

SQL & PL/SQL :: Can Call A Function Within Decode Statement

Oct 19, 2011

Can we call a function within decode statement. I am able to do the same for simple example function . But In my actual procedure it's giving the error message . Are there any restrictions to call function with in decode statement?

View 4 Replies View Related

SQL & PL/SQL :: Getting Equivalent DECODE For CASE Statement?

Feb 21, 2013

case when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age

the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode

View 10 Replies View Related

SQL & PL/SQL :: How To Use DECODE For A Column In Update Statement

Apr 9, 2010

I have to conditionally update a set of columns in a table. If the column status_code_stage_3 IS NULL THEN I have to update the column status_code_stage_2. The below query is giving error:-

Test Table create scripts
CREATE TABLE test11( date_stage_3 date, reason_code_stage_3 varchar2(20),
reason_code_stage_2 varchar2(20), opportunity_date date )
INSERT INTO test11 values(sysdate,'reason1',NULL,sysdate)
INSERT INTO test11 values(sysdate,NULL,'reason2',sysdate)

[code]....

how to work out the update statement to use the conditional statement for columns.

View 2 Replies View Related

SQL & PL/SQL :: Using Select Statement In Decode Function?

May 11, 2010

I am trying to use decode function in sql and inside decode function can I use select statement ?

here is my sql

select we.wf_entity_id, decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
) from wf_entity we
where

[code]....

see this

decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
)

will this work? Its not working for me?

View 2 Replies View Related

SQL & PL/SQL :: Write Nested Decode Statement With Multiple Searches

Nov 27, 2010

I was trying to write nested decode statement with multiple searches. For example

Case when seq = 1 and date1 > date2 then Record_Name End

How can i modify this to decode.

View 4 Replies View Related

PL/SQL :: Query For Emp With Decode

Dec 26, 2012

How 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]....

View 2 Replies View Related

SQL & PL/SQL :: Using NULL And DECODE In Dynamic Query

Sep 2, 2011

small 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.

View 10 Replies View Related

PL/SQL :: Using Function On Decode Or Case In Query

Mar 3, 2013

I am using oracle 11G database,I have to check length of name column value from employee table and if length(name) > 39 then value should be substr(name,0,39) else value should be name only. i tried below code

select CASE when length(name) > 39,substr(name,0,39)
else name
END

from employee but its not working ..can I do this using decode too ? ,,which one would be better or this is not a right way ?

View 3 Replies View Related

Application Express :: Decode - Define Query Condition For The Value

Jan 24, 2013

how can i define the following condition in query definitions tab to define query condition for the value which is linked from previous tab/report ,:P2_SR_ BUSINESS_UNIT is the Item which are getting value from another report Value.

BUSINESS_UNIT=DECODE(:P2_SR_BUSINESS_UNIT,'BS&I','Business Sales And Information','WS','Whole Sale')

I am new in apex and havent had any chance to create a simple report.Version Application Express 4.1.1.00.23

View 6 Replies View Related

SQL & PL/SQL :: Include / Exclude In One Query Statement

Aug 31, 2011

how to write this query using all three tables in one query statement.

Result:

Input value of "GM" would return "GM@EMAIL.COM"

Input value of "GM" and "GMC" would return "GMC@EMAIL.COM"

If "GM" is supplied, then only return the "GM@EMAIL.COM" email.

If "GM" and "GMC" is supplied, then only return the "GMC@EMAIL.COM" email

Test Case : GM would return "GM@EMAIL.COM" email

Test Case : GM and GMC would return "GMC@EMAIL.COM" email

select email
from email a,
company b,
sub_company c
where (company = "GM" but no match found in sub_company)
(company = "GM" and sub_company_name = "GMC" match is found in sub_company)

[code]....

View 11 Replies View Related

SQL & PL/SQL :: Distinct With Analytical Query And IN Statement?

Nov 18, 2011

We've got a query which returns one row, but uses an IN statement. The IN statement links to more than one row in the subquery. When we use a combination of DISTINCT and an ANALYTICAL sum, the sum total is multiplied by the number of rows in the sub query. Remove the DISTINCT and we get a single value.

A simplified example of the problem is below.

I can't see how a query which returns a single row then returns multiple values with the addition of a DISTINCT. Removing the analytical sum also provides a single row, but we need this in the actual query we're running. So it seems to be some combination of DISTINCT, ANALYTICAL SUM and IN query is causing multiple values to be returned.

CREATE TABLE go_test_distinct1
(gtd_value NUMBER);
-- Three identical values
-- To replicate the three identical values returned by

[code].....

View 12 Replies View Related

SQL & PL/SQL :: Select Statement Query Calculation

Sep 28, 2011

I'm unable to create a select statement which calculates & brings the below fields :

- Act_Net_Last_Month (LastMonth)
- Act_Net_Same_Month_Last_Year (LM_LastYear)

My Table :

Acc_ID - Period_Name - Period_Year - Act_Net
000044 - Aug-2011 - 2011 - 4493
000044 - Aug-2010 - 2010 - 4300
000044 - Jul-2011 - 2011 - 4389
000044 - Jul-2010 - 2010 - 4266

Example :

Acc_ID - Period_Name - Period_Year - Act_Net - LastMonth - LM_LastYaer
000044 - Aug-2011 - 2011 - 4493 - 4389 - 4266

View 7 Replies View Related

Multiple Select Statement In 1 Query?

Nov 19, 2012

I have following queries:-

#select name from v$database;
#select log_mode from v$database;
#select count(*)"INVALID_OBJECTS" from dba_objects where status='INVALID';
#select count(*) "INVALID_N/A_INDEXES" from dba_indexes where status!='VALID';
#select count(*)"Invalid Triggers" from user_objects where OBJECT_NAME like '%TRIGGERS%' and status='VALID';
#select count(*) "Broken Jobs" from dba_jobs where broken!='Y';
#select count(*) "Block Corruption" from v$database_block_corruption;

i want a table which can be generated just by select cmd and it will list the result of all the above queires as follow:-

DB_NAME ARCH_MOD INV_OBJ INV_IDX INV_TRG B_JOB BLK_CRP
---------- -------------------------------------- -------------------------------------- ---------- ---------- ---------- ----------
PROD NOARCHIVELOG 0 86 6 3 0

I mean to say i want multiple select queries into 1 table (note:- i m not saying to create a tables and then insert,update(using select from other tables), its just a sheel script that will fetch these record into a txt file)

View 7 Replies View Related

HAVING Statement From Query Removes All Rows?

Jun 21, 2012

The "HAVING" statement from the query below removes all rows where the "name" field starts with "Port" and it removes all rows where count is less than 11. What I want is to removes all rows where the "name" field starts with "Port" and the count for that row is less than 11.

SELECT deviceEventClassId "Event ID", deviceAddress "Device Address", name "Event Name", deviceHostName "Device Host Name", count( concat(deviceEventClassId, deviceHostName, name)) "Count"
FROM events
WHERE (deviceVendor = "Bilbo" and

[code]...

View 1 Replies View Related

PL/SQL :: Update Statement In Join Query

Nov 16, 2012

I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:

UPDATE tbl1
SET tbl1.EMPID =
(SELECT  tbl2.EMPIDA  FROM tbl2 
WHERE LOWER(tbl1.EMAILCOL) =  LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE  tbl1.EMAILCOL= tbl2.EMAILCOL2 );

But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.

View 12 Replies View Related

Update Statement - Query Hanging Or Processing

Oct 6, 2010

I am running one update statement which is running almost one hour still no response.

I would like to know either query is processing or hanging(suppose to finish the update within few minutes).

Is there any way or sql to find either the statement(my update query) is running or hanging.

View 4 Replies View Related

SQL & PL/SQL :: How To Use (select Statement) Rather Than (in Clause) In Pivot Query

May 27, 2011

I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.

SQL> CREATE TABLE TEST1
2 (
3 UIN NUMBER(8) NOT NULL,
4 TESTING_ID NUMBER(4),
5 PFA_RESULT VARCHAR2(30 BYTE)
6 );

[code]....

I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.

select * from
(select uin,testing_id,pfa_result from test1)
pivot xml (max(pfa_result) as result
for (testing_id) in (select distinct testing_id from test1));

[code]....

Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.

View 3 Replies View Related

SQL & PL/SQL :: Select Statement - Inner Query Returns Data

Aug 23, 2012

I am new to oracle. I am creating a view, where in the inner query returns below kind data.

column1 column2
-------------------
a y
a y
b y
b n
c y
d n

I want to extract all column1 values which always has "y" in column2.

In this case, output has to be

output
------
a
c

View 10 Replies View Related

PL/SQL :: Query NVARCHAR2 Column In Select Statement

Sep 3, 2013

I have table desc xx_testName    

Null     Type          

-------- -------- --------------

COL1     NOT NULL NVARCHAR2(100)  COL2     NOT NULL NVARCHAR2(100) COL3     NOT NULL NVARCHAR2(100)

i am able to query select * from xx_test however if i query as select col1 from xx_test then it is giving error.

ORA-00904: "COL1": invalid identifier00904. 00000 -  "%s: invalid identifier"*

Cause:    *Action:Error at Line: 3,131 Column: 13  

Let me know how to query NVARCHAR2 column and how can we put in WHERE condition ?

View 5 Replies View Related

SQL Server Query - Oracle Does Not Support OUTER APPLY Statement

Feb 1, 2012

DECLARE @MainTable TABLE (UniqueID INTEGER, Category VARCHAR(200), WeekDate DATETIME, VALUE INTEGER)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/07/2011', 5000)
INSERT INTO @MainTable VALUES(123, 'Shirts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(124, 'Pants', '10/07/2011', 4000)
INSERT INTO @MainTable VALUES(125, 'Shorts', '10/14/2011', 8000)
INSERT INTO @MainTable VALUES(126, 'Shoes', '10/21/2011', 9000);
--select * from @MainTable;
[code]...

The query works with all the CTEs up to the last select statement. Oracle does not support the OUTER APPLY statement, how should the last piece be written to make it work in Oracle?

View 1 Replies View Related

SQL & PL/SQL :: Convert Update Query To Dynamic To Be Executed From Execute Immediate Statement

Oct 25, 2010

I want to convert the below SQL to a dynamic sql to be executed from execute immediate statement.

UPDATE transaction SET loannum = lpad(loannum,12,'0')

View 15 Replies View Related

Client Tools :: How To Get SQL Query Output Into Text File Without Spool Statement

Mar 7, 2011

I would like to store my sql query output into text file.Like for example:

select name from emp where emp_id=101;
Here output should be in text file as
swapna.

I dont want to use spool statement here,since If I use it,spool statement will also be printed in text file which is not my requirement.I just want to take only output.

View 1 Replies View Related

PL/SQL :: DML Query Execution - Length Exceeds 4000 Characters With Execute Immediate Statement

Jun 18, 2013

I want to execute a DML query with execute immediate statement. That DML query length exceeds 4000 characters. This query has Xquery related conditions, i can not split the query. when i tried execute it is giving "string literal too long".  I tried with DBMS_SQL.Parse() and DBMS_SQL.Execute also, but it is giving same error. I have to execute this DML query inside a Procedure. We are using oracle 10g version

View 13 Replies View Related

Reports & Discoverer :: Case Statement Inside Oracle Query - Missing Right Parenthesis

Jun 13, 2013

Can I use CASE statement Inside a Oracle Report Query. I'm using Oracle Reports Builder 10g.

My Report Query looks like this,

select invh_code, invh_number, invh_date, invh_cm_code, im_description
from invoice_head, invoice_det, unit_of_measurement, item_master
where invd_invh_code = invh_code and im_code = invd_item_code
AND
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4)) BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)
else 1
end)
order by invh_number

:p_flag is a parameter that i pass from oracle form and based on that value (:p_flag=1) i need to include this specific condition
else omit that condition.

But the CASE statement is throwing Error

ORA-00907 :Missing Right Paranthesis
(case :p_flag when 1 then (substr(invd_item_number,0,(length(invd_item_number)-4))
==> BETWEEN :P_V_ITM_FRM AND :P_V_ITM_TO)

View 6 Replies View Related

How To Use Decode And Sum

Nov 16, 2009

Which 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

View 2 Replies View Related

SQL & PL/SQL :: What Does DECODE Do

Sep 7, 2011

Just 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;

View 8 Replies View Related

SQL & PL/SQL :: Use Of Decode

Feb 28, 2011

In my Hard code i saw decode like this

DECODE(tablename.columnname,'k',text,NULL,text, NULL)

can I use decode like this.

View 6 Replies View Related







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