Dummy value for date attribute while doing UNION - SELECT query in DQL

Hi,

I am doing UNION query of two tables.But the old table is having two additional columns of datatype date than my new table.

But I need the result of both the tables.So is there any default value for date that I can use as dummy variable in DQL.

If I used nulldate or null its throwing parse error.My expectation is when I am doing the UNION query in DQL I need the result from both the tables and if the returned result is from new table(that doesn't have two date columns) then that column should be null.If it is from new table then it should return the actual value.

ex:

select char1,date1,date2 from old table union select char1 from new table.

If I queried like this i will get "incorrect number of columns" in DQL.

So whats the dummy value for date.

Please help me to resolve this.Its urgent.

Find more posts tagged with

Comments

AlanZ

Your message has been moved to the Documentum developer community where it has a better chance of being seen by someone who can answer your question.

aflowers001

When using NULLDATE did you enter DATE(NULLDATE) ? If not then try tthat and if it still fails then pick a date as an invalid one and use that as the value and ignore it in the processing routines, i.e. DATE(’01-01-1970’)

Rgayathri

Hi Andy,

Thanks for the reply.

I have tried using date(nulldate) and its showing parse error.I am already using your second option DATE(’01-01-1970’) only , in my query.But I don't like to hard code.So that only I am searching for other options.

If I add just nulldate itself am getting the same parse error.Thanks.

mszurap

Hi,

You can do a trick, when the "new table" has at least one date column:

select char1,date1,date2,'true' as validdates from old_table union select char1, newtabledate as dummydate_1, newtabledate as dummydate_2,'false' as validdates from new_table

So when you process the results, you can distinguish between the two portions of the select by the "validdates" column.

One more thing: I would use "union all" instead of "union", it is faster and I assume the two result sets do not have "common" values, which must be represented in the result set only once.