Dear J. M.
I've been reading the correspondence on this question and ahve some
thoughts:
>SELECT cndp, cgratuit, cpaysori, cpreference, cana1, cana2, cana3,+
> cana4, cana5, ctaxe1, tauxt1, ctaxe2, tauxt2, ctaxe3, tauxt3,+
> ctva, COUNT(*) FROM ligne WHERE numcde = .vnumcde +
> GROUP BY cndp, cgratuit, cpaysori, cpreference, cana1, cana2, cana3,+
> cana4, cana5, ctaxe1, tauxt1, ctaxe2, tauxt2, ctaxe3, tauxt3, ctva +
> HAVING COUNT(*) > 1
>
>The difference is including all the GROUP BY columns in the select
> - you get every instance with more than one entry
>and removing the INTO var.
>
>If you need it in a var, you must set a cursor. Multiple instances do not
>go into a single variable.
The point is being missed here: what is YOUR desired outcome? Do you
simply what to do know if there ARE multiple instances (Yes / No), or do
you need to be able to KEEP those multiple instances and work with them in
some way?
If the former, this does it:
CREATE VIEW JMTest (cndp,
>cgratuit, cpaysori, cpreference, cana1, cana2, cana3,+
> cana4, cana5, ctaxe1, tauxt1, ctaxe2, tauxt2, ctaxe3, tauxt3,+
> ctva,
NumDups) AS
>SELECT cndp, cgratuit, cpaysori, cpreference, cana1, cana2, cana3,+
> cana4, cana5, ctaxe1, tauxt1, ctaxe2, tauxt2, ctaxe3, tauxt3,+
> ctva, COUNT(*) FROM ligne WHERE numcde =
(
>.vnumcde
)
> +
> GROUP BY cndp, cgratuit, cpaysori, cpreference, cana1, cana2, cana3,+
> cana4, cana5, ctaxe1, tauxt1, ctaxe2, tauxt2, ctaxe3, tauxt3, ctva +
> HAVING COUNT(*) > 1
SELECT COUNT(*) INTO vTEST IND vI1 FROM JMTEST
IF vTest > 0 THEN
PAUSE 1 USING 'There are dups'
ELSE
PAUSE 1 USING 'There are no dups'
ENDIF
If you need to DO something with them, then you've got them in the view.
You can DECLARE a cursor to the view, or use some part of it in a
sub-select in a WHERE clause.
Hope this helps.
David Blocker
