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.

PROBLEM: Using SELECT statement in the FROM clause.

6 posts in General Discussion Last posting was on 2012-07-10 14:24:10.0Z
Nick N. Posted on 2012-07-09 20:12:54.0Z
Sender: 3a49.4ffb3a61.1804289383@sybase.com
From: Nick N.
Newsgroups: sybase.public.ase.general
Subject: PROBLEM: Using SELECT statement in the FROM clause.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffb3b46.3a67.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Jul 2012 13:12:54 -0700
X-Trace: forums-1-dub 1341864774 172.20.134.41 (9 Jul 2012 13:12:54 -0700)
X-Original-Trace: 9 Jul 2012 13:12:54 -0700, 172.20.134.41
Lines: 106
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31229
Article PK: 74118

Sybase ASE 15.02 on Unix.

When I use a select statement in the FROM clause as below,
with an "outer" WHERE clause, things work fine. But if I try
to insert into a table, in this case, a temp table, it will
ignore the outer WHERE clause.

So this works:
select r_ny_gro_application_group,
r_ny_pcl_account_id,
r_ho_pci_account,
effdt,
status,
r_ho_pro_product_code,
r_ho_cou_economic_agent_code,
r_ny_gaa_gaap_id,
r_ny_act_activity_id,
r_ho_pro_provic,
r_mat_pci_acct_type,
r_mat_pci_al_indicator,
cr_user,
crdt,
upd_user,
upddt,
rep_ny_pcl_descr,
rep_mat_pci_descr
FROM
(
SELECT distinct a.r_ny_gro_application_group,
a.r_ny_pcl_account_id,
a.r_ho_pci_account,
a.effdt,
a.status,
a.r_ho_pro_product_code,
a.r_ho_cou_economic_agent_code,
a.r_ny_gaa_gaap_id,
a.r_ny_act_activity_id,
a.r_ho_pro_provic,
a.r_mat_pci_acct_type,
a.r_mat_pci_al_indicator,
a.cr_user,
a.crdt,
a.upd_user,
a.upddt,
b.descr as rep_ny_pcl_descr,
c.descr as rep_mat_pci_descr
FROM map_pcl_pci a
LEFT OUTER JOIN rep_ny_pcl b ON
a.r_ny_pcl_account_id = b.account_id
LEFT OUTER JOIN rep_mat_pci c ON a.r_ho_pci_account
= c.account and c.status <> 'I' and c.effdt = '01/01/1901'

) as t
where rep_ny_pcl_descr = "QTXGT00423"

This does not. It runs, but it ignores the outer WHERE
clause and insert all rows that is contains in the select
statement in the FROM clause.

insert #myTable
select r_ny_gro_application_group,
r_ny_pcl_account_id,
r_ho_pci_account,
effdt,
status,
r_ho_pro_product_code,
r_ho_cou_economic_agent_code,
r_ny_gaa_gaap_id,
r_ny_act_activity_id,
r_ho_pro_provic,
r_mat_pci_acct_type,
r_mat_pci_al_indicator,
cr_user,
crdt,
upd_user,
upddt,
rep_ny_pcl_descr,
rep_mat_pci_descr
FROM
(
SELECT distinct a.r_ny_gro_application_group,
a.r_ny_pcl_account_id,
a.r_ho_pci_account,
a.effdt,
a.status,
a.r_ho_pro_product_code,
a.r_ho_cou_economic_agent_code,
a.r_ny_gaa_gaap_id,
a.r_ny_act_activity_id,
a.r_ho_pro_provic,
a.r_mat_pci_acct_type,
a.r_mat_pci_al_indicator,
a.cr_user,
a.crdt,
a.upd_user,
a.upddt,
b.descr as rep_ny_pcl_descr,
c.descr as rep_mat_pci_descr
FROM map_pcl_pci a
LEFT OUTER JOIN rep_ny_pcl b ON
a.r_ny_pcl_account_id = b.account_id
LEFT OUTER JOIN rep_mat_pci c ON a.r_ho_pci_account
= c.account and c.status <> 'I' and c.effdt = '01/01/1901'

) as t
where rep_ny_pcl_descr = "QTXGT00423"


