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.

Outer Join

9 posts in DataWindow Last posting was on 2008-11-12 17:57:13.0Z
Yasir Masood Posted on 2008-11-11 17:41:06.0Z
Sender: 6912.4919c190.1804289383@sybase.com
From: Yasir Masood
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Outer Join
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4919c3b1.693e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Nov 2008 09:41:06 -0800
X-Trace: forums-1-dub 1226425266 10.22.241.41 (11 Nov 2008 09:41:06 -0800)
X-Original-Trace: 11 Nov 2008 09:41:06 -0800, 10.22.241.41
Lines: 50
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88156
Article PK: 417410

Hello,

I have recently updated our application from PB8 to PB10.
We are using SQL Server 2000 and PB 10.5.2 Build 7757. The
Users use Citrix to access our application .

I have the following values in the INI File.

DBMS = "OLE DB"
DBParm="PROVIDER='SQLOLEDB',DATASOURCE='TEST1',PROVIDERSTRING='database=TEST'
,PBTrimCharColumns='YES',INTEGRATEDSECURITY='SSPI',OJSyntax='PB'
,appname='YardMaster',StaticBind=0"

The issue I have is that one of the DW does not work on the
Citrix. It works fine in PB8 and when I run the application
local on my computer but I do not get any result on the
Citrix.

Here is the select statemnet for my DW. I would appreciate
your help.

SELECT ym_rail_rack_plan.plant_id,
ym_rail_rack_plan.yard_id,
ym_rail_rack_plan.track_id,
ym_rail_rack_plan.spot_id,
ym_rail_rack_plan.sched_date_time,
ym_rail_rack_plan.session_no,
ym_rail_rack_plan.car_init,
ym_rail_rack_plan.car_no,
ym_rail_rack_plan.order_no,
ym_rail_rack_plan.switch_req_gen,
ym_spot.spot_id,
ym_spot.position
FROM ym_spot,
ym_rail_rack_plan
WHERE ( ym_spot.spot_id *= ym_rail_rack_plan.spot_id)
and
( ym_spot.track_id *= ym_rail_rack_plan.track_id)
and
( ym_spot.yard_id *= ym_rail_rack_plan.yard_id)
and
( ym_spot.plant_id *= ym_rail_rack_plan.plant_id)
and
( ym_spot.track_id = :track_id ) AND
( ym_spot.yard_id = :yard_id ) AND
( ym_spot.plant_id = :plant_id ) AND
( ym_rail_rack_plan.sched_date_time = :sched_date )
AND
( ym_rail_rack_plan.session_no = :session_no )
ORDER BY ym_spot.position ASC


Scott Morris Posted on 2008-11-11 20:44:41.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4919c3b1.693e.1681692777@sybase.com>
Subject: Re: Outer Join
Lines: 56
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; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4919eeb9$1@forums-1-dub>
Date: 11 Nov 2008 12:44:41 -0800
X-Trace: forums-1-dub 1226436281 10.22.241.152 (11 Nov 2008 12:44:41 -0800)
X-Original-Trace: 11 Nov 2008 12:44:41 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88158
Article PK: 417412


> SELECT ym_rail_rack_plan.plant_id,
> ym_rail_rack_plan.yard_id,
> ym_rail_rack_plan.track_id,
> ym_rail_rack_plan.spot_id,
> ym_rail_rack_plan.sched_date_time,
> ym_rail_rack_plan.session_no,
> ym_rail_rack_plan.car_init,
> ym_rail_rack_plan.car_no,
> ym_rail_rack_plan.order_no,
> ym_rail_rack_plan.switch_req_gen,
> ym_spot.spot_id,
> ym_spot.position
> FROM ym_spot,
> ym_rail_rack_plan
> WHERE ( ym_spot.spot_id *= ym_rail_rack_plan.spot_id)
> and
> ( ym_spot.track_id *= ym_rail_rack_plan.track_id)
> and
> ( ym_spot.yard_id *= ym_rail_rack_plan.yard_id)
> and
> ( ym_spot.plant_id *= ym_rail_rack_plan.plant_id)
> and
> ( ym_spot.track_id = :track_id ) AND
> ( ym_spot.yard_id = :yard_id ) AND
> ( ym_spot.plant_id = :plant_id ) AND
> ( ym_rail_rack_plan.sched_date_time = :sched_date )
> AND
> ( ym_rail_rack_plan.session_no = :session_no )
> ORDER BY ym_spot.position ASC

