Home » SQL & PL/SQL » SQL & PL/SQL » Decode and To_date
Decode and To_date [message #662649] Mon, 08 May 2017 14:17 Go to next message
SamP17
Messages: 5
Registered: May 2017
Junior Member
I'm trying to write a sql script to run through some reporting software and am having issues with a date field. The field in the table stores years in the format 2017/18. From this I'm extracting the years and creating the actual start and end dates of the years which is working fine. However, when the value of the initial field is null (which it is going to be quite often) the script errors.

Someone suggested using Decode with to_date to feed a dummy date through to the reporting software in cases where the field is null but I get an error about date format picture ending before converting entire input string.

The script I'm running at the minute is

to_date(decode(B211_FINANCIAL_YEAR_2,null,'01/05/1950',TO_DATE('01/04/'||substr(translate(B211_FINANCIAL_YEAR_2,'`',''''), 1, 4),'dd/mm/yyyy')),'dd/mm/yyyy') Financial_Year2A,

which works fine in SQL, but errors in business objects (BOXI/WEBI). I need the field to come through as a date for the formulae to work in the reporting software, whether that's as a null value or a dummy date I can then filter out.

Any help appreciated!
Re: Decode and To_date [message #662650 is a reply to message #662649] Mon, 08 May 2017 15:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also format your SQL, if you don't know how to do it, learn it using SQL Formatter.

"decode(B211_FINANCIAL_YEAR_2,null,'01/05/1950',TO_DATE('01/04/'||substr(translate(B211_FINANCIAL_YEAR_2,'`',''''), 1, 4),'dd/mm/yyyy'))" is wrong, the first value returned by DECODE is a STRING, the second one is a DATE, so mismatch and error.

If "The field in the table stores years in the format 2017/18." is true then ",TO_DATE('01/04/'||substr(translate(B211_FINANCIAL_YEAR_2,'`',''''), 1, 4)" is just "substr(B211_FINANCIAL_YEAR_2, 1, 4)".
In the end the whole expression is:

A question raises: "why '01/05/1950' and '01/04/'||substr..." and not the same month?" This seems not correct.

Assuming the same month then the whole expression is:
to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_2, 1, 4),'1950'),'dd/mm/yyyy')

Re: Decode and To_date [message #662667 is a reply to message #662649] Tue, 09 May 2017 06:50 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
SamP17 wrote on Mon, 08 May 2017 14:17
I'm trying to write a sql script to run through some reporting software and am having issues with a date field. The field in the table stores years in the format 2017/18.
Not if the table is designed correctly. A correctly designed table will store dates as data type DATE. And as such, they store the data in an internal, binary format. And if the data is NOT in a DATE column, that is a big design failure.

So, before this discussion can have any meaning at, you need to show us the DDL that created the table, or at the least the output of a sqlplus 'describe' command on the table. Until we see that, any proposed solutions are based on assumptions that very well may not be valid.


On date "formats", read this.
Re: Decode and To_date [message #662668 is a reply to message #662667] Tue, 09 May 2017 06:53 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
If you can't desc the table in business objects, try the DUMP function on the column.

I'd suggest doing it in business objects rather than sql*plus in this case because that's where the unexpected behaviour is happening and in case it's buried under views or synonyms etc as is often the case.
Re: Decode and To_date [message #662671 is a reply to message #662668] Tue, 09 May 2017 08:17 Go to previous messageGo to next message
SamP17
Messages: 5
Registered: May 2017
Junior Member
Thanks for the help. Apologies for the lack of formatting in the original post - I realized after I'd posted and couldn't get back to edit it.

Quote:

Assuming the same month then the whole expression is:
to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_2, 1, 4),'1950'),'dd/mm/yyyy')
That works like a charm (and the month doesn't matter, so grand). Again, perfect in SQL but now I'm getting a 'non-numeric character' warning when I run it against the whole table and the same picture format error when I run it against an entry which has no blank fields in those dates. This is the code

	substr(translate(B211_FINANCIAL_YEAR_1,'`',''''), 1, 4) Financial_Year1,
  	 to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_1, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year1A,
    	 to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_1, 6, 2),'51'),'dd/mm/yyyy') Financial_Year1B,
	substr(translate(B211_FINANCIAL_YEAR_2,'`',''''), 1, 4) Financial_Year2,
  	 to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_2, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year2A,
	 to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_2, 6, 2),'51'),'dd/mm/yyyy')  Financial_Year2B,
  	substr(translate(B211_FINANCIAL_YEAR_3,'`',''''), 1, 4) Financial_Year3,
 	 to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_3, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year3A,
	 to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_3, 6, 2),'51'),'dd/mm/yyyy')  Financial_Year3B,
	substr(translate(B211_FINANCIAL_YEAR_4,'`',''''), 1, 4) Financial_Year4,
  	  to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_4, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year4A,
	  to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_4, 6, 2),'51'),'dd/mm/yyyy')  Financial_Year4B,
	substr(translate(B211_FINANCIAL_YEAR_5,'`',''''), 1, 4) Financial_Year5,
  	 to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_5, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year5A,
	 to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_5, 6, 2),'51'),'dd/mm/yyyy')  Financial_Year5B,
  	substr(translate(B211_FINANCIAL_YEAR_6,'`',''''), 1, 4) Financial_Year6,  	
  	 to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_6, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year6A,
	 to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_6, 6, 2),'51'),'dd/mm/yyyy')  Financial_Year6B,
  	substr(translate(B211_FINANCIAL_YEAR_7,'`',''''), 1, 4) Financial_Year7,
  	 to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_7, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year7A,
	 to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_7, 5, 2),'51'),'dd/mm/yyyy')  Financial_Year7B,
	substr(translate(B211_FINANCIAL_YEAR_8,'`',''''), 1, 4) Financial_Year8,
  	  to_date('01/04'||nvl(substr(B211_FINANCIAL_YEAR_8, 1, 4),'1950'),'dd/mm/yyyy') Financial_Year8A,
	 to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_8, 6, 2),'51'),'dd/mm/yyyy')  Financial_Year8B,