Nick N. Posted on 2012-07-09 20:47:01.0Z
Sender: 3a49.4ffb3a61.1804289383@sybase.com
From: Nick N.
Newsgroups: sybase.public.ase.general
Subject: Re: PROBLEM: Using SELECT statement in the FROM clause.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffb4345.3b4e.1681692777@sybase.com>
References: <4ffb3b46.3a67.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 9 Jul 2012 13:47:01 -0700
X-Trace: forums-1-dub 1341866821 172.20.134.41 (9 Jul 2012 13:47:01 -0700)
X-Original-Trace: 9 Jul 2012 13:47:01 -0700, 172.20.134.41
Lines: 120
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31230
Article PK: 74119

Looks like when the outer WHERE clause is referring to an
"outer" table, for example the column rep_ny_pcl_descr which
comes from rep_ny_pcl, it will insert all rows from the
"driving" table, which is map_pcl_pci, into the #temp table.

If I select any column from the "driving" table for the
outer WHERE clause, it works as it should.

It remains, that if I don't try to insert into a table and
just leave it as a select statement, it returns one row,
which is correct.

> Sybase ASE 15.02 on Unix.
>
> When I use a select statement in the FROM clause as below,
> with an "outer" WHERE clause, things work fine. But if I
> try to insert into a table, in this case, a temp table, it
> will ignore the outer WHERE clause.
>
> So this works:
> select r_ny_gro_application_group,
> r_ny_pcl_account_id,
> r_ho_pci_account,
> effdt,
> status,
> r_ho_pro_product_code,
> r_ho_cou_economic_agent_code,
> r_ny_gaa_gaap_id,
> r_ny_act_activity_id,
> r_ho_pro_provic,
> r_mat_pci_acct_type,
> r_mat_pci_al_indicator,
> cr_user,
> crdt,
> upd_user,
> upddt,
> rep_ny_pcl_descr,
> rep_mat_pci_descr
> FROM
> (
> SELECT distinct a.r_ny_gro_application_group,
> a.r_ny_pcl_account_id,
> a.r_ho_pci_account,
> a.effdt,
> a.status,
> a.r_ho_pro_product_code,
> a.r_ho_cou_economic_agent_code,
> a.r_ny_gaa_gaap_id,
> a.r_ny_act_activity_id,
> a.r_ho_pro_provic,
> a.r_mat_pci_acct_type,
> a.r_mat_pci_al_indicator,
> a.cr_user,
> a.crdt,
> a.upd_user,
> a.upddt,
> b.descr as rep_ny_pcl_descr,
> c.descr as rep_mat_pci_descr
> FROM map_pcl_pci a
> LEFT OUTER JOIN rep_ny_pcl b ON
> a.r_ny_pcl_account_id = b.account_id
> LEFT OUTER JOIN rep_mat_pci c ON
> a.r_ho_pci_account = c.account and c.status <> 'I' and
> c.effdt = '01/01/1901'
>
> ) as t
> where rep_ny_pcl_descr = "QTXGT00423"
>
> This does not. It runs, but it ignores the outer WHERE
> clause and insert all rows that is contains in the select
> statement in the FROM clause.
>
> insert #myTable
> select r_ny_gro_application_group,
> r_ny_pcl_account_id,
> r_ho_pci_account,
> effdt,
> status,
> r_ho_pro_product_code,
> r_ho_cou_economic_agent_code,
> r_ny_gaa_gaap_id,
> r_ny_act_activity_id,
> r_ho_pro_provic,
> r_mat_pci_acct_type,
> r_mat_pci_al_indicator,
> cr_user,
> crdt,
> upd_user,
> upddt,
> rep_ny_pcl_descr,
> rep_mat_pci_descr
> FROM
> (
> SELECT distinct a.r_ny_gro_application_group,
> a.r_ny_pcl_account_id,
> a.r_ho_pci_account,
> a.effdt,
> a.status,
> a.r_ho_pro_product_code,
> a.r_ho_cou_economic_agent_code,
> a.r_ny_gaa_gaap_id,
> a.r_ny_act_activity_id,
> a.r_ho_pro_provic,
> a.r_mat_pci_acct_type,
> a.r_mat_pci_al_indicator,
> a.cr_user,
> a.crdt,
> a.upd_user,
> a.upddt,
> b.descr as rep_ny_pcl_descr,
> c.descr as rep_mat_pci_descr
> FROM map_pcl_pci a
> LEFT OUTER JOIN rep_ny_pcl b ON
> a.r_ny_pcl_account_id = b.account_id
> LEFT OUTER JOIN rep_mat_pci c ON
> a.r_ho_pci_account = c.account and c.status <> 'I' and
> c.effdt = '01/01/1901'
>
> ) as t
> where rep_ny_pcl_descr = "QTXGT00423"