Your query is ambiguous since it applies retrieval arguments to the
unpreserved table ym_rail_rack_plan. In such a case, I don't think there is
any official documetation from MS regarding how the where clause criteria
are evaluated during plan generation. In other words, the engine may apply
the ym_rail_rack_plan criteria during the join (which is what you appear to
desire) or after the join (which would effectively turn the outer join into
an inner join). Assuming that I am correct in understanding your intent, I
suggest that you change the query to use the "outer join" syntax and move
the unpreserved table criteria into the join clause. Unfortunately, the PB
data source editor will not allow you to do this in graphic mode; you will
need to convert it to syntax and manually apply the retrieval arguments.
For example:

select ...
FROM ym_spot as spt
left join ym_rail_rack_plan as pln
on spt.col1 = pln.col1
...
and pln.sched_date_time = :sched_date
and pln.session_no = :session_no
where ...

Note that the *= syntax is deprecated and discouraged. In SQL Server 2005,
this syntax is not supported in the most current compatibility setting.


Yasir Masood Posted on 2008-11-11 21:45:26.0Z
Sender: 6912.4919c190.1804289383@sybase.com
From: Yasir Masood
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Outer Join
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4919fcf6.6ede.1681692777@sybase.com>
References: <4919eeb9$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 11 Nov 2008 13:45:26 -0800
X-Trace: forums-1-dub 1226439926 10.22.241.41 (11 Nov 2008 13:45:26 -0800)
X-Original-Trace: 11 Nov 2008 13:45:26 -0800, 10.22.241.41
Lines: 49
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88161
Article PK: 417416

I have tried changing the Outer Join Syntax to ANSI.
And modified my SELECT statement to use the following,
but I still do not get any results.

FROM ym_spot left outer join ym_rail_rack_plan on
( ym_spot.spot_id = ym_rail_rack_plan.spot_id) and
( ym_spot.track_id = ym_rail_rack_plan.track_id)
and
( ym_spot.yard_id = ym_rail_rack_plan.yard_id) and
( ym_spot.plant_id = ym_rail_rack_plan.plant_id)
and
( ym_spot.track_id = :track_id ) AND
( ym_spot.yard_id = :yard_id ) AND
( ym_spot.plant_id = :plant_id ) AND
( ym_rail_rack_plan.sched_date_time = :sched_date )
AND
( ym_rail_rack_plan.session_no = :session_no )

> Your query is ambiguous since it applies retrieval
> arguments to the unpreserved table ym_rail_rack_plan. In
> such a case, I don't think there is any official
> documetation from MS regarding how the where clause
> criteria are evaluated during plan generation. In other
> words, the engine may apply the ym_rail_rack_plan
> criteria during the join (which is what you appear to
> desire) or after the join (which would effectively turn
> the outer join into an inner join). Assuming that I am
> correct in understanding your intent, I suggest that you
> change the query to use the "outer join" syntax and move
> the unpreserved table criteria into the join clause.
> Unfortunately, the PB data source editor will not allow
> you to do this in graphic mode; you will need to convert
> it to syntax and manually apply the retrieval arguments.
> For example:
>
> select ...
> FROM ym_spot as spt
> left join ym_rail_rack_plan as pln
> on spt.col1 = pln.col1
> ...
> and pln.sched_date_time = :sched_date
> and pln.session_no = :session_no
> where ...
>
> Note that the *= syntax is deprecated and discouraged. In
> SQL Server 2005, this syntax is not supported in the most
> current compatibility setting.
>
>


Paul Horan[Sybase] Posted on 2008-11-12 04:51:04.0Z
From: "Paul Horan[Sybase]" <phoran_nospam@sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4919eeb9$1@forums-1-dub> <4919fcf6.6ede.1681692777@sybase.com>
Subject: Re: Outer Join
Lines: 62
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; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <491a60b8$1@forums-1-dub>
Date: 11 Nov 2008 20:51:04 -0800
X-Trace: forums-1-dub 1226465464 10.22.241.152 (11 Nov 2008 20:51:04 -0800)
X-Original-Trace: 11 Nov 2008 20:51:04 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88166
Article PK: 417419

