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.
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 ?
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
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
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;
im 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.
I 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"
I 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:
I 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"
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
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
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.
Is 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"
I 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)
While 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.
First I would like to thak this Burleson Consulting for providing valueable documents and information on Oracle internals .I am working on Oracle Packet Parsing. I am stuck when I am tring to understand the Packets of TNS Type ID =6 TTI ID = 0x02(Exchange of Data type representations)
I would like to use the below decode in DBMS_OUTPUT in place of cur_rec.data_type. Could I know how can I achieve this. The DBMS_OUTPUT is inside a loop and output is shown below as an example.
NOT NULL -- EMP_ID NUMBER ,NOT NULL -- EMP_NAME NUMBER ,NOT NULL -- HIRE_DT DATE ,NOT NULL -- SALARY NUMBER