Re: Query help

Sounds like you should probably use 'group by' with agregate function of the fields which are different

something like:

select MAX(e.shipdate), ..., MAX(a.jobnum), ... group by b.custnum;

"DM" <dmagill_at_venture-encoding.com> a écrit dans le message news: cad51b06.0110261132.324e35c2_at_posting.google.com...
> this is the results I get running my query
>
> SELECT e.shipdate,a.acctnum,a.custnum as
> parentnum,b.Listable,b.ListDisplayCount,
> a.jobnum,a.acctnum,b.ShowOne,b.custnum,c.proddesc as description
> From co_acctdtl a,v_custprodinfo b,v_products c,v_job e
> Where a.custnum = '67500Z' and a.prodnum = b.custnum
> and a.acctnum = '1263235' and
> a.custnum = b.parentnum and c.custnum = a.custnum and c.prodnum =
> a.prodnum and
> b.docviewenabled = 'Y' AND a.custnum = e.custnum AND
> a.jobnum = e.jobnum AND b.Listable = 'Y' AND rtrim(a.redojob) is null
>
>
>

SHIPDATE|ACCTNUM|PARENTNUM|LISTABLE|LISTDISPLAYCOUNT|JOBNUM|ACCTNUM|SHOWONE| CUSTNUM|DESCRIPTION
> 2001-10-22 07:50:00|1263235|67500Z|Y|2|078017|1263235|Y|675002|MONTHLY
> BILLING STATEMENTS
> 2001-09-21 19:42:00|1263235|67500Z|Y|2|074436|1263235|Y|675003|ESCROW
> ANALYSIS STATEMENTS
> 2001-09-25 10:50:00|1263235|67500Z|Y|2|074881|1263235|Y|675002|MONTHLY
> BILLING STATEMENTS
> 2001-09-25 14:16:00|1263235|67500Z|Y|2|075094|1263235|Y|675002|MONTHLY
> BILLING STATEMENTS
>
> The problem I have is this is not what I want I only want to pull the
> number of lines that have been entered in the LISTDISPLAYCOUNT field,
> basically
> I want to be able to list the lines per custnum based on that field.
> Any way of doings this easily. I really need to get it all with one
> query, due to the way we are using it as a recordset in our program.
>
>
> any ideas????
Received on Sat Oct 27 2001 - 08:06:19 CDT