Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Multiple Outer joins question using MSS

9 posts in DataWindow Last posting was on 2008-08-16 00:43:59.0Z
GW Posted on 2008-08-14 20:26:05.0Z
From: "GW" <gw@gw>
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Multiple Outer joins question using MSS
Lines: 739
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a494dd@forums-1-dub>
Date: 14 Aug 2008 13:26:05 -0700
X-Trace: forums-1-dub 1218745565 10.22.241.152 (14 Aug 2008 13:26:05 -0700)
X-Original-Trace: 14 Aug 2008 13:26:05 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87551
Article PK: 416808

Hi all

I have a dw with multiple oj's that works great using ASA but throws the
atttached error when trying to run against MSS 2005. Before I go down the
road of changing names as it suggests(I have no idea how to do that) I was
wondering if this is something that is firing because of my setup. Can
anybody point to a setting that may suppress this? I see PB offers an
"Outer join syntax" and I tried ANSI and ANSI_Escape but that didn't seem to
change anything

PB 10.5 7611

Thanks

Greg

dw_error.jpg

KL Posted on 2008-08-14 21:05:34.0Z
Sender: 373.48a49d7b.1804289383@sybase.com
From: KL
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Multiple Outer joins question using MSS
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <48a49e1e.399.1681692777@sybase.com>
References: <48a494dd@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 14 Aug 2008 14:05:34 -0700
X-Trace: forums-1-dub 1218747934 10.22.241.41 (14 Aug 2008 14:05:34 -0700)
X-Original-Trace: 14 Aug 2008 14:05:34 -0700, 10.22.241.41
Lines: 30
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87552
Article PK: 416807

Probably need your SQL to help, but
could be just need to specifiy the table as
table1.sightings and table2.sightings.

Or may need to use aliases - which would be if you are
retrieving the same field from the same table more than
once.

KL

> Hi all
>
> I have a dw with multiple oj's that works great using ASA
> but throws the atttached error when trying to run against
> MSS 2005. Before I go down the road of changing names as
> it suggests(I have no idea how to do that) I was
> wondering if this is something that is firing because of
> my setup. Can anybody point to a setting that may
> suppress this? I see PB offers an "Outer join syntax"
> and I tried ANSI and ANSI_Escape but that didn't seem to
> change anything
>
> PB 10.5 7611
>
> Thanks
>
> Greg
>
>
>
> [Attachment: dw_error.jpg]


GW Posted on 2008-08-14 22:16:28.0Z
From: "GW" <gw@gw>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48a494dd@forums-1-dub> <48a49e1e.399.1681692777@sybase.com>
Subject: Re: Multiple Outer joins question using MSS
Lines: 40
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a4aebc@forums-1-dub>
Date: 14 Aug 2008 15:16:28 -0700
X-Trace: forums-1-dub 1218752188 10.22.241.152 (14 Aug 2008 15:16:28 -0700)
X-Original-Trace: 14 Aug 2008 15:16:28 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87554
Article PK: 416809

Actually, the culprit was 4 right oj's. Now that I've looked at this closer
they can all be straight joins and that works fine. I think we'll stick with
that for now.

Thanks

<KL> wrote in message news:48a49e1e.399.1681692777@sybase.com...
> Probably need your SQL to help, but
> could be just need to specifiy the table as
> table1.sightings and table2.sightings.
>
> Or may need to use aliases - which would be if you are
> retrieving the same field from the same table more than
> once.
>
> KL
>> Hi all
>>
>> I have a dw with multiple oj's that works great using ASA
>> but throws the atttached error when trying to run against
>> MSS 2005. Before I go down the road of changing names as
>> it suggests(I have no idea how to do that) I was
>> wondering if this is something that is firing because of
>> my setup. Can anybody point to a setting that may
>> suppress this? I see PB offers an "Outer join syntax"
>> and I tried ANSI and ANSI_Escape but that didn't seem to
>> change anything
>>
>> PB 10.5 7611
>>
>> Thanks
>>
>> Greg
>>
>>
>>
>> [Attachment: dw_error.jpg]
>


