very useful information about Netezza

ERROR:  to_timestamp(): bad value -26 for day of the month.

Solution:-  Check the format defined.

Ex:- Select to_timestamp('2002-09-26^15:00:00','YYYY-MMDD^HH24:MI:SS')

Correction Select to_timestamp('2002-09-26^15:00:00','YYYY-MM-DD^HH24:MI:SS')

If you see the above function usage, the timestamp passing and defined don’t match (highlighted in different color)

ERROR:-

When using the to_timestamp conversion function, which takes an input and a template string, the number of spaces in both strings must match. The following example uses the character“^” to represent a space.

to_timestamp('2002-09-26^15:00:00','YYYY-MM-DD^HH24:MI:SS')

In the previous example, the conversion function works as expected. In the following example, there is an extra space in the template string:

To_timestamp('2002-09-26^15:00:00','YYYY-MM-DD^^HH24:MI:SS').

In this case, the extra space before the “HH” causes the function to ignore the “1” in the “15” string, thus returning an incorrect result.

Problem:-   SQL operations on a float column can yield inconsistent results if not used properly. 

Soulution:-   The hashing of floating point data can result in certain SQL operations that return inconsistent results if not used correctly. Inconsistent results can occur if floating point columns are used as the distribution key, as a join column, or in group by, order by, and distinct select operations.

Do not use floating point columns for such operations.

Note: A floating point column is a column defined in the CREATE statement with a data type of FLOAT, DOUBLE, FLOAT4, or FLOAT8.

Information:-   By default Neetzza system treats time values without timezones as GMT times. An example follows: 

create table TIME1(TM timetz); (a time with timezone column)
insert into TIME1 values (‘1:00’); (a time value without a timezone)

select * from TIME1; (shows the value that has a timezone offset of  (GMT)).

Information:-
Certain operations do not work on synonyms. For example, you cannot generate statistics on synonyms, nor can you truncate a synonym.

Performance :-  For system efficiency, avoid the use of joins with external tables.
If one try to perform they may face the below error 

Error: 1000000161: query does not support complex External Table scan

Information :- Update in Netezza with self join. 

UPDATE product_dim B
SET    B.prod_nm = NAME
FROM   (SELECT product_id,
               NAME
        FROM   product_dim
               LEFT OUTER JOIN product_dis
                            ON product_id = productid)A
WHERE  A.product_id = B.product_id