Re: Pro*C COUNT -need help!

> erlst3+_at_pitt.edu (Eric R Lapp) writes:
> I need to do a count that would do the following:
>
> assuming tables:
>
> A
> ID F_Name L_Name Num
> ---------------------
> 1 A B 3
> 1 A A 2
> 1 A C 6
> 1 A B 1
>
> I want to get back a 3 in this situation.
> i.e. where distinct ID, F_Name, and L_Name
>
> How do I do a count that would produce the desired results?
>
> SELECT COUNT(DISTINCT ID, F_NAME, L_Name)
> FROM A
> WHERE ID = 1;
>
> I guess after I do the WHERE ID = 1 all that I would need is a count of the
> distinct combinations of F_Name and L_Name. I know the above DISTINCT is
> wrong...
>
> I am able to do this in SQL*Plus, but NOT in the Pro*C:
>
> SELECT COUNT(*)
> FROM (SELECT DISTINCT F_Name, L_Name FROM A WHERE ID = 1);
>
> This (I think works), but Pro*C doesn't allow a subquery in the FROM clause.
>
> If anyone can help that would be GREAT!!!! TIA
>
> --
> ******************************************************************************
> ** Eric R. Lapp E-Mail: erlst3+_at_pitt.edu **
> ******************************************************************************
>
>>>>

You didn't say, but you must be using the 1.6 Pro*C. One of the developers I work with had the same problem. You can get around it by turning off sqlcheck for that select using:

exec oracle option (sqlcheck=none)

and then turning it back to your normal setting afterwards. I believe the 2.0 version of Pro*C will accept this syntax. The inline view is new to Oracle 7 and the 1.6 pre-complier orignated in Oracle 6.

Paul Received on Sat Aug 10 1996 - 00:00:00 CEST