Paul Horan[TeamSybase] Posted on 2008-08-15 00:24:55.0Z
From: "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48a494dd@forums-1-dub> <48a49e1e.399.1681692777@sybase.com> <48a4aebc@forums-1-dub>
Subject: Re: Multiple Outer joins question using MSS
Lines: 69
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a4ccd7@forums-1-dub>
Date: 14 Aug 2008 17:24:55 -0700
X-Trace: forums-1-dub 1218759895 10.22.241.152 (14 Aug 2008 17:24:55 -0700)
X-Original-Trace: 14 Aug 2008 17:24:55 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87557
Article PK: 416812

Right Outer Joins were created by Satan himself... <G>

For the most part, this is a left-to-right society (apologies to those
cultures that use right-to-left character sets...). There is NO QUERY that
uses ROJs that can't be represented in its syntactic equivalent with all
LOJs. I challenge anyone to prove me wrong on that.

You just need to make sure that you don't do this:
A LOJ B, (note the comma)
B LOJ C

This is the crux of your error. Table B is represented twice without a
distinguishing correlation. Rewrite it this way:

A LOJ B (note: no comma)
LOJ C

The fault is PBs actually... The dw painter has never been smart enough to
rewrite a query into format 2 when you select the tables in the wrong order.
IOW, if you paint the B to C join first, then the A to B join, you get
format 1. If you paint the A to B first, then the B to C, it's all good and
your error never occurs. I haven't retested this in 11, but I've complained
about it since the 7.x days.

Paul Horan[TeamSybase]

"GW" <gw@gw> wrote in message news:48a4aebc@forums-1-dub...
> Actually, the culprit was 4 right oj's. Now that I've looked at this
> closer they can all be straight joins and that works fine. I think we'll
> stick with that for now.
>
> Thanks
>
> <KL> wrote in message news:48a49e1e.399.1681692777@sybase.com...
>> Probably need your SQL to help, but
>> could be just need to specifiy the table as
>> table1.sightings and table2.sightings.
>>
>> Or may need to use aliases - which would be if you are
>> retrieving the same field from the same table more than
>> once.
>>
>> KL
>>> Hi all
>>>
>>> I have a dw with multiple oj's that works great using ASA
>>> but throws the atttached error when trying to run against
>>> MSS 2005. Before I go down the road of changing names as
>>> it suggests(I have no idea how to do that) I was
>>> wondering if this is something that is firing because of
>>> my setup. Can anybody point to a setting that may
>>> suppress this? I see PB offers an "Outer join syntax"
>>> and I tried ANSI and ANSI_Escape but that didn't seem to
>>> change anything
>>>
>>> PB 10.5 7611
>>>
>>> Thanks
>>>
>>> Greg
>>>
>>>
>>>
>>> [Attachment: dw_error.jpg]
>>
>
>


GW Posted on 2008-08-15 02:42:28.0Z
From: "GW" <gw@gw>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48a494dd@forums-1-dub> <48a49e1e.399.1681692777@sybase.com> <48a4aebc@forums-1-dub> <48a4ccd7@forums-1-dub>
Subject: Re: Multiple Outer joins question using MSS
Lines: 89
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a4ed14$1@forums-1-dub>
Date: 14 Aug 2008 19:42:28 -0700
X-Trace: forums-1-dub 1218768148 10.22.241.152 (14 Aug 2008 19:42:28 -0700)
X-Original-Trace: 14 Aug 2008 19:42:28 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87558
Article PK: 416814

Thanks for the explaination Paul but in my case the joins were not in
succession. It was 4 tables pointing to the one:

A roj B
A roj C
A roj D
A roj E

The only reason I can see having them all roj's was to retrieve any null
columns in A, but, since this original design the users can't even save a
row in A unless all cols are filled, so the roj's are now redundant. Not
really a fix but a smooth sidestep I guess

What appreciable affect does the "Outer join syntax" have?

"Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message
news:48a4ccd7@forums-1-dub...
> Right Outer Joins were created by Satan himself... <G>
>
> For the most part, this is a left-to-right society (apologies to those
> cultures that use right-to-left character sets...). There is NO QUERY
> that uses ROJs that can't be represented in its syntactic equivalent with
> all LOJs. I challenge anyone to prove me wrong on that.
>
> You just need to make sure that you don't do this:
> A LOJ B, (note the comma)
> B LOJ C
>
> This is the crux of your error. Table B is represented twice without a
> distinguishing correlation. Rewrite it this way:
>
> A LOJ B (note: no comma)
> LOJ C
>
> The fault is PBs actually... The dw painter has never been smart enough
> to rewrite a query into format 2 when you select the tables in the wrong
> order. IOW, if you paint the B to C join first, then the A to B join, you
> get format 1. If you paint the A to B first, then the B to C, it's all
> good and your error never occurs. I haven't retested this in 11, but I've
> complained about it since the 7.x days.
>
> Paul Horan[TeamSybase]
>
> "GW" <gw@gw> wrote in message news:48a4aebc@forums-1-dub...
>> Actually, the culprit was 4 right oj's. Now that I've looked at this
>> closer they can all be straight joins and that works fine. I think we'll
>> stick with that for now.
>>
>> Thanks
>>
>> <KL> wrote in message news:48a49e1e.399.1681692777@sybase.com...
>>> Probably need your SQL to help, but
>>> could be just need to specifiy the table as
>>> table1.sightings and table2.sightings.
>>>
>>> Or may need to use aliases - which would be if you are
>>> retrieving the same field from the same table more than
>>> once.
>>>
>>> KL
>>>> Hi all
>>>>
>>>> I have a dw with multiple oj's that works great using ASA
>>>> but throws the atttached error when trying to run against
>>>> MSS 2005. Before I go down the road of changing names as
>>>> it suggests(I have no idea how to do that) I was
>>>> wondering if this is something that is firing because of
>>>> my setup. Can anybody point to a setting that may
>>>> suppress this? I see PB offers an "Outer join syntax"
>>>> and I tried ANSI and ANSI_Escape but that didn't seem to
>>>> change anything
>>>>
>>>> PB 10.5 7611
>>>>
>>>> Thanks
>>>>
>>>> Greg
>>>>
>>>>
>>>>
>>>> [Attachment: dw_error.jpg]
>>>
>>
>>
>
>
>


Paul Horan[TeamSybase] Posted on 2008-08-15 04:05:02.0Z
From: "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48a494dd@forums-1-dub> <48a49e1e.399.1681692777@sybase.com> <48a4aebc@forums-1-dub> <48a4ccd7@forums-1-dub> <48a4ed14$1@forums-1-dub>
Subject: Re: Multiple Outer joins question using MSS
Lines: 115
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a5006e$1@forums-1-dub>
Date: 14 Aug 2008 21:05:02 -0700
X-Trace: forums-1-dub 1218773102 10.22.241.152 (14 Aug 2008 21:05:02 -0700)
X-Original-Trace: 14 Aug 2008 21:05:02 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87559
Article PK: 416815

That still doesn't strike me as a correct design...

This statement of yours: "the users can't even save a row in A unless all
cols are filled" tells me that B, C, D, and E are the independent entities
(a row can exist in B without a corresponding entry in A), and A is the
dependent entity (it has dependent relationship to B, C, D, and E).

In that case, if you want to retrieve rows in A where there might be NULL
values in one or more of the fk cols to B, C, D, or E, (and yeah, I know you
said this is not possible), then you'd want exactly the opposite of what was
originally coded.

A LOJ B
LOJ C
LOJ D
LOJ E

So (using lower case to indicate the join column name) if there was a row
where A.b, A.c, and A.d had non-null values, but A.e was NULL, this query
would still return the row.

Paul Horan[TeamSybase]

