Netezza / PureData – How To Get A List Of When A Store Procedure Was Last Changed Or Created

Netezza / Puredata - SQL (Structured Query Language)

Netezza / Puredata – SQL (Structured Query Language)

In the continuing journey to track down impacted objects and to determine when the code in a database was last changed or added, here is another quick SQL, which can be used in Aginity Workbench for Netezza to retrieve a list of when Store Procedures were last updated or were created.

SQL List of When A Stored Procedure was Last Changed or Created

select t.database — Database
, t.OWNER — Object Owner
, t.PROCEDURE — Procedure Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime

from _V_OBJECT o
, _v_procedure t
where
o.objid = t.objid
and t.DATABASE = ‘<<Database Name>>
order by o.objmodified Desc, o.objcreated Desc;

 

Related References

 

Netezza / PureData – How To Get a SQL List of When View Was Last Changed or Created

Netezza / PureData SQL (Structured Query Language)

Netezza / PureData SQL (Structured Query Language)

Sometimes it is handy to be able to get a quick list of when a view was changed last.  It could be for any number of reason, but sometimes folks just lose track of when a view was last updated or even need to verify that it hadn’t been changed recently.  So here is a quick SQL, which can be dropped in Aginity Workbench for Netezza to create a list of when a view was created or was update dated last.  Update the Database name in the SQL and run it.

SQL List of When A view was Last Changed or Created

select t.database — Database
, t.OWNER — Object Owner
, t.VIEWNAME — View Name
, o.objmodified — The Last Modified Datetime
, o.objcreated — Created Datetime

from _V_OBJECT o
,_V_VIEW_XDB t
where
o.objid = t.objid
and DATABASE = ‘<<Database Name>>
order by o.objcreated Desc, o.objmodified Desc;

Related References

 

Netezza / PureData – Table Describe SQL

Netezza Puredata Table Describe SQL

Netezza / Puredata Table Describe SQL

If you want to describe a PureData / Netezza table in SQL, it can be done, but Netezza doesn’t have a describe command.  Here is a quick SQL, which will give the basic structure of a table or a view.  Honestly, if you have Aginity Generating the DDL is fast and more informative, at least to me.  If you have permissions to access NZSQL you can also use the slash commands (e.g. \d).

Example Netezza Table Describe SQL

select  name as Table_name,

owner as Table_Owner,

Createdate as Table_Created_Date,

type as Table_Type,

Database as Database_Name,

schema as Database_Schema,

attnum as Field_Order,

attname as Field_Name,

format_type as Field_Type,

attnotnull as Field_Not_Null_Indicator,

attlen as Field_Length

from _v_relation_column

where

name='<<Table Name Here>>’

Order by attnum;

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Command-line options for nzsql, Internal slash options

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza getting started tips, About the Netezza data warehouse appliance, Commands and queries, Basic Netezza SQL information, Commonly used nzsql internal slash commands

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL introduction, The nzsql command options, Slash options

 

 

Netezza / PureData – Substring Function Example

SQL (Structured Query Language), Netezza PureData – Substring Function Example, Substr

Netezza / PureData – Substring Function Example

The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string.

Substring Functions Basic Syntax

SUBSTRING Function Syntax

SUBSTRING(<<CharacterField>>,<< StartingPosition integer>>, <<for Number of characters Integer–optional>>)

 

SUBSTR Function Syntax

SUBSTR((<>,<< StartingPosition integer>>, <>)

 

Example Substring SQL

Netezza / PureData Substring Example

Netezza / PureData Substring Example

Substring SQL Used In Example

SELECT  LOCATIONTEXT

— From the Left Of the String

— Using SUBSTRING Function

,’==SUBSTRING From the Left==’ as Divider1

,SUBSTRING(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,7,6) as Middle_Using_SUBSTRING_LFT

,SUBSTRING(LOCATIONTEXT,15) as End_Using_SUBSTRING_LFT

,’==SUBSTR From the Left==’ as Divider2

—Using SUBSTR Function

 

,SUBSTR(LOCATIONTEXT,1,5) as Beggining_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,7,6) as Middle_Using_SUBSTR_LFT

,SUBSTR(LOCATIONTEXT,15) as End_Using_SUBSTR_LFT

— From the right of the String

,’==SUBSTRING From the Right==’ as Divider3

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTRING_RGT

,SUBSTRING(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTRING_RGT

,’==SUBSTR From the right==’ as Divider4

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-18, 8) as Beggining_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-9, 6) as Middle_Using_SUBSTR_RGT