It's hard to tell from your query alone, which is the "base" table in the
query, and which is the "null-supplying" side. You have equality conditions
on both sides of the join - that really makes me think you don't need an
outer join at all...

Do you want all the rows in ym_spot, whether or not there's a match in
ym_rail_rack_plan, or vice versa?

Paul Horan[Sybase]

<Yasir Masood> wrote in message news:4919fcf6.6ede.1681692777@sybase.com...
>I have tried changing the Outer Join Syntax to ANSI.
> And modified my SELECT statement to use the following,
> but I still do not get any results.
>
> FROM ym_spot left outer join ym_rail_rack_plan on
> ( ym_spot.spot_id = ym_rail_rack_plan.spot_id) and
> ( ym_spot.track_id = ym_rail_rack_plan.track_id)
> and
> ( ym_spot.yard_id = ym_rail_rack_plan.yard_id) and
> ( ym_spot.plant_id = ym_rail_rack_plan.plant_id)
> and
> ( ym_spot.track_id = :track_id ) AND
> ( ym_spot.yard_id = :yard_id ) AND
> ( ym_spot.plant_id = :plant_id ) AND
> ( ym_rail_rack_plan.sched_date_time = :sched_date )
> AND
> ( ym_rail_rack_plan.session_no = :session_no )
>
>> Your query is ambiguous since it applies retrieval
>> arguments to the unpreserved table ym_rail_rack_plan. In
>> such a case, I don't think there is any official
>> documetation from MS regarding how the where clause
>> criteria are evaluated during plan generation. In other
>> words, the engine may apply the ym_rail_rack_plan
>> criteria during the join (which is what you appear to
>> desire) or after the join (which would effectively turn
>> the outer join into an inner join). Assuming that I am
>> correct in understanding your intent, I suggest that you
>> change the query to use the "outer join" syntax and move
>> the unpreserved table criteria into the join clause.
>> Unfortunately, the PB data source editor will not allow
>> you to do this in graphic mode; you will need to convert
>> it to syntax and manually apply the retrieval arguments.
>> For example:
>>
>> select ...
>> FROM ym_spot as spt
>> left join ym_rail_rack_plan as pln
>> on spt.col1 = pln.col1
>> ...
>> and pln.sched_date_time = :sched_date
>> and pln.session_no = :session_no
>> where ...
>>
>> Note that the *= syntax is deprecated and discouraged. In
>> SQL Server 2005, this syntax is not supported in the most
>> current compatibility setting.
>>
>>


Yasir Masood Posted on 2008-11-12 12:36:56.0Z
Sender: 6912.4919c190.1804289383@sybase.com
From: Yasir Masood
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Outer Join
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <491acde8.59a.1681692777@sybase.com>
References: <491a60b8$1@forums-1-dub>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Nov 2008 04:36:56 -0800
X-Trace: forums-1-dub 1226493416 10.22.241.41 (12 Nov 2008 04:36:56 -0800)
X-Original-Trace: 12 Nov 2008 04:36:56 -0800, 10.22.241.41
Lines: 72
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88168
Article PK: 417423

The base table is ym_spot. I need all the rows from this
table
even if there are no matching rows in the ym_rail_rack_plan
table. But if there are any rows in the secondary table, I
need
to retrieve it based on the Date and Session.

Thanks

