Oracle SQL - Join keys

Question

These are my tables and sample records:

DEPARTMENT.Dept_nbr  (VARCHAR)
---------------------
(NULL)
UNK
00309
309

FPLAN.Department (NUMBER)
---------------------
D0309

Scenario:

I want FPLAN.Department (D0309) to be the reference and will display DEPARTMENT.Dept_nbr values.

My code:

select 
TRIM(REPLACE(FPL.DEPARTMENT, 'D')) as DEPARTMENT , DEPT.*
from
FPLAN FPL ,
(
select distinct(TRIM(LEADING 0 FROM DEPT_NBR)) DEPT_NBR from DEPARTMENT
) DEPT
WHERE
TRIM(LEADING 0 FROM FPL.DEPARTMENT) = DEPT.DEPT_NBR;

This brings NO RESULT.

What do I need to do to get this DEPT_NBR:

00309
309

this is not your private service site. It is hoped every question and answer will help others in the future. You got free advice, which helped you. The least you can do to "pay it forward" is to clarify what the problem was, so others would know if the answer could apply to their problem as well. Too bad this is too much of a demand on your time. — , Nov 26 '16 at 01:11

score 2 · Answer 1 · edited Jun 20 '20 at 09:12

Comment on nested queries and order of execution

The inner query is not guaranteed to be executed before the outer query and therefore does not protect you from bad conversions attempts.

create table t (x varchar2(10)); 

insert into t(x) values ('X123'); 

select  * 
from    (select x from t where regexp_instr(x,'([^0-9])') = 0) 
where   to_number (x) > 3
;

[Code: 1722, SQL State: 42000] ORA-01722: invalid number

Protected code -

select  * 
from    t
where   to_number(case regexp_instr(x,'([^0-9])') when 0 then x end) > 3
;

Aleksej · Accepted Answer · 2016-11-25T22:24:58.723

0

If your meaningful strings are always in the format 'DXXXX' where XXXX represents a number, you may convert everything to numbers and then compare numeric values:

select Dept_nbr
from (
     select *
     from DEPARTMENT
     where regexp_instr( Dept_nbr,'([^0-9])') = 0
     ) 
       inner join FPLAN
         on ( to_number(trim( 'D' from Department)) = to_number(Dept_nbr) )

edited Nov 25 '16 at 22:24

answered Nov 25 '16 at 22:01

Aleksej

22,443
5
33
38

Now i checked the datatypes: FPLAN is NUMBER, while DEPARTMENT is VARCHAR. – boombox2014 Nov 25 '16 at 22:06
@boombox2014: FPLAN is a number and contains 'D0309'? – Aleksej Nov 25 '16 at 22:07
oh sorry.. it's varchar. both are varchars – boombox2014 Nov 25 '16 at 22:09
@boombox2014 So, if the format of DEPARTMENT is always D00000 and Dept_nbr only contains numbers, this should work – Aleksej Nov 25 '16 at 22:10
i saw UNK and NULL values in DEPT_NBR. :( – boombox2014 Nov 25 '16 at 22:15
so, what to do in this case? Please edit the question by adding these cases and explaining the needed result for these values – Aleksej Nov 25 '16 at 22:17
Updated the post. I put the additional records. But still the same requirement - Get 00309 and 309. – boombox2014 Nov 25 '16 at 22:23
Edited my answer, have a try – Aleksej Nov 25 '16 at 22:25
Downvoted. This code is bad from some aspect but the worse of them is counting on an inner query to protect you from bad conversions. You have no guarantee that the inner query is going to be executed as a preliminary step before the `to_number` function takes place. – David דודו Markovitz Nov 26 '16 at 13:11

score -1 · Answer 3 · edited Jun 20 '20 at 09:12

-1

SQL Server

If the field is already a string, this will work

SELECT RIGHT('000'+ISNULL(field,''),3) If you want nulls to show as '000'

It might be an integer -- then you would want

SELECT RIGHT('000'+CAST(field AS VARCHAR(3)),3) copied from here

edited Jun 20 '20 at 09:12

Community

1
1

answered Nov 25 '16 at 21:59

Zameer Faiz

14
3

both of them are varchar datatypes – boombox2014 Nov 25 '16 at 22:10

Oracle SQL - Join keys

3 Answers3

Protected code -

SQL Server