SQL Vertica, Cast Long issue

Question

I have the following SQL statement

 cast('long', ((substr(D.business_day, 1, 4)||substr(D.business_day, 6, 2))||substr(D.business_day, 9, 2))) AS bdate_id_yyyymmdd,

I have tried to change the 'long' to integer to see if that would work.

cast(integer, ((substr(D.business_day, 1, 4)||substr(D.business_day, 6, 2))||substr(D.business_day, 9, 2))) AS bdate_id_yyyymmdd,

Also tried integer(8)

getting error:

Error: [Vertica][VJDBC](4856) ERROR: Syntax error at or near ","
SQLState:  42601
ErrorCode: 4856

[according to the manual](https://my.vertica.com/docs/9.0.x/HTML/index.htm#Authoring/SQLReferenceManual/LanguageElements/Operators/DataTypeCoercionOperatorsCAST.htm%3FTocPath%3DSQL%2520Reference%2520Manual%7CSQL%2520Language%2520Elements%7COperators%7CData%2520Type%2520Coercion%2520Operators%2520(CAST)%7C_____0) Vertica uses standard SQL cast: `cast(expresion AS data type)` — , Mar 08 '18 at 17:14
@a_horse_with_no_name , tried `cast(expression AS integer` but same error — excelguy, Mar 08 '18 at 17:25

score 0 · Answer 1 · answered Mar 12 '18 at 12:43

It's not only a syntax error. Vertica is relatively pecky at data types, too.

So you want to make an integer consisting of yyyymmdd from something that looks like an ISO date.

A string?

A date?

There are two ways to go about that. You can't SUBSTR() on a date; you can't call a function getting the day, month, or year, number, from an ISO formatted string.

This example illustrates what you can do. Remember, extracting integers from dates is faster than working with strings and substrings. If your business_day is a DATE type, do what I do with business_date below.

Oh, and Vertica also supports this syntax:

<expression>::INTEGER

to cast to integer.

Happy playing ...

Marco

WITH
d(business_date,business_day) AS (
          SELECT DATE '2018-03-04',  '2018-03-04'
UNION ALL SELECT DATE '1957-04-22',  '1957-04-22'
UNION ALL SELECT DATE '1959-09-27',  '1959-09-27'
)
SELECT
  CAST(
         substr(d.business_day, 1, 4)
       ||substr(d.business_day, 6, 2)
       ||substr(d.business_day, 9, 2)
    AS int
  ) AS with_substr
, (  YEAR (business_date)*10000
   + MONTH(business_date)*100
   + DAY  (business_date)
  ) AS with_calc
FROM d;

with_substr|with_calc
 20,180,304|20,180,304
 19,570,422|19,570,422
 19,590,927|19,590,927

SQL Vertica, Cast Long issue

1 Answers1