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.

Converting from *= (outer join) to T-SQL outer join

5 posts in DataWindow Last posting was on 2009-03-17 19:22:57.0Z
glitchy Posted on 2009-03-17 13:13:08.0Z
Sender: 71ad.49bf9e51.1804289383@sybase.com
From: glitchy
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Converting from *= (outer join) to T-SQL outer join
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <49bfa1e3.7269.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 17 Mar 2009 05:13:08 -0800
X-Trace: forums-1-dub 1237295588 10.22.241.41 (17 Mar 2009 05:13:08 -0800)
X-Original-Trace: 17 Mar 2009 05:13:08 -0800, 10.22.241.41
Lines: 83
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88975
Article PK: 418225

Hi guys, please help me with these SQL syntax on datawindow:
I need to convert these ASA 9 SQL statements to ASA 10
compatible outer join statements, I have tried converting
but I dont seem to get the desired output, my syntax perhaps
is wrong, please help me with this one. thank you.

ASA 9 SQL:
===============
SELECT ap.ap_code,
ap.ap_type,
ap.supplier_code,
supplier.supplier_name,
supplier.supplier_grp_code,
supplier_grp.supplier_grp_desc,
ap.trans_type_code,
trans_type.trans_type_desc,
ap.dt_posted,
'' ap_code_dtl,
'' ap_type_dtl
FROM ap,
supplier,
supplier_grp,
trans_type
WHERE ( supplier.company_code *=
supplier_grp.company_code ) AND (
supplier.supplier_grp_code *= supplier_grp.supplier_grp_code
) and
( ap.company_code = supplier.company_code ) and
( ap.supplier_code = supplier.supplier_code ) and
( ap.company_code *= trans_type.company_code ) and
( ap.trans_type_code *= trans_type.trans_type_code ) and
( ap.company_code = '000' ) AND
( ap.status = 'P' ) AND
( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
AND
(supplier.supplier_code >= :as_supplier_fr OR
:as_supplier_fr = '') AND
(supplier.supplier_code <= :as_supplier_to OR
:as_supplier_to = '') AND
(supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
'') AND
(supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
'')

=========
i have converted these SQL statements to ASA 10.0 T-SQL:

SELECT ap.ap_code,
ap.ap_type,
ap.supplier_code,
supplier.supplier_name,
supplier.supplier_grp_code,
supplier_grp.supplier_grp_desc,
ap.trans_type_code,
trans_type.trans_type_desc,
ap.dt_posted,
'' ap_code_dtl,
'' ap_type_dtl
FROM {oj supplier b LEFT OUTER JOIN supplier_grp c
ON b.company_code = c.company_code
AND b.supplier_grp_code = c.supplier_grp_code},
{oj ap a LEFT OUTER JOIN trans_type d
ON a.company_code = d.company_code
AND a.trans_type_code = d.trans_type_code}, ap, supplier,
supplier_grp, trans_type
WHERE ( ap.company_code = supplier.company_code ) and
( ap.supplier_code = supplier.supplier_code ) and
( ap.company_code = '000' ) AND
( ap.status = 'P' ) AND
( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
AND
(supplier.supplier_code >= :as_supplier_fr OR
:as_supplier_fr = '') AND
(supplier.supplier_code <= :as_supplier_to OR
:as_supplier_to = '') AND
(supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
'') AND
(supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
'')

==============
there must be something wrong with my INNER JOIN syntax.
please help. thank you


M. Searer Posted on 2009-03-17 16:10:14.0Z
From: "M. Searer" <nospam@nospam.com>
Newsgroups: sybase.public.powerbuilder.datawindow
References: <49bfa1e3.7269.1681692777@sybase.com>
In-Reply-To: <49bfa1e3.7269.1681692777@sybase.com>
Subject: Re: Converting from *= (outer join) to T-SQL outer join
Lines: 101
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
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: <49bfcb66@forums-1-dub>
Date: 17 Mar 2009 08:10:14 -0800
X-Trace: forums-1-dub 1237306214 10.22.241.152 (17 Mar 2009 08:10:14 -0800)
X-Original-Trace: 17 Mar 2009 08:10:14 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88980
Article PK: 418231

you should rewrite it by hand to simpify it.
that generated syntax is difficult to read and understand.
you should have something like:

from ap
join supplier on
join supplier_grp on
join trans_type on
left outer join supplier b on
left outer join supplier_grp c on ...

what you have appears to retrieve all supplier_grp and trans_type rows with a cartesian product

"glitchy" wrote in message news:49bfa1e3.7269.1681692777@sybase.com...
> Hi guys, please help me with these SQL syntax on datawindow:
> I need to convert these ASA 9 SQL statements to ASA 10
> compatible outer join statements, I have tried converting
> but I dont seem to get the desired output, my syntax perhaps
> is wrong, please help me with this one. thank you.
>
> ASA 9 SQL:
> ===============
> SELECT ap.ap_code,
> ap.ap_type,
> ap.supplier_code,
> supplier.supplier_name,
> supplier.supplier_grp_code,
> supplier_grp.supplier_grp_desc,
> ap.trans_type_code,
> trans_type.trans_type_desc,
> ap.dt_posted,
> '' ap_code_dtl,
> '' ap_type_dtl
> FROM ap,
> supplier,
> supplier_grp,
> trans_type
> WHERE ( supplier.company_code *=
> supplier_grp.company_code ) AND (
> supplier.supplier_grp_code *= supplier_grp.supplier_grp_code
> ) and
> ( ap.company_code = supplier.company_code ) and
> ( ap.supplier_code = supplier.supplier_code ) and
> ( ap.company_code *= trans_type.company_code ) and
> ( ap.trans_type_code *= trans_type.trans_type_code ) and
> ( ap.company_code = '000' ) AND
> ( ap.status = 'P' ) AND
> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
> AND
> (supplier.supplier_code >= :as_supplier_fr OR
> :as_supplier_fr = '') AND
> (supplier.supplier_code <= :as_supplier_to OR
> :as_supplier_to = '') AND
> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
> '') AND
> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
> '')
>
> =========
> i have converted these SQL statements to ASA 10.0 T-SQL:
>
> SELECT ap.ap_code,
> ap.ap_type,
> ap.supplier_code,
> supplier.supplier_name,
> supplier.supplier_grp_code,
> supplier_grp.supplier_grp_desc,
> ap.trans_type_code,
> trans_type.trans_type_desc,
> ap.dt_posted,
> '' ap_code_dtl,
> '' ap_type_dtl
> FROM {oj supplier b LEFT OUTER JOIN supplier_grp c
> ON b.company_code = c.company_code
> AND b.supplier_grp_code = c.supplier_grp_code},
> {oj ap a LEFT OUTER JOIN trans_type d
> ON a.company_code = d.company_code
> AND a.trans_type_code = d.trans_type_code}, ap, supplier,
> supplier_grp, trans_type
> WHERE ( ap.company_code = supplier.company_code ) and
> ( ap.supplier_code = supplier.supplier_code ) and
> ( ap.company_code = '000' ) AND
> ( ap.status = 'P' ) AND
> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
> AND
> (supplier.supplier_code >= :as_supplier_fr OR
> :as_supplier_fr = '') AND
> (supplier.supplier_code <= :as_supplier_to OR
> :as_supplier_to = '') AND
> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
> '') AND
> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
> '')
>
> ==============
> there must be something wrong with my INNER JOIN syntax.
> please help. thank you


"Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_ Posted on 2009-03-17 14:42:51.0Z
From: "Jason 'Bug' Fenter [TeamSybase]" <NO_SPAMMING_jason.fenter_@_teamsybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Converting from *= (outer join) to T-SQL outer join
References: <49bfa1e3.7269.1681692777@sybase.com>
In-Reply-To: <49bfa1e3.7269.1681692777@sybase.com>
X-Enigmail-Version: 0.95.7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <49bfb6eb$1@forums-1-dub>
Date: 17 Mar 2009 06:42:51 -0800
X-Trace: forums-1-dub 1237300971 10.22.241.152 (17 Mar 2009 06:42:51 -0800)
X-Original-Trace: 17 Mar 2009 06:42:51 -0800, vip152.sybase.com
Lines: 100
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88979
Article PK: 418232

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why not just make sure you can see the SQL in graphics mode and let the
PB engine generate the SQL for you?

glitchy wrote:
> Hi guys, please help me with these SQL syntax on datawindow:
> I need to convert these ASA 9 SQL statements to ASA 10
> compatible outer join statements, I have tried converting
> but I dont seem to get the desired output, my syntax perhaps
> is wrong, please help me with this one. thank you.
>
> ASA 9 SQL:
> ===============
> SELECT ap.ap_code,
> ap.ap_type,
> ap.supplier_code,
> supplier.supplier_name,
> supplier.supplier_grp_code,
> supplier_grp.supplier_grp_desc,
> ap.trans_type_code,
> trans_type.trans_type_desc,
> ap.dt_posted,
> '' ap_code_dtl,
> '' ap_type_dtl
> FROM ap,
> supplier,
> supplier_grp,
> trans_type
> WHERE ( supplier.company_code *=
> supplier_grp.company_code ) AND (
> supplier.supplier_grp_code *= supplier_grp.supplier_grp_code
> ) and
> ( ap.company_code = supplier.company_code ) and
> ( ap.supplier_code = supplier.supplier_code ) and
> ( ap.company_code *= trans_type.company_code ) and
> ( ap.trans_type_code *= trans_type.trans_type_code ) and
> ( ap.company_code = '000' ) AND
> ( ap.status = 'P' ) AND
> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
> AND
> (supplier.supplier_code >= :as_supplier_fr OR
> :as_supplier_fr = '') AND
> (supplier.supplier_code <= :as_supplier_to OR
> :as_supplier_to = '') AND
> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
> '') AND
> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
> '')
>
> =========
> i have converted these SQL statements to ASA 10.0 T-SQL:
>
> SELECT ap.ap_code,
> ap.ap_type,
> ap.supplier_code,
> supplier.supplier_name,
> supplier.supplier_grp_code,
> supplier_grp.supplier_grp_desc,
> ap.trans_type_code,
> trans_type.trans_type_desc,
> ap.dt_posted,
> '' ap_code_dtl,
> '' ap_type_dtl
> FROM {oj supplier b LEFT OUTER JOIN supplier_grp c
> ON b.company_code = c.company_code
> AND b.supplier_grp_code = c.supplier_grp_code},
> {oj ap a LEFT OUTER JOIN trans_type d
> ON a.company_code = d.company_code
> AND a.trans_type_code = d.trans_type_code}, ap, supplier,
> supplier_grp, trans_type
> WHERE ( ap.company_code = supplier.company_code ) and
> ( ap.supplier_code = supplier.supplier_code ) and
> ( ap.company_code = '000' ) AND
> ( ap.status = 'P' ) AND
> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
> AND
> (supplier.supplier_code >= :as_supplier_fr OR
> :as_supplier_fr = '') AND
> (supplier.supplier_code <= :as_supplier_to OR
> :as_supplier_to = '') AND
> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
> '') AND
> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
> '')
>
> ==============
> there must be something wrong with my INNER JOIN syntax.
> please help. thank you

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iEYEARECAAYFAkm/tuYACgkQFnUQ/Ci/qM7PyQCfTRyx1xRbalTzhTtXDmaTmMHA
dz0AoJpVaAdlpaOA9xw4ygTbDNpYvAbP
=+Uab
-----END PGP SIGNATURE-----


