CODEWITHSUNDEEP
sqloracle-databasejoininner-join
boombox2014
boombox2014

Reputation: 65

Oracle SQL - Join keys

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

Upvotes: 0

Views: 151

Answers (3)

David דודו Markovitz
David דודו Markovitz

Reputation: 44971

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
;

Upvotes: 2

Zameer Faiz
Zameer Faiz

Reputation: 14

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

Upvotes: -1

Aleksej
Aleksej

Reputation: 22959

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) )

Upvotes: 0

Related Questions