I think this also works
PROJECT TEMPORARY tbltemp FROM gl_pur_01 USING gl_acct WHERE +
gl_acct IN (SELECT DISTINCT gl_acct FROM gl_pur_01 WHERE gl_acct IS NOT NULL)
At 12:56 PM 6/25/01 -0400, you wrote:
>Gary
>
>I had given up on the one select fits all until I say your post and I
>found that it would not limit the values to only one instance of what
>was in the table. I added a simple distinct to your using and BAM it's
>a cool treat. Check this out
>
>PROJECT TEMPORARY tbltemp FROM gl_pur_01 USING DISTINCT gl_acct WHERE
>gl_acct IN (SELECT gl_acct FROM gl_pur_01 WHERE gl_acct IS NOT NULL)
>
>This returned only the values I wanted only one time... Still some more
>testing, but I think we are on the right track here.
>
>Thanks
>Jim Limburg
>
>
>GARY Winzeler wrote:
> >
> > (formatting removed..... sorry)
> > Another way would be to create a temp table .....
> >
> > PROJECT TEMPORARY tablename FROM gl_pur_01 USING gl_acct WHERE gl_acct IN +
> > (SELECT gl_acct FROM gl_pur_01 WHERE gl_acct IS NOT NULL)
> >
> > this way you will have all the values you want and only using one command.
> >
> > GARY
> >
> > At 09:36 AM 6/22/01 -0400, you wrote:
> > >G-Day all
> > >
> > >I started out this morning to do what I thought a simple thing. To build
> > >a sub-select statement that would return into a variable the values in a
> > >column that are not null and distinct even if it is in the column more
> > >than once..
> > >
> > >I want the variable to contain 6090,7010,4050,1320,1590 ... and so on...
> > >
> > >The data in the columns is text/12 and here is what I attempted so far.
> > >
> > >SELECT gl_acct INTO vg_gl_acct FROM gl_pur_01 WHERE gl_acct IN +
> > >(SELECT gl_acct FROM gl_pur_01 where gl_acct is not null and limit=1)
> > >
> > >Thanks
> > >Jim Limburg
Regards,
Gary L. Winzeler
DAQtech, Inc.
Data Acquisition Technology
<mailto:[EMAIL PROTECTED]>
<http://www.daqtech.com/>
Office 408-847-4800
Fax 408-847-4097
Cellular 408-483-7739
