SAS PROC IMPORT does not create an OUT data set as ordered

Situation: I'm importing an xlsx file with PROC IMPORT and wanting to send the data OUT to a new netezza database table. My issue: SAS appears to run fine, but the log shows a completely different table name was been created with a libref that I'm no

Inactive Netezza sessions affect performance

There are times when a Netezza appliance becomes busy with many active sessions processing requests. At these times, there are normally some number of idle sessions. Is it possible for these idle sessions to harm Netezza performance, for instance doe

Select a range based on another column

Given a (sample) table like this: PERIOD ORD DATA... Dec16 1 Sep16 3 Aug16 4 Jul16 5 Jun16 7 May16 8 Apr16 9 Mar16 11 Feb16 12 Jan16 13 Dec15 15 Nov15 16 Oct15 17 Sep15 19 Aug15 20 Jul15 21 Jun15 23 May15 24 Apr15 25 Mar15 27 Feb15 28 Jan15 29 where

With the execution of the clause

I've always thought that With clause was working as a one-time execute statement, which behaves as a normal table - you can do all SQL operations on it as you would on a regular table. But it turned out that in several databases(Oracle, Netezza, Syba

Best way to load 2000 files faster in SSIS

I currently am optimizing an SSIS package that processes approximately 2000 files when it runs and loads it into an ETL table. It is a simple foreach loop with fastload into a netezza table. Obviously, it takes quite a bit of time, mostly because eac

Netezza GROUP BY sub-column converting from Sybase

I encountered a problem while converting from Sybase to Netezza. In example ill simplify the SELECT. While this Sybase select works: SELECT t1.col1, CONVERT(CHAR(20), ymd(year(t1.date_col ),month(t1.date_col ),1) ,112)id_date, CONVERT(CHAR(20), ymd(y

Equivalent to less in netezza

I want to compare data between two different db tables in netezza. In oracle we can do that by minus operator. How can the same operation be done in netezza? SELECT CUSTOMER_SRC_ID,CUSTOMER_SRC_DESC FROM CIDB_SIT..CUSTOMER_SRC MINUS SELECT CUSTOMER_S

Overlapping dates in SQL

I have a table listing all of the times that a patient has received any type of medication that looks something like this: Pat ID Type of Admin Order ID Start Date End Date 1 Medication 1 01/01/2016 04:20 01/04/2016 05:30 1 Medication 2 01/03/2016 18

Netezza Stored Procedure Date Argument

I really need someone saving me here. I get this error when running the below stored procedure: x and y are Date columns and z an integer one. I used to work around these errors. But this one is very persistent! I used all ways of adding comma around

SQL - Aggregation over time series

I need to find aggregate per second data for a field in a table which may have one or more records every second, as in table1: table1 Time field1 09:37.43.755 0 09:37.44.256 0 09:37.44.757 0 09:37.44.758 1 09:37.45.756 0 09:37.45.759 1 09:37.46.758 1

How to create 'Macro' in netezza sql

I have to create several tables using the same commands except using different year's of data. For example: create temp table test_2002 as select * from claim where purchase_year = '2002'; create temp table test_2003 as select * from claim where purc

Converting a mixed fraction to a floating number in Netezza

I have a field with numbers stored as text in 3 formats: xx. (example: 31.) xx.x (example: 31.2) xx x/x (example: 31 2/7) For the final result, I need all numbers to be in decimal format (that is, xx.x). Converting the first two formats into decimals

Merge multiple columns into a single column using sql

I have data into below format : Col1 Col2 Col3 Col4 ABC 12 34 45 I want output as below : Col1 Col2 ABC 12 ABC 34 ABC 45 You can use union: select col1, col2 union all select col1, col3 union all select col1, col4

Dbfit for Netezza does not work

Hi I am new to dbFit and while trying to use dbFit for Netezza giving error Cannot load Netezza database driver org.netezza.Driver. Code and error details are as below: Code: !path lib/*.jar !| dbfit.NetezzaTest | !| Connect | ip | user| password | d

Difference Between Minus and Subquery

i want to know what is the difference between Minus and subquery in the below example. I got 2 different results for both. Basically i am trying to find records that exist in the first but not the 2nd table. Query 1: select count(*) from ( (select Or

Drop if exists in netezza

I need a command to delete a table if that exists in NETEZZA, Something like that: drop table if exists xxx; I have searched and tried many but it didn't work. Can you help me here?In netezza you can use this syntax: drop table table_name if exists;

Comparison of Netezza strings

I have 2 columns string and Character. My requirement is to compare those 2, if the second column(char) is available in first column(string) the answer should be some value(say 1). I tried using translate but it doesn't work. Any functions available

Python pyodbc connections to IBM Netezza Erroring

So. This issue is almost exactly the same as the one discussed here -- but the fix (such as it is) discussed in that post doesn't fix things for me. I'm trying to use Python 2.7.5 and pyodbc 3.0.7 to connect from an Ubuntu 12.04 64bit machine to an I

Netezza INSERT - ignore invalid dates?

Is there an option or setting that will tell Netezza to ignore invalid dates ? The entire INSERT fails in the example below because the 3rd row of the source table has an invalid date. I would expect that the offending row may be skipped, but to inse

Subqueries in Netezza

I am trying to port a SQL from Oracle 11 G to Netezza. Here is my query in Oracle: SELECT ID, TEACHERID, CLASS, SECTION, MAJOR, SUPERVISOR, COURSE, SCORE, SCOREDATE, (select SCORE from STUDENT_SCORES d2 where d2.ID=d1.ID and d2.SCOREDATE (d1.SCOREDAT

Netezza Incremental Load From SQL Server Using SSIS

I am trying to do a incremental load from Sql server 2008 to Netezza (Nps6) using SSIS. Netezza 5.x version OLEDB driver used. I am using Table or View - Fast Load option with Maximum insert commit size = 0. Here I am trying to insert few thousands o

create an external table with headers in netezza (postgres)

I am creating an external table as shown below CREATE EXTERNAL TABLE '~\test.csv' USING ( DELIMITER ',' Y2BASE 2000 ENCODING 'internal' REMOTESOURCE 'ODBC' ESCAPECHAR '\' ) AS SELECT * FROM TEST_TABLE; It works fine. My question is : Is there a way w

Query Optimization (Netezza)

I am trying to solve a problem in Netezza for a friend of mine. My friend is trying to do something really simple in Netezza: select * from test where ID1 = 12345 and date >= 12/1/2012 and event date <= 12/31/2012 and ID2 in (x1, x2, ..., x150000) T

Finding Dates - SQL

I have the following from and to dates and need to list using a dynamic sql query only the 3 dates that are mentioned below in netezza database From To 20120603 20120831 Required Result: 20120702 20120703 20120801 Is there a possibility using dynamic

Netezza SQL - gives a parameter

SELECT to_char(START_D,???) The issue I have above is that I can't seem to find the right parameter for the above to work. The date is numeric e.g 1102. I want to turn this into a character. Actually even better is to turn the numeric field to 11/02,

Does Netezza have an ODBC DESCRIBE function?

I would like to DESCRIBE a table held on Netezza (so can see the variable formats, distribute on clause etc). I know this is possible (natively) using \d <tablename> However I am using SAS to connect (via ODBC). Is this possible using SAS 9.1.3 code