> It's hard to tell from your query alone, which is the
> "base" table in the query, and which is the
> "null-supplying" side. You have equality conditions on
> both sides of the join - that really makes me think you
> don't need an outer join at all...
>
> Do you want all the rows in ym_spot, whether or not
> there's a match in ym_rail_rack_plan, or vice versa?
>
> Paul Horan[Sybase]
>
> <Yasir Masood> wrote in message
> news:4919fcf6.6ede.1681692777@sybase.com... >I have tried
> > changing the Outer Join Syntax to ANSI. And modified my
> > SELECT statement to use the following, but I still do
> not get any results. >
> > FROM ym_spot left outer join ym_rail_rack_plan on
> > ( ym_spot.spot_id = ym_rail_rack_plan.spot_id)
> > and ( ym_spot.track_id =
> > ym_rail_rack_plan.track_id) and
> > ( ym_spot.yard_id = ym_rail_rack_plan.yard_id)
> > and ( ym_spot.plant_id =
> > ym_rail_rack_plan.plant_id) and
> > ( ym_spot.track_id = :track_id ) AND
> > ( ym_spot.yard_id = :yard_id ) AND
> > ( ym_spot.plant_id = :plant_id ) AND
> > ( ym_rail_rack_plan.sched_date_time =
> > :sched_date ) AND
> > ( ym_rail_rack_plan.session_no = :session_no )
> >
> >> Your query is ambiguous since it applies retrieval
> >> arguments to the unpreserved table ym_rail_rack_plan.
> In >> such a case, I don't think there is any official
> >> documetation from MS regarding how the where clause
> >> criteria are evaluated during plan generation. In
> other >> words, the engine may apply the
> ym_rail_rack_plan >> criteria during the join (which is
> what you appear to >> desire) or after the join (which
> would effectively turn >> the outer join into an inner
> join). Assuming that I am >> correct in understanding
> your intent, I suggest that you >> change the query to
> use the "outer join" syntax and move >> the unpreserved
> table criteria into the join clause. >> Unfortunately, the
> PB data source editor will not allow >> you to do this in
> graphic mode; you will need to convert >> it to syntax
> and manually apply the retrieval arguments. >> For
> example: >>
> >> select ...
> >> FROM ym_spot as spt
> >> left join ym_rail_rack_plan as pln
> >> on spt.col1 = pln.col1
> >> ...
> >> and pln.sched_date_time = :sched_date
> >> and pln.session_no = :session_no
> >> where ...
> >>
> >> Note that the *= syntax is deprecated and discouraged.
> In >> SQL Server 2005, this syntax is not supported in
> the most >> current compatibility setting.
> >>
> >>
>
>


"Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_ Posted on 2008-11-12 17:18:00.0Z
From: "Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_@_teamsybase.com>
User-Agent: Thunderbird 2.0.0.17 (Windows/20080914)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Outer Join
References: <491a60b8$1@forums-1-dub> <491acde8.59a.1681692777@sybase.com>
In-Reply-To: <491acde8.59a.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <491b0fc8$1@forums-1-dub>
Date: 12 Nov 2008 09:18:00 -0800
X-Trace: forums-1-dub 1226510280 10.22.241.152 (12 Nov 2008 09:18:00 -0800)
X-Original-Trace: 12 Nov 2008 09:18:00 -0800, vip152.sybase.com
Lines: 109
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88175
Article PK: 417425

Your JOIN clause should *only* contain the criteria needed for the JOIN.
Your filtering criteria should live in the WHERE clause only. It looks
like you need something along the lines of:

SELECT ym_rail_rack_plan.plant_id,
ym_rail_rack_plan.yard_id,
ym_rail_rack_plan.track_id,
ym_rail_rack_plan.spot_id,
ym_rail_rack_plan.sched_date_time,
ym_rail_rack_plan.session_no,
ym_rail_rack_plan.car_init,
ym_rail_rack_plan.car_no,
ym_rail_rack_plan.order_no,
ym_rail_rack_plan.switch_req_gen,
ym_spot.spot_id,
ym_spot.position
FROM ym_spot
LEFT JOIN ym_rail_rack_plan ON
ym_spot.spot_id = ym_rail_rack_plan.spot_id
AND ym_spot.track_id = ym_rail_rack_plan.track_id
AND ym_spot.yard_id = ym_rail_rack_plan.yard_id
AND ym_spot.plant_id = ym_rail_rack_plan.plant_id
WHERE ym_spot.track_id = :track_id
AND ym_spot.yard_id = :yard_id
AND ym_spot.plant_id = :plant_id
AND ( ym_rail_rack_plan.<some required column> IS NULL
OR ( ym_rail_rack_plan.sched_date_time = :sched_date
AND ym_rail_rack_plan.session_no = :session_no
)
)

The "ym_rail_rack_plan.<some required column> IS NULL" line makes sure
you still grab rows that don't have anything in the ym_rail_rack_plan
table. But if you do have rows there, the two lines after the OR
operator make sure that the rows fit in your criteria.