"Chris Keating [Sybase iAnywhere]" <keating_no Posted on 2009-03-17 19:18:40.0Z
From: "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Converting from *= (outer join) to T-SQL outer join
References: <49bfa1e3.7269.1681692777@sybase.com>
In-Reply-To: <49bfa1e3.7269.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: <49bff790$1@forums-1-dub>
Date: 17 Mar 2009 11:18:40 -0800
X-Trace: forums-1-dub 1237317520 10.22.241.152 (17 Mar 2009 11:18:40 -0800)
X-Original-Trace: 17 Mar 2009 11:18:40 -0800, vip152.sybase.com
Lines: 130
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88994
Article PK: 418244

SQL Anwyhere has a system function that you can use. See REWRITE() in
the documentation.

NOTE: The single quotes will need to be doubled-up when this syntax is
passed to the rewrite function:

i.e.,

select rewrite(
' SELECT ap.ap_code,
ap.ap_type,
ap.supplier_code,
supplier.supplier_name,
supplier.supplier_grp_code,
supplier_grp.supplier_grp_desc,
ap.trans_type_code,
trans_type.trans_type_desc,
ap.dt_posted,
'''' ap_code_dtl,
'''' ap_type_dtl
FROM ap,
supplier,
supplier_grp,
trans_type
WHERE ( supplier.company_code *=
supplier_grp.company_code ) AND (
supplier.supplier_grp_code *= supplier_grp.supplier_grp_code
) and
( ap.company_code = supplier.company_code ) and
( ap.supplier_code = supplier.supplier_code ) and
( ap.company_code *= trans_type.company_code ) and
( ap.trans_type_code *= trans_type.trans_type_code ) and
( ap.company_code = ''000'' ) AND
( ap.status = ''P'' ) AND
( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
AND
(supplier.supplier_code >= :as_supplier_fr OR
:as_supplier_fr = '''') AND
(supplier.supplier_code <= :as_supplier_to OR
:as_supplier_to = '''') AND
(supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
'''') AND
(supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
'''')')

glitchy wrote:
> Hi guys, please help me with these SQL syntax on datawindow:
> I need to convert these ASA 9 SQL statements to ASA 10
> compatible outer join statements, I have tried converting
> but I dont seem to get the desired output, my syntax perhaps
> is wrong, please help me with this one. thank you.
>
> ASA 9 SQL:
> ===============
> SELECT ap.ap_code,
> ap.ap_type,
> ap.supplier_code,
> supplier.supplier_name,
> supplier.supplier_grp_code,
> supplier_grp.supplier_grp_desc,
> ap.trans_type_code,
> trans_type.trans_type_desc,
> ap.dt_posted,
> '' ap_code_dtl,
> '' ap_type_dtl
> FROM ap,
> supplier,
> supplier_grp,
> trans_type
> WHERE ( supplier.company_code *=
> supplier_grp.company_code ) AND (
> supplier.supplier_grp_code *= supplier_grp.supplier_grp_code
> ) and
> ( ap.company_code = supplier.company_code ) and
> ( ap.supplier_code = supplier.supplier_code ) and
> ( ap.company_code *= trans_type.company_code ) and
> ( ap.trans_type_code *= trans_type.trans_type_code ) and
> ( ap.company_code = '000' ) AND
> ( ap.status = 'P' ) AND
> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
> AND
> (supplier.supplier_code >= :as_supplier_fr OR
> :as_supplier_fr = '') AND
> (supplier.supplier_code <= :as_supplier_to OR
> :as_supplier_to = '') AND
> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
> '') AND
> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
> '')
>
> =========
> i have converted these SQL statements to ASA 10.0 T-SQL:
>
> SELECT ap.ap_code,
> ap.ap_type,
> ap.supplier_code,
> supplier.supplier_name,
> supplier.supplier_grp_code,
> supplier_grp.supplier_grp_desc,
> ap.trans_type_code,
> trans_type.trans_type_desc,
> ap.dt_posted,
> '' ap_code_dtl,
> '' ap_type_dtl
> FROM {oj supplier b LEFT OUTER JOIN supplier_grp c
> ON b.company_code = c.company_code
> AND b.supplier_grp_code = c.supplier_grp_code},
> {oj ap a LEFT OUTER JOIN trans_type d
> ON a.company_code = d.company_code
> AND a.trans_type_code = d.trans_type_code}, ap, supplier,
> supplier_grp, trans_type
> WHERE ( ap.company_code = supplier.company_code ) and
> ( ap.supplier_code = supplier.supplier_code ) and
> ( ap.company_code = '000' ) AND
> ( ap.status = 'P' ) AND
> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
> AND
> (supplier.supplier_code >= :as_supplier_fr OR
> :as_supplier_fr = '') AND
> (supplier.supplier_code <= :as_supplier_to OR
> :as_supplier_to = '') AND
> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
> '') AND
> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
> '')
>
> ==============
> there must be something wrong with my INNER JOIN syntax.
> please help. thank you


