my query is some thing like this but having more column in select. when i am firing this query it is giving result but that is not proper,\.
my problem is , like if there are 3 more values for uh.sflowtype (0,1,2) then group by is not working for them and those are coming in different row , i need them to be combined
query is :
select substr(uh.sstartdatetime,1,8) DateTime,
( case
when uh.sflowtype=7 then 'sms'
when uh.sflowtype=9 then 'mms'
when uh.sflowtype=10 then 'gprs'
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)
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 ?
CASE expects data type consistency, DECODE not expecting.
Obviously both functions handling data types are different let it be
SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual;
RESULT --- 2 SQL> select case 2 2 when 1 then 1 3 when 2 then 2 4 else 3 5 end "RESULT" from dual;
RESULT 2 SQL> select case 2 2 when 1 then 1 3 when '2' then 2 4 else 3 5 end "RESULT" from dual;
ERROR at line 3: ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
know cause of error here????I mean " every time case exp checking data type consistency"
my thought is
I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug!
I want to know how oracle handles here ???? i mean 3rd query.Purely i am testing this function with dual(dummy) table... obviously, no possibilities for different data type.next one i am not sure about Oracle compares int variables to int variables, char variables to char variables,
I think so .... that's why oracle throws error. am i right ??ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
see this query
SQL> select case 2 2 when 1 then 1 3 when '2' then 2 4 else 3 5 end "RESULT" from dual;
// clearly i am stating what's error on 3rd line ?????Even if I refer reference books some concepts are blind to understand.
Data Type Consistency CASE and Decode...CASE expects data type consistency, DECODE not expecting.Obviously both functions handling data types are different..let it be
SQL> select decode(2,1,1, 2,'2', ' three' )"RESULT" from dual; [color=red]RESULT --- 2 SQL> select case 2 2 when 1 then 1 3 when 2 then 2 4 else 3 5 end "RESULT" from dual; [code]...
I am trying to get same output but different methods. Yes, clearly states this is one of the Oracle bug! Oracle compares int variables to int variables, char variables to char variables, If any discrepancy between the two data types, then the query will fail but here not different data types.
Even if I refer reference books some concepts are blind to understand.
I am trying to use decode funtion with GROUP BY ROLLUP.
MY query is working fine when i use this two queris individually
SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ((DEPTNO),(DEPTNO,JOB)); SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP((JOB),(DEPTNO,JOB)); But when i use Decode funtion so that i can combine above two scenarios it is not working SELECT SUM(SAL),DEPTNO,JOB FROM EMP GROUP BY ROLLUP ( DECODE(:A,'S',((DEPTNO),(DEPTNO,JOB) ),((JOB),(DEPTNO,JOB) ) ) )
BANNER ---------------------------------------------------------------- Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi PL/SQL Release 10.2.0.1.0 - Production CORE 10.2.0.1.0 Production TNS for Linux: Version 10.2.0.1.0 - Production NLSRTL Version 10.2.0.1.0 - Production
I'm getting this error while executing a package.But this is unpredictable because sometimes it's coming and sometimes it's not. Everytime I'm passing the value as 'ALERT' for the transaction name. Sometimes it's successful and sometimes it's throwing ORA-06592
CASE UPPER(IC_TRANSACTION_NAME) WHEN 'ALERT' THEN SELECT A.FACILITY_ID INTO VN_FACILITY_ID FROM ALERT A WHERE A.ALERT_ID = IN_PARENT_NODE_ID; INSERT INTO TRANSACTION_HISTORY (TXN_HISTORY_ID,
I'm trying to group sets of data based on time separations between records and then count how many records are in each group.
In the example below, I want to return the count for each group of data, so Group 1=5, Group 2=5 and Group 3=5
SELECT AREA_ID AS "AREA ID", LOC_ID AS "LOCATION ID", TEST_DATE AS "DATE", TEST_TIME AS "TIME" FROM MON_TEST_MASTER WHERE AREA_ID =89 AND LOC_ID ='3015' AND TEST_DATE ='10/19/1994';
[code]....
Group 1 = 8:00:22 to 8:41:22
Group 2 = 11:35:47 to 11:35:47
Group 3 = 15:13:46 to 15:13:46
Keep in mind the times will always change, and sometime go over the one hour mark, but no group will have more then a one hour separation between records.
I read that rownum is applied after the selection is made and before "order by". So, in order to get the sum of salaries for all employees in all departments with a row number starting from 1, i wrote :
select ROWNUM,department_id,sum(salary) from employees group by department_id
If i remove rownum, it gives the correct output. Why can't rownum be used here ?
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"
