Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

help with create SQL function

JCA
Contributor

Hi Exasol Community,

I would like to write my own "Dummy" function to map day IDs to the real day name,

  • ID = 1 -> Monday
  • ID = 2 -> Tuesday
  • ...
  • ID = 7 -> Sunday

Using the example code in "https://docs.exasol.com/db/latest/sql/create_function.htm"

CREATE OR REPLACE FUNCTION percentage (fraction DECIMAL,
                                       entirety DECIMAL)
   RETURN VARCHAR(10)
   IS
     res DECIMAL;
   BEGIN
     IF entirety = 0
       THEN res := NULL;
     ELSE
       res := (100*fraction)/entirety;
     END IF;
     RETURN res || ' %';
   END percentage;
/

i am unable to make my function work.

This is the code of my function

CREATE OR REPLACE FUNCTION ID_2_DAY (mID DECIMAL) RETURN VARCHAR(10)
IS res VARCHAR(10); -- What is this for?
BEGIN
      res := CASE
           WHEN mID = 1 THEN 'Montag'
           WHEN mID = 2 THEN 'Dienstag'
           WHEN mID = 3 THEN 'Mittwoch'
           WHEN mID = 4 THEN 'Donnerstag'
           WHEN mID = 5 THEN 'Freitag'
           WHEN mID = 6 THEN 'Samstag'
           WHEN mID = 7 THEN 'Sonntag'
           ELSE 'ERROR'

           END -- CASE

RETURN res ;
END  ID_2_DAY ;
/

 

I would like also to ask about 

JCA_1-1646299285296.png

What is this for?

JCA_0-1646299208749.png

Thanks in advance

 

Best Regards

Javier Conte Alcaraz

2 ACCEPTED SOLUTIONS

Accepted Solutions

exa-Nico
Community Manager
Community Manager

Hi @JCA 

I think you are just missing a ; after your CASE...END. This works for me:

--/
CREATE OR REPLACE FUNCTION ID_2_DAY (mID DECIMAL) RETURN VARCHAR(10)
IS res VARCHAR(10); -- What is this for?
BEGIN
      res := CASE
           WHEN mID = 1 THEN 'Montag'
           WHEN mID = 2 THEN 'Dienstag'
           WHEN mID = 3 THEN 'Mittwoch'
           WHEN mID = 4 THEN 'Donnerstag'
           WHEN mID = 5 THEN 'Freitag'
           WHEN mID = 6 THEN 'Samstag'
           WHEN mID = 7 THEN 'Sonntag'
           ELSE 'ERROR'

           END; -- CASE

RETURN res ;
END  ID_2_DAY ;
/

select id_2_day(1);

As to what the IS statement does... I'll let some other people chime in since I don't know personally. 😁

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

View solution in original post

JCA
Contributor

Hi @exa-Nico ,

I think the problem was DBeaver and the command to execute SQL Code (Strg + Eingabe) or (Strg + N). If I select the entire code and execute the querry, i do not have any errors.

JCA_0-1646301752918.png

Thanks for the help! @exa-Nico and @mwellbro . 

you learn something new every day 😄

Best regards

Javier

View solution in original post

5 REPLIES 5

mwellbro
Xpert
Hi @JCA

for the first part, wouldn't a to_char(current_date,'DAY') map the day to it's full name ?
Or do you need to have this in form of a custom function?

Sorry for the formatting, on the bus right now so putting this down via mobile😉

Cheers,
Malte

exa-Nico
Community Manager
Community Manager

Hi @JCA 

I think you are just missing a ; after your CASE...END. This works for me:

--/
CREATE OR REPLACE FUNCTION ID_2_DAY (mID DECIMAL) RETURN VARCHAR(10)
IS res VARCHAR(10); -- What is this for?
BEGIN
      res := CASE
           WHEN mID = 1 THEN 'Montag'
           WHEN mID = 2 THEN 'Dienstag'
           WHEN mID = 3 THEN 'Mittwoch'
           WHEN mID = 4 THEN 'Donnerstag'
           WHEN mID = 5 THEN 'Freitag'
           WHEN mID = 6 THEN 'Samstag'
           WHEN mID = 7 THEN 'Sonntag'
           ELSE 'ERROR'

           END; -- CASE

RETURN res ;
END  ID_2_DAY ;
/

select id_2_day(1);

As to what the IS statement does... I'll let some other people chime in since I don't know personally. 😁

Sports Enthusiast. Database Guy. Member of Team Exasol.
Having trouble? Just let me know!

Please Give Kudos if you find this post useful and also mark this as Accepted Solution if this is the required solution

mwellbro
Xpert
Adding to what Nico wrote:
I think the IS part is the beginning of the actual function definition and the res... is the definition of the variable.

Cheers,
Malte

JCA
Contributor

Hello @mwellbro ,

yes, the function does it. But I wanted to try to write my own function to learn how to do it, and that is why i wrote "dummy" at the beginning of the post :D.

thanks for the message!

Best regards

Javier

JCA
Contributor

Hi @exa-Nico ,

I think the problem was DBeaver and the command to execute SQL Code (Strg + Eingabe) or (Strg + N). If I select the entire code and execute the querry, i do not have any errors.

JCA_0-1646301752918.png

Thanks for the help! @exa-Nico and @mwellbro . 

you learn something new every day 😄

Best regards

Javier