,SUBSTR(LOCATIONTEXT,LENGTH(LOCATIONTEXT)-1) as End_Using_SUBSTR_RGT

FROM BLOG.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’;

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Substring Function On Specific Delimiter

SQL (Structured Query Language), Netezza / PureData - Substring Function On Specific Delimiter, substr

Netezza / PureData – Substring Function On Specific Delimiter

The function Substring (SUBSTR) in Netezza PureData provides the capability parse character type fields based on position within a character string.  However, it is possible, with a little creativity, to substring based on the position of a character in the string. This approach give more flexibility to the substring function and makes the substring more useful in many cases. This approach works fine with either the substring or substr functions.  In this example, I used the position example provide the numbers for the string command.

 

Example Substring SQL

Netezza PureData Substring Function On Specific Character In String, substring, substr

Netezza PureData Substring Function On Specific Character In String

 

Substring SQL Used In Example

select LOCATIONTEXT

,position(‘,’ in LOCATIONTEXT) as Comma_Postion_In_String

—without Adjustment

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)) as Substring_On_Comma

—Adjusted to account for extra space

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Substring_On_Comma_Ajusted

,’==Breaking_Up_The_Sting==’ as Divider

— breaking up the string

,SUBSTRING(LOCATIONTEXT,1, position(‘ ‘ in LOCATIONTEXT)-1) as Beggining_of_String

,SUBSTRING(LOCATIONTEXT,position(‘ ‘ in LOCATIONTEXT)+1, position(‘ ‘ in LOCATIONTEXT)-1) as Middle_Of_String

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as End_Of_String

 

FROM Blog.D_ZIPCODE

where STATE = ‘PR’

AND CITY = ‘REPTO ROBLES’

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

Netezza / PureData – Position Function

SQL (Structured Query Language), Netezza PureData Position Function, SQL, Position Function

Netezza / PureData Position Function

 

The position function in Netezza is a simple enough function, it just returns the number of a specified character within a string (char, varchar, nvarchar, etc.) or zero if the character not found. The real power of this command is when you imbed it with character function, which require a numeric response, but the character may be inconsistent from row to row in a field.

The Position Function’s Basic Syntax

position(<<character or Character String>> in <<CharacterFieldName>>)

 

Example Position Function SQL

Netezza PureData Position Function, SQL, Position Function

Netezza PureData Position Function

 

Position Function SQL Used in Example

select LOCATIONTEXT, CITY

,’==Postion Funtion Return Values==’ as Divider

,position(‘,’ in LOCATIONTEXT) as Postion_In_Nbr_String

,position(‘-‘ in LOCATIONTEXT) as Postion_Value_Not_Found

,’==Postion Combined with Substring Function==’ as Divider2

,SUBSTRING(LOCATIONTEXT,position(‘,’ in LOCATIONTEXT)+2) as Position_Used_in_Substring_Function

FROM Blog.D_ZIPCODE  where STATE = ‘MN’ AND CITY = ‘RED WING’ limit 1;

 

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

 

PureData / Netezza – What date/time ranges are supported by Netezza?

SQL (Structured Query Language), Date/Time ranges supported by Netezza

Date/Time ranges supported by Netezza

Here is a synopsis of the temporal ranges ( date, time, and timestamp), which Netezza / PureData supports.

Temporal Type

Supported Ranges

Size In Bytes

Date

A month, day, and year. Values range from January 1, 0001, to December 31, 9999. 4 bytes

Time

An hour, minute, and second to six decimal places (microseconds). Values range from 00:00:00.000000 to 23:59:59.999999. 8 bytes

Related References

Temporal data types

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Data types, Temporal data types

Netezza date/time data type representations

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza user-defined functions, Data type helper API reference, Temporal data type helper functions, Netezza date/time data type representations

Date/time functions

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Date/time functions

Netezza / PureData – How to add a Foreign Key

DDL (Data Definition Language), Netezza PureData How to add a Foreign Key

DDL (Data Definition Language)

Adding a forging key to tables in Netezza / PureData is a best practice; especially, when working with dimensionally modeled data warehouse structures and with modern governance, integration (including virtualization), presentation semantics (including reporting, business intelligence and analytics).

Foreign Key (FK) Guidelines

  • A primary key must be defined on the table and fields (or fields) to which you intend to link the foreign key
  • Avoid using distribution keys as foreign keys
  • Foreign Key field should not be nullable
  • Your foreign key link field(s) must be of the same format(s) (e.g. integer to integer, etc.)
  • Apply standard naming conventions to constraint name:
    • FK_<<Constraint_Name>>_<<Number>>
    • <<Constraint_Name>>_FK<<Number>>
  • Please note that foreign key constraints are not enforced in Netezza

