VFP 9.1 Unions in subqueries

The following query works:
select ph.Source, ph.SrcRec, rpl.Agency ;
from ( ;
select * ;
from HasOtherRecLevelErrs ;
union ;
select * ;
from HasFieldLevelErrs ;
) as rpl ;
inner join PEDHold as ph ;
on rpl.Agency=ph.Agency ;
into cursor OthersToHoldDetail

but when we try
select ....... where in (select ...... with union here)
does not work.

What is the official policy on what is allowed. The documentation is
sparce on this subject.

Gene
Thu Jan 11 11:57:06 CST 2007

"Jim Czeb" <james.czebiniak@pearlcarroll.com> wrote:

>The following query works:
>select ph.Source, ph.SrcRec, rpl.Agency ;
> from ( ;
> select * ;
> from HasOtherRecLevelErrs ;
> union ;
> select * ;
> from HasFieldLevelErrs ;
> ) as rpl ;
> inner join PEDHold as ph ;
> on rpl.Agency=ph.Agency ;
> into cursor OthersToHoldDetail
>
>but when we try
>select ....... where in (select ...... with union here)
^
>does not work.

Where WHAT is in the subquery? e.g.
select * from orderheader;
where M.PARTNBR in (select partnbr from orderbody)

>What is the official policy on what is allowed. The documentation is
>sparce on this subject.

Sincerely,

Gene Wirchenko

Computerese Irregular Verb Conjugation:
I have preferences.
You have biases.
He/She has prejudices.

Cindy
Thu Jan 11 14:52:02 CST 2007

Hi Jim,

Can you explain a little more what you're trying to do? It sounds like "rpl"
stands for "replacement" but you're joining on Agency being equal so what's
the difference between Agency in the main table and agency in either of the
errors tables.

Since you're doing a Union it looks like you can have two separate queries
against the replacement tables and Union them:


Create Cursor Ph(Source I, SrcRec I, Agency I)
Insert Into Ph Values (1, 1, 1)
Insert Into Ph Values (2, 2, 2)
Insert Into Ph Values (3, 3, 3)
Insert Into Ph Values (4, 4, 4)

Create Cursor HasOtherRecLevelErrs (Source I, SrcRec I, Agency I)
Insert Into HasOtherRecLevelErrs Values (1, 1, 1)
Insert Into HasOtherRecLevelErrs Values (2, 2, 2)

Create Cursor HasFieldLevelErrs (Source I, SrcRec I, Agency I)
Insert Into HasFieldLevelErrs Values (2, 2, 2)
Insert Into HasFieldLevelErrs Values (4, 4, 4)

Create Cursor PedHold (Agency I)
Insert Into PedHold Values (1)
Insert Into PedHold Values (2)
Insert Into PedHold Values (4)


Select Ph.Source, Ph.SrcRec, HasOtherRecLevelErrs.Agency ;
From Ph ;
Inner Join HasOtherRecLevelErrs On ;
Ph.Agency = HasOtherRecLevelErrs.Agency ;
Union ;
Select Ph.Source, Ph.SrcRec, HasFieldLevelErrs.Agency ;
From Ph ;
Inner Join HasFieldLevelErrs On ;
Ph.Agency = HasFieldLevelErrs.Agency ;
Into Cursor OthersToHoldDetail ReadWrite


--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@cindywinegarden.com


"Jim Czeb" <james.czebiniak@pearlcarroll.com> wrote in message
news:1168536327.473155.218800@i56g2000hsf.googlegroups.com...
> The following query works:
> select ph.Source, ph.SrcRec, rpl.Agency ;
> from ( ;
> select * ;
> from HasOtherRecLevelErrs ;
> union ;
> select * ;
> from HasFieldLevelErrs ;
> ) as rpl ;
> inner join PEDHold as ph ;
> on rpl.Agency=ph.Agency ;
> into cursor OthersToHoldDetail
>
> but when we try
> select ....... where in (select ...... with union here)
> does not work.
>
> What is the official policy on what is allowed. The documentation is
> sparce on this subject.
>

tim_witort
Fri Jan 12 10:30:05 CST 2007

Gene Wirchenko seemed to utter in news:3aucq2977rmlvum0ceq2qig9aklv43bpui@
4ax.com:

> "Jim Czeb" <james.czebiniak@pearlcarroll.com> wrote:
>
>>The following query works:
>>select ph.Source, ph.SrcRec, rpl.Agency ;
>> from ( ;
>> select * ;
>> from HasOtherRecLevelErrs ;
>> union ;
>> select * ;
>> from HasFieldLevelErrs ;
>> ) as rpl ;
>> inner join PEDHold as ph ;
>> on rpl.Agency=ph.Agency ;
>> into cursor OthersToHoldDetail
>>
>>but when we try
>>select ....... where in (select ...... with union here)
> ^
>>does not work.
>
> Where WHAT is in the subquery? e.g.
> select * from orderheader;
> where M.PARTNBR in (select partnbr from orderbody)
>

