1

I Am trying to get the results for the 'CENTRE' whereby if the 'DEP2' does not exist it has to use 'DEP'

Have done this by using SUBSTR and CONCAT in my query But only get back 3 from 'DEP' when using it like this

My Query:

SELECT 
DEP,
DEP2,
SUBSTR(DEP2 || DEP,0,4)CENTRE

FROM DEPARTMENT

I Get the results back fine but with 'DEP' only showing 3 numbers instead of the 4

----------------------------------
DEP    | DEP2      | CENTRE      | 
----------------------------------
0132    | 7000     | 7000        |
0177    | 7000     | 7000        |
7051    | 7000     | 7000        |
7051    |          |  705        |
7051    |          |  705        | 
----------------------------------
3

If DEP2 is Null then DEP value will be returned

SELECT DEP, DEP2, NVL(DEP2, DEP) CENTRE
FROM DEPARTMENT;
  • Great Thank you it's not taking into account the DEP when DEP2 does not exist the CENTRE returns no results when DEP2 has no data? – Frikkels Feb 13 '18 at 9:50
  • It does return DEP value when DEP2 has no value, have you tried it? – Husam Mohamed Feb 13 '18 at 9:55
  • Ah I found that the DEP2 did not have NULLS I did a TRIM on DEP2 then was able to use the COALESCE. Thanks Very Much ! – Frikkels Feb 13 '18 at 10:24
3

if the 'DEP2' does not exist it has to use 'DEP'

Typical NVL/COALESCE situation, not something that should be handled with string functions.

SELECT DEP, DEP2, COALESCE(DEP2, DEP) CENTRE
FROM DEPARTMENT

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.