Steps to add a Foreign Key

The process for adding foreign keys involves just a few steps:

  • Verify guidelines above
  • Alter table add constraint SQL command
  • Run statistics, which is optional, but strongly recommended

Basic Foreign Key SQL Command Structure

Here is the basic syntax for adding Foreign key:

ALTER TABLE <<Owner>>.<<NAME_OF_TABLE_BEING_ALTERED>>

ADD CONSTRAINT <<Constraint_Name>>_fk<Number>>

FOREIGN KEY (<<Field_Name or Field_Name List>>) REFERENCES <<Owner>>.<<target_FK_Table_Name>.(<<Field_Name or Field_Name List>>) <<On Update | On Delete>> action;

Example Foreign Key SQL Command

This is a simple one field example of the foreign key (FK)

 

ALTER TABLE Blog.job_stage_fact

ADD CONSTRAINT job_stage_fact_host_dim_fk1

FOREIGN KEY (hostid) REFERENCES Blog.host_dim(hostid) ON DELETE cascade ON UPDATE no action;

Related References

Alter Table

PureData System for Analytics, PureData System for Analytics 7.2.1, IBM Netezza database user documentation, Netezza SQL command reference, Alter Table, constraints

 

 

Database – What is a foreign key?

Acronyms, Abbreviations, Terms, And Definitions, DDL (Data Definition Language), What is a foreign key

Acronyms, Abbreviations, Terms, And Definitions

Definition of a Foreign Key

  • A foreign Key (FK) is a constraint that references the unique primary key (PK) of another table.

Facts About Foreign Keys

  • Foreign Keys act as a cross-reference between tables linking the foreign key (Child record) to the Primary key (parent record) of another table, which establishing a link/relationship between the table keys
  • Foreign keys are not enforced by all RDBMS
  • The concept of referential integrity is derived from foreign key theory
  • Because Foreign keys involve more than one table relationship, their implementation can be more complex than primary keys
  • A foreign-key constraint implicitly defines an index on the foreign-key column(s) in the child table, however, manually defining a matching index may improve join performance in some database
  • The SQL, normally, provides the following referential integrity actions for deletions, when enforcing foreign-keys

Cascade

  • The deletion of a parent (primary key) record may cause the deletion of corresponding foreign-key records.

No Action

  • Forbids the deletion of a parent (primary key) record, if there are dependent foreign-key records.   No Action does not mean to suppress the foreign-key constraint.

Set null

  • The deletion of a parent (primary key) record causes the corresponding foreign-key to be set to null.

Set default

  • The deletion of a record causes the corresponding foreign-keys be set to a default value instead of null upon deletion of a parent (primary key) record

Related References

 

Netezza / PureData – How to rebuild a Netezza view in Aginity

How To Generate View or table DDL in Aginity For Netezza PureData

How To Generate View or table DDL in Aginity For Netezza

 

Rebuilding Netezza view sometimes becomes necessary when the view’s source table have changed underneath the view.  Rebuilding a view can be done on Netezza or in Aginity. In Aginity, it is a simple process, assume your user has permissions to create or replace a view.  The process breaks down into just a few steps:

Generate the create / replace view SQL of the original view into the query window, if you don’t have it already

In the object browser:

  • Navigate to the Database and view you wish to rebuild
  • Select the view and right click
  • Select ‘Scripts’, then ‘DDL to Query window’

Make may updates to create / replace View SQL

  • This step is not always necessary, sometimes the changes which invalided the view did not actually impact the code of the view. If changes are necessary, make may updates to the SQL code.

Execute The code

  • This I usually do by choosing the ‘Execute as a single batch’ option.  Make sure the code executes successfully.

Verify the view

  • To verify the simply execute a select statement and make it executes without errors and/or warning.

Related References

 

Netezza / PureData Date – Difference in Days SQL

Netezza PureData Date Difference in Days SQL, Subtracting Non-inclusive dates, Subtracting inclusive dates

Netezza /PureData Date Difference in Days

Since Netezza does not have a datediff function, the ‘old school’ of calculating the difference, in days, between dates must be used.

Subtracting Inclusive Dates

To subtract to day and include end date, as a day, in calculation (1 day is added)

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates

From _v_dual;

 

Subtracting Non-inclusive dates

To subtract dates non-inclusive simply subtract the dates

