cancel
Showing results for 
Search instead for 
Did you mean: 

Why data type sql select statement in IDT is incorrect?

Former Member
0 Kudos

Dear Experts,

I'm facing problems when I select data from 2 tables by using UNION it shows incorrect data as image below:

Please help me.

Best regards,

Chenna Yon

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi chenna,

Hope this helps!

SELECT Sale_ID, Cast(null as Date) as order_date, CAST("Sale Transaction".Sale_Date as date) as s_date

FROM "Sale Transaction"

UNION

SELECT Sale_ID, Cast("Order Transaction".order_date as Date) as order_date, Cast(null as Date) as s_date

FROM "Order Transaction"

Regards,

Answers (2)

Answers (2)

Former Member
0 Kudos

Chenna,

Check the data types for the two columns that you believe are dates - one may not be a date and may need a to_date conversion or similar.

Regards,

Mark

Former Member
0 Kudos

Dear Mark,

How can I set default value to new field(null as order_date) when I create derive table by using UNION in IDT?

If I change the top sql to button than new field(null as s_date) will display blank.

Best regards,

Chenna Yon

Former Member
0 Kudos

I'd imagine that you'd have to cast your null as date, otherwise it will assume it's a varchar

Former Member
0 Kudos

Dear Mark ,

I don't how to cast null value to default date.

Can you provide me sql statement as cast null value to date as default in IDT?

Best regards,

Chenna Yon

Former Member
0 Kudos

Something like CAST(NULL as datetime) would probably be what is needed.

Former Member
0 Kudos

Hi Chenna,

Just an observation, why your second select statement has Order Date in Quotes.(i.e. "Order Date" as order_date)

Can you try selecting the same directly from the list?

I believe Order Date is your database field and not text.

Thanks

Gaurav

former_member4998
Active Contributor
0 Kudos

Hi


Check both the date type as selected as Date or not.

Select the Business Layer à select the objects à Check the data type

And also select the Order date from Order Transaction table as said by Gaurav Saxena

Expand the Order Transaction Table --> Select the Order date