join

[denis115]

Hello! I've got a problem! help me please.

I want to join 2 tables ,they both have same fields the only one difference is place FROM where it exctracting those files. 

-----Table 1   :

Prime_Price:
select
rownrs,
prodid as "ИД ТМЦ",
DOCDATE as "Дата",
StoreID as "ИД Место хранение",
QTY as "Кол-во себ.",
PRICE_COST as "Пр. По Прайсу",
PRIME_COST as "Пр. Себес.",
DOC_COST as "Пр. по Чеку",
DOCTYPE as "Тип Докум."
from
sm_sales;

----Table 2 :

//same fields like "Table 1"

from sm_purch;

the error is : column ambiguously defined

THANK YOU !

[denis115]

--------------------TABLE 1

Prime_Price:
select
rownrs,
prodid as "ИД ТМЦ",
DOCDATE as "Дата",
StoreID as "ИД Место хранение",
QTY as  "Кол-во себ.",
PRICE_COST as "Пр. По Прайсу",
PRIME_COST as "Пр. Себес.",
DOC_COST as "Пр. по Чеку",
DOCTYPE as "Тип Докум."
from
vbnz.sm_sales_with_prime_price
where docdate>='01.JAN.2015';
//where docdate >= '$(BeginningThisExecTime)' ;

Concatenate (Prime_Price) LOAD
ROWNRS
,"ИД ТМЦ"
, "Дата"
,"ИД Место хранение"
,"Кол-во себ."
,"Пр. По Прайсу"
,"Пр. Себес."
,"Пр. по Чеку"
,"Тип Докум."
from Prime_Price.qvd (qvd)
where not exists (ROWNRS) ;

Inner Join
sql
select ROWNRS
from vbnz.sm_purch_with_prime_price
where docdate >= '01.JAN.2015' ;

STORE Prime_Price into Prime_Price.qvd;

------------------TABLE 2

Prime_Purchase:
select
ROWNRS,
PRODID as "ИД ТМЦ",
DOCDATE as "Дата",
STOREID as "ИД Место хранение",
QTY as  "Кол-во себ.",
PRICE_COST as "Пр. По Прайсу",
PRIME_COST as "Пр. Себес.",
DOC_COST as "Пр. по Чеку",
DOCTYPE as "Тип Докум."
from
vbnz.sm_purch_with_prime_price
//where docdate>='01.JAN.2015';
where docdate >= '01.JAN.2015' ;

Concatenate (Prime_Purchase) LOAD
ROWNRS
,"ИД ТМЦ"
, "Дата"
,"ИД Место хранение"
,"Кол-во себ."
,"Пр. По Прайсу"
,"Пр. Себес."
,"Пр. по Чеку"
,"Тип Докум."
from Prime_Purchase.qvd (qvd)
where not exists (ROWNRS) ;

Inner Join
sql
select ROWNRS
from vbnz.sm_purch_with_prime_price
where docdate >= '01.JAN.2015' ;

STORE Prime_Purchase into Prime_Purchase.qvd;

[PrashantSangle · Accepted solution]

Hi,

Use noconcatenate before

Prime_Pruchase:

Select...

Regards,

Prashant

[denis115]

Thank you! I thing that's what I need.

[eduardo_dimperio]

Hi Denis, i saw that you solve your problem, but just answer you.

Yes, if you use concatenate you need both tables, to concatenate one in another, cause the compiler need to know what input you'll use (what tables in that case). In your case you can use inner join, case all fields are the same.

But i recomend this material to better understand the concept (i use all the time )

Understanding Join, Keep and Concatenate

Good Luck