Help with column associations.

[#1]

Hi, Im having trouble with association on Column. As I understand, If a column has the same name, it should get associated, but Im getting blanks and duplicates in the columns ItemCode & ItemName when I query this 3 tables.

Thank you for your advice,

Jose.

SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;-$#,##0.00';
SET TimeFormat='hh:mm:ss TT';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff] TT';
SET MonthNames='ene;feb;mar;abr;may;jun;jul;ago;sep;oct;nov;dic';
SET DayNames='lun;mar;mié;jue;vie;sáb;dom';

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=Ponderosa;Data Source=sapserver.ponderosa.local;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=JOSEINES-PC;Use Encryption for Data=False;Tag with column collation when possible=False] (XPassword is TWUANBFGBCZaWUZNRC);

noconcatenate
OITM:
sql
SELECT
ItemCode
,ItemName

,CAT.ItmsGrpNam as Categoria
FROM OITM INNER JOIN OITB CAT ON
CAT.ItmsGrpCod = OITM.ItmsGrpCod ;

NOCONCATENATE
entregas:
SQL
SELECT
D.DocDate as Fecha
,D.CardCode as CardCode
,D.CardName as CardName
,D1.ItemCode as ItemCode
,IT.ItemName as ItemName
,D1.Quantity as Quantity_Entregas
,IT.InvntryUOM as InvntryUOM_Entregas
,(IT.SWEight1*D1.Quantity) as Kilos_Entregas
,CONVERT(varchar(20),MONTH(D.DocDate)) + CONVERT(varchar(20),YEAR(D.DocDate)) as Mes
FROM ODLN D INNER JOIN DLN1 D1 ON
D.DocEntry = D1.DocEntry INNER JOIN OITM IT ON
IT.ItemCode = D1.ItemCode;

NOCONCATENATE
pedidos:
SQL
SELECT
R.DocDueDate as Fecha
,R.CardCode as CardCode
,R.CardName as CardName
,R1.ItemCode as ItemCode
,IT.ItemName as ItemName
,R1.Quantity as Quantity_Pedidos
,IT.InvntryUOM as InvntryUOM
,(IT.SWEight1*R1.Quantity) as Kilos_Pedidos
,CONVERT(varchar(20),MONTH(r.DocDate)) + CONVERT(varchar(20),YEAR(r.DocDate)) as Mes
,r1.grssprofit as Contribucion_Marginal_Pedidos
FROM ORDR R INNER JOIN RDR1 R1 ON
R.DocEntry = R1.DocEntry inner JOIN OITM IT ON
IT.ItemCode = R1.ItemCode ;

NOCONCATENATE
OWOR:
sql
SELECT
w.DocNum AS OWOR_DocNum
,w.DocEntry as OWOR_DocEntry
,w.ItemCode
,w.uom AS InvntryUOM
,w.PlannedQty as OWOR_PlannedQty
,w.CmpltQty as OWOR_CmpltQty
,w.PostDate as Fecha
,(SELECT SUM(LineTotal) FROM iGE1 where IGE1.BaseRef = W.DocEntry AND IGE1.BaseType = 202) AS OWOR_Costo_Componente_Real
,(SELECT SUM(LineTotal) FROM IGN1 WHERE IGN1.BaseRef = W.DocEntry AND IGN1.BaseType = 202) AS OWOR_Costo_Producto
,(SELECT SUM(w1.IssuedQty*oitm.Avgprice) FROM WOR1 W1 INNER JOIN OITM ON W1.ItemCode = OITM.ItemCode
WHERE W1.DocEntry = W.DocEntry AND W1.IssueType = 'B' AND IssuedQty > 0) as OWOR_Costo_Extra
FROM OWOR w
WHERE W.Type <> 'D' AND W.CloseDate IS NOT NULL AND W.PostDate > '20100321';

[vgutkovsky · Accepted solution]

First of all, you don't need "noconcatenate" since QV would not concatenate these table by default. Second, if you want to use "as" to rename fields, you will need a preceding load (easiest to generate this using the wizard in the edit script dialog). Third, you typically only want to have 1 key field--if you have >1, it will cause synthetic keys to be generated and this is probably not what you want. You can verify that synthetic keys are being generated by going to the Table Viewer. In your case, your key field should probably be only Itemcode and you should not load Itemname more than once. You might want to consider a new table that will contain only Itemcode and Itemname and use that as a sort of link table...

Regards,

[#3]

Vlad, thank you for your assistance!

Jose.