syb Posted on 2012-07-10 09:13:30.0Z
Reply-To: <syb>
From: "syb" <No>
Newsgroups: sybase.public.ase.general
References: <4ffb3b46.3a67.1681692777@sybase.com> <4ffb4345.3b4e.1681692777@sybase.com>
Subject: Re: PROBLEM: Using SELECT statement in the FROM clause.
Lines: 128
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4ffbf23a$1@forums-1-dub>
Date: 10 Jul 2012 02:13:30 -0700
X-Trace: forums-1-dub 1341911610 172.20.134.152 (10 Jul 2012 02:13:30 -0700)
X-Original-Trace: 10 Jul 2012 02:13:30 -0700, vip152.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31231
Article PK: 74120

This is CR 614959, fixed from 15.0.3 ESD#4 upwards.
You should really look to install the latest 15.7 ESD#1 if at all possible.

<Nick N.> wrote in message news:4ffb4345.3b4e.1681692777@sybase.com...
> Looks like when the outer WHERE clause is referring to an
> "outer" table, for example the column rep_ny_pcl_descr which
> comes from rep_ny_pcl, it will insert all rows from the
> "driving" table, which is map_pcl_pci, into the #temp table.
>
> If I select any column from the "driving" table for the
> outer WHERE clause, it works as it should.
>
> It remains, that if I don't try to insert into a table and
> just leave it as a select statement, it returns one row,
> which is correct.
>
>> Sybase ASE 15.02 on Unix.
>>
>> When I use a select statement in the FROM clause as below,
>> with an "outer" WHERE clause, things work fine. But if I
>> try to insert into a table, in this case, a temp table, it
>> will ignore the outer WHERE clause.
>>
>> So this works:
>> select r_ny_gro_application_group,
>> r_ny_pcl_account_id,
>> r_ho_pci_account,
>> effdt,
>> status,
>> r_ho_pro_product_code,
>> r_ho_cou_economic_agent_code,
>> r_ny_gaa_gaap_id,
>> r_ny_act_activity_id,
>> r_ho_pro_provic,
>> r_mat_pci_acct_type,
>> r_mat_pci_al_indicator,
>> cr_user,
>> crdt,
>> upd_user,
>> upddt,
>> rep_ny_pcl_descr,
>> rep_mat_pci_descr
>> FROM
>> (
>> SELECT distinct a.r_ny_gro_application_group,
>> a.r_ny_pcl_account_id,
>> a.r_ho_pci_account,
>> a.effdt,
>> a.status,
>> a.r_ho_pro_product_code,
>> a.r_ho_cou_economic_agent_code,
>> a.r_ny_gaa_gaap_id,
>> a.r_ny_act_activity_id,
>> a.r_ho_pro_provic,
>> a.r_mat_pci_acct_type,
>> a.r_mat_pci_al_indicator,
>> a.cr_user,
>> a.crdt,
>> a.upd_user,
>> a.upddt,
>> b.descr as rep_ny_pcl_descr,
>> c.descr as rep_mat_pci_descr
>> FROM map_pcl_pci a
>> LEFT OUTER JOIN rep_ny_pcl b ON
>> a.r_ny_pcl_account_id = b.account_id
>> LEFT OUTER JOIN rep_mat_pci c ON
>> a.r_ho_pci_account = c.account and c.status <> 'I' and
>> c.effdt = '01/01/1901'
>>
>> ) as t
>> where rep_ny_pcl_descr = "QTXGT00423"
>>
>> This does not. It runs, but it ignores the outer WHERE
>> clause and insert all rows that is contains in the select
>> statement in the FROM clause.
>>
>> insert #myTable
>> select r_ny_gro_application_group,
>> r_ny_pcl_account_id,
>> r_ho_pci_account,
>> effdt,
>> status,
>> r_ho_pro_product_code,
>> r_ho_cou_economic_agent_code,
>> r_ny_gaa_gaap_id,
>> r_ny_act_activity_id,
>> r_ho_pro_provic,
>> r_mat_pci_acct_type,
>> r_mat_pci_al_indicator,
>> cr_user,
>> crdt,
>> upd_user,
>> upddt,
>> rep_ny_pcl_descr,
>> rep_mat_pci_descr
>> FROM
>> (
>> SELECT distinct a.r_ny_gro_application_group,
>> a.r_ny_pcl_account_id,
>> a.r_ho_pci_account,
>> a.effdt,
>> a.status,
>> a.r_ho_pro_product_code,
>> a.r_ho_cou_economic_agent_code,
>> a.r_ny_gaa_gaap_id,
>> a.r_ny_act_activity_id,
>> a.r_ho_pro_provic,
>> a.r_mat_pci_acct_type,
>> a.r_mat_pci_al_indicator,
>> a.cr_user,
>> a.crdt,
>> a.upd_user,
>> a.upddt,
>> b.descr as rep_ny_pcl_descr,
>> c.descr as rep_mat_pci_descr
>> FROM map_pcl_pci a
>> LEFT OUTER JOIN rep_ny_pcl b ON
>> a.r_ny_pcl_account_id = b.account_id
>> LEFT OUTER JOIN rep_mat_pci c ON
>> a.r_ho_pci_account = c.account and c.status <> 'I' and
>> c.effdt = '01/01/1901'
>>
>> ) as t
>> where rep_ny_pcl_descr = "QTXGT00423"


