skip to Main Content

Please do tell me how to get rid of this error

Here is my code:

%%sql 
DROP TABLE IF EXISTS ages;
CREATE TABLE ages AS
SELECT CODEDESC, SUM(age) AS age FROM
(SELECT CODEDESC, (CASE WHEN  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))< 30 THEN 30
             ELSE  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))
        END)AS age FROM filtered_webextract
INNER JOIN cuisine
ON filtered_webextract.CUISINECODE = cuisine.CUISINECODE
GROUP BY CODEDESC, camis) as foo
GROUP BY CODEDESC;

Here is the error:

 * postgresql://docker:***@nycinspection.tditrain.com:5433/NYCinspection
Done.
(psycopg2.errors.UndefinedFunction) function julianday(timestamp without time zone) does not exist
LINE 3: (SELECT CODEDESC, (CASE WHEN  (JULIANDAY(MAX(inspdate))-JULI...
                                       ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.

[SQL: CREATE TABLE ages AS
SELECT CODEDESC, SUM(age) AS age FROM
(SELECT CODEDESC, (CASE WHEN  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))< 30 THEN 30
             ELSE  (JULIANDAY(MAX(inspdate))-JULIANDAY(MIN(inspdate)))
        END)AS age FROM filtered_webextract
INNER JOIN cuisine
ON filtered_webextract.CUISINECODE = cuisine.CUISINECODE
GROUP BY CODEDESC, camis) as foo
GROUP BY CODEDESC;]
(Background on this error at: http://sqlalche.me/e/13/f405)

Please let me know how to change my code.
Thanks in advance

2

Answers


  1. This error occur because there is no user defined or inbuilt function calls JULIANDAY in postgresql. Therefore replace JULIANDAY(timestamp) with extract(julian from 'timestamp').Then it will work.

    Login or Signup to reply.
  2. try MAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < 30 or MAX(JULIANDAY(inspdate)) - MIN(JULIANDAY(inspdate)) < INTERVAL '30 days', or even use DATE_PART('day', end - start)

    UPD: assuming that the JULIANDAY() is your custom function

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search