very useful information about Netezza

Posted: October 16, 2014 in Netezza

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

Solution:-  Check the format defined.
Ex:- Selectto_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(‘200209-26^15:00:00‘,‘YYYYMM-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 0 (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 :- How to update table using self join in Netezza.

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 


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s