Home » SQL & PL/SQL » SQL & PL/SQL » Decode and To_date
Decode and To_date [message #662649] |
Mon, 08 May 2017 14:17 |
|
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 |
|
Michel Cadot
Messages: 68694 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 |
|
EdStevens
Messages: 1376 Registered: September 2013
|
Senior Member |
|
|
SamP17 wrote on Mon, 08 May 2017 14:17I'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 |
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 |
|
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 |
cookiemonster
Messages: 13943 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 #662676 is a reply to message #662672] |
Tue, 09 May 2017 08:41 |
|
SamP17
Messages: 5 Registered: May 2017
|
Junior Member |
|
|
cookiemonster wrote on Tue, 09 May 2017 08:27It 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?
[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 #662685 is a reply to message #662679] |
Tue, 09 May 2017 10:03 |
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
|
|
|
|
|
Goto Forum:
Current Time: Sat Sep 28 19:37:17 CDT 2024
|