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


ADVERTISEMENT

SQL & PL/SQL :: Decode Functionality With Null

Jun 29, 2010

Explain 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'

View 10 Replies View Related

SQL & PL/SQL :: How To Use NOT NULL Inside DECODE

Jun 28, 2007

DECODE(FIELD_1,NOT NULL,'working',NULL)

Is it possible to do such? else how to proceed so?

View 9 Replies View Related

SQL & PL/SQL :: How To Validate Dynamic Hours Value Using Decode

Nov 1, 2013

I want to validate dynamic hours value using decode in sql...

If value is greater than 24PM it should display some statement...

if value is less than 0AM it should display some statment...

View 10 Replies View Related

SQL & PL/SQL :: Hierarchical Query - Connect NON-NULL Rows To Preceding NULL Row

Aug 29, 2012

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

View 15 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 :: 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 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 :: Create View From Dynamic Query (or Function Returning Query)

Dec 5, 2012

I have a dynamic query stored in a function that returns a customized SQL statement depending on the environment it is running in. I would like to create a Materialized View that uses this dynamic query.

View 1 Replies View Related

Query Testing For Null Value In IN

Nov 1, 2013

Oracle 10.2.0.4 I belive the following query is incorrect in my opinion. (there is an index on col but NULLS are ignored? 

SELECT COUNT(*) from <TABLE>

where col in (null,'a','b','c') this works (no errors) and returns pretty quick. However I think the correct query would be SELECT COUNT(*) from <TABLE> where col IS NULL OR col in ('a','b','c') This one takes a long time. As I see it it does a table scan for NULL part and uses the index for the rest as the index cannot be used for NULL values. explanation on this, especially why Oracle accepts the first query "where col in (null,'a','b','c')" without any issue. 

View 3 Replies View Related

Tuning SQL Query / Mmt.attribute3 Is Null?

Nov 11, 2010

I have approximately 3 lakh records in mtl_material_transactions table. The value of attribute3 is populated with sysdate based on some business condition.

Out of these 3 lakh records, attribute3 has 5 records which are null. The problem is with the condition 'mmt.attribute3 is null'. The query is pasted below -

select mmt.*
from mtl_material_transactions mmt,
mtl_transaction_types mtt,
mtl_parameters p
where mmt.transaction_type_id = mtt.transaction_type_id
and mp.organization_id = mmt.organization_id
and (upper(mtt.attribute1) ='OUTBOUND' or (upper(mtt.attribute1)='FORM' and upper(mtt.attribute2) in 'ISSUE','RECEIPT'))
and 1=decode(mmt.transaction_action_id,2,decode(mmt.subinventory_code,'WIP',0,1),1)
and mmt.attribute3 is NULL

View 3 Replies View Related

Query Ignore Null Values

Aug 5, 2010

My query is ignoring the null value

CODEselect count(*) as COUNT from EMP where EMP_ID in '1001,1002,1003'

returns the result as

CODECOUNT
----------------------
0
1
2

EMP_ID '1001' has 0 records(i.e., EMP_ID 1001 doesnt exists in the table)
EMP_ID '1001' has 2 records
EMP_ID '1001' has 3 records

where as the query

CODEselect count(*) as COUNT, EMP_ID from EMP where EMP_ID in '1001,1002,1003' group by EMP_ID

returns the result as

CODECOUNT | EMP_ID
---------------------
1     | 1002
2     | 1003

I want the null value also to be counted when I run the second query i.e., the output should be like

CODECOUNT | EMP_ID
---------------------
0     | 1001
1     | 1002
2     | 1003

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Column Value Query?

Nov 3, 2010

I have a Strange requirement from client data is loaded from excel to Oracle Table- TST_TBL (with Header in Excel)

CREATE TABLE TST_TBL
(
JOB_DETAIL_ID NUMBER,
SHEET_NAME VARCHAR2(100 BYTE),
COL1 VARCHAR2(400 BYTE),
COL2 VARCHAR2(400 BYTE),
COL3 VARCHAR2(400 BYTE),
COL4 VARCHAR2(400 BYTE),

[Code]...

After the Data is loaded, we see data look like the above.

(1) Always COL3 column name have data value as 'Gen1' which is the indication for us from where data starts. But Gen1, Gen2, Gen3 etc... is dynamic. ie. This month we get gen1 and gen2 columns followed by null value in a column. Next month we get gen1,2,3,4 followed by null column.
(2) Null Column indicate us that there is a break in the column.
(3) Then next we need to look for next group of data (Monthly) and then insert into the same table again with different sheet_name column.
(4) Next for Quater and then YTD. None of the column Values are fixed and its all dynamic.

If you load the below data, you will come to know what i am looking for. I tried using UNPIVOT. But couldnt able to achieve it. Is there an option to do it in sigle query? or Do I need to go for Stored Procedure?

Insert into TST_TBL
(JOB_DETAIL_ID, SHEET_NAME, COL1, COL2, COL3,
COL4)
Values
(100, 'Wire_1', 'Gen1',
'Gen2', 'Gen3', 'Gen4');
Insert into TST_TBL

[Code]....

View 1 Replies View Related

SQL & PL/SQL :: Query To Return Not Null Column Values With Priority

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

SQL & PL/SQL :: Query To Display Repeated Column Values As Null?

Dec 13, 2012

I have table with the values as below.

C1C2C3C4
NAMEJOHN10ABC
NAMESMITH30DEF
NAMEROBERT60XYZ

I dont want to print the repeated value(NAME) of C1 multiple times as below.

C1C2C3C4
NAMEJOHN10ABC
SMITH30DEF
ROBERT60XYZ

I could do it using the below query using union with the rownum.

select * from (
select rownum rn, c1,c2,c3,c4 from table_new
) where rn =1
union
select * from (
select rownum rn, decode(c1,null,null),c2,c3,c4 from table_new
) where rn between 2 and 3

Is there any other way of displaying using a single sql query.

View 17 Replies View Related

SQL & PL/SQL :: Query To Retrieve Number Of Not Null Rows For Each Column?

Jul 7, 2011

I need a generic query to generate total # of records for each table in a schema, total # of records that are not null for each column in the table, and total # of records that are null for each of those columns in those tables.

ex:

the output should look like this.

owner schema table_name total# recs in the table, column_name,
------ ------ ---------- ------------------------- -----------

# of records not null # of records null
---------------------- --------------------

View 12 Replies View Related

SQL & PL/SQL :: Define A Cursor Using Dynamic Query?

Aug 29, 2010

Is it possible to define a cursor using dynamic sql. Reason being is, I want to fetch data from 4 diffrent tables but dont want 4 diffrent cursors for this purpose.

I have a procedure that takes an in parameter . Cursor is declared in this procedure. Again is there a way to use dynamic sql so that this cursor declared in procedure uses all 4 table one at a time i.e cursor c1 is select * from table_name(I want this table name to be updated every time with new table name).

View 5 Replies View Related

SQL & PL/SQL :: Using Escape Character In Dynamic Query?

Aug 28, 2012

I'm trying to replace the variable value v_tblname in dynamic SQL. But not able to escape the single quotes character.

DECLARE
v_sqlcols VARCHAR2(2000);
v_sqlcols1 VARCHAR2(2000);
v_tblname VARCHAR2(50) := 'DEPT';
BEGIN
v_sqlcols := q'[SELECT LISTAGG(COL,',' ||CHR(10)) WITHIN GROUP (ORDER BY COL)
FROM (

[code]....

View 5 Replies View Related

SQL & PL/SQL :: How To Get Count On Executing Dynamic Query

Sep 16, 2010

l_query := 'SELECT sedol ' ||
'FROM integration.tmp_attributed_sedol ' ||
'WHERE ' || p_field || '=''' || p_ref_number || ''' ';

by using the execute immediate or any other command, how can i check whether the query returned any rows or not?

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Query For Swapping Names

Mar 25, 2011

writing dynamic sql query for swapping names:

the output shuld be like that

input output

a a
a b b a
a b c c b a
a b c d d c b a

i have writen it in static form by using instr,substr.But i m having difficulty in making it dynamic by using select statement.I have to make it for retrieving data from database.

View 13 Replies View Related

PL/SQL :: Pipelined Function With Dynamic Query?

Nov 12, 2013

 Orcl Ver: 11g R2. I am leveraging the Oracle's pipelined table function.It's working fine for static SQL.  

create or replace package test13_pkg as type r_disc_req is record(disc_line_id number,              

req_id number);    type t_disc_req is table of r_disc_req;    function F_GetDiscReq return t_disc_req pipelined;     procedure P_ProcessDiscReq;end;  CREATE OR REPLACE PACKAGE BODY test13_pkgAS   FUNCTION F_GetDiscReq      RETURN t_disc_req      PIPELINED   IS      lo_disc_req   r_disc_req;   BEGIN      FOR r_row IN (SELECT disc_line_id, req_id                      FROM edms_disc_lines_stg                     WHERE ROWNUM < 10)      LOOP         lo_disc_req.disc_line_id := r_row.disc_line_id;         lo_disc_req.req_id := r_row.req_id;         PIPE ROW (lo_disc_req);  
   
[code]...

View 11 Replies View Related

SQL & PL/SQL :: How To Overcome 32k Limit In Dynamic Query

Dec 22, 2010

I have a dynamic query that is ending up getting larger than 32k and this query is the base of a ref cursor that would in turn return the results to the application. How can I solve this problem since the largest content of a pl sql variable or literal is 32k ( said by oracle ) ?

PROCEDURE sp_large_query (c1 OUT sys_refcursor)
IS
BEGIN
OPEN c1 FOR ' SELECT STATEMENT WITH MORE THAN 32K ';
END;

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Sql Query String - Getting Error?

Aug 22, 2010

FUNCTION get_attributed_sedol( p_ref_number IN VARCHAR2,
p_field IN VARCHAR2 )
RETURN VARCHAR2
IS
l_query VARCHAR2(1000);

[code]...

It gives me an error on the line where the execute immediate statement is.
Quote:
ORA-00905
missing keyword

Cause: A required keyword is missing.

Action: Correct the syntax.

But when I check my stack trace table to see what the actual query string looks like, I see this

Quote:
SELECT sedol INTO l_attributed_sedol_code FROM integration.tmp_attributed_sedol WHERE CLIENT_LEDGER_REF='LEAE057090' AND ROWNUM=1

There's nothing wrong with that, is there? It executes fine if I try it manually.

View 3 Replies View Related

How Many Quotes To Use In Dynamic Query Using IN Clause With String

Nov 23, 2011

I have a dynamic query which has this clause in it: WHERE [COLUMN NAME] IN (' || theString || ')

My problem is that theString is being passed in through a C# call and the variable is a bunch of strings concatenated together and separated by a comma. Ex: theString = "'val1','val2'"

How many quotes are supposed to go around val1 and val2?

I've tried the following and none work:
'val1','val2'
''val1','val2''
''val1'',''val2''
'''val1'',''val2'''
''''val1'',''val2''''

When I run the procedure in Oracle it works with '''val1'',''val2'''

View 1 Replies View Related

SQL & PL/SQL :: How To Use Column Names Generated From Dynamic Query

Feb 8, 2013

I have written an SQL which will dynamically generate the Select statement with from and where clause in it. But that select statement when executed will get me hundreds of rows and i want to insert each row separately into one more table.

For that i have used a ref cursor to open and insert the table.

In the select list the column names will also be as follows: COLUMN1, COLUMN2, COLUMN3,....COLUMNn

find below the sample

TYPE ref_csr IS REF CURSOR;
insert_csr ref_csr;
v_select VARCHAR2 (4000) := NULL;

[Code].....

View 5 Replies View Related

SQL & PL/SQL :: Bind Variable For Dynamic Query In Procedure

May 14, 2013

I have to use bind variable for dynamic sql in a procedure. Is there a way to have control on these values. Say for example:

Procedur MyProc
(
In_EmpID Number default null,
In_EmpName Varchar2 default null,
in_JoinDate Date default null
[code]....

I have more than 5 In parameters, all 5 is not compulsory by default they are null and sql formation is also dynamic with in the procedure.I need to map bind variable to a proper one.. Is there a way to handle bind variable.

View 2 Replies View Related

SQL & PL/SQL :: Dynamic Query In View For Correct Column Name

Feb 20, 2012

I don't know what column name would there for my new view. so can i use dynamic SQL in View so that I can get correct column name?

View 2 Replies View Related

SQL & PL/SQL :: Oracle Dynamic Query Returning To Out Sys_refcursor

Nov 8, 2011

I'm trying to build a dynamic sql inside an Oracle procedure, and return it as a SYS_REFCURSOR to ADO.NET using ODP.NET in Oracle 11R2. Below is my table:

CREATE TABLE "TFWDB_ENTIDADE"
(
"CD_ENTIDADE" NUMBER(10,0) NOT NULL,
"DC_ENTIDADE" VARCHAR2(100 BYTE) NOT NULL,
"NM_ENTIDADE" VARCHAR2(255 BYTE) NOT NULL,
"CD_TIPO" NUMBER(5,0) NOT NULL,
"ID_STATUS" CHAR(1 BYTE) DEFAULT ('1') NOT NULL,
CONSTRAINT "PKFWDB_ENTIDADE" PRIMARY KEY ("CD_ENTIDADE"),
CONSTRAINT "CHFWDB_ENTIDADE" CHECK (ID_STATUS IN ('0', '1'))
);

Below is the procedure I build to SELECT it:

create or replace procedure spfwdb_sl_entidade_1(
p_cd_entidade in number,
p_dc_entidade in varchar2,
p_nm_entidade in varchar2,
p_cd_tipo in number,
p_id_status in char,
[code]........

I'm calling this code from C#, and I get an empty Result Set. What am I doing wrong? Can I output the dynamic sys_refcursor that I created? Below is my C# code using ODP.NET:

string pConnectionString = "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ORASERVER)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORASID)));User Id=user;Password=password;";
OracleConnection oConn = new OracleConnection(pConnectionString);
oConn.Open();
string pNomeProc = "spfwdb_sl_entidade_1";
[code]........

View 7 Replies View Related

SQL & PL/SQL :: Execute Dynamic Query Stored In Table?

May 27, 2011

I want to execute a dynamic query which is stored in a Table.

Output of that query should be stored in database server.

Is there any way i can create a dynamic procedure? I have created a sample code but issue is i cannot make the below data type dynamic as per the query.

en com_fund_info_m%ROWTYPE;
CREATE TABLE TEMP (SQLSTATEMENT VARCHAR2(100))
DECLARE
TYPE r_cursor IS REF CURSOR;
c_emp r_cursor;
en com_fund_info_m%ROWTYPE;

[code].....

View 3 Replies View Related







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