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"
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)
The following query gets input parameter from the Front End application, which User queries to get Reports.There are many drop down boxes like LOB, FAMILY, BRAND etc., The user may or may not select values from drop down boxes.
If the user select any one or more values ( against each drop down box) it has to fetch all matching values from DB. If the user does'nt select any values it has to fetch all the records, in this case application will send a value 'DEFAULT' (which is not a value in DB ) so that the DB will fetch all the records.
For getting this I wrote a query like below using DECODE, which colleague suggested that will hamper performance.From the below query all the variables V_ are defined in procedure which gets the values selected by user as a comma separated string here V_SELLOB and LOB_DESC is column in DB.
DECODE (V_SELLOB, 'DEFAULT', V_SELLOB, LOB_DESC) IN OPEN v_refcursor FOR SELECT /*+ FULL(a) PARALLEL(a, 5) */ * FROM items a WHERE a.sku_status = 'A'
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:
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"
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
