HELP with INSERT INTO statement

General

HELP with INSERT INTO statement

Hi all

am new to this forum and im looking for help on an Insert INto statement. Whenever i try to run the below i am getting an error - Corresponding Select-list expressions are incompatible'

INSERT INTO TRANS_BSE

SELECT t1.start_dt,

t2.ID1,

t2.ID2,

t2.ID3,

t2.Shopcode,

t2.ID_Descritpion,

SUM(t2.volume1) s1,

SUM(t2.value1) s2,

SUM(t2.AM) s3

FROM load_date t1

CROSS JOIN

(

SELECT ID1,

ID2,

ID3,

shopcode,

ID_Description,

Volume1,

Value,

0 AS AM

FROM md_transactions_t2

UNION

SELECT ID1,

ID2,

ID3,

shopcode,

ID_description,

volume1,

value1,

COALESCE(AM,0)

FROM md_transactions_t3

) t2

GROUP BY 1,2,3,4,5,6

;

All help appreciated. thanks

1 REPLY
Enthusiast

Re: HELP with INSERT INTO statement

Hi jonny,

The first thing is you have not provided with the table definition in which you are inserting the rows.

The second thing is this error means that the select statements you are using in UNION do not have same data types for all the columns.

The issue is witht the following select:

SELECT ID1,
ID2,
ID3,
shopcode,
ID_Description,
Volume1,
Value,
0 AS AM
FROM md_transactions_t2
UNION
SELECT ID1,
ID2,
ID3,
shopcode,
ID_description,
volume1,
value1,
COALESCE(AM,0)
FROM md_transactions_t3

As I dont know the data types of these columns, so cant tell which column is actually incompatible. But you can check this and use cast to make compatible, or provide column data types and I will be able to comment. 

Khurram