Nick N. Posted on 2012-07-10 14:02:11.0Z
Sender: 5c5b.4ffc3545.1804289383@sybase.com
From: Nick N.
Newsgroups: sybase.public.ase.general
Subject: Re: PROBLEM: Using SELECT statement in the FROM clause.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffc35e3.5c7e.1681692777@sybase.com>
References: <4ffbf23a$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Jul 2012 07:02:11 -0700
X-Trace: forums-1-dub 1341928931 172.20.134.41 (10 Jul 2012 07:02:11 -0700)
X-Original-Trace: 10 Jul 2012 07:02:11 -0700, 172.20.134.41
Lines: 135
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31232
Article PK: 74121

Thank you very much!

Unfortunately I am not in charge of such matters, however I
will relay the message to the DBA's.

> This is CR 614959, fixed from 15.0.3 ESD#4 upwards.
> You should really look to install the latest 15.7 ESD#1 if
> at all possible.
>
>
> <Nick N.> wrote in message
> > news:4ffb4345.3b4e.1681692777@sybase.com... Looks like
> > when the outer WHERE clause is referring to an "outer"
> > table, for example the column rep_ny_pcl_descr which
> > comes from rep_ny_pcl, it will insert all rows from the
> "driving" table, which is map_pcl_pci, into the #temp
> table. >
> > If I select any column from the "driving" table for the
> > outer WHERE clause, it works as it should.
> >
> > It remains, that if I don't try to insert into a table
> > and just leave it as a select statement, it returns one
> > row, which is correct.
> >
> >> Sybase ASE 15.02 on Unix.
> >>
> >> When I use a select statement in the FROM clause as
> below, >> with an "outer" WHERE clause, things work fine.
> But if I >> try to insert into a table, in this case, a
> temp table, it >> will ignore the outer WHERE clause.
> >>
> >> So this works:
> >> select r_ny_gro_application_group,
> >> r_ny_pcl_account_id,
> >> r_ho_pci_account,
> >> effdt,
> >> status,
> >> r_ho_pro_product_code,
> >> r_ho_cou_economic_agent_code,
> >> r_ny_gaa_gaap_id,
> >> r_ny_act_activity_id,
> >> r_ho_pro_provic,
> >> r_mat_pci_acct_type,
> >> r_mat_pci_al_indicator,
> >> cr_user,
> >> crdt,
> >> upd_user,
> >> upddt,
> >> rep_ny_pcl_descr,
> >> rep_mat_pci_descr
> >> FROM
> >> (
> >> SELECT distinct a.r_ny_gro_application_group,
> >> a.r_ny_pcl_account_id,
> >> a.r_ho_pci_account,
> >> a.effdt,
> >> a.status,
> >> a.r_ho_pro_product_code,
> >> a.r_ho_cou_economic_agent_code,
> >> a.r_ny_gaa_gaap_id,
> >> a.r_ny_act_activity_id,
> >> a.r_ho_pro_provic,
> >> a.r_mat_pci_acct_type,
> >> a.r_mat_pci_al_indicator,
> >> a.cr_user,
> >> a.crdt,
> >> a.upd_user,
> >> a.upddt,
> >> b.descr as rep_ny_pcl_descr,
> >> c.descr as rep_mat_pci_descr
> >> FROM map_pcl_pci a
> >> LEFT OUTER JOIN rep_ny_pcl b ON
> >> a.r_ny_pcl_account_id = b.account_id
> >> LEFT OUTER JOIN rep_mat_pci c ON
> >> a.r_ho_pci_account = c.account and c.status <> 'I' and
> >> c.effdt = '01/01/1901'
> >>
> >> ) as t
> >> where rep_ny_pcl_descr = "QTXGT00423"
> >>
> >> This does not. It runs, but it ignores the outer WHERE
> >> clause and insert all rows that is contains in the
> select >> statement in the FROM clause.
> >>
> >> insert #myTable
> >> select r_ny_gro_application_group,
> >> r_ny_pcl_account_id,
> >> r_ho_pci_account,
> >> effdt,
> >> status,
> >> r_ho_pro_product_code,
> >> r_ho_cou_economic_agent_code,
> >> r_ny_gaa_gaap_id,
> >> r_ny_act_activity_id,
> >> r_ho_pro_provic,
> >> r_mat_pci_acct_type,
> >> r_mat_pci_al_indicator,
> >> cr_user,
> >> crdt,
> >> upd_user,
> >> upddt,
> >> rep_ny_pcl_descr,
> >> rep_mat_pci_descr
> >> FROM
> >> (
> >> SELECT distinct a.r_ny_gro_application_group,
> >> a.r_ny_pcl_account_id,
> >> a.r_ho_pci_account,
> >> a.effdt,
> >> a.status,
> >> a.r_ho_pro_product_code,
> >> a.r_ho_cou_economic_agent_code,
> >> a.r_ny_gaa_gaap_id,
> >> a.r_ny_act_activity_id,
> >> a.r_ho_pro_provic,
> >> a.r_mat_pci_acct_type,
> >> a.r_mat_pci_al_indicator,
> >> a.cr_user,
> >> a.crdt,
> >> a.upd_user,
> >> a.upddt,
> >> b.descr as rep_ny_pcl_descr,
> >> c.descr as rep_mat_pci_descr
> >> FROM map_pcl_pci a
> >> LEFT OUTER JOIN rep_ny_pcl b ON
> >> a.r_ny_pcl_account_id = b.account_id
> >> LEFT OUTER JOIN rep_mat_pci c ON
> >> a.r_ho_pci_account = c.account and c.status <> 'I' and
> >> c.effdt = '01/01/1901'
> >>
> >> ) as t
> >> where rep_ny_pcl_descr = "QTXGT00423"
>
>


