Ошибка

Четверг, 05 Июня 2014 г. 13:18 + в цитатник

Поправьте кто может с SQL переписываю вьюшку но вместо RIGHT JOIN нужен INNER как видно з SQL запроса, но
INNER JOIN: tbl_ref_Countries.CountryId = tbl_ref_Producers.CountryId выдает ошибку

SELECT DISTINCT 
                         h_tbl_Material.MCode, vw_MaterialStatus.StatusId AS Status, vw_MaterialStatus.StatusName, h_tbl_Material.Brand_lim, 
                         h_tbl_Material.Categ_local, h_tbl_Material.Cat_HF, h_tbl_Material.DescShortUA, h_tbl_Material.DescFullUA, h_tbl_Material.DescShortEN, 
                         h_tbl_Material.DescFullEN, h_tbl_Material.DescShortRU, h_tbl_Material.DescFullRU, vw_MaterialPalletizing_Unit_Case.UC, h_tbl_Material.CP, 
                         h_tbl_Material.ZUN_ZCU, h_tbl_Material.ZCU_CT, h_tbl_Material.CU_W_G, h_tbl_Material.CU_GRSW_G, h_tbl_Material.CS_GRSW_G, 
                         h_tbl_Material.PAL_GRSW_G, h_tbl_Material.EAN13, h_tbl_Material.CertNum, h_tbl_Material.Certificate, h_tbl_Material.CertIssuedDate, 
                         h_tbl_Material.CertValidToDate, h_tbl_Material.CertByBatch, h_tbl_Material.CertInd, h_tbl_Material.VetConf, h_tbl_Material.OriginationID, 
                         h_tbl_Material.Origination, h_tbl_Material.Org, h_tbl_Material.Sourcing, h_tbl_Material.ProducerId, tbl_ref_Producers.ProducerNameEN, 
                         tbl_ref_Producers.ProducerNameShort, tbl_ref_Countries.CountryNameEN AS ProducerCountryEN, 
                         tbl_ref_Countries.CountryNameRU AS ProducerCountryRU, tbl_ref_Countries.CountryNameUA AS ProducerCountryUA, h_tbl_Material.OnStock, 
                         h_tbl_Material.SupplierID, h_tbl_Material.Supplier, tbl_ref_Suppliers.SupplierName, tbl_ref_Suppliers.TrPeriodDays AS TrPeriod, 
                         h_tbl_Material.A1, h_tbl_Material.SLPeriodM, h_tbl_Material.HFPeriodD, h_tbl_Material.HygNum, h_tbl_Material.Hygienic, 
                         h_tbl_Material.HygIssuedDate, h_tbl_Material.CER_MoscRepHier, h_tbl_Material.PriceCurrExport, h_tbl_Material.PricePerExport, 
                         h_tbl_Material.PriceValueExport, h_tbl_Material.Created, h_tbl_Material.HoldInd, h_tbl_Material.SC, h_tbl_Material.DescTranslit, 
                         h_tbl_Material.DescInvEN, h_tbl_Material.FC, h_tbl_Material.Plt_GRSW_KG, h_tbl_Material.Plt_NETW_KG, h_tbl_Material.PriceCurr, 
                         h_tbl_Material.PriceValue, h_tbl_Material.PricePer, h_tbl_Material.HarmonizedCode, h_tbl_Material.CustomsDuty, 
                         h_tbl_Material.PackingGroup, h_tbl_Material.PackingData, h_tbl_Material.HygValidToDate, h_tbl_Material.U_MU, h_tbl_Material.MUPack, 
                         h_tbl_Material.METROCode AS METROCodeDotted, h_tbl_Material.CUVolWeightValue, h_tbl_Material.CUVolWeightUoM, 
                         vwMetroListing_Short.KA_MCode AS MetroCode, vwMetroListing_Short.PackQty AS MetroPackQty, vwMetroListing_Short.PackType AS MetroPackType, 
                         vwMetroListing_Short.KA_EAN AS MetroEAN, vwAuchanListing_Short.KA_MCode AS AuchanCode, vwAuchanListing_Short.PackQty AS AuchanPackQty, 
                         vwAuchanListing_Short.PackType AS AuchanPackType, vwAuchanListing_Short.KA_EAN AS AuchanEAN, vwFozzyListing_Short.KA_MCode AS FozzyCode, 
                         vwFozzyListing_Short.PackQty AS FozzyPackQty, vwFozzyListing_Short.PackType AS FozzyPackType, vwFozzyListing_Short.KA_EAN AS FozzyEAN, 
                         h_tbl_Material.CustVal_Unilever, h_tbl_Material.CustVal_DMSU, vw_MaterialStatus.ValidSinceYrWkSat AS StatusValidSinceYrWkSat, 
                         vw_MaterialStatus.SuccessorMCode, vwCosmoListing_Short.KA_MCode AS CosmoCode, vwCosmoListing_Short.PackQty AS CosmoPackQty, 
                         vwCosmoListing_Short.PackType AS CosmoPackType, vwCosmoListing_Short.KA_EAN AS CosmoEAN, vwREALListing_Short.KA_MCode AS RealCode, 
                         vwREALListing_Short.PackQty AS RealPackQty, vwREALListing_Short.PackType AS RealPackType, vwREALListing_Short.KA_EAN AS RealEAN, 
                         vwKaravanListing_Short.KA_MCode AS KaravanCode, vwKaravanListing_Short.PackQty AS KaravanPackQty, 
                         vwKaravanListing_Short.PackType AS KaravanPackType, vwKaravanListing_Short.KA_EAN AS KaravanEAN, vwEKOListing_Short.KA_MCode AS EKOCode, 
                         vwEKOListing_Short.PackQty AS EKOPackQty, vwEKOListing_Short.PackType AS EKOPackType, vwEKOListing_Short.KA_EAN AS EKOEAN, 
                         h_tbl_Material.DescSemiUA80