Yasir Masood wrote:
> The base table is ym_spot. I need all the rows from this
> table
> even if there are no matching rows in the ym_rail_rack_plan
> table. But if there are any rows in the secondary table, I
> need
> to retrieve it based on the Date and Session.
>
> Thanks
>
>> It's hard to tell from your query alone, which is the
>> "base" table in the query, and which is the
>> "null-supplying" side. You have equality conditions on
>> both sides of the join - that really makes me think you
>> don't need an outer join at all...
>>
>> Do you want all the rows in ym_spot, whether or not
>> there's a match in ym_rail_rack_plan, or vice versa?
>>
>> Paul Horan[Sybase]
>>
>> <Yasir Masood> wrote in message
>> news:4919fcf6.6ede.1681692777@sybase.com... >I have tried
>>> changing the Outer Join Syntax to ANSI. And modified my
>>> SELECT statement to use the following, but I still do
>> not get any results. >
>>> FROM ym_spot left outer join ym_rail_rack_plan on
>>> ( ym_spot.spot_id = ym_rail_rack_plan.spot_id)
>>> and ( ym_spot.track_id =
>>> ym_rail_rack_plan.track_id) and
>>> ( ym_spot.yard_id = ym_rail_rack_plan.yard_id)
>>> and ( ym_spot.plant_id =
>>> ym_rail_rack_plan.plant_id) and
>>> ( ym_spot.track_id = :track_id ) AND
>>> ( ym_spot.yard_id = :yard_id ) AND
>>> ( ym_spot.plant_id = :plant_id ) AND
>>> ( ym_rail_rack_plan.sched_date_time =
>>> :sched_date ) AND
>>> ( ym_rail_rack_plan.session_no = :session_no )
>>>
>>>> Your query is ambiguous since it applies retrieval
>>>> arguments to the unpreserved table ym_rail_rack_plan.
>> In >> such a case, I don't think there is any official
>>>> documetation from MS regarding how the where clause
>>>> criteria are evaluated during plan generation. In
>> other >> words, the engine may apply the
>> ym_rail_rack_plan >> criteria during the join (which is
>> what you appear to >> desire) or after the join (which
>> would effectively turn >> the outer join into an inner
>> join). Assuming that I am >> correct in understanding
>> your intent, I suggest that you >> change the query to
>> use the "outer join" syntax and move >> the unpreserved
>> table criteria into the join clause. >> Unfortunately, the
>> PB data source editor will not allow >> you to do this in
>> graphic mode; you will need to convert >> it to syntax
>> and manually apply the retrieval arguments. >> For
>> example: >>
>>>> select ...
>>>> FROM ym_spot as spt
>>>> left join ym_rail_rack_plan as pln
>>>> on spt.col1 = pln.col1
>>>> ...
>>>> and pln.sched_date_time = :sched_date
>>>> and pln.session_no = :session_no
>>>> where ...
>>>>
>>>> Note that the *= syntax is deprecated and discouraged.
>> In >> SQL Server 2005, this syntax is not supported in
>> the most >> current compatibility setting.
>>>>
>>


Paul Horan[Sybase] Posted on 2008-11-12 17:06:48.0Z
From: "Paul Horan[Sybase]" <phoran_nospam@sybase.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <491a60b8$1@forums-1-dub> <491acde8.59a.1681692777@sybase.com>
Subject: Re: Outer Join
Lines: 94
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5512
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <491b0d28$1@forums-1-dub>
Date: 12 Nov 2008 09:06:48 -0800
X-Trace: forums-1-dub 1226509608 10.22.241.152 (12 Nov 2008 09:06:48 -0800)
X-Original-Trace: 12 Nov 2008 09:06:48 -0800, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88174
Article PK: 417428

That's this:

FROM ym_spot left outer join ym_rail_rack_plan on
( ym_spot.spot_id = ym_rail_rack_plan.spot_id) and
( ym_spot.track_id = ym_rail_rack_plan.track_id) and
( ym_spot.yard_id = ym_rail_rack_plan.yard_id) and
( ym_spot.plant_id = ym_rail_rack_plan.plant_id) and
( ym_rail_rack_plan.sched_date_time = :sched_date ) AND
( ym_rail_rack_plan.session_no = :session_no )
WHERE
( ym_spot.track_id = :track_id ) AND
( ym_spot.yard_id = :yard_id ) AND
( ym_spot.plant_id = :plant_id ) AND

If that doesn't return any rows, then the problem isn't syntax - it would be
something with your data.

Paul Horan[Sybase]

