All Forums General
jonnykhan 1 post Joined 12/13
06 Dec 2013
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

M.Saeed Khurram 544 posts Joined 09/12
06 Dec 2013

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

You must sign in to leave a comment.