FROM            tbl_ref_Countries INNER JOIN
                         tbl_ref_Producers ON tbl_ref_Countries.CountryId = tbl_ref_Producers.CountryId RIGHT OUTER JOIN
                         h_tbl_Material INNER JOIN
                         vw_MaterialStatus ON h_tbl_Material.MCode = vw_MaterialStatus.MCode 
						 LEFT OUTER JOIN
                         vw_KAListing_Short AS vwCosmoListing_Short ON h_tbl_Material.MCode = vwCosmoListing_Short.UL_MCode AND 
                         vwCosmoListing_Short.KAId = 6 
						 LEFT OUTER JOIN
                         vw_MaterialPalletizing_Unit_Case ON h_tbl_Material.MCode = vw_MaterialPalletizing_Unit_Case.MCode ON 
                         tbl_ref_Producers.ProducerId = h_tbl_Material.ProducerId 
						 LEFT OUTER JOIN
                         vw_KAListing_Short AS vwAuchanListing_Short ON h_tbl_Material.MCode = vwAuchanListing_Short.UL_MCode AND 
                         vwAuchanListing_Short.KAId = 2 
						 LEFT OUTER JOIN
                         vw_KAListing_Short AS vwFozzyListing_Short ON h_tbl_Material.MCode = vwFozzyListing_Short.UL_MCode AND 
                         vwFozzyListing_Short.KAId = 5 
						 LEFT OUTER JOIN
                         vw_KAListing_Short AS vwMetroListing_Short ON h_tbl_Material.MCode = vwMetroListing_Short.UL_MCode AND 
                         vwMetroListing_Short.KAId = 1 
						 LEFT OUTER JOIN
                         vw_KAListing_Short AS vwKaravanListing_Short ON h_tbl_Material.MCode = vwKaravanListing_Short.UL_MCode AND 
                         vwKaravanListing_Short.KAId = 7 
						 LEFT OUTER JOIN
                         vw_KAListing_Short AS vwEKOListing_Short ON h_tbl_Material.MCode = vwEKOListing_Short.UL_MCode AND 
                         vwEKOListing_Short.KAId = 8 
						 LEFT OUTER JOIN
                         tbl_ref_Suppliers ON h_tbl_Material.SupplierID = tbl_ref_Suppliers.SupplierId 
						 LEFT OUTER JOIN
                         vw_KAListing_Short AS vwREALListing_Short ON vwREALListing_Short.UL_MCode = h_tbl_Material.MCode AND 
						 vwREALListing_Short.KAId = 4