<Yasir Masood> wrote in message news:491acde8.59a.1681692777@sybase.com...
> The base table is ym_spot. I need all the rows from this
> table
> even if there are no matching rows in the ym_rail_rack_plan
> table. But if there are any rows in the secondary table, I
> need
> to retrieve it based on the Date and Session.
>
> Thanks
>
>> It's hard to tell from your query alone, which is the
>> "base" table in the query, and which is the
>> "null-supplying" side. You have equality conditions on
>> both sides of the join - that really makes me think you
>> don't need an outer join at all...
>>
>> Do you want all the rows in ym_spot, whether or not
>> there's a match in ym_rail_rack_plan, or vice versa?
>>
>> Paul Horan[Sybase]
>>
>> <Yasir Masood> wrote in message
>> news:4919fcf6.6ede.1681692777@sybase.com... >I have tried
>> > changing the Outer Join Syntax to ANSI. And modified my
>> > SELECT statement to use the following, but I still do
>> not get any results. >
>> > FROM ym_spot left outer join ym_rail_rack_plan on
>> > ( ym_spot.spot_id = ym_rail_rack_plan.spot_id)
>> > and ( ym_spot.track_id =
>> > ym_rail_rack_plan.track_id) and
>> > ( ym_spot.yard_id = ym_rail_rack_plan.yard_id)
>> > and ( ym_spot.plant_id =
>> > ym_rail_rack_plan.plant_id) and
>> > ( ym_spot.track_id = :track_id ) AND
>> > ( ym_spot.yard_id = :yard_id ) AND
>> > ( ym_spot.plant_id = :plant_id ) AND
>> > ( ym_rail_rack_plan.sched_date_time =
>> > :sched_date ) AND
>> > ( ym_rail_rack_plan.session_no = :session_no )
>> >
>> >> Your query is ambiguous since it applies retrieval
>> >> arguments to the unpreserved table ym_rail_rack_plan.
>> In >> such a case, I don't think there is any official
>> >> documetation from MS regarding how the where clause
>> >> criteria are evaluated during plan generation. In
>> other >> words, the engine may apply the
>> ym_rail_rack_plan >> criteria during the join (which is
>> what you appear to >> desire) or after the join (which
>> would effectively turn >> the outer join into an inner
>> join). Assuming that I am >> correct in understanding
>> your intent, I suggest that you >> change the query to
>> use the "outer join" syntax and move >> the unpreserved
>> table criteria into the join clause. >> Unfortunately, the
>> PB data source editor will not allow >> you to do this in
>> graphic mode; you will need to convert >> it to syntax
>> and manually apply the retrieval arguments. >> For
>> example: >>
>> >> select ...
>> >> FROM ym_spot as spt
>> >> left join ym_rail_rack_plan as pln
>> >> on spt.col1 = pln.col1
>> >> ...
>> >> and pln.sched_date_time = :sched_date
>> >> and pln.session_no = :session_no
>> >> where ...
>> >>
>> >> Note that the *= syntax is deprecated and discouraged.
>> In >> SQL Server 2005, this syntax is not supported in
>> the most >> current compatibility setting.
>> >>
>> >>
>>
>>


Scott Morris Posted on 2008-11-12 13:32:48.0Z
From: "Scott Morris" <bogus@bogus.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <4919eeb9$1@forums-1-dub> <4919fcf6.6ede.1681692777@sybase.com>
Subject: Re: Outer Join
Lines: 34
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; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <491adb00$1@forums-1-dub>
Date: 12 Nov 2008 05:32:48 -0800
X-Trace: forums-1-dub 1226496768 10.22.241.152 (12 Nov 2008 05:32:48 -0800)
X-Original-Trace: 12 Nov 2008 05:32:48 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88169
Article PK: 417424


<Yasir Masood> wrote in message news:4919fcf6.6ede.1681692777@sybase.com...
>I have tried changing the Outer Join Syntax to ANSI.
> And modified my SELECT statement to use the following,
> but I still do not get any results.
>
> FROM ym_spot left outer join ym_rail_rack_plan on
> ( ym_spot.spot_id = ym_rail_rack_plan.spot_id) and
> ( ym_spot.track_id = ym_rail_rack_plan.track_id)
> and
> ( ym_spot.yard_id = ym_rail_rack_plan.yard_id) and
> ( ym_spot.plant_id = ym_rail_rack_plan.plant_id)
> and
> ( ym_rail_rack_plan.sched_date_time = :sched_date )
> AND
> ( ym_rail_rack_plan.session_no = :session_no )

