Having a problem with a join

[masha2008]

Based on this SQL I have to make a report...

select              T1."ACCOUNT" "c1" , 
                    T4."ACCOUNT" "c5" , 

from  	     	  "ACCT_DIM" T1,
 	          "ACCT_DIM" T4,

where   and T6."GL_ACCT_KEY" = T1."ACCT_KEY" 
 	and T6."ACL_ACCT_KEY" = T4."ACCT_KEY" 
 	and T4."ACCOUNT" = '11'

ACL_ACCT_KEY 1,n GL_ACCT_KEY

Everytime I make a join in both columns I see ACCOUNT 11, and so I get only one transaction, I need to see in column 5 '11' and in column 1, many other accounts.
Basically this ACCOUNT column is based on gl_accts and acl_accts
Please someone explain, how do I make this join in Report Studio, i have all these columns in my FM model...
Thank you

 

[blom0344]

Your SQL does not make any sense. You have an alias T6 but what does it reference?
The FM model is the place to define joins between query subjects, only in very rare cases would one create seperate queries in Report Studio join them in the report.
Please try and give the proper SQL and we take it from there..

Ties Blom

 

[masha2008]

Im sorrry I didnt define T6 in FROM statement
The code is like this

select T1."ACCOUNT" "c1" , 
       T4."ACCOUNT" "c5" , 

from "ACCT_DIM" T1,
     "ACCT_DIM" T4,
     "ACCT_GL_FACT" T6,

where and T6."GL_ACCT_KEY" = T1."ACCT_KEY" 
      and T6."ACL_ACCT_KEY" = T4."ACCT_KEY" 
      and T4."ACCOUNT" = '11'

When I ran it, I need one column with ACCOUNT based on GL_ACCT_KEY, and the other column based on ACL_ACT_KEY.
Do I drag ACCOUNT data item to my report twice, and then go from there creating joins? So far everything I tried gives me empty report or both columns with the same account number
My output should be
ACCOUNT......ACCOUNT
13425........11
23456........11
5673.........11

Both of these accounts are coming from the same column..

Thank you for your help

 

[blom0344]

Hard to tell without knowing your data. Some thoughts:

1. Are the Foreign keys of the facttable nullable (would yield data-loss when working with inner joins)?

2. Try writing your SQL in ANSI style, in your case this would be:

select T1."ACCOUNT" "c1" ,
       T4."ACCOUNT" "c5"

from "ACCT_DIM" T1
      INNER JOIN
     "ACCT_GL_FACT" T6
      ON T1."ACCT_KEY" = T6."GL_ACCT_KEY"
      INNER JOIN 
      "ACCT_DIM" T4
      ON T4."ACCT_KEY" = T6."ACL_ACCT_KEY"
     "ACCT_GL_FACT" T6
      where 
      T4."ACCOUNT" = '11'

This ensures that all joins are performed and the filter of the where clause is applied as last step.

Ties Blom