"GW" <gw@gw> wrote in message news:48a4ed14$1@forums-1-dub...
> Thanks for the explaination Paul but in my case the joins were not in
> succession. It was 4 tables pointing to the one:
>
> A roj B
> A roj C
> A roj D
> A roj E
>
> The only reason I can see having them all roj's was to retrieve any null
> columns in A, but, since this original design the users can't even save a
> row in A unless all cols are filled, so the roj's are now redundant. Not
> really a fix but a smooth sidestep I guess
>
> What appreciable affect does the "Outer join syntax" have?
>
> "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message
> news:48a4ccd7@forums-1-dub...
>> Right Outer Joins were created by Satan himself... <G>
>>
>> For the most part, this is a left-to-right society (apologies to those
>> cultures that use right-to-left character sets...). There is NO QUERY
>> that uses ROJs that can't be represented in its syntactic equivalent with
>> all LOJs. I challenge anyone to prove me wrong on that.
>>
>> You just need to make sure that you don't do this:
>> A LOJ B, (note the comma)
>> B LOJ C
>>
>> This is the crux of your error. Table B is represented twice without a
>> distinguishing correlation. Rewrite it this way:
>>
>> A LOJ B (note: no comma)
>> LOJ C
>>
>> The fault is PBs actually... The dw painter has never been smart enough
>> to rewrite a query into format 2 when you select the tables in the wrong
>> order. IOW, if you paint the B to C join first, then the A to B join, you
>> get format 1. If you paint the A to B first, then the B to C, it's all
>> good and your error never occurs. I haven't retested this in 11, but
>> I've complained about it since the 7.x days.
>>
>> Paul Horan[TeamSybase]
>>
>> "GW" <gw@gw> wrote in message news:48a4aebc@forums-1-dub...
>>> Actually, the culprit was 4 right oj's. Now that I've looked at this
>>> closer they can all be straight joins and that works fine. I think we'll
>>> stick with that for now.
>>>
>>> Thanks
>>>
>>> <KL> wrote in message news:48a49e1e.399.1681692777@sybase.com...
>>>> Probably need your SQL to help, but
>>>> could be just need to specifiy the table as
>>>> table1.sightings and table2.sightings.
>>>>
>>>> Or may need to use aliases - which would be if you are
>>>> retrieving the same field from the same table more than
>>>> once.
>>>>
>>>> KL
>>>>> Hi all
>>>>>
>>>>> I have a dw with multiple oj's that works great using ASA
>>>>> but throws the atttached error when trying to run against
>>>>> MSS 2005. Before I go down the road of changing names as
>>>>> it suggests(I have no idea how to do that) I was
>>>>> wondering if this is something that is firing because of
>>>>> my setup. Can anybody point to a setting that may
>>>>> suppress this? I see PB offers an "Outer join syntax"
>>>>> and I tried ANSI and ANSI_Escape but that didn't seem to
>>>>> change anything
>>>>>
>>>>> PB 10.5 7611
>>>>>
>>>>> Thanks
>>>>>
>>>>> Greg
>>>>>
>>>>>
>>>>>
>>>>> [Attachment: dw_error.jpg]
>>>>
>>>
>>>
>>
>>
>>
>
>


GW Posted on 2008-08-15 16:01:23.0Z
From: "GW" <gw@gw>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48a494dd@forums-1-dub> <48a49e1e.399.1681692777@sybase.com> <48a4aebc@forums-1-dub> <48a4ccd7@forums-1-dub> <48a4ed14$1@forums-1-dub> <48a5006e$1@forums-1-dub>
Subject: Re: Multiple Outer joins question using MSS
Lines: 1335
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a5a853@forums-1-dub>
Date: 15 Aug 2008 09:01:23 -0700
X-Trace: forums-1-dub 1218816083 10.22.241.152 (15 Aug 2008 09:01:23 -0700)
X-Original-Trace: 15 Aug 2008 09:01:23 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87563
Article PK: 416820

Paul

I don't have alot of experience in this so I really don't follow you
completely. Maybe if I saw it done before my eyes. If you look at the PB
painter definition of the roj it makes perfect sense to me that it's use is
for situations like this where you want to retrieve a row in A where the
emp_id is missing....see attached

"Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message
news:48a5006e$1@forums-1-dub...
> That still doesn't strike me as a correct design...
>
> This statement of yours: "the users can't even save a row in A unless all
> cols are filled" tells me that B, C, D, and E are the independent entities
> (a row can exist in B without a corresponding entry in A), and A is the
> dependent entity (it has dependent relationship to B, C, D, and E).
>
> In that case, if you want to retrieve rows in A where there might be NULL
> values in one or more of the fk cols to B, C, D, or E, (and yeah, I know
> you
> said this is not possible), then you'd want exactly the opposite of what
> was
> originally coded.
>
> A LOJ B
> LOJ C
> LOJ D
> LOJ E
>
> So (using lower case to indicate the join column name) if there was a row
> where A.b, A.c, and A.d had non-null values, but A.e was NULL, this query
> would still return the row.
>
> Paul Horan[TeamSybase]
>
> "GW" <gw@gw> wrote in message news:48a4ed14$1@forums-1-dub...
>> Thanks for the explaination Paul but in my case the joins were not in
>> succession. It was 4 tables pointing to the one:
>>
>> A roj B
>> A roj C
>> A roj D
>> A roj E
>>
>> The only reason I can see having them all roj's was to retrieve any null
>> columns in A, but, since this original design the users can't even save a
>> row in A unless all cols are filled, so the roj's are now redundant. Not
>> really a fix but a smooth sidestep I guess
>>
>> What appreciable affect does the "Outer join syntax" have?
>>
>> "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message
>> news:48a4ccd7@forums-1-dub...
>>> Right Outer Joins were created by Satan himself... <G>
>>>
>>> For the most part, this is a left-to-right society (apologies to those
>>> cultures that use right-to-left character sets...). There is NO QUERY
>>> that uses ROJs that can't be represented in its syntactic equivalent
>>> with
>>> all LOJs. I challenge anyone to prove me wrong on that.
>>>
>>> You just need to make sure that you don't do this:
>>> A LOJ B, (note the comma)
>>> B LOJ C
>>>
>>> This is the crux of your error. Table B is represented twice without a
>>> distinguishing correlation. Rewrite it this way:
>>>
>>> A LOJ B (note: no comma)
>>> LOJ C
>>>
>>> The fault is PBs actually... The dw painter has never been smart enough
>>> to rewrite a query into format 2 when you select the tables in the wrong
>>> order. IOW, if you paint the B to C join first, then the A to B join,
>>> you
>>> get format 1. If you paint the A to B first, then the B to C, it's all
>>> good and your error never occurs. I haven't retested this in 11, but
>>> I've complained about it since the 7.x days.
>>>
>>> Paul Horan[TeamSybase]
>>>
>>> "GW" <gw@gw> wrote in message news:48a4aebc@forums-1-dub...
>>>> Actually, the culprit was 4 right oj's. Now that I've looked at this
>>>> closer they can all be straight joins and that works fine. I think
>>>> we'll
>>>> stick with that for now.
>>>>
>>>> Thanks
>>>>
>>>> <KL> wrote in message news:48a49e1e.399.1681692777@sybase.com...
>>>>> Probably need your SQL to help, but
>>>>> could be just need to specifiy the table as
>>>>> table1.sightings and table2.sightings.
>>>>>
>>>>> Or may need to use aliases - which would be if you are
>>>>> retrieving the same field from the same table more than
>>>>> once.
>>>>>
>>>>> KL
>>>>>> Hi all
>>>>>>
>>>>>> I have a dw with multiple oj's that works great using ASA
>>>>>> but throws the atttached error when trying to run against
>>>>>> MSS 2005. Before I go down the road of changing names as
>>>>>> it suggests(I have no idea how to do that) I was
>>>>>> wondering if this is something that is firing because of
>>>>>> my setup. Can anybody point to a setting that may
>>>>>> suppress this? I see PB offers an "Outer join syntax"
>>>>>> and I tried ANSI and ANSI_Escape but that didn't seem to
>>>>>> change anything
>>>>>>
>>>>>> PB 10.5 7611
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> Greg
>>>>>>
>>>>>>
>>>>>>
>>>>>> [Attachment: dw_error.jpg]
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>
>
>

roj_syntax.jpg

M. Searer Posted on 2008-08-15 21:12:12.0Z
From: "M. Searer" <nospam@nospam.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48a494dd@forums-1-dub> <48a49e1e.399.1681692777@sybase.com> <48a4aebc@forums-1-dub> <48a4ccd7@forums-1-dub> <48a4ed14$1@forums-1-dub> <48a5006e$1@forums-1-dub> <48a5a853@forums-1-dub>
In-Reply-To: <48a5a853@forums-1-dub>
Subject: Re: Multiple Outer joins question using MSS
Lines: 139
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Windows Mail 6.0.6001.18000
X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6001.18049
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a5f12c@forums-1-dub>
Date: 15 Aug 2008 14:12:12 -0700
X-Trace: forums-1-dub 1218834732 10.22.241.152 (15 Aug 2008 14:12:12 -0700)
X-Original-Trace: 15 Aug 2008 14:12:12 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87569
Article PK: 416825