Though it shouldn't make a difference as far as I can tell, the preceding
criteria are those for the join. The following are those that limit your
result set to the desired items. Personally, I would put them in the WHERE
clause. It also occurs to me that perhaps the relationship between spot and
plan is based on spot_id alone - is so, that is another problem with the
query.

> ( ym_spot.track_id = :track_id ) AND
> ( ym_spot.yard_id = :yard_id ) AND
> ( ym_spot.plant_id = :plant_id ) AND


I suggest you try your query first in Query Analyzer (or some other tool) to
eliminate any PB issues that might be contributing to the problem. Based on
what you posted, that should do exactly what you want. If it isn't doing
that, then there must be some other issue you need to identify.


KM Posted on 2008-11-12 17:57:13.0Z
Sender: 6e5c.4919f5e9.1804289383@sybase.com
From: KM
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Outer Join
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <491b18f8.da9.1681692777@sybase.com>
References: <4919c3b1.693e.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 12 Nov 2008 09:57:13 -0800
X-Trace: forums-1-dub 1226512633 10.22.241.41 (12 Nov 2008 09:57:13 -0800)
X-Original-Trace: 12 Nov 2008 09:57:13 -0800, 10.22.241.41
Lines: 70
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88177
Article PK: 417430

Personally, I am not super-confident when doing complex
queries,
so I would start with something like:

SELECT ym_rail_rack_plan.plant_id, ym_spot.spot_id
FROM ym_spot
LEFT OUTER JOIN ym_rail_rack_plan ON
ym_rail_rack_plan.spot_id
= ym_spot.spot_id
WHERE ym_spot.track_id = :track_id

And, looking at the data to be sure there is a
ym_spot.track_id matching your :track_id and that there is a
row for ym_rail_rack_plan.spot_id for that :track_id

Then add the rest of the query elements one by one.

HTH KM

> Hello,
>
> I have recently updated our application from PB8 to PB10.
> We are using SQL Server 2000 and PB 10.5.2 Build 7757. The
> Users use Citrix to access our application .
>
> I have the following values in the INI File.
>
> DBMS = "OLE DB"
> DBParm="PROVIDER='SQLOLEDB',DATASOURCE='TEST1'
> ,PROVIDERSTRING='database=TEST' ,PBTrimCharColumns='YES'
> ,INTEGRATEDSECURITY='SSPI',OJSyntax='PB'
> ,appname='YardMaster',StaticBind=0"
>
> The issue I have is that one of the DW does not work on
> the Citrix. It works fine in PB8 and when I run the
> application local on my computer but I do not get any
> result on the Citrix.
>
> Here is the select statemnet for my DW. I would appreciate
> your help.
>
> SELECT ym_rail_rack_plan.plant_id,
> ym_rail_rack_plan.yard_id,
> ym_rail_rack_plan.track_id,
> ym_rail_rack_plan.spot_id,
> ym_rail_rack_plan.sched_date_time,
> ym_rail_rack_plan.session_no,
> ym_rail_rack_plan.car_init,
> ym_rail_rack_plan.car_no,
> ym_rail_rack_plan.order_no,
> ym_rail_rack_plan.switch_req_gen,
> ym_spot.spot_id,
> ym_spot.position
> FROM ym_spot,
> ym_rail_rack_plan
> WHERE ( ym_spot.spot_id *= ym_rail_rack_plan.spot_id)
> and
> ( ym_spot.track_id *=
> ym_rail_rack_plan.track_id) and
> ( ym_spot.yard_id *= ym_rail_rack_plan.yard_id)
> and
> ( ym_spot.plant_id *=
> ym_rail_rack_plan.plant_id) and
> ( ym_spot.track_id = :track_id ) AND
> ( ym_spot.yard_id = :yard_id ) AND
> ( ym_spot.plant_id = :plant_id ) AND
> ( ym_rail_rack_plan.sched_date_time = :sched_date
> ) AND
> ( ym_rail_rack_plan.session_no = :session_no )
> ORDER BY ym_spot.position ASC