The field is the table is a VARCHAR and I'm sorry but I don't know the oracle version - don't have access to that information. I only have basic reporting access to the database as I'm not an admin
Thanks
Re: Decode and To_date [message #662672 is a reply to message #662671] Tue, 09 May 2017 08:27 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
It helps if you post the exact error message, but it's a fairly safe bet that some of the data in b211_financial_year isn't in the format YYYY/YY
Re: Decode and To_date [message #662673 is a reply to message #662671] Tue, 09 May 2017 08:29 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
select * from v$version; -- produces what results?
Re: Decode and To_date [message #662675 is a reply to message #662673] Tue, 09 May 2017 08:31 Go to previous messageGo to next message
SamP17
Messages: 5
Registered: May 2017
Junior Member
BlackSwan wrote on Tue, 09 May 2017 08:29
select * from v$version; -- produces what results?
Thank you!

Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
"CORE 12.1.0.2.0 Production" 0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0
Re: Decode and To_date [message #662676 is a reply to message #662672] Tue, 09 May 2017 08:41 Go to previous messageGo to next message
SamP17
Messages: 5
Registered: May 2017
Junior Member
cookiemonster wrote on Tue, 09 May 2017 08:27
It helps if you post the exact error message, but it's a fairly safe bet that some of the data in b211_financial_year isn't in the format YYYY/YY
The error when run against the whole table is shown in the image. I've looked through the output and it's all either yyyy/yy or null - no rogue values in it so I think it's how I've written the code?

/forum/fa/13512/0/

[mod-edit: image inserted into message body by bb]

[Updated on: Thu, 11 May 2017 05:52] by Moderator

Report message to a moderator

Re: Decode and To_date [message #662679 is a reply to message #662676] Tue, 09 May 2017 09:05 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
write a FUNCTION that returns the column value when it is malformed (not a valid DATE) & returns NULL when it is a valid DATE.
then test by doing as below

SELECT VALIDATE_COLUMN(<column_name>) from <problem_table> where VALIDATE_COLUMN(<column_name>) IS NOT NULL;
Re: Decode and To_date [message #662684 is a reply to message #662679] Tue, 09 May 2017 09:59 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_7, 5, 2),'51'),'dd/mm/yyyy') Financial_Year7B,

That 5 should be a 6.
Re: Decode and To_date [message #662685 is a reply to message #662679] Tue, 09 May 2017 10:03 Go to previous messageGo to next message
Bill B
Messages: 1971
Registered: December 2004
Senior Member
This will get you the start and end of the year.

SELECT TRUNC (TO_DATE (CASE WHEN B211_financial_year_2 IS NULL THEN TO_CHAR (SYSDATE, 'YYYY') 
                       ELSE SUBSTR (B211_financial_year_2, 1, 4) END, 'YYYY'), 'YEAR') Beg_year,
         ADD_MONTHS (TRUNC (TO_DATE (CASE WHEN B211_financial_year_2 IS NULL THEN TO_CHAR (SYSDATE, 'YYYY') 
                                     ELSE SUBSTR (B211_financial_year_2, 1, 4) END, 'YYYY'), 'YEAR'), 12) - 1 End_year
  FROM My_table

Re: Decode and To_date [message #662694 is a reply to message #662684] Tue, 09 May 2017 13:45 Go to previous messageGo to next message
SamP17
Messages: 5
Registered: May 2017
Junior Member
cookiemonster wrote on Tue, 09 May 2017 09:59
to_date('31/03/19'||nvl(substr(B211_FINANCIAL_YEAR_7, 5, 2),'51'),'dd/mm/yyyy') Financial_Year7B,

That 5 should be a 6.
Genius! Thank you so much!
Re: Decode and To_date [message #662715 is a reply to message #662694] Wed, 10 May 2017 03:24 Go to previous message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
You really want to create a function for that calculation, then you don't have to worry about typos in one version of it.
Previous Topic: invalid file operation
Next Topic: problem with FLOOR function
Goto Forum:
  


Current Time: Fri Mar 29 02:34:28 CDT 2024