If you look at the syntax tab of your original dw, you will see the mess that PB makes in generating the syntax that goes to the DB.

I think Pauls point is that most sql developers use LOJ and never ROJ as a standard in order to make the code readable and more
understandable.
PB tends to do both, and the error you got is one that I get somewhat often.
PB using ROJ is not the reason for the error, but the use of ROJ makes it that much harder to rewrite the query in syntax since PB.

I still get this type of error in 11.1. It is a bug that hasn't been addressed.

"GW" <gw@gw> wrote in message news:48a5a853@forums-1-dub...
> Paul
>
> I don't have alot of experience in this so I really don't follow you completely. Maybe if I saw it done before my eyes. If you
> look at the PB painter definition of the roj it makes perfect sense to me that it's use is for situations like this where you want
> to retrieve a row in A where the emp_id is missing....see attached
>
>
> "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message news:48a5006e$1@forums-1-dub...
>> That still doesn't strike me as a correct design...
>>
>> This statement of yours: "the users can't even save a row in A unless all
>> cols are filled" tells me that B, C, D, and E are the independent entities
>> (a row can exist in B without a corresponding entry in A), and A is the
>> dependent entity (it has dependent relationship to B, C, D, and E).
>>
>> In that case, if you want to retrieve rows in A where there might be NULL
>> values in one or more of the fk cols to B, C, D, or E, (and yeah, I know you
>> said this is not possible), then you'd want exactly the opposite of what was
>> originally coded.
>>
>> A LOJ B
>> LOJ C
>> LOJ D
>> LOJ E
>>
>> So (using lower case to indicate the join column name) if there was a row
>> where A.b, A.c, and A.d had non-null values, but A.e was NULL, this query
>> would still return the row.
>>
>> Paul Horan[TeamSybase]
>>
>> "GW" <gw@gw> wrote in message news:48a4ed14$1@forums-1-dub...
>>> Thanks for the explaination Paul but in my case the joins were not in
>>> succession. It was 4 tables pointing to the one:
>>>
>>> A roj B
>>> A roj C
>>> A roj D
>>> A roj E
>>>
>>> The only reason I can see having them all roj's was to retrieve any null
>>> columns in A, but, since this original design the users can't even save a
>>> row in A unless all cols are filled, so the roj's are now redundant. Not
>>> really a fix but a smooth sidestep I guess
>>>
>>> What appreciable affect does the "Outer join syntax" have?
>>>
>>> "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message
>>> news:48a4ccd7@forums-1-dub...
>>>> Right Outer Joins were created by Satan himself... <G>
>>>>
>>>> For the most part, this is a left-to-right society (apologies to those
>>>> cultures that use right-to-left character sets...). There is NO QUERY
>>>> that uses ROJs that can't be represented in its syntactic equivalent with
>>>> all LOJs. I challenge anyone to prove me wrong on that.
>>>>
>>>> You just need to make sure that you don't do this:
>>>> A LOJ B, (note the comma)
>>>> B LOJ C
>>>>
>>>> This is the crux of your error. Table B is represented twice without a
>>>> distinguishing correlation. Rewrite it this way:
>>>>
>>>> A LOJ B (note: no comma)
>>>> LOJ C
>>>>
>>>> The fault is PBs actually... The dw painter has never been smart enough
>>>> to rewrite a query into format 2 when you select the tables in the wrong
>>>> order. IOW, if you paint the B to C join first, then the A to B join, you
>>>> get format 1. If you paint the A to B first, then the B to C, it's all
>>>> good and your error never occurs. I haven't retested this in 11, but
>>>> I've complained about it since the 7.x days.
>>>>
>>>> Paul Horan[TeamSybase]
>>>>
>>>> "GW" <gw@gw> wrote in message news:48a4aebc@forums-1-dub...
>>>>> Actually, the culprit was 4 right oj's. Now that I've looked at this
>>>>> closer they can all be straight joins and that works fine. I think we'll
>>>>> stick with that for now.
>>>>>
>>>>> Thanks
>>>>>
>>>>> <KL> wrote in message news:48a49e1e.399.1681692777@sybase.com...
>>>>>> Probably need your SQL to help, but
>>>>>> could be just need to specifiy the table as
>>>>>> table1.sightings and table2.sightings.
>>>>>>
>>>>>> Or may need to use aliases - which would be if you are
>>>>>> retrieving the same field from the same table more than
>>>>>> once.
>>>>>>
>>>>>> KL
>>>>>>> Hi all
>>>>>>>
>>>>>>> I have a dw with multiple oj's that works great using ASA
>>>>>>> but throws the atttached error when trying to run against
>>>>>>> MSS 2005. Before I go down the road of changing names as
>>>>>>> it suggests(I have no idea how to do that) I was
>>>>>>> wondering if this is something that is firing because of
>>>>>>> my setup. Can anybody point to a setting that may
>>>>>>> suppress this? I see PB offers an "Outer join syntax"
>>>>>>> and I tried ANSI and ANSI_Escape but that didn't seem to
>>>>>>> change anything
>>>>>>>
>>>>>>> PB 10.5 7611
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> Greg
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>> [Attachment: dw_error.jpg]
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>>
>
>
>