select date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Example SQL From Graphic

select (date(‘2015-12-31’) – date(‘2015-01-01’))+1 as Inclusive_dates,

date(‘2015-12-31’) – date(‘2015-01-01’) as Non_Inclusive_dates

From _v_dual;

 

Related References

What are the types of Database Management Systems (DBMS)?

Database Management Systems (DBMS)

Database Management Systems (DBMS)

It is important to understand the differences between Database Management Systems (DBMS) types, since the structure of each type will influence integrations approaches, functionality, overall speed, and scalability.

The Five Types of Database Management Systems (DBMS)?

The five basic types of databases are:

  • Hierarchical
  • Object-Orientated
  • Network
  • Relational
  • Flat File

Hierarchical Database (HDB)

  • A hierarchical database (HDB or HDBMS)is a design that uses a one-to-many relationship for data elements. Hierarchical database models use a tree structure that links several disparate elements to one “owner,” or “parent,” primary record.

Object-Oriented Database (OODB)

  • Object-Orientated databases (OODB or OODBM) integrate object orientation with database capabilities. Object orientation allows a more direct representation and modeling of real-world problems, and database functionality is needed to ensure persistence and concurrent sharing of information in applications.

Network Database (NDB)

  • Network databases (NDB or NDBMS) are quite like hierarchical databases, except it allows multiple records to be linked to the same owner file. The model can be seen as an upside down tree where the branches are the member information linked to the owner, which is the bottom of the tree. The multiple linkages which this information allows the network database model to be very flexible. In addition, the relationship that the information has in the network database model is defined as many-to-many relationship because one owner file can be linked to many member files and vice versa.

Relational Database (RDB)

  • In simplest terms, a relational database (RDB or RDBMS)is one that presents information in formally described tables with rows and columns. A table is referred to as a relation in the sense that it is a collection of objects of the same type (rows). Data in a table can be related per common keys or concepts, and the ability to retrieve related data from a table, which is the basis for the term relational database. Data can be accessed or reassembled in many ways without having to reorganize the database tables structure.

Flat File Database (FFDB)

  •  A flat file database (FFDB or FFDBM) describes any of various means to encode a database model as a single file or collection of files, which can be a plain text file or a binary file. There are usually no structural relationships between the records. Each line of the text file holds one record, with fields separated by delimiters, such as commas or tabs. This is a very old database approach, but can still be found in use to, often with some relation capability enhancements,.  Some example of current flat file databases are: GRAV, Jekyll, Kerby, and Monstra.

Related References

 

Database – What is a Composite Primary Key?

Database Table

Database Table

What is a Composite Primary Key?

A Composite Primary key is Primary key What a primary key, which is defined by having multiple fields (columns) in it.  Like a Primary Key what a composite Primary Key is depends on the database.  Essentially a Composite Primary Key:

  • Is a combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And may define parent-Child relationship between tables

Related References

Database – What is a Primary Key?

Database Table

Database Table

What is a primary Key?

What a primary key is depends, somewhat, on the database.  However, in its simplest form a primary key:

  • Is a field (Column) or combination of Fields (columns) which uniquely identifies every row.
  • Is an index in database systems which use indexes for optimization
  • Is a type of table constraint
  • Is applied with a data definition language (DDL) alter command
  • And, depending on the data model can, define parent-Child relationship between tables

Related References

Netezza / PureData – Substring Function

SQL (Structured Query Language), Netezza Puredata Substring Function, substr, substring

Netezza Puredata Substring Function

 

Substring is a common enough function in SQL, however, the exact language format used to perform this function can vary from one database to another.  So, here are a few quick notes on the substring format in Netezza / PureData.

What is the purpose of a substring?

SUBSTRING allows SQL to extract part of a string with a field, which in Netezza / PureData is based on positions with the string.

Substring Function Syntax

Substring(<<Input Field>>)from <<start-position>> [for <<length in Characters>>]).

 

Notes:
·        Square brackets ‘[ ]’ indicate optional content, which can be useful under certain circumstances
·        If you do not specify the optional for length [for <<length in Characters>>], then the remained of the string will be returned

Example Substring SQL Using ‘For Length’ property

Netezza / Puredata Substring Function

Netezza / Puredata Substring Function

Example Substring SQL

SELECT  SUBSTRING(DD.DATE_SRKY FROM 1 FOR 4)  as  First_Four_Digits

, SUBSTRING(DD.DATE_SRKY FROM 5 FOR 2)  as  Middle_Two_Digits

