CODEWITHSUNDEEP
sqljoinsql-server-2012ssms
user2673722
user2673722

Reputation: 295

Inner Join Ambiguous Syntax

I'm not super familiar with SQL but I know the basics. I was recently trying to replicate some logic form reports to SQL Server 2012. I started with the custom query from Webi (a reporting tool) and was trying to make a view from it in SQL.

Here is what the query look like:

SELECT
   dimGlobalSalesAnalysisTbl.globalSalesAnalysisDesc,
   dimGlobalShipDestinationCountryTbl.area,
   dimGlobalShipDestinationCountryTbl.subarea,
   dimGlobalCurrentProductTbl.sbuCodeDesc,
   dimGlobalShipDateVw.shipDayOfWeekDesc,
   sum(factSalesTblVw.globalSalesValue) AS 'Global Sales Value',
 SUM(factSalesTblVw.salesUnitQuantity*GlobalFiles.dimCurrentGTINTbl.unitQty) AS 'Sales Unit Quantity'
  FROM
  dimGlobalCookCompaniesTbl INNER JOIN factSalesTblVw ON 
 (dimGlobalCookCompaniesTbl.globalCookCompanyID=factSalesTblVw.globalCookCompanyID)
 INNER JOIN dimGlobalHistProductTbl ON (dimGlobalHistProductTbl.globalHistProductID=factSalesTblVw.globalHistProductID)
 INNER JOIN dimGlobalCurrentProductTbl ON (dimGlobalHistProductTbl.globalCurrentProductID=dimGlobalCurrentProductTbl.globalCurrentProductID)
 INNER JOIN dimGlobalHistShipCustomerTbl ON (factSalesTblVw.globalHistShipCustomerID=dimGlobalHistShipCustomerTbl.globalHistShipCustomerID)
 INNER JOIN dimGlobalCurrentShipCustomerTbl ON (dimGlobalHistShipCustomerTbl.shipCustomerID=dimGlobalCurrentShipCustomerTbl.globalCurrentShipCustomerID)
 ***INNER JOIN dimGlobalCountryTbl dimGlobalShipDestinationCountryTbl ON (dimGlobalCurrentShipCustomerTbl.shipDestCountryDesc=dimGlobalShipDestinationCountryTbl.countryCode)***
 INNER JOIN dimGlobalSalesAnalysisTbl ON (factSalesTblVw.globalSalesAnalysisID=dimGlobalSalesAnalysisTbl.globalSalesAnalysisID)
 INNER JOIN dimGlobalShipDateVw ON (dimGlobalShipDateVw.shipJulianDate=factSalesTblVw.shipDateID)
 INNER JOIN GlobalFiles.dimCurrentGTINTbl ON (GlobalFiles.dimCurrentGTINTbl.curGtinId=factSalesTblVw.GtinID)

WHERE
 (
   dimGlobalShipDateVw.shipYearNumber  IN (DATEPART(yy,GETDATE())-1)
   AND
   dimGlobalCurrentShipCustomerTbl.shipCustomerNumberDesc
   IN  ( 'JPC000222-3','CNC000012-1'  )
   AND
  dimGlobalSalesAnalysisTbl.globalSalesAnalysisDesc  =  'Return Credits'
 )
GROUP BY
dimGlobalShipDateVw.shipDate, 
dimGlobalSalesAnalysisTbl.globalSalesAnalysisDesc, 
dimGlobalShipDestinationCountryTbl.area, 
dimGlobalShipDestinationCountryTbl.subarea, 
dimGlobalCurrentProductTbl.sbuCodeDesc, 
Upper(dimGlobalCurrentProductTbl.familyCodeDesc), 
dimGlobalShipDateVw.shipYearNumber, 
dimGlobalShipDateVw.shipDayOfWeekDesc, 
dimGlobalCurrentProductTbl.madeByAbbr, 
dimGlobalCookCompaniesTbl.companyDesc

This particular query runs on the production system if ran in the relevant database. When trying to make a view of this query in a different database, I precede the objects by [database_name].[schema/dbo] name.

On running the query, I get the error:

Invalid object name 'WWS.dbo.dimGlobalShipDestinationCountryTbl'

I try to find this particular table on the database, but it isn't there, though hovering over the table name in the query give a table definition but no script.

This table is present in an weird looking inner join (6th inner join) syntax like this:

INNER JOIN dimGlobalCountryTbl dimGlobalShipDestinationCountryTbl ON (dimGlobalCurrentShipCustomerTbl.shipDestCountryDesc=dimGlobalShipDestinationCountryTbl.countryCode)

Two questions: 1. Can someone please explain this query syntax for inner join ? 2. This is pretty stupid but any ideas on how to look into possibly hidden table definitions ?

Upvotes: 0

Views: 107

Answers (1)

xQbert
xQbert

Reputation: 35323

Two questions: 1. Can someone please explain this query syntax for inner join ?

The inner join in this case is nothing more than a table alias. The creator of the query thought aliasing the table would be easier to understand this name instead of the actual table name, or the same table is referenced twice and one would have to have an alias.

2. This is pretty stupid but any ideas on how to look into possibly hidden table definitions ? Why? I think you just have a syntax error on your SQL when you added the database_name.schema syntax.

Think of the table alias like a column alias.... but and just like columns, you can omit the 'AS' keyword...

dimGlobalCountryTbl dimGlobalShipDestinationCountryTbl is the same as

dimGlobalCountryTbl AS dimGlobalShipDestinationCountryTbl

Upvotes: 1

Related Questions