Nick N. Posted on 2012-07-10 14:24:10.0Z
Sender: 5caa.4ffc3690.1804289383@sybase.com
From: Nick N.
Newsgroups: sybase.public.ase.general
Subject: Re: PROBLEM: Using SELECT statement in the FROM clause.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffc3b0a.5d56.1681692777@sybase.com>
References: <4ffc35e3.5c7e.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Jul 2012 07:24:10 -0700
X-Trace: forums-1-dub 1341930250 172.20.134.41 (10 Jul 2012 07:24:10 -0700)
X-Original-Trace: 10 Jul 2012 07:24:10 -0700, 172.20.134.41
Lines: 142
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31234
Article PK: 74123

Assuming that the upgrade to 15.0.3 won't happen anytime
soon (lots of apps running against these databases so it's
not a 1-2-3 decision) is there any other way to write the
SQL?

> Thank you very much!
>
> Unfortunately I am not in charge of such matters, however
> I will relay the message to the DBA's.
>
>
> > This is CR 614959, fixed from 15.0.3 ESD#4 upwards.
> > You should really look to install the latest 15.7 ESD#1
> > if at all possible.
> >
> >
> > <Nick N.> wrote in message
> > > news:4ffb4345.3b4e.1681692777@sybase.com... Looks like
> > > when the outer WHERE clause is referring to an "outer"
> > > table, for example the column rep_ny_pcl_descr which
> > > comes from rep_ny_pcl, it will insert all rows from
> > the "driving" table, which is map_pcl_pci, into the
> > #temp table. >
> > > If I select any column from the "driving" table for
> > > the outer WHERE clause, it works as it should.
> > >
> > > It remains, that if I don't try to insert into a table
> > > and just leave it as a select statement, it returns
> > > one row, which is correct.
> > >
> > >> Sybase ASE 15.02 on Unix.
> > >>
> > >> When I use a select statement in the FROM clause as
> > below, >> with an "outer" WHERE clause, things work
> > fine. But if I >> try to insert into a table, in this
> > case, a temp table, it >> will ignore the outer WHERE
> > clause. >>
> > >> So this works:
> > >> select r_ny_gro_application_group,
> > >> r_ny_pcl_account_id,
> > >> r_ho_pci_account,
> > >> effdt,
> > >> status,
> > >> r_ho_pro_product_code,
> > >> r_ho_cou_economic_agent_code,
> > >> r_ny_gaa_gaap_id,
> > >> r_ny_act_activity_id,
> > >> r_ho_pro_provic,
> > >> r_mat_pci_acct_type,
> > >> r_mat_pci_al_indicator,
> > >> cr_user,
> > >> crdt,
> > >> upd_user,
> > >> upddt,
> > >> rep_ny_pcl_descr,
> > >> rep_mat_pci_descr
> > >> FROM
> > >> (
> > >> SELECT distinct a.r_ny_gro_application_group,
> > >> a.r_ny_pcl_account_id,
> > >> a.r_ho_pci_account,
> > >> a.effdt,
> > >> a.status,
> > >> a.r_ho_pro_product_code,
> > >> a.r_ho_cou_economic_agent_code,
> > >> a.r_ny_gaa_gaap_id,
> > >> a.r_ny_act_activity_id,
> > >> a.r_ho_pro_provic,
> > >> a.r_mat_pci_acct_type,
> > >> a.r_mat_pci_al_indicator,
> > >> a.cr_user,
> > >> a.crdt,
> > >> a.upd_user,
> > >> a.upddt,
> > >> b.descr as rep_ny_pcl_descr,
> > >> c.descr as rep_mat_pci_descr
> > >> FROM map_pcl_pci a
> > >> LEFT OUTER JOIN rep_ny_pcl b ON
> > >> a.r_ny_pcl_account_id = b.account_id
> > >> LEFT OUTER JOIN rep_mat_pci c ON
> > >> a.r_ho_pci_account = c.account and c.status <> 'I'
> > and >> c.effdt = '01/01/1901'
> > >>
> > >> ) as t
> > >> where rep_ny_pcl_descr = "QTXGT00423"
> > >>
> > >> This does not. It runs, but it ignores the outer
> > WHERE >> clause and insert all rows that is contains in
> > the select >> statement in the FROM clause.
> > >>
> > >> insert #myTable
> > >> select r_ny_gro_application_group,
> > >> r_ny_pcl_account_id,
> > >> r_ho_pci_account,
> > >> effdt,
> > >> status,
> > >> r_ho_pro_product_code,
> > >> r_ho_cou_economic_agent_code,
> > >> r_ny_gaa_gaap_id,
> > >> r_ny_act_activity_id,
> > >> r_ho_pro_provic,
> > >> r_mat_pci_acct_type,
> > >> r_mat_pci_al_indicator,
> > >> cr_user,
> > >> crdt,
> > >> upd_user,
> > >> upddt,
> > >> rep_ny_pcl_descr,
> > >> rep_mat_pci_descr
> > >> FROM
> > >> (
> > >> SELECT distinct a.r_ny_gro_application_group,
> > >> a.r_ny_pcl_account_id,
> > >> a.r_ho_pci_account,
> > >> a.effdt,
> > >> a.status,
> > >> a.r_ho_pro_product_code,
> > >> a.r_ho_cou_economic_agent_code,
> > >> a.r_ny_gaa_gaap_id,
> > >> a.r_ny_act_activity_id,
> > >> a.r_ho_pro_provic,
> > >> a.r_mat_pci_acct_type,
> > >> a.r_mat_pci_al_indicator,
> > >> a.cr_user,
> > >> a.crdt,
> > >> a.upd_user,
> > >> a.upddt,
> > >> b.descr as rep_ny_pcl_descr,
> > >> c.descr as rep_mat_pci_descr
> > >> FROM map_pcl_pci a
> > >> LEFT OUTER JOIN rep_ny_pcl b ON
> > >> a.r_ny_pcl_account_id = b.account_id
> > >> LEFT OUTER JOIN rep_mat_pci c ON
> > >> a.r_ho_pci_account = c.account and c.status <> 'I'
> > and >> c.effdt = '01/01/1901'
> > >>
> > >> ) as t
> > >> where rep_ny_pcl_descr = "QTXGT00423"
> >
> >