, SUBSTRING(DD.DATE_SRKY FROM 7 FOR 2)  as  Last_Two_Digits

,DD.DATE_SRKY

FROM DATE_DIM DD

Order by DD.DATE_SRKY DESC

 

Example Substring SQL without the optional ‘For Length’ property

Netezza String Function Without For Length Property

Netezza String Function Without For Length Property

Example Substring SQL

SELECT  SUBSTRING(DD.DATE_SRKY FROM 5 )  as  Remainder_String_From_Position_5

, SUBSTRING(DD.DATE_SRKY FROM 7 )  as  Remainder_String_From_Position_7

,DD.DATE_SRKY

FROM DATE_DIM DD

Order by DD.DATE_SRKY DESC

 

Example Substring SQL which bring the parts together in a new format

Netezza / Puredata Substring Function Reassembled As Date

Netezza / Puredata Substring Function Reassembled As Date

 

Example Substring SQL

SELECT  SUBSTRING(DD.DATE_SRKY FROM 1 FOR 4)||’-‘||

SUBSTRING(DD.DATE_SRKY FROM 5 FOR 2)||’-‘||

SUBSTRING(DD.DATE_SRKY FROM 7 FOR 2) ||  ‘ 00:00:00’ as Reassembled_As_Date_Format

,DD.DATE_SRKY

FROM DATE_DIM DD

Order by DD.DATE_SRKY DESC

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL command reference, Functions

 

 

 

Netezza / PureData – Row number within a group

Netezza / PureData – row number within a group

SQL (Structured Query Language)

Sometimes it is useful to be able number data with it groups, which can be quickly and easily done suing the Row Number function in Netezza. However, you do need to pay particular attention to you ‘partition by’ (conceptually a group by operation) field list and field order, as well as, you ‘Order by’ field list.  Otherwise, you can get some bizarre  results.  it find it helpful to think of them as having a parent, child, relationship.  In this line of thought, your ‘partition by’ field list should usually have one less more field than than your ‘order by’.  This is not an absolute rule, but under normal circumstances your  ‘order by’ field list should contain all the field in your ‘partition by’  and, occasionally, provide a orby direction (ascending or descending).

SQL Netezza Row Number Format

ROW_NUMBER() OVER( PARTITION BY <<Partition_By_Field_list>> ORDER BY <<Sort_By_Field_List [direction asc/desc]>> ) as ROW_NUM

 

Example Row Number SQL

Row Number Within A Group

Row Number Within A Group

SELECT

qh_database

,qh_sessionid

, qh_tend

,ROW_NUMBER() OVER( PARTITION BY qh_database, qh_sessionid

ORDER BY qh_database, qh_sessionid, qh_tend desc ) as ROW_NUM

FROM nz_query_history

where qh_database = ‘system’

order by qh_database, qh_sessionid, qh_tend desc

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Netezza SQL extensions, Character string functions

IBM Knowledge Center, PureData System for Analytics, Version 7.1.0

IBM Netezza Database User’s Guide, Netezza SQL basics, Functions and operators, Functions, Standard string functions

 

 

 

 

 

 

Netezza / PureData – Case Statement Example

Netezza PureData Case statement Function example

Netezza PureData Case Function

The Case Statement, is one which I occasionally use within Netezza/PureData SQL and Stored Procedures.  Basically, the Case Function provides an ‘IF-THEN-ELSE’ decision capability.  This ‘IF-THEN-ELSE’ capability allows the evaluation conditions and return a value when the first condition is met and/ or else break out logic, if the condition or conditions are not met.

Case Function Language Structure

·       Case Function Basic Structure

CASE

WHEN <search-condition-1> THEN <result-1>

WHEN <search-condition-2> THEN <result-2>

WHEN <search-condition-n> THEN <result-n>

ELSE <default-result>

END

·       Case Function Simple Structure

CASE

WHEN <search-condition-1> THEN <result-1>

ELSE <default-result>

END

Case Function Embedded in SQL Select Example

Case Function Embedded In SQL Select Statement

Case Function Embedded In SQL Select Statement

 

SQL Used in Embedded Example

Select

now() as “Time”,

current_date as “Today”,

(

case

when (DATE_PART(‘HOUR’,NOW())>12 )

THEN date(current_date +  cast(‘1 days’ as interval))

ELSE  current_date

END ) as “PROCESSING_DATE”

From ADMIN._v_dual;

 

Related References

IBM Knowledge Center, PureData System for Analytics, Version 7.2.1

IBM Netezza database user documentation, Netezza SQL basics, Functions and operators, Functions