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
ADVERTISEMENT
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
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
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
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
Feb 21, 2013
case when age <= 17 then '<= 17'
when age >= 40 then '>= 40'
else to_char(t.age)
end age
the case statement above doesn't work in my 8.1.7 cursor statement within my pl/sql block so I need an equivalent decode
View 10 Replies
View Related
Apr 9, 2010
I have to conditionally update a set of columns in a table. If the column status_code_stage_3 IS NULL THEN I have to update the column status_code_stage_2. The below query is giving error:-
Test Table create scripts
CREATE TABLE test11( date_stage_3 date, reason_code_stage_3 varchar2(20),
reason_code_stage_2 varchar2(20), opportunity_date date )
INSERT INTO test11 values(sysdate,'reason1',NULL,sysdate)
INSERT INTO test11 values(sysdate,NULL,'reason2',sysdate)
[code]....
how to work out the update statement to use the conditional statement for columns.
View 2 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
Nov 27, 2010
I was trying to write nested decode statement with multiple searches. For example
Case when seq = 1 and date1 > date2 then Record_Name End
How can i modify this to decode.
View 4 Replies
View Related
Jul 21, 2011
I have 2 sql's statement below, and just wondering if their is difference between the two sql's.
FIELDS data type:
--------------------
a.field is DATE
b.field is also a DATE
SQL1:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field = b.field
WHERE a.field between b.field AND b.field + 2
;
SQL2:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field between b.field AND b.field + 2
;
OR
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field >= b.field AND
a.field <= (b.field + 2)
;
which ever is correct between the two sql.
QUESTION: would be the two sql's generate same result set.
View 1 Replies
View Related
Mar 14, 2011
I am trying to run following sql query,but it is throwing following error.
SQL> delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CON
NECTION_ID_TEMP=COMPLEMENTS.feature_conn_id
2 WHERE a.current_designation is null and a.current_low is null and a.current
_high is null;
delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CONNECTI
ON_ID_TEMP=COMPLEMENTS.feature_conn_id
*
ERROR at line 1:
ORA-00933: SQL command not properly ended
View 4 Replies
View Related
Nov 16, 2012
I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:
UPDATE tbl1
SET tbl1.EMPID =
(SELECT tbl2.EMPIDA FROM tbl2
WHERE LOWER(tbl1.EMAILCOL) = LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE tbl1.EMAILCOL= tbl2.EMAILCOL2 );
But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.
View 12 Replies
View Related
May 2, 2009
I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.
I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example
SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';
View 6 Replies
View Related
Aug 17, 2010
This is my query:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
[Code]...
The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:
UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds
[Code]...
but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.
View 9 Replies
View Related
Oct 19, 2013
Lets say I have three tables t1 and t2 and t3.
SELECT * FROM T1;
Id
____
1
2
3
4
SELECT * FROM T2;
Id
____
1
SELECT * FROM T3;
Id
____
1
Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join
select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id
However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4
One way of doing that is using the not exists clause
select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)
Is there a better way of doing this in sql ?
View 5 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
Aug 14, 2009
i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?
View 6 Replies
View Related
Aug 10, 2011
HOW to use variable P_TMPLID in following statement
TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;
because its throwing error while compiling
and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION
View 2 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
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
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
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
