Join into new data

buzmay · #1 (**permalink**) 01-19-12, 06:51

Hi guys,

I need help with joining 2 tables and putting the result into a new table.
Table export has the following columns:
Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k

Table cc_split has the following columns:
Company, Fun_Account, CC_Account, x, y, Split

The new table called export2 has exactly the same values as export i.e. :
Period, Actual, Company, Currency, Account, Amount, a,b,c,d,e,f,g,h,i,j,k

So what I need to join from export and split into export2 are company, cc_account (that shall go into account column of export2 together with the account of export) and the amount, which shall be ( split column * amount column of export), the rest values shall be the same.

So what I have so far is

Code:

 INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT,
                                                EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY,
                                                COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE)
                SELECT COMPANY, CC_ACCOUNT, SPLIT
                FROM EXP_DATA INNER JOIN CC_SPLIT ON EXP_DATA.COMPANY=CC_SPLIT.COMPANY AND
                EXP_DATA.ACCOUNT = CC_SPLIT.CC_ACCOUNT
                WHERE EXP_DATA.PERIOD IN ('1102', '1103', '1104')]

So at the end basically table exp_data 2, shall have the same values as exp_data but the account column shall include the same values of account column of exp_data plus the values of cc_account of cc_split table. And also the amount column shall be the multiplication of the column split from cc_split * amount of exp_data

thanks in advance

I have realised that I need an alias, maybe a for exp_data and b for cc_split.

Wim · #2 (**permalink**) 01-19-12, 09:04

I have only thrown a shallow look at your query.
You will at least have to synchronise the destination columns and the source columns. There MUST be a 1 to 1 correlation between those. You specified more than 15 columns at the destination, and only 3 at the source side.

Code:

INSERT INTO EXP_DATA_2(PERIOD, ACTUALITY, COMPANY, CURRENCY, ACCOUNT, AMOUNT,
                   EXTDIM1, EXTDIM2, EXTDIM3,EXTDIM4,JOURNAL_TYPE,C_COMPANY,
                   COMPANY,C_DIM, TRANAMOUNT, TRANCURR,REGION, TRANSF_DATE)
                SELECT COMPANY, CC_ACCOUNT, SPLIT

When you specify 15 columns on the destination side, you also have to specify 15 at the source side, but they don't have to have the same name, only the position is important (and the data types, they must be compatible).

buzmay · #3 (**permalink**) 01-19-12, 09:08

Hi Wim,

Thanks for the answer I realized that, so far what I have is this

Code:

INSERT INTO EXP_DATA_2
SELECT A.PERIOD, A.ACTUALITY, A.COMPANY, (B.CC_ACCOUNT)ACCOUNT, (B.SPLIT * A.AMOUNT)AMOUNT,A.EXTDIM1,A.EXTDIM2,
           A.EXTDIM3,A.EXTDIM4,A.JOURNAL_TYPE,A.C_COMPANY,A.C_DIM,A.TRANAMOUNT,A.TRANCURR,A.REGION,
           A.TRANSF_DATE
FROM EXP_DATA AS A,
CC_SPLIT AS B
WHERE A.COMPANY = B.COMPANY AND A.ACCOUNT = B.FUNC_ACCOUNT
AND A.PERIOD IN ('1102', '1103', '1104')

But I get column of name or number of supplied values does not match. Any ideas?

Wim · #4 (**permalink**) 01-19-12, 09:35

Write down each and every column on both the source and destination side.

Don't use
INSERT INTO TableName SELECT ...
on the destination side, but
INSERT INTO TableName (Column1, column2, ...) SELECT ...

Don't use
SELECT * FROM ...
on the source side but
SELECT ColumnA, ColumnB, .... FROM ...

The easier you think you're making it for yourself, the harder you get hit in reality.

buzmay · #5 (**permalink**) 01-23-12, 08:21

Thanks for the advice Wim, I figure it out by not complicating myself

Thanks for the help

Internet Services

Web Development

Digital Marketing

Consumer Tech