в Access добился похожего результата, через RIGHT JOIN, но количество записей не то

SELECT 
h_tbl_Material.MCode, vw_MaterialStatus.StatusId AS Status, vw_MaterialStatus.StatusName, h_tbl_Material.Brand_lim, 
                         h_tbl_Material.Categ_local, h_tbl_Material.Cat_HF, h_tbl_Material.DescShortUA, h_tbl_Material.DescFullUA, h_tbl_Material.DescShortEN, 
                         h_tbl_Material.DescFullEN, h_tbl_Material.DescShortRU, h_tbl_Material.DescFullRU, vw_MaterialPalletizing_Unit_Case.UC, h_tbl_Material.CP, 
                         h_tbl_Material.ZUN_ZCU, h_tbl_Material.ZCU_CT, h_tbl_Material.CU_W_G, h_tbl_Material.CU_GRSW_G, h_tbl_Material.CS_GRSW_G, 
                         h_tbl_Material.PAL_GRSW_G, h_tbl_Material.EAN13, h_tbl_Material.CertNum, h_tbl_Material.Certificate, h_tbl_Material.CertIssuedDate, 
                         h_tbl_Material.CertValidToDate, h_tbl_Material.CertByBatch, h_tbl_Material.CertInd, h_tbl_Material.VetConf, h_tbl_Material.OriginationID, 
                         h_tbl_Material.Origination, h_tbl_Material.Org, h_tbl_Material.Sourcing, h_tbl_Material.ProducerId, tbl_ref_Producers.ProducerNameEN, 
                         tbl_ref_Producers.ProducerNameShort, tbl_ref_Countries.CountryNameEN AS ProducerCountryEN, 
                         tbl_ref_Countries.CountryNameRU AS ProducerCountryRU, tbl_ref_Countries.CountryNameUA AS ProducerCountryUA, h_tbl_Material.OnStock, 
                         h_tbl_Material.SupplierID, h_tbl_Material.Supplier, tbl_ref_Suppliers.SupplierName, tbl_ref_Suppliers.TrPeriodDays AS TrPeriod, 
                         h_tbl_Material.A1, h_tbl_Material.SLPeriodM, h_tbl_Material.HFPeriodD, h_tbl_Material.HygNum, h_tbl_Material.Hygienic, 
                         h_tbl_Material.HygIssuedDate, h_tbl_Material.CER_MoscRepHier, h_tbl_Material.PriceCurrExport, h_tbl_Material.PricePerExport, 
                         h_tbl_Material.PriceValueExport, h_tbl_Material.Created, h_tbl_Material.HoldInd, h_tbl_Material.SC, h_tbl_Material.DescTranslit, 
                         h_tbl_Material.DescInvEN, h_tbl_Material.FC, h_tbl_Material.Plt_GRSW_KG, h_tbl_Material.Plt_NETW_KG, h_tbl_Material.PriceCurr, 
                         h_tbl_Material.PriceValue, h_tbl_Material.PricePer, h_tbl_Material.HarmonizedCode, h_tbl_Material.CustomsDuty, 
                         h_tbl_Material.PackingGroup, h_tbl_Material.PackingData, h_tbl_Material.HygValidToDate, h_tbl_Material.U_MU, h_tbl_Material.MUPack, 
                         h_tbl_Material.METROCode AS METROCodeDotted, h_tbl_Material.CUVolWeightValue, h_tbl_Material.CUVolWeightUoM, 
                         vwMetroListing_Short.KA_MCode AS MetroCode, vwMetroListing_Short.PackQty AS MetroPackQty, vwMetroListing_Short.PackType AS MetroPackType, 
                         vwMetroListing_Short.KA_EAN AS MetroEAN, vwAuchanListing_Short.KA_MCode AS AuchanCode, vwAuchanListing_Short.PackQty AS AuchanPackQty, 
                         vwAuchanListing_Short.PackType AS AuchanPackType, vwAuchanListing_Short.KA_EAN AS AuchanEAN, vwFozzyListing_Short.KA_MCode AS FozzyCode, 
                         vwFozzyListing_Short.PackQty AS FozzyPackQty, vwFozzyListing_Short.PackType AS FozzyPackType, vwFozzyListing_Short.KA_EAN AS FozzyEAN, 
                         h_tbl_Material.CustVal_Unilever, h_tbl_Material.CustVal_DMSU, vw_MaterialStatus.ValidSinceYrWkSat AS StatusValidSinceYrWkSat, 
                         vw_MaterialStatus.SuccessorMCode, vwCosmoListing_Short.KA_MCode AS CosmoCode, vwCosmoListing_Short.PackQty AS CosmoPackQty, 
                         vwCosmoListing_Short.PackType AS CosmoPackType, vwCosmoListing_Short.KA_EAN AS CosmoEAN, vwREALListing_Short.KA_MCode AS RealCode, 
                         vwREALListing_Short.PackQty AS RealPackQty, vwREALListing_Short.PackType AS RealPackType, vwREALListing_Short.KA_EAN AS RealEAN, 
                         vwKaravanListing_Short.KA_MCode AS KaravanCode, vwKaravanListing_Short.PackQty AS KaravanPackQty, 
                         vwKaravanListing_Short.PackType AS KaravanPackType, vwKaravanListing_Short.KA_EAN AS KaravanEAN, vwEKOListing_Short.KA_MCode AS EKOCode, 
                         vwEKOListing_Short.PackQty AS EKOPackQty, vwEKOListing_Short.PackType AS EKOPackType, vwEKOListing_Short.KA_EAN AS EKOEAN, 
                         h_tbl_Material.DescSemiUA80
