Re: Columns in outer joins

But using oracle couldn't you use a decode in the columns selected?

something like
select

decode(outer_joinedtable.phone_type,'MAIN',outer_joinedtable.phone_type,BLAN K)
from 2 tables
where address_studentid = phone_studentid (+)

and since we know decode evaluates the first, returns the second if matched and the third if not you still get your answer.

Doug

Ed Prochak wrote in message <7qefki$1d9$1_at_nnrp1.deja.com>...
>But Ken,
>
> he was talking about a QUI query build tool. It's not a SQL question,
>it's a GUI tool question. Bascially he is saying the tool forces him to
>go from the graphical view to the SQl (text) view of the query in order
>to get it right. I haven't used a variety of QUI query builders, but it
>looks to me like he has a point.
>
>It shouldn't be hard to add this option, except it could be fooled by
>the order that you build the conditions. (imagine making the join,
>adding the 'Main' condition, than changing the join to an outer join.
>Should it then change the other conditions to match??) Why it's not
>there (at least as an option) is beyond my range of knowledge.
>
>
>
>In article <37CA7275.42D14740_at_Unforgettable.com>,
> BluesSax_at_Unforgettable.com wrote:
>> But that's the point. IT IS how SQL works. Why should there be any
>> problem with using the syntax of a language as it is documented. It
>may
>> seem strange, but it is consistant and those of us who have been
>writing
>> sql for years are used to writing outer joins that way. It has nothing
>> to do with vendor-specific implementation.
>>
>> Ken
>>
>> stuart_kohler_at_my-deja.com wrote:
>>
>> > In evaluating gui SQL reporting tools, it seems
>> > common to assist in the outer joining of tables
>> > but ignore outer joining column references in the
>> > outer joined table.
>> >
>> > For example, when joining an address table and
>> > phone table, the table join is built correctly,
>> > as:
>> > address_studentid = phone_studentid (+)
>> >
>> > This will correctly pull students with or without
>> > phones.
>> >
>> > However, when there is a phone, we usually
>> > only want to pull the main phone number and so add
>> > a statement like:
>> > phone_type = 'Main'
>> >
>> > Unless this line is manually edited to
>> > phone_type (+) = 'Main'
>> > the table outer join is useless and only pulls
>> > students with phones, specifically of the type
>> > 'Main' - and therefore negates having outer joined
>> > the tables in the first place.
>> >
>> > When I've queried the software vendors, they seem
>> > to shrug and say "That's the way SQL works."
>> >
>> > Fundamentally, this makes no sense, since I cannot
>> > imagine an instance where after having outer
>> > joined tables that I would NOT want
>> > phone_type (+) = 'Main'
>> > so I don't understand why any vendor would choose
>> > to implement the behavior of
>> > phone_type = 'Main'
>> > and claim it's the user's problem to outer join
>> > the phone_type column.
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>------------------------------------------------------------------------
>------------------------
>>
>>
>
>--
>Ed Prochak
>Magic Interface, Ltd.
>ORACLE services
>440-498-3700 <<<NOTE new number
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
Received on Mon Aug 30 1999 - 19:46:13 CDT