Re: DISTINCT
From: Pete <Pete_at_xxx.kolton.com>
Date: Thu, 6 May 1999 11:04:52 +0100
Message-ID: <elesGh6l#GA.306_at_cpmsnbbsa05>
Date: Thu, 6 May 1999 11:04:52 +0100
Message-ID: <elesGh6l#GA.306_at_cpmsnbbsa05>
This is almost correct, except that the ROWID replacement should be the first column in the list, and the constant that you use needs to be a valid format for a ROWID.
The question that then springs to mind is why you would want to hide some of the records from the form. If some records are duplicated and some are not, do you really want to hide those from the user? Don't forget that they will appear when the user updates the first record. It seems to me that something is wrong with your system design.
-- ======================================================== Pete Kolton ORACLE Systems Consultant All reasonable offers considered :-) http://www.kolton.com ======================================================== Prabhakar Narayanan <prabhakar.narayanan_at_clsasia.com> wrote in message news:373135E5.4470A2A6_at_clsasia.com...Received on Thu May 06 1999 - 12:04:52 CEST
> Hi
>
> To my knowledge, you cannot use DISTINCT in the where clause as ORACLE
> appends the rowid of every record fetched in the database block
>
> However there is a work around. You have to use UNION in your where clause
> and forcefully stop the 1st query fetching records, for instance you can
> specify
>
> SELECT COL1, COL2, COL3, rowid (oracle will append rowid)
> FROM TABLE 1
> WHERE 1 = 2 (one will never be equal to 2, hence the 1st select will fail)
>
> UNION
>
> SELECT DISTINCT COL1, COL2, CHARTOROWID('a')
> FROM TABLE 2
> ..
> ..
>
> (the above query will return your desired values)..
>
> Union will expect all the columns to be of same data type. Therefore you
> have to use CHARTOROWID of a constant (in our example 'a') and proceed
with
> our logic
>
> Hope this helps
>
> Regards
>
> N.Prabhakar
>
>
> Markus Schwabe wrote:
>
> > Hello,
> >
> > Question:
> > How do I make a DISTINCT -Clause in my Base-Table-Block?
> >
> > SELECT DISTINCT columns FROM table....
> >
> > Do I have to program it in a Trigger? Which one? And how?
> > In the Properties I can handle a Where-Clause, an
> > Order-By-Clause...fine.
> > But what is with a Distinct? Why exists no Switch in the Properties like
> >
> > Distinct: true or false, yes or no...
> >
> > What is with Forms 5/6?
> >
> > Thank you
> > Markus
>