FROM ((((((((tbl_ref_Countries RIGHT JOIN (tbl_ref_Producers RIGHT JOIN ((h_tbl_Material INNER JOIN vw_MaterialStatus ON h_tbl_Material.MCode = vw_MaterialStatus.MCode) 
LEFT JOIN 
tbl_ref_Suppliers ON h_tbl_Material.SupplierID = tbl_ref_Suppliers.SupplierId) ON tbl_ref_Producers.ProducerId = h_tbl_Material.ProducerId) ON tbl_ref_Countries.CountryId = tbl_ref_Producers.CountryId) 
LEFT JOIN 
vw_MaterialPalletizing_Unit_Case ON h_tbl_Material.MCode = vw_MaterialPalletizing_Unit_Case.MCode
) 
LEFT JOIN 
vw_KAListing_Short AS vwREALListing_Short ON (h_tbl_Material.MCode = vwREALListing_Short.UL_MCode AND 
						 vwREALListing_Short.KAId = 4
)) 
LEFT JOIN 
vw_KAListing_Short AS vwEKOListing_Short ON (h_tbl_Material.MCode = vwEKOListing_Short.UL_MCode AND 
                         vwEKOListing_Short.KAId = 8
))
LEFT JOIN 
vw_KAListing_Short AS vwKaravanListing_Short ON (h_tbl_Material.MCode = vwKaravanListing_Short.UL_MCode AND 
                         vwKaravanListing_Short.KAId = 7
))
LEFT JOIN 
vw_KAListing_Short AS vwMetroListing_Short ON (h_tbl_Material.MCode = vwMetroListing_Short.UL_MCode AND 
                         vwMetroListing_Short.KAId = 1
))
LEFT JOIN 
vw_KAListing_Short AS vwFozzyListing_Short ON (h_tbl_Material.MCode = vwFozzyListing_Short.UL_MCode AND 
                         vwFozzyListing_Short.KAId = 5
))
LEFT JOIN 
vw_KAListing_Short AS vwAuchanListing_Short ON (h_tbl_Material.MCode = vwAuchanListing_Short.UL_MCode AND 
                         vwAuchanListing_Short.KAId = 2
)) 
LEFT JOIN 
vw_KAListing_Short AS vwCosmoListing_Short ON (h_tbl_Material.MCode = vwCosmoListing_Short.UL_MCode AND 
                         vwCosmoListing_Short.KAId = 6
						 )