CODEWITHSUNDEEP
informix
Nwn
Nwn

Reputation: 571

How to add an additional fake null column to select result in Informix?

I need to select an additional column which does not exist in the current table in order to unload the data in the correct format.

Suppose I have two different tables as follows.

**

tab1 
----
col1 col2 col3

tab2
---
col1 col2

**

Suppose I need to unload 1000 records from tab2 and need to load them into tab1. But there is a mismatch when considering the number of columns. But I'm ok to enter null values for col3 when loading the data into tab1

So my unload command is as follows 

unload to data.unl select col1, col2, null as col3 from tab2;

Then I can load the contents of data.unl into tab1. But my problem is that I get a syntax error when I'm trying to unload the data in that way. Please correct me. Someone can argue that I can use a simple insert command with selecting the data from the tab2. But in my actual use-case it is not possible because data volume is so high and I hope to use the ipload.

Here is my sample error: 

nwn@nwnhost$ echo "select CURRENT as col1,null as col2 from sysdual" | dbaccess sysmaster

Database selected.


  201: A syntax error has occurred.
Error in line 1
Near character position 29


Database closed.

Upvotes: 1

Views: 562

Answers (1)

Jonathan Leffler
Jonathan Leffler

Reputation: 753675

Cast the NULL to the desired type:

SELECT CURRENT AS col1, NULL::INTEGER AS col2
  FROM sysmaster:"informix".sysdual;

Upvotes: 3

Related Questions