FAQ about built-in functions

Does MaxCompute support converting the 2010/1/3 format to 2010-01-03?

If a date is in the 2010/01/03 format, you can use the to_char(to_date('2010/01/03', 'yyyy/mm/dd'), 'yyyy-mm-dd') function to convert it. For more information, see TO_DATE and TO_CHAR.

If a date is in the 2010/1/3 format, you must write a User-Defined Function (UDF) to convert the date format. For more information about how to write a UDF, see Overview of MaxCompute UDFs.

How do I convert a UNIX timestamp to a date value?

You can use the FROM_UNIXTIME function to convert a numeric UNIX timestamp to a date value. For more information, see FROM_UNIXTIME.

How do I get the current system time?

You can use the GETDATE function to retrieve the current system time. For more information, see GETDATE.

Why does the "cannot be resolved" error occur when I use the YEAR, QUARTER, MONTH, or DAY date function?

• Symptoms

  When you use the YEAR, QUARTER, MONTH, or DAY date function, the following error is returned.

  FAILED: ODPS-0130071:[1,8] Semantic analysis exception - function or view 'year' cannot be resolved

• Cause

  YEAR, QUARTER, MONTH, and DAY are extension functions in MaxCompute V2.0. You must enable MaxCompute V2.0 data types to use them.

• Solution

  Add the statement set odps.sql.type.system.odps2 = true; before your SQL statement to enable the new data types for MaxCompute V2.0.

Why does the "doesn't have minute part" error occur when I execute the TO_DATE function?

• Problem

  When you execute the SQL statement to_date('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mm:ss'), the following error is returned.

  FAILED: ODPS-0121095:Invalid arguments - format string has second part, but doesn’t have minute part : yyyy-MM-dd HH:mm:ss                   

• Cause

  The format of the second parameter in the TO_DATE function is incorrect. Both mm and MM represent the month. You must use mi for the minute.

• Solution

  Change the SQL statement to to_date('2016-07-18 18:18:18', 'yyyy-MM-dd HH:mi:ss').

Why is there a bias when I use the ROUND function to round a DOUBLE data type?

select round(4.515, 2),round(125.315, 2);                   

The DOUBLE type is an 8 byte double-precision floating-point number, which can have precision errors. The DOUBLE representation of 4.515 is 4.514999999.... Therefore, the value is rounded down to 4.51.

Which function can I use in MaxCompute to set an auto-incrementing sequence?

You can use the ROW_NUMBER function to set an auto-incrementing sequence. For more information, see ROW_NUMBER.

How do I concatenate values from the same field?

You can use the WM_CONCAT function to concatenate values from the same field. For more information, see WM_CONCAT.

Does MaxCompute support the MD5 function?

Yes, it does. For more information, see MD5.

How do I pad a fixed-length string with leading zeros?

You can use the LPAD function. For more information, see LPAD.

Is the SUBSTRING_INDEX function from MySQL supported in MaxCompute?

Yes, it is. For more information, see SUBSTRING_INDEX.

Does the pattern parameter of the REGEXP_COUNT function support embedded query statements?

No, it does not. For usage information, see REGEXP_COUNT.

Does MaxCompute support the Oracle usage of to_char(data, FM9999.00)?

No, it does not. If you only need to adjust the display format of a number, you can use the FORMAT_NUMBER function. The following is a sample command.

-- Returns 12,332.123.
select format_number(12332.123456, '#,###,###,###.###');

Why does the "Invalid function" error occur when I use the IFNULL function?

• Problem

  The following SQL statement uses the IFNULL function.

  select a.id as id > , ifnull(concat('phs\xxx', a.insy, '\xxxb\xxx', ifnull()))

  The following error is returned.

  Semantic analysis exception - Invalid function : line 1:41 'ifnull'

• Cause

  This error occurs because MaxCompute does not support the ifnull function.

• Solution

  You can use a case when expression or the coalesce command. For more information, see CASE WHEN expression or COALESCE.

How do I combine all fields that meet a specific condition into a single JSON object?

In MaxCompute SQL, you can use filter conditions, such as like, to query data that meets your criteria. Then, use the ARRAY or MAP function to structure the data into MAP or ARRAY complex types. Finally, use the TO_JSON function for aggregation.

How do I treat each key in a JSON string as a separate field?

You can use the GET_JSON_OBJECT function to fetch the fields.

How do I convert a JSON string to the ARRAY format?

You can use the FROM_JSON function to perform the conversion. For example, select from_json(<col_name>, "array<bigint>");.

Which MaxCompute function corresponds to the IFNULL function in MySQL?

The NVL function in MaxCompute corresponds to the IFNULL function in MySQL. For a comparison of built-in functions among MaxCompute, Hive, MySQL, and Oracle, see Comparison of built-in functions in Hive, MySQL, and Oracle.

How do I convert one row into multiple rows?

You can use the TRANS_COLS function to convert one row of data into multiple rows.

Why does the "Expression not in GROUP BY key" error occur when I use the COALESCE function?

• Problem

  When you use the COALESCE function with more than one expression, the following error is returned.

  FAILED: ODPS-0130071:Semantic analysis exception - Expression not in GROUP BY key : line 8:9 "$.table"                    

  The following SQL statement causes the error.

  select  
  md5(concat(aid,bid)) as id
  ,aid
  , bid
  , sum(amountdue) as amountdue
  , coalesce(
  sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
  decode(get_json_object(extended_x, '$.table'), null, 0, 1)
  ) as tableparty
  , decode(sum(headcount),null,0,sum(headcount) ) as headcount
  , 'a' as pt
  from e_orders
  where pt='20170425'
  group by aid, bid;

• Cause

  A grouping field is missing from the GROUP BY clause.

• Solution

  The return value of the following expression is a field. Therefore, you must add the entire expression to the GROUP BY clause.

  coalesce(
  sum(regexp_count(get_json_object(extended_x, '$.table.tableParties'), '{')),
  decode(get_json_object(extended_x, '$.table'), null, 0, 1)
  ) as tableparty
  , decode(sum(headcount),null,0,sum(headcount) ) as headcount

Why does an implicit type conversion error occur when I use a MaxCompute built-in function?