Follow

How to create all possible combinations across two (not joined) tables

This is one of the more odd requests I have had for a while, so I thought I would document the solution.

The question

We have a customer who wants under certain conditions create all combinations from the certain G/L Account No. concatenated with some dimension value... so he can create a reporting dimension...

so from the account no.

1410

And list of dimensions

10

20

30

40

50

60

He would like to have rows created in the staging area of

1410-10

1410-20

1410-30

..

1410-60

Can we do this in TX ? if so, how ?

Note: As added information, there is no join between the tables.

The solution

The easiest way is to create a custom view using the following syntax.

 

CREATE VIEW [dbo].[vAccountDims] AS

SELECT [AccountNo] + '-' + DimID AS AccountDim

FROM [dbo].[Accounts_V] A, [dbo].[Dims_V]

 

With data as per above, the results of the view looks like this:

 

Account_Dim.png

I have attached a project in timeXtender 4.5.37 format as a reference. Please be aware that this might not load in all future versions of timeXtender.

 

Was this article helpful?
2 out of 2 found this helpful
Have more questions? Submit a request

4 Comments

  • 0
    Avatar
    Kamil Karbowiak

    Thanks Thomas... Works like a charm...

  • 0
    Avatar
    Dirk Van der Straeten

    Hi Thomas,

    Strange that you would even use a join function at all, this is a standard crossjoin which you could also write:

     

    CREATE VIEW [dbo].[vAccountDims] AS

    SELECT  [AccountNo] + '-' + DimID AS AccountDim

    FROM  [dbo].[Accounts_V], [dbo].[Dims_V]

     

    Just put the two tables in the from clause, will perform a full crossjoin.

    Regards

    Dirk

  • 0
    Avatar
    Thomas Christiansen

    Hi Dirk

    I didn't even think of that. Thank you for pointing that out, I stand corrected :-)

    I updated the post to reflect your suggestion, as it is more intuitive and there is no need for the dummy join.

    Best regards

    Thomas

  • 0
    Avatar
    Stuart Cuthbertson

    I'm years late to this party, but please don't use or promote the ANSI SQL-89 syntax for any kind of join, cross (cartesian) or otherwise. 

    SQL-92 presented the replacement syntax of 'CROSS JOIN ...' and 1989-style has been discouraged ever since. Whilst it does still work on Microsoft SQL Server, it won't for ever. Microsoft's transact-SQL documentation is pretty clear that they want everyone to use SQL-92 syntax (https://docs.microsoft.com/en-us/sql/t-sql/queries/from-transact-sql?view=sql-server-2017).

    The SQL Community has been mostly in agreement for literally ages that this is a bad way to write code, for example:

    https://stackoverflow.com/questions/3918570/what-is-the-difference-between-using-a-cross-join-and-putting-a-comma-between-th/31441463

    https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins (I recognize some disagreement in the comments there, but the overall consensus swings to avoiding SQL-89 syntax.)

    The correct way to write the query required for this view is: 

     

    SELECT  [AccountNo] + '-' + DimID AS AccountDim

    FROM  [dbo].[Accounts_V]

    CROSS JOIN [dbo].[Dims_V]

    Edited by Stuart Cuthbertson
Please sign in to leave a comment.