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.

Oracle outer join

3 posts in Appeon (partner product) Last posting was on 2008-06-27 21:42:15.0Z
Chris Posted on 2008-06-26 17:30:18.0Z
From: Chris <cmetalic@gmail.com>
Newsgroups: sybase.public.appeon
Subject: Oracle outer join
Date: Thu, 26 Jun 2008 10:30:18 -0700 (PDT)
Organization: http://groups.google.com
Lines: 34
Message-ID: <801b8c13-36f5-415e-8390-7bf93c6703bf@y38g2000hsy.googlegroups.com>
NNTP-Posting-Host: 192.85.37.96
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1214501418 933 127.0.0.1 (26 Jun 2008 17:30:18 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Thu, 26 Jun 2008 17:30:18 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: y38g2000hsy.googlegroups.com; posting-host=192.85.37.96; posting-account=9m3qFAoAAACMZc-bJgOV727WMHBNHILK
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!y38g2000hsy.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.appeon:2164
Article PK: 21551

Hi, I see that there's a workaround for oracle outer joins, using {oj
outerjoin} syntax. Can someone give me an example of that? I use (+)
to indicate my outer joins using native drivers and haven't ever used
the {oj} format. Here's a sample of one of my queries, can someone
give me an example of how to restructure?

Thanks,Chris

My example:

SELECT "INDV_SCHED"."START_DT", "INDV_SCHED"."START_TM",
"INDV_SCHED"."END_TM",
"INDV_SCHED"."SUBJECT", "INDV_SCHED"."MEMO_TX",
"AGENCY"."AGENCY_NM",
"FACILITY"."FACILITY_TX","PROVIDER"."PROV_LAST_NM",
"PROVIDER"."PROV_FIRST_NM",
"PROVIDER"."PROV_MI_NM"
FROM "INDV_SCHED","FACILITY", "AGENCY" , "PROVIDER"
WHERE ( indv_sched.mtf_dmis_id = :mtf_dmis_id ) AND
( indv_sched.provider_id = :provider_id ) AND
( indv_sched.start_dt >= :start_dt_arg ) AND
( indv_sched.start_dt <= :end_dt_arg ) AND
( "INDV_SCHED"."PROVIDER_ID" = "PROVIDER"."PROVIDER_ID" )
and
( "INDV_SCHED".mtf_dmis_id = "PROVIDER".mtf_dmis_id ) and
( indv_sched.cancel_ind is null or indv_sched.cancel_ind =
0 ) AND
( indv_sched.facility_id = facility.facility_id (+))
AND << --- outer join on facility table
( indv_sched.mtf_dmis_id = facility.mtf_dmis_id (+))
AND << --- outer join on facility table
( agency.agency_id = facility.agency_id) AND
( agency.mtf_dmis_id = facility.mtf_dmis_id)


Paul Horan[TeamSybase] Posted on 2008-06-26 19:04:12.0Z
From: "Paul Horan[TeamSybase]" <phoran_nospam@sybase.com>
Newsgroups: sybase.public.appeon
References: <801b8c13-36f5-415e-8390-7bf93c6703bf@y38g2000hsy.googlegroups.com>
Subject: Re: Oracle outer join
Lines: 54
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.3198
X-RFC2646: Format=Flowed; Original
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4863e82c@forums-1-dub>
Date: 26 Jun 2008 12:04:12 -0700
X-Trace: forums-1-dub 1214507052 10.22.241.152 (26 Jun 2008 12:04:12 -0700)
X-Original-Trace: 26 Jun 2008 12:04:12 -0700, vip152.sybase.com
X-Authenticated-User: TeamSybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.appeon:2166
Article PK: 13311

Not really an Appeon question...

Do you need the escape sequence with Oracle? Or can you just ANSI-standard
syntax?


SELECT
"INDV_SCHED"."START_DT",
"INDV_SCHED"."START_TM",
"INDV_SCHED"."END_TM",
"INDV_SCHED"."SUBJECT",
"INDV_SCHED"."MEMO_TX",
"AGENCY"."AGENCY_NM",
"FACILITY"."FACILITY_TX",
"PROVIDER"."PROV_LAST_NM",
"PROVIDER"."PROV_FIRST_NM",
"PROVIDER"."PROV_MI_NM"
FROM
"INDV_SCHED"
JOIN "PROVIDER"
ON "INDV_SCHED"."PROVIDER_ID" = "PROVIDER"."PROVIDER_ID"
and "INDV_SCHED".mtf_dmis_id = "PROVIDER".mtf_dmis_id
LEFT OUTER JOIN "FACILITY"
ON indv_sched.facility_id = facility.facility_id
AND indv_sched.mtf_dmis_id = facility.mtf_dmis_id
LEFT OUTER JOIN "AGENCY"
ON agency.agency_id = facility.agency_id
AND agency.mtf_dmis_id = facility.mtf_dmis_id,

WHERE
( indv_sched.mtf_dmis_id = :mtf_dmis_id ) AND
( indv_sched.provider_id = :provider_id ) AND
( indv_sched.start_dt >= :start_dt_arg ) AND
( indv_sched.start_dt <= :end_dt_arg ) AND
( indv_sched.cancel_ind is null or indv_sched.cancel_ind = 0 )

Paul Horan[TeamSybase]

"Chris" <cmetalic@gmail.com> wrote in message
news:801b8c13-36f5-415e-8390-7bf93c6703bf@y38g2000hsy.googlegroups.com...
> Hi, I see that there's a workaround for oracle outer joins, using {oj
> outerjoin} syntax. Can someone give me an example of that? I use (+)
> to indicate my outer joins using native drivers and haven't ever used
> the {oj} format. Here's a sample of one of my queries, can someone
> give me an example of how to restructure?
>
> Thanks,Chris
>
> My example:
>

>


Chris Posted on 2008-06-27 21:42:15.0Z
From: Chris <cmetalic@gmail.com>
Newsgroups: sybase.public.appeon
Subject: Re: Oracle outer join
Date: Fri, 27 Jun 2008 14:42:15 -0700 (PDT)
Organization: http://groups.google.com
Lines: 3
Message-ID: <4179ca0e-4664-40dd-b3b8-6b1e922455cc@m36g2000hse.googlegroups.com>
References: <801b8c13-36f5-415e-8390-7bf93c6703bf@y38g2000hsy.googlegroups.com> <4863e82c@forums-1-dub>
NNTP-Posting-Host: 192.85.37.65
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
X-Trace: posting.google.com 1214602935 30168 127.0.0.1 (27 Jun 2008 21:42:15 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Fri, 27 Jun 2008 21:42:15 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: m36g2000hse.googlegroups.com; posting-host=192.85.37.65; posting-account=9m3qFAoAAACMZc-bJgOV727WMHBNHILK
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 5.1; .NET CLR 1.1.4322; InfoPath.1; .NET CLR 2.0.50727; .NET CLR 3.0.04506.648; .NET CLR 3.5.21022),gzip(gfe),gzip(gfe)
Path: forums-1-dub!forums-master!newssvr.sybase.com!news-sj-1.sprintlink.net!news-peer1.sprintlink.net!nntp1.phx1.gblx.net!nntp.gblx.net!nntp.gblx.net!border2.nntp.dca.giganews.com!nntp.giganews.com!postnews.google.com!m36g2000hse.googlegroups.com!not-for-mail
Xref: forums-1-dub sybase.public.appeon:2167
Article PK: 21553

Great! Thanks, that's just what I needed to make it work in Appeon.

Chris