1

I trying to set a nvl() with the value OPEN on -> 

        select to_char(max(CLOSE_DATE),'dd.mm.yyyy hh24:mi:ss')

If it is closed is getting the date ('dd.mm.yyyy hh24:mi:ss') otherwise it should display OPEN

Any ideas, where I can put correctly the NVL()?

Solution:

        select nvl (to_char(max(CLOSE_DATE),'dd.mm.yyyy hh24:mi:ss'), 'OPEN')
Stef
  • 139
  • 12
  • Sample data and expected output would help a lot. What default do you want to apply? – William Robertson Apr 21 '18 at 07:55
  • the idea is that if no date 'dd.mm.yyyy hh24:mi:ss' is available to display a wording like 'still open' instead that the output is empty. The script is working but i have difficult to set 'still open' in case the date 'dd.mm.yyyy hh24:mi:ss' is not available.. script: select to_char(max(CLOSE_DATE),'dd.mm.yyyy hh24:mi:ss' from...... as Close_date – Stef Apr 21 '18 at 08:16
  • By *the date 'dd.mm.yyyy hh24:mi:ss'*, do you mean *the date*? I mean, if it's null it's null, regardless of what format you want to apply. Anyway, sample data would still help. – William Robertson Apr 21 '18 at 08:21
  • yes i mean the date: out put is like this Product-ID: 55766 Product-Status: OPEN Creation-Date: 11.04.2018 19:26:32 Close-Date: here should be displayed 'still open' – Stef Apr 21 '18 at 08:47
  • I meant a detailed example added to your question that we could try out for ourselves. – William Robertson Apr 21 '18 at 08:53

1 Answers1

2

This?

select decode(close_date, null, 'OPEN', 
                                to_char(close_date, 'dd.mm.yyyy hh24:mi:ss'
             ) result
from your_table

Or

select nvl(to_char(close_date, 'dd.mm.yyyy hh24:mi:ss'), 'OPEN') result
from your_table
Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Why not simple Standard SQL instead of proprietary syntax? `COALESCE(to_char(close_date, 'dd.mm.yyyy hh24:mi:ss'), 'OPEN')` – dnoeth Apr 20 '18 at 21:11
  • Because it is tagged as "Oracle"? Apart from that, no other reason, @dnoeth; thank you for the comment, though. – Littlefoot Apr 20 '18 at 21:35
  • thx for all feedbacks, but OPEN doesn't appear field is still empty... in case there is no date. other ideas? – Stef Apr 21 '18 at 07:45