He's asking if you can use a UNION in a subquery when
you are using the "IN" clause:

SELECT * ;
FROM myTable ;
WHERE myField IN ( ;
SELECT otherField ;
FROM otherTable ;
UNION ;
SELECT stillOtherField ;
FROM stillOtherTable ;
)

Since I've not delved much into VFP9 yet, I'll leave that
to those who have.

-- TRW
_______________________________________
t i m
a t
w i t o r t d o t c o m
_______________________________________

Jim
Fri Jan 12 10:54:15 CST 2007

Cindy, the first query isn't of concern. That one works ok and is just
a copy of one a co-worker has done.
The issue is the following union:
select * from table1 where in (select col1,col2 from table2 union
select col1,col2 from table3)

If you try to write a select like this it gets an error and the help
says it is not allowed. The SQL server manual seems to avoid the
issue. Are unions allowed there or do we revert to a 2 step process
(which is ok). Just want to know the ins and outs of this because the
microsoft vfp help is not HELPFUL.

thanks
Czeb

Cindy
Fri Jan 12 11:36:34 CST 2007

Hi Jim,

The FoxPro error says "SQL: Use of UNION in subquery is invalid," so that
means a query of this type is invalid, even in VFP9. As for SQL Server, I
can't say; the question is best asked in a SQL Server newsgroup or tested in
the SQL Server SSMS.

As far as I can see,

Select Field From Table1 ;
Where Field in ;
(Select Field From Table2 Union Select Field from Table3)

is logically equivalent to:

Select Field From Table1 Where Field In (Select Field From Table2) ;
Union ;
Select Field From Table1 Where Field in (Select Field From Table3)

or,

Select Field From Table1 ;
Inner Join Table2 On Table1.Field = Table2.Field ;
Union ;
Select Field From Table1 ;
Inner Join Table3 On Table1.Field = Table2.Field


Often a problematic complex query can be written in a different, but
functionally equivalent way.

--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy@cindywinegarden.com


"Jim Czeb" <james.czebiniak@pearlcarroll.com> wrote in message
news:1168620854.807108.319580@51g2000cwl.googlegroups.com...
> Cindy, the first query isn't of concern. That one works ok and is just
> a copy of one a co-worker has done.
> The issue is the following union:
> select * from table1 where in (select col1,col2 from table2 union
> select col1,col2 from table3)
>
> If you try to write a select like this it gets an error and the help
> says it is not allowed. The SQL server manual seems to avoid the
> issue. Are unions allowed there or do we revert to a 2 step process
> (which is ok). Just want to know the ins and outs of this because the
> microsoft vfp help is not HELPFUL.
>
> thanks
> Czeb
>

Anders
Fri Jan 12 12:13:34 CST 2007

Hi Jim
There's a problem with selecting more than 1 column in an IN subquery.
But anyway, apart from that, VFP does not support UNION in an IN subquery.

You can work around it like this:
CREATE CURSOR x (ii int)
INSERT INTO x VALUES (1)
CREATE CURSOR y (ii int)
CREATE CURSOR z (ii int)
INSERT INTO y VALUES (2)
INSERT INTO z VALUES (1)

SELECT ii FROM X WHERE ii IN ;
(SELECT ii FROM (SELECT ii FROM Y ;
UNION SELECT ii FROM Z ) As Q )

UNION an an IN subqueryis ok in SQL Server

****
PUBLIC n
n = sqlstringconnect("driver=sql native
client;server=dell8600\sqlexpress;trusted_connection=yes")

? SQLEXEC(n, "use tempdb")


TEXT TO lcSQL1 NOSHOW
CREATE TABLE #x (ii int);
INSERT INTO #x VALUES (1);
CREATE TABLE #y (ii int);
CREATE TABLE #z (ii int);
INSERT INTO #y VALUES (2);
INSERT INTO #z VALUES (1)
ENDTEXT

?SQLEXEC(n, lcSQL1)

TEXT TO lcSQL2 NOSHOW
SELECT ii FROM #x WHERE ii IN
(SELECT ii FROM #y UNION
select ii FROM #z )
ENDTEXT

?SQLEXEC(n, lcSQL2 , 'crs1' )
BROWSE LAST NOWAIT
****

-Anders

Jim
Tue Jan 16 06:35:33 CST 2007

Thanks Cindy, your response answered the question I was asking.
Sometimes, when the manuals aren't really clear a lot of legend becomes
prevelant. Just wanted to be sure.

Jim