SQL & PL/SQL :: Assignment In Result In Decode Function
Dec 13, 2011
I have 3 tables.
Table 1 have 3 columns
ID, CUS_NAME, LOC
insert into table1 values (001,ABC,North);
insert into table1 values (002,DEF,South);
insert into table1 values (003,GHI,West);
Table 2 have 3 columns
ID, CUS_NAME, LOC
insert into table2 values (001,ABC,North);
insert into table1 values (002,DEF,East);
insert into table1 values (003,JKL,South);
Table 3 is Result_Tab table having 8 columns
ID, TAB1_CUS_NAME, TAB2_CUS_NAME, Cus_Name_Res, TAB1_CUS_LOC, TAB2_CUS_LOC, Cus_LOC_Res, Comment.
I have written two cursors which fetches data from both the tables and compares each data between each other and inserts the value into the result table.
the code is as follow:
Insert into Result_Tab values
(T1.ID, T1.Cus_Name, T2.Cus_Name, decode(T1.Cus_Name,T2.Cus_Name,'Y','N'),T1.LOC, T2.LOC, decode(T1.LOC,T2.LOC,'Y','N'),Null);
Now I want the resul as follows:
ID T1.N T2.N N_Res T1.L T2.L L_Res Comment
001 ABC ABC Y North North Y Null
002 DEF DEF Y South East N Loc
003 GHI JKL N West South N Name, Loc
Is there a way wherein i could capture the column names in decode function when it doesn't match, so that I can insert the same in the comment column.
View 4 Replies
ADVERTISEMENT
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
Mar 3, 2011
When I run a menu (which was working fine yesterday and hasn't been changed in months) I click on an item and it runs the following
"
DECLARE
module_name VARCHAR2(125);
BEGIN
module_name := :GLOBAL.FORMS_PATH || 'covenants';
Open_Form(module_name);
END;
"
The module_name variable should be assigned the value 'covenants', because :GLOBAL.FORMS_PATH is '' (nothing). I stepped thru the code and in the debug system values window there is nothing in :GLOBAL.FORMS_PATH. But instead, it gives me: 'FORM_NAMEcovenants' where 'FORM_NAME' is the form the menu was called from.
View 5 Replies
View Related
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
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
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
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
Oct 18, 2010
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) ) ) )
View 3 Replies
View Related
Oct 19, 2011
Can we call a function within decode statement. I am able to do the same for simple example function . But In my actual procedure it's giving the error message . Are there any restrictions to call function with in decode statement?
View 4 Replies
View Related
May 11, 2010
I am trying to use decode function in sql and inside decode function can I use select statement ?
here is my sql
select we.wf_entity_id, decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
) from wf_entity we
where
[code]....
see this
decode(object_type_id,
1, select audit_number from ea_audit_general where sys_audit_id=object_id
2,'test',
object_type_id
)
will this work? Its not working for me?
View 2 Replies
View Related
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
Sep 16, 2010
I have Two cursor record block..which is attached in form..
My TASK IS
In my first Block, When DBCR Column = 'D' Then in backend this column value should be save as a '1'
WHEN DBCR Column = 'C' Then Then in backend this column value should be save as a '2'
My Both Field is on Data Block...
In Property palette of this field can we write any decode condition..so it reflects directly on database.
View 2 Replies
View Related
Jun 5, 2009
I have an arithmatic expression which is dynamic say
expression = [Col5]/[Col1]
I will be using this in building a dynamic SQL.so i have to make sure that the divisible by zero is taken care. In the expression Col1,Col5 are values coming from a SQL.
For the above expression, my conversion will be
ROUND( ((CCol4 / DECODE (CCol1, 0, NULL, CCol1) )), 0).
I have to build a function which takes in the expression and fetches me the result.My expression can be any combination of arithmaticexpression involving columns.
Ex:
[Col4 + Col2]/[Col3-Col1]*[Col5].
View 1 Replies
View Related
Feb 23, 2012
How can I refer the result of inner query - max(t.col3) in the outer query. I tried the 'group by' but it is not performing well
select
t1.col5, t1.col6
from t1,
(select t.col1
t.col2
[code].......
View 8 Replies
View Related
Oct 30, 2012
difference between Difference between Deterministic functions and Function result cache in 11g?
View 4 Replies
View Related
Apr 4, 2012
I'm working with Object types containing other object types, and I'm getting the error PLS-00363 (Expression cannot be used as an assignment).I'm putting exlpicity all 'SELF' parameters as 'IN OUT', but still get the error...
CREATE OR REPLACE TYPE TYP_PERSON AS OBJECT (
strName VARCHAR2(100),
--
CONSTRUCTOR FUNCTION TYP_PERSON RETURN SELF AS RESULT,
--
MEMBER FUNCTION getName (SELF IN OUT TYP_PERSON) RETURN VARCHAR2,
MEMBER PROCEDURE setName (SELF IN OUT TYP_PERSON, pNewName VARCHAR2)
) NOT FINAL;
[code]....
How can I do this parentObject.getChildObject().setChildFunction()?
View 5 Replies
View Related
May 27, 2013
I have the following table structure with values
CREATE TABLE DUMMY
(
SR_NUMBER VARCHAR2(100 CHAR),
ASSIGNMENT_GROUP VARCHAR2(100 CHAR),
REASSIGNMENT_COUNT VARCHAR2(100 CHAR),
CREATED DATE,
[code]...
?I have the following requirement, the output should be:
Ticket count (sr_number)
% of tickets inside DL
Number of tickets inside DL
Average cycle time (cycle time = closed date - created date)
Total cycle time (cycle time = closed date - created date)
Number of reassignments (sum)
DL - (deadline) formula is, closed date <= target_date
This should be displayed, grouped by year, then month and then by assignment group. The values should be in descending order(dates) Not sure how group by will work here.I am able to write the basic code for the above, but group by based on year, month and assignment group is pretty confusing to me.
View 10 Replies
View Related
Aug 18, 2011
It appears that the Oracle RAC cluster does not recognize the 'dba' group membership assignment to a user, unless it is assigned as the user's primary group.This also only affects newly created users, older users do not have this issue.
This affects us in the following way:
We are unable to create new cluster resources with the SAPCTL tool with the <sid>adm user.
When we try to create a resource using sapctl as the <sid>adm user we receive the following error:
CRS-0259: Owner of the resource does not belong to the group. SAP ABAP VIP creation failed.
The description of this error is that the resource owner (<sid>adm) is not a member of the group defined by 'osdbagrp' (what the cluster expects group membership for os users to control cluster resources), in our case this group is 'dba'.This is despite the user definitely being a member of the group.
Resource creation workaround:We are able to get around the resource creation by creating the resources either as root user, or by assigning 'dba' as <sid>adm's primary group.However, although we are able to control the VIP cluster resource, we are unable to control the other SAP enqueue resources as we assume that they need to be created by a user with 'sapsys' as a primary group (i.e. the resources do not start or it appears that there is not even an attempt to start the resources as there are no SAP startup logs created).
We have attempted multiple variations of creating and controlling cluster resources: changing <sid>adm primary group, changing cluster resource permissions, with nothing working for us.
The bottom line is that we need to be able to create these sapctl resources with a <sid>adm user that has 'sapsys' as a primary group while also being a member of the 'dba' group.
View 1 Replies
View Related
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
View Related
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
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
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
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
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
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
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
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
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
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
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