2

I am developing an application which uses external datasources. My Application supports multiple databases(viz. MySQl,MsSQl,Teradata, Oracle, DB2 etc.). When i create a datasource, I allow user to assign a primary key(pk) to the datasource. Now, I am not checking if the user selected column is primary key or not in actual database. I just want that, while retrieving data from database, the records which have null/blank value in user selected primary key should get dropped. I have created a filter supporting all other databases except for DB2 and Teradata.

Sample Query for other databases:

  1. Select * from MY_TABLE where PK_COLUMN IS NOT NULL and PK_COLUMN !='';
  2. Select * from MY_TABLE where PK_COLUMN IS NOT NULL AND cast(PK_COLUMN as varchar) !=''

DB2 and Teradata:

The PK_COLUMN !='' and cast(PK_COLUMN as varchar) !='' conditions gives error for int datatype in DB2 and teradata because: - column with int type cannot be gven the above mentioned conditions and also we cannot cast the int type columns to varchar directly in DB2 and Teradata.

I want to create a query to drop null/blank value from the database provided table name and user pk column name as string. (I do not know the pk_column_type while creating the query so the query should be uniform to support all datatypes)

NOTE: The pk here is not actual pk, it is just a dummy pk assigned by my application user. So this can be a normal column and thus can have null/blank values.

I have created a query as:

Select * from MY_TABLE where PK_COLUMN IS NOT NULL AND cast(cast(PK_COLUMN as char) as varchar) !=''

My Question:

  1. Will this solution(double casting) support all datatypes in DB2 and Teradata?
  2. If not, can I come up with a better solution?
Chetan Kole
  • 137
  • 2
  • 7

1 Answers1

1

Of course you can cast an INT to a VARCHAR in both Teradata and DB2, but you have to specify the length of a VARCHAR, there's no default length.

Casting to a CHAR without a length defaults to CHAR(1) in Standard SQL, which might cause some "string truncation" error.

You need to cast to a VARCHAR(n) where n is the maximum length based on the DBMS.

Plus there's no != operator in SQL, this should be <> instead.

Finally there's a fundamental difference between an empty string and a NULL (except on Oracle), one or more blanks might also have a meaning and will be filtered when compared to ''.

And what is an empty INT supposed to be? If your query worked zero would be casted to '0' which is not equal to '', so it would fail.

You should simply use IS NOT NULL and add a check for an empty string only for character column (and add an option for the user to decide if an empty string is equal to NULL).

dnoeth
  • 59,503
  • 4
  • 39
  • 56