"Chris Keating [Sybase iAnywhere]" <keating_no Posted on 2009-03-17 19:22:57.0Z
From: "Chris Keating [Sybase iAnywhere]" <keating_no@spam_sybase.com>
User-Agent: Thunderbird 2.0.0.19 (Windows/20081209)
MIME-Version: 1.0
Newsgroups: sybase.public.powerbuilder.datawindow
Subject: Re: Converting from *= (outer join) to T-SQL outer join
References: <49bfa1e3.7269.1681692777@sybase.com> <49bff790$1@forums-1-dub>
In-Reply-To: <49bff790$1@forums-1-dub>
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: <49bff891$1@forums-1-dub>
Date: 17 Mar 2009 11:22:57 -0800
X-Trace: forums-1-dub 1237317777 10.22.241.152 (17 Mar 2009 11:22:57 -0800)
X-Original-Trace: 17 Mar 2009 11:22:57 -0800, vip152.sybase.com
Lines: 139
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.powerbuilder.datawindow:88995
Article PK: 418245

Of course, make sure that you include the ANSI to use the function to
convert to ANSI OJ

i.e., SELECT rewrite(myTSqlStmt,'ANSI')

where myTSqlStmt is the SQL statement to process.

Chris Keating [Sybase iAnywhere] wrote:
> SQL Anwyhere has a system function that you can use. See REWRITE() in
> the documentation.
>
> NOTE: The single quotes will need to be doubled-up when this syntax is
> passed to the rewrite function:
>
> i.e.,
>
> select rewrite(
> ' SELECT ap.ap_code,
> ap.ap_type,
> ap.supplier_code,
> supplier.supplier_name,
> supplier.supplier_grp_code,
> supplier_grp.supplier_grp_desc,
> ap.trans_type_code,
> trans_type.trans_type_desc,
> ap.dt_posted,
> '''' ap_code_dtl,
> '''' ap_type_dtl
> FROM ap,
> supplier,
> supplier_grp,
> trans_type
> WHERE ( supplier.company_code *=
> supplier_grp.company_code ) AND (
> supplier.supplier_grp_code *= supplier_grp.supplier_grp_code
> ) and
> ( ap.company_code = supplier.company_code ) and
> ( ap.supplier_code = supplier.supplier_code ) and
> ( ap.company_code *= trans_type.company_code ) and
> ( ap.trans_type_code *= trans_type.trans_type_code ) and
> ( ap.company_code = ''000'' ) AND
> ( ap.status = ''P'' ) AND
> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
> AND
> (supplier.supplier_code >= :as_supplier_fr OR
> :as_supplier_fr = '''') AND
> (supplier.supplier_code <= :as_supplier_to OR
> :as_supplier_to = '''') AND
> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
> '''') AND
> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
> '''')')
>
>
> glitchy wrote:
>> Hi guys, please help me with these SQL syntax on datawindow:
>> I need to convert these ASA 9 SQL statements to ASA 10
>> compatible outer join statements, I have tried converting
>> but I dont seem to get the desired output, my syntax perhaps
>> is wrong, please help me with this one. thank you.
>>
>> ASA 9 SQL:
>> ===============
>> SELECT ap.ap_code,
>> ap.ap_type,
>> ap.supplier_code,
>> supplier.supplier_name,
>> supplier.supplier_grp_code,
>> supplier_grp.supplier_grp_desc,
>> ap.trans_type_code,
>> trans_type.trans_type_desc,
>> ap.dt_posted,
>> '' ap_code_dtl,
>> '' ap_type_dtl
>> FROM ap,
>> supplier,
>> supplier_grp,
>> trans_type
>> WHERE ( supplier.company_code *=
>> supplier_grp.company_code ) AND (
>> supplier.supplier_grp_code *= supplier_grp.supplier_grp_code
>> ) and
>> ( ap.company_code = supplier.company_code ) and
>> ( ap.supplier_code = supplier.supplier_code ) and
>> ( ap.company_code *= trans_type.company_code ) and
>> ( ap.trans_type_code *= trans_type.trans_type_code ) and
>> ( ap.company_code = '000' ) AND
>> ( ap.status = 'P' ) AND
>> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
>> AND
>> (supplier.supplier_code >= :as_supplier_fr OR
>> :as_supplier_fr = '') AND
>> (supplier.supplier_code <= :as_supplier_to OR
>> :as_supplier_to = '') AND
>> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
>> '') AND
>> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
>> '')
>>
>> =========
>> i have converted these SQL statements to ASA 10.0 T-SQL:
>>
>> SELECT ap.ap_code,
>> ap.ap_type,
>> ap.supplier_code,
>> supplier.supplier_name,
>> supplier.supplier_grp_code,
>> supplier_grp.supplier_grp_desc,
>> ap.trans_type_code,
>> trans_type.trans_type_desc,
>> ap.dt_posted,
>> '' ap_code_dtl,
>> '' ap_type_dtl
>> FROM {oj supplier b LEFT OUTER JOIN supplier_grp c
>> ON b.company_code = c.company_code
>> AND b.supplier_grp_code = c.supplier_grp_code},
>> {oj ap a LEFT OUTER JOIN trans_type d
>> ON a.company_code = d.company_code
>> AND a.trans_type_code = d.trans_type_code}, ap, supplier,
>> supplier_grp, trans_type
>> WHERE ( ap.company_code = supplier.company_code ) and
>> ( ap.supplier_code = supplier.supplier_code ) and
>> ( ap.company_code = '000' ) AND
>> ( ap.status = 'P' ) AND
>> ( ap.date_doc >= :adate_fr AND ap.date_doc <= :adate_to )
>> AND
>> (supplier.supplier_code >= :as_supplier_fr OR
>> :as_supplier_fr = '') AND
>> (supplier.supplier_code <= :as_supplier_to OR
>> :as_supplier_to = '') AND
>> (supplier.supplier_grp_code >= :as_grp_fr OR :as_grp_fr =
>> '') AND
>> (supplier.supplier_grp_code <= :as_grp_to OR :as_grp_to =
>> '')
>>
>> ==============
>> there must be something wrong with my INNER JOIN syntax.
>> please help. thank you