FULL OUTER JOIN with non-syntax joins 2

[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 (the (+) indicates the table/s should be expanded (with NULLs) where no matching rows exist ) 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 dimv
 
 
            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

 

[monksnake]

I'm not quite understanding what you want cause I see some strange stuff, so I'll just share a few things I see.

First, I see you referencing the table agldescription 4 times after your FROM statement

FROM agltransact t, [COLOR=red]agldescription d5, agldescription d7, agldescription d9, agldescription d14 [/color], aaguser u,agldimvalue dimv

You only need to reference this one time.
I see you have an IN statement

AND t.client IN ('EH')

Since t.client is compared to one value, you should set it equal to 'EH'

AND t.client = 'EH'

The same thing goes with your LIKE statement

AND t.voucher_no LIKE '10000620'

There are no wildcard characters, so just set it equal

AND t.voucher_no = '10000620'

<.

 

[gmmastros]

You really should start using the new ANSI syntax for joins. It takes a little while to get used to, but I heard somewhere that future versions of SQL Server won't support the older form.

This question is difficult to answer because it involves many tables, I am posting some code, but you should be made aware that it probably won't work for you. However, it should give you a good example of how to implement full outer joins. Also notice that the filter conditions are moved to the ON clause for each table. When you put the condition in the WHERE clause, it essentially changes the full outer join to an inner join.

Anyway... I hope this helps.

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
       Inner Join agldescription d5
         On  t.client=d5.client
         AND t.account=d5.dim_value
         AND d5.attribute_id='A0'
         AND d5.language='EN'
       Inner Join agldescription d7
         On  t.client=d7.client
         AND t.dim_1=d7.dim_value
         AND t.att_1_id = d7.attribute_id
         AND d7.language='EN'
       Inner Join agldescription d9
         On  t.client=d9.client
         AND t.dim_3=d9.dim_value
         AND t.att_3_id = d9.attribute_id
         AND d9.language='EN'
       Inner Join agldescription d14
         ON  t.client=d14.client
         AND t.dim_6=d14.dim_value
         AND t.att_6_id = d14.attribute_id
         AND d14.language='EN'
       Inner Join aaguser u
         ON  t.user_id=u.user_id
       Full Outer Join agldimvalue dimv
         ON  dimv.dim_value=t.dim_5
         AND dimv.attribute_id=t.att_5_id
         AND dimv.status='N'
         AND dimv.client='EH'
         AND t.client IN ('EH')
         AND t.voucher_no LIKE '10000620'
ORDER BY t.sequence_no

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

Further anytime you use the + sign in what you think is a left or right join, SQL Server 2000 will not consistently give the correct results. Sometimes it does a cross join in stead of a left or right join. Never use the old syntax if you are doing anything other than an inner join. George has shown you the recommended way to do joins in SQL server.

You honestly should change all your code to use this syntax, but you should immediately change any of the code you have using the plus syntax as those queries may not be returning the correct information consistently. This can lead to serious data integrity problems or to management making decisions based on bad information so it is a critial change.

Questions about posting. See faq183-874

 

[SQLDenis]

>>but I heard somewhere that future versions of SQL Server won't support the older form.

SQL server 2005 doesn't support *= or =* already
below is the error

Server: Msg 4147, Level 15, State 1, Line 2
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80 or lower, using stored procedure sp_dbcmptlevel. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

 

[FloggingDolphin]

Thanks so much to everyone who has helped. Especially MonkSnake and GMMastros. My sql obviously needs some "cleaning" up and I appreciate the advice. I'll try it in a while.

To give more backgound to my situation. Basically we use an Agresso system that keeps databases on microsoft sql but uses oracle to access it (it's kinda confusing like sometimes you have to use DBselect instead of select). Anyway to create a user-defined report, you make two files. One that has sql code defining what to SELECT, FROM, WHERE and then the other is an excel spread sheet where we specify where to display the columns via the COLUMNS command (not sure if that's sql as well).

This is an enquiry on journal transactions but since agresso stores descriptions in the description table we need to link properly. Sometimes though a field that was input did not have a description for it or sometimes the field is blank, hence the need for a full outer join, otherwise I cannot see all the transaction details I enquire on.

I really hope that explains it better...
thanks for the help again,
FD