FULL OUTER JOIN with non-syntax joins 1

[FloggingDolphin]

Hi Everyone,

I'm quite the novice at SQL coding as compared to you guys, so hope I can explain this right!

I'm trying to do a full outer join table agltransact and agldimvalue for the description. However, because sometimes there is no values in agldimvalue or no values in agltransact i get an incomplete query result.

I tried to use the (+) on both sides in dimv.dim_value=t.dim_5 but obviously it does not work since you can only have it on one side. I've read about the full out join solving this problem, but I do not know how I can incorporate it into my existing code (see below).

 SELECT t.voucher_type AS trans_type,
                  t.voucher_date AS Trans_date,
                  t.sequence_no,
                  t.period,
                  t.account,
                  d5.description AS Account_text,
                  t.dim_1 AS CostC,
                  d7.description AS CostC_text,
                  t.dim_3 AS Employee,
                  d9.description AS Employee_text,
                  t.dim_5 AS Various,
                  dimv.description AS Various_desc,
                  t.dim_6 AS PAYEM,
                  d14.description AS PAYEM_Text,
                  t.dim_7 as Accrual,
                  t.voucher_no as Transno,
                  t.tax_code,
                  t.description as JournDesc,
                  t.amount,
                  t.user_id,
                  u.description as name
              
 
             FROM agltransact t, agldescription d5, agldescription d7, agldescription d9, agldescription d14, aaguser u,agldimvalue dim_v
 
 
            WHERE t.client=d5.client
              AND t.account=d5.dim_value
              AND d5.attribute_id='A0'
              AND d5.language='EN'
              AND t.client=d7.client
              AND dimv.client='EH'
              AND dimv.dim_value=t.dim_5
              AND dimv.attribute_id=t.att_5_id
              AND dimv.status='N'
              AND t.dim_1=d7.dim_value
              AND d7.attribute_id=t.att_1_id
              AND d7.language='EN'
              AND t.client=d9.client
              AND t.dim_3=d9.dim_value
              AND d9.attribute_id=t.att_3_id
              AND d9.language='EN'
              AND t.client=d14.client
              AND t.dim_6=d14.dim_value
              AND d14.attribute_id=t.att_6_id(+)
              AND d14.language='EN'
              AND t.user_id=u.user_id
              AND t.client IN ('EH')
              AND t.voucher_no LIKE '10000620'
              
 ORDER BY t.sequence_no

I hope this all makes sense~
thanks in advance,
FD

 

[r937]

it doesn't make sense to me, and i will frankly not take the time to try to figure out what those silly plus signs mean

this is the ANSI SQL forum, and i would respectfully suggest that if you wish to use non-standard SQL, please post your question in the forum most appropriate for your particular database system, whatever it might be

r937.com | rudy.ca

 

[FloggingDolphin]

Alright, sorry about that. I will move it to Microsoft SQL language as this is a database that uses this.

FD