Nick N. Posted on 2012-07-10 14:06:14.0Z
Sender: 5caa.4ffc3690.1804289383@sybase.com
From: Nick N.
Newsgroups: sybase.public.ase.general
Subject: Re: PROBLEM: Using SELECT statement in the FROM clause.
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4ffc36d6.5cbf.1681692777@sybase.com>
References: <4ffbf23a$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 10 Jul 2012 07:06:14 -0700
X-Trace: forums-1-dub 1341929174 172.20.134.41 (10 Jul 2012 07:06:14 -0700)
X-Original-Trace: 10 Jul 2012 07:06:14 -0700, 172.20.134.41
Lines: 134
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31233
Article PK: 74124

Is the jump from 15.0.2 to 15.0.3 ESD #4 a big jump? 15.0.2
to 15.7 will never happen without tripping over LOTS of red
tape.

> This is CR 614959, fixed from 15.0.3 ESD#4 upwards.
> You should really look to install the latest 15.7 ESD#1 if
> at all possible.
>
>
> <Nick N.> wrote in message
> > news:4ffb4345.3b4e.1681692777@sybase.com... Looks like
> > when the outer WHERE clause is referring to an "outer"
> > table, for example the column rep_ny_pcl_descr which
> > comes from rep_ny_pcl, it will insert all rows from the
> "driving" table, which is map_pcl_pci, into the #temp
> table. >
> > If I select any column from the "driving" table for the
> > outer WHERE clause, it works as it should.
> >
> > It remains, that if I don't try to insert into a table
> > and just leave it as a select statement, it returns one
> > row, which is correct.
> >
> >> Sybase ASE 15.02 on Unix.
> >>
> >> When I use a select statement in the FROM clause as
> below, >> with an "outer" WHERE clause, things work fine.
> But if I >> try to insert into a table, in this case, a
> temp table, it >> will ignore the outer WHERE clause.
> >>
> >> So this works:
> >> select r_ny_gro_application_group,
> >> r_ny_pcl_account_id,
> >> r_ho_pci_account,
> >> effdt,
> >> status,
> >> r_ho_pro_product_code,
> >> r_ho_cou_economic_agent_code,
> >> r_ny_gaa_gaap_id,
> >> r_ny_act_activity_id,
> >> r_ho_pro_provic,
> >> r_mat_pci_acct_type,
> >> r_mat_pci_al_indicator,
> >> cr_user,
> >> crdt,
> >> upd_user,
> >> upddt,
> >> rep_ny_pcl_descr,
> >> rep_mat_pci_descr
> >> FROM
> >> (
> >> SELECT distinct a.r_ny_gro_application_group,
> >> a.r_ny_pcl_account_id,
> >> a.r_ho_pci_account,
> >> a.effdt,
> >> a.status,
> >> a.r_ho_pro_product_code,
> >> a.r_ho_cou_economic_agent_code,
> >> a.r_ny_gaa_gaap_id,
> >> a.r_ny_act_activity_id,
> >> a.r_ho_pro_provic,
> >> a.r_mat_pci_acct_type,
> >> a.r_mat_pci_al_indicator,
> >> a.cr_user,
> >> a.crdt,
> >> a.upd_user,
> >> a.upddt,
> >> b.descr as rep_ny_pcl_descr,
> >> c.descr as rep_mat_pci_descr
> >> FROM map_pcl_pci a
> >> LEFT OUTER JOIN rep_ny_pcl b ON
> >> a.r_ny_pcl_account_id = b.account_id
> >> LEFT OUTER JOIN rep_mat_pci c ON
> >> a.r_ho_pci_account = c.account and c.status <> 'I' and
> >> c.effdt = '01/01/1901'
> >>
> >> ) as t
> >> where rep_ny_pcl_descr = "QTXGT00423"
> >>
> >> This does not. It runs, but it ignores the outer WHERE
> >> clause and insert all rows that is contains in the
> select >> statement in the FROM clause.
> >>
> >> insert #myTable
> >> select r_ny_gro_application_group,
> >> r_ny_pcl_account_id,
> >> r_ho_pci_account,
> >> effdt,
> >> status,
> >> r_ho_pro_product_code,
> >> r_ho_cou_economic_agent_code,
> >> r_ny_gaa_gaap_id,
> >> r_ny_act_activity_id,
> >> r_ho_pro_provic,
> >> r_mat_pci_acct_type,
> >> r_mat_pci_al_indicator,
> >> cr_user,
> >> crdt,
> >> upd_user,
> >> upddt,
> >> rep_ny_pcl_descr,
> >> rep_mat_pci_descr
> >> FROM
> >> (
> >> SELECT distinct a.r_ny_gro_application_group,
> >> a.r_ny_pcl_account_id,
> >> a.r_ho_pci_account,
> >> a.effdt,
> >> a.status,
> >> a.r_ho_pro_product_code,
> >> a.r_ho_cou_economic_agent_code,
> >> a.r_ny_gaa_gaap_id,
> >> a.r_ny_act_activity_id,
> >> a.r_ho_pro_provic,
> >> a.r_mat_pci_acct_type,
> >> a.r_mat_pci_al_indicator,
> >> a.cr_user,
> >> a.crdt,
> >> a.upd_user,
> >> a.upddt,
> >> b.descr as rep_ny_pcl_descr,
> >> c.descr as rep_mat_pci_descr
> >> FROM map_pcl_pci a
> >> LEFT OUTER JOIN rep_ny_pcl b ON
> >> a.r_ny_pcl_account_id = b.account_id
> >> LEFT OUTER JOIN rep_mat_pci c ON
> >> a.r_ho_pci_account = c.account and c.status <> 'I' and
> >> c.effdt = '01/01/1901'
> >>
> >> ) as t
> >> where rep_ny_pcl_descr = "QTXGT00423"
>
>