GW Posted on 2008-08-16 00:43:59.0Z
From: "GW" <gw@gw>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <48a494dd@forums-1-dub> <48a49e1e.399.1681692777@sybase.com> <48a4aebc@forums-1-dub> <48a4ccd7@forums-1-dub> <48a4ed14$1@forums-1-dub> <48a5006e$1@forums-1-dub> <48a5a853@forums-1-dub> <48a5f12c@forums-1-dub>
Subject: Re: Multiple Outer joins question using MSS
Lines: 176
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3138
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3350
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <48a622cf$1@forums-1-dub>
Date: 15 Aug 2008 17:43:59 -0700
X-Trace: forums-1-dub 1218847439 10.22.241.152 (15 Aug 2008 17:43:59 -0700)
X-Original-Trace: 15 Aug 2008 17:43:59 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:87570
Article PK: 416826

OK, I understand the conversion limitations you both speak of, but, I still
don't see how to get creative enough with the LOJ's to allow
nulls.....again.....once I see it in action I'm sure it would be clear to
me. I just don't have time to monkey with it and the straight joins do the
trick so I've kinda moved on

Thanks for your input

"M. Searer" <nospam@nospam.com> wrote in message
news:48a5f12c@forums-1-dub...
> If you look at the syntax tab of your original dw, you will see the mess
> that PB makes in generating the syntax that goes to the DB.
>
> I think Pauls point is that most sql developers use LOJ and never ROJ as a
> standard in order to make the code readable and more understandable.
> PB tends to do both, and the error you got is one that I get somewhat
> often.
> PB using ROJ is not the reason for the error, but the use of ROJ makes it
> that much harder to rewrite the query in syntax since PB.
>
> I still get this type of error in 11.1. It is a bug that hasn't been
> addressed.
>
>
> "GW" <gw@gw> wrote in message news:48a5a853@forums-1-dub...
>> Paul
>>
>> I don't have alot of experience in this so I really don't follow you
>> completely. Maybe if I saw it done before my eyes. If you look at the PB
>> painter definition of the roj it makes perfect sense to me that it's use
>> is for situations like this where you want to retrieve a row in A where
>> the emp_id is missing....see attached
>>
>>
>> "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message
>> news:48a5006e$1@forums-1-dub...
>>> That still doesn't strike me as a correct design...
>>>
>>> This statement of yours: "the users can't even save a row in A unless
>>> all
>>> cols are filled" tells me that B, C, D, and E are the independent
>>> entities
>>> (a row can exist in B without a corresponding entry in A), and A is the
>>> dependent entity (it has dependent relationship to B, C, D, and E).
>>>
>>> In that case, if you want to retrieve rows in A where there might be
>>> NULL
>>> values in one or more of the fk cols to B, C, D, or E, (and yeah, I know
>>> you
>>> said this is not possible), then you'd want exactly the opposite of what
>>> was
>>> originally coded.
>>>
>>> A LOJ B
>>> LOJ C
>>> LOJ D
>>> LOJ E
>>>
>>> So (using lower case to indicate the join column name) if there was a
>>> row
>>> where A.b, A.c, and A.d had non-null values, but A.e was NULL, this
>>> query
>>> would still return the row.
>>>
>>> Paul Horan[TeamSybase]
>>>
>>> "GW" <gw@gw> wrote in message news:48a4ed14$1@forums-1-dub...
>>>> Thanks for the explaination Paul but in my case the joins were not in
>>>> succession. It was 4 tables pointing to the one:
>>>>
>>>> A roj B
>>>> A roj C
>>>> A roj D
>>>> A roj E
>>>>
>>>> The only reason I can see having them all roj's was to retrieve any
>>>> null
>>>> columns in A, but, since this original design the users can't even save
>>>> a
>>>> row in A unless all cols are filled, so the roj's are now redundant.
>>>> Not
>>>> really a fix but a smooth sidestep I guess
>>>>
>>>> What appreciable affect does the "Outer join syntax" have?
>>>>
>>>> "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com> wrote in message
>>>> news:48a4ccd7@forums-1-dub...
>>>>> Right Outer Joins were created by Satan himself... <G>
>>>>>
>>>>> For the most part, this is a left-to-right society (apologies to those
>>>>> cultures that use right-to-left character sets...). There is NO QUERY
>>>>> that uses ROJs that can't be represented in its syntactic equivalent
>>>>> with
>>>>> all LOJs. I challenge anyone to prove me wrong on that.
>>>>>
>>>>> You just need to make sure that you don't do this:
>>>>> A LOJ B, (note the comma)
>>>>> B LOJ C
>>>>>
>>>>> This is the crux of your error. Table B is represented twice without
>>>>> a
>>>>> distinguishing correlation. Rewrite it this way:
>>>>>
>>>>> A LOJ B (note: no comma)
>>>>> LOJ C
>>>>>
>>>>> The fault is PBs actually... The dw painter has never been smart
>>>>> enough
>>>>> to rewrite a query into format 2 when you select the tables in the
>>>>> wrong
>>>>> order. IOW, if you paint the B to C join first, then the A to B join,
>>>>> you
>>>>> get format 1. If you paint the A to B first, then the B to C, it's
>>>>> all
>>>>> good and your error never occurs. I haven't retested this in 11, but
>>>>> I've complained about it since the 7.x days.
>>>>>
>>>>> Paul Horan[TeamSybase]
>>>>>
>>>>> "GW" <gw@gw> wrote in message news:48a4aebc@forums-1-dub...
>>>>>> Actually, the culprit was 4 right oj's. Now that I've looked at this
>>>>>> closer they can all be straight joins and that works fine. I think
>>>>>> we'll
>>>>>> stick with that for now.
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> <KL> wrote in message news:48a49e1e.399.1681692777@sybase.com...
>>>>>>> Probably need your SQL to help, but
>>>>>>> could be just need to specifiy the table as
>>>>>>> table1.sightings and table2.sightings.
>>>>>>>
>>>>>>> Or may need to use aliases - which would be if you are
>>>>>>> retrieving the same field from the same table more than
>>>>>>> once.
>>>>>>>
>>>>>>> KL
>>>>>>>> Hi all
>>>>>>>>
>>>>>>>> I have a dw with multiple oj's that works great using ASA
>>>>>>>> but throws the atttached error when trying to run against
>>>>>>>> MSS 2005. Before I go down the road of changing names as
>>>>>>>> it suggests(I have no idea how to do that) I was
>>>>>>>> wondering if this is something that is firing because of
>>>>>>>> my setup. Can anybody point to a setting that may
>>>>>>>> suppress this? I see PB offers an "Outer join syntax"
>>>>>>>> and I tried ANSI and ANSI_Escape but that didn't seem to
>>>>>>>> change anything
>>>>>>>>
>>>>>>>> PB 10.5 7611
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>> Greg
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> [Attachment: dw_error.jpg]
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
>