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


ADVERTISEMENT

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

Alternative To DECODE In Regards To Just SQL (not PL)

Jul 1, 2007

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.

View 6 Replies View Related

SQL & PL/SQL :: Decode With Like Operator

Jul 12, 2013

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"

How to write this.

View 5 Replies View Related

SQL & PL/SQL :: Decode In Where Clause

Aug 3, 2013

I have a scenario where in report table I need to get

IF FLAG='Y' AND LOC=118443 THEN STATE='FIN'
IF FLAG='Y' AND LOC!=118443 THEN STATE SHOULD BE ('COMP','PART')
IF FLAG='N' IT SHOULD INCLUDE ALL STATES.

I tried using decode in WHERE CLAUSE , but not successful.

Create Table Report(Id Number,Loc Number,Flag Varchar2(3),State Varchar2(20));
table REPORT created.

Insert Into Report Values(1,1,'Y','COMP');
1 rows inserted.

Insert Into Report Values(2,118443,'Y','FIN');
1 rows inserted.

Insert Into Report Values(7,118443,'Y','COMP');
1 rows inserted.
[code]...

View 14 Replies View Related

SQL & PL/SQL :: Case Vs Decode?

Feb 13, 2011

How to use decode and case in "where" and "from" clause of a select statement.

I know the decode can't be used in where clause. In that case how we can use decode and case in from clasue that is: table definition

View 7 Replies View Related

SQL & PL/SQL :: Decode On One Line

Jul 28, 2010

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:

SELECT sgk_invfg_organization_items.description,
sgk_invfg_organization_items.inventory_item,
sgk_invfg_organization_items.product_line,
sgk_invfg_organization_items.product_type,
Decode(sgk_item_master_launch_v.sgk_model_item, 20,
sgk_item_master_launch_v.sgk_model_number) brand_1,
Decode (sgk_item_master_launch_v.sgk_model_item, 30,
[code]...

View 5 Replies View Related

SQL & PL/SQL :: Decode With Between Clause

Aug 11, 2011

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"

View 4 Replies View Related

SQL & PL/SQL :: How To Use Decode Function

Oct 31, 2011

How can i use the decode function?

for example

I have the value of 1000 then the numbers 50-100 will be 'A' and 1-49 = 'B'?

View 9 Replies View Related

SQL & PL/SQL :: Decode Function

Apr 4, 2013

DECODE(:P_PERIOD_TYPE,'PJTD','PROJECT-TO-DATE','PTD','PERIOD-TO-DATE','YTD','YEAR-TO-DATE')

what does it mean..

View 3 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 :: Use Decode Function

Aug 19, 2010

select
DECODE(PAA.C_TYPE_DESC,'TXIS CASUAL LEAVE',NVL(PAA.ABSENCE_DAYS,0)) TCL,
DECODE(PAA.C_TYPE_DESC,'TXIS SICK LEAVE',NVL(PAA.ABSENCE_DAYS,0)) TSL,
DECODE(PAA.C_TYPE_DESC,'TXIS PRIVELEGE LEAVE',NVL(PAA.ABSENCE_DAYS,0)) TPL
from PER_ABSENCE_ATTENDANCES_V PAA

i want to use nvl function within decode function

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

View 12 Replies View Related

Using 2 Tables With Decode Function

Nov 8, 2009

What I have to do is this:

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

Orders:
ORD_ID
SUPPLIER_ID
ISSUING_EMP_ID
ORDER_DATE
ORDER_STATUS

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.

View 1 Replies View Related

Decode Based On Different Column

Jul 24, 2007

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"

View 5 Replies View Related

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 :: DECODE And CASE In WHERE Clause

May 7, 2010

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)

View 3 Replies View Related

SQL & PL/SQL :: Decode Using Multiple Values

Jan 23, 2013

i am using the following within queries:

decode(qt_flag,'KT','Y',decode(qt_flag,'IT','Y','N'))

It works fine but when i use in ETL tool it is not working. Is there a way to use decode just once with multiple values like above.

View 4 Replies View Related

SQL & PL/SQL :: DECODE And Date Format?

Jul 25, 2011

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.

View 4 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

SQL & PL/SQL :: How To Use Outer Join With Decode

Sep 19, 2012

I need to apply an outer join on following statement.

Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)=B.Fin_Wid;

I used following but error

Select * From Abc A,Def B
Where Decode(A.Fin_Wid,10,A.Gry_Wid,A.Fin_Wid)(+)=B.Fin_Wid;

View 1 Replies View Related

SQL & PL/SQL :: Decode / Case Functions?

Aug 19, 2010

I am having some records in the table. If the record num is

1--It should show the month as "Apr"
2--"May"
3--"Jun"
4--"July"
5--"Aug"
6--"Sept"

if it is having other than these 6 should show "0" for the remaining months.

View 10 Replies View Related

Oracle TNS Packet Decode?

Jan 23, 2012

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 am not able to decode the Data Packet.

0.437308 192.168.1.108 192.168.1.102 TCP kwdb-commn > iax
[PSH, ACK] Seq=823 Ack=589 Win=64947 Len=67
0000 00 0c 29 0c 9a c7 00 0c 29 fd 07 3d 08 00 45 00 ..).....)..=..E.
0010 00 6b 05 15 40 00 80 06 71 55 c0 a8 01 6c c0 a8 .k..@...qU...l..
0020 01 66 04 67 11 d9 0e a7 cd b9 b6 88 7c 6e 50 18 .f.g........|nP.

[code]...

View 5 Replies View Related

SQL & PL/SQL :: Decode Functionality In DBMS_OUTPUT

Jun 28, 2010

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

DBMS_OUTPUT STMT:

Dbms_Output.Put_Line(nullable ||CHR(9)||CHR(9)||' -- ' ||upper(cur_rec.column_name)||CHR(9)||CHR(9)||cur_rec.data_type);

STMT to use in place of cur_rec.data_type:

substr(decode( data_type, 'NUMBER', decode( data_precision, NULL, NULL,'('||data_precision||','||data_scale||')' ),data_length),1,11)

Also I am not able to align the output using CHR(9).

View 14 Replies View Related

SQL & PL/SQL :: Use DECODE Function For Getting Result

Aug 28, 2012

I have a table like Create temptbl (Client_status CHAR(1), Rollbak NUMERIC(3), count_org NUMERIC(3), count_member NUMERIC(3))

With Values
Cliet_Status Rollbak count_org count_member
------ ------- --------- ------------
Z 3 7 5
P 39 5 8
R 49 1 6
S 5 6 4

I need to use DECODE function for getting the result like

IF client_status NOT IN('Z', 'P')
THEN rollbak in(7, 39,49,10)and count_org is not null and count_member is not null.
ELSE NULL

View 3 Replies View Related

SQL & PL/SQL :: How To Convert Case To DECODE

Oct 20, 2011

I had written follwoing sql with CASE statments, but in oracle 8i i'm unable to use CASE in pl/SQL.

how to convert following sql to DECODE ? Or is there any option other than DECODE ?

SELECT ol.order_no,
ol.transaction_line_id,
ol.pd_actl_tonnage,
ol.real_tonnage_high,
ol.req_tonnage,
il.id,
il.transaction_ref,

[code].....

View 18 Replies View Related

SQL & PL/SQL :: Decode Logic Not Working

Feb 19, 2013

what is the right syntax to use the below decode logic.

Decode(EXTRACT(MONTH FROM sysdate) > 2, ltrim_blanks(GLAMOUNTS.CR_AMOUNT_02) + ltrim_blanks(GLAMOUNTS.DB_AMOUNT_02), 0)

View 12 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

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







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