Conditional Break by with multiple field

S

sharika_ganju

New Member
Hello All,

I needed some help. I have a for statement with the following break by -

for each xshd_det no-lock where ...... use-index xshd_shipdate,
each ad_mstr no-lock where....... use-index ad_addr,
each pt_mstr no-lock where..... use-index pt_part,
each ih_hist no-lock where ...... use-index ih_nbr,
each cm_mstr no-lock where .... use-index cm_addr
break by
(if sortby = "M" then pt_site else
if sortby = "S" then xshd_line_site else
if sortby = "P" then xshd_part else
if sortby = "B" then pt_buyer else
xshd_cust)
with down frame b:
....
....
end.

Now I want to use the corresponding domain fields in the break by. How do I do the same.

The following query does not work -

for each xshd_det no-lock where ...... use-index xshd_shipdate,
each ad_mstr no-lock where....... use-index ad_addr,
each pt_mstr no-lock where..... use-index pt_part,
each ih_hist no-lock where ...... use-index ih_nbr,
each cm_mstr no-lock where .... use-index cm_addr
break by
(if sortby = "M" then pt_domain by pt_site else
if sortby = "S" then xshd_domain by xshd_line_site else
if sortby = "P" then xshd_domain by xshd_part else
if sortby = "B" then pt_domain by pt_buyer else
xshd_cust)
with down frame b:
....
....
end.

How do we put multiple fields in the break by clause if it is done conditionally.

Thanks in advance.
 
Try the following by moving the second 'by' out of the first one.

for each xshd_det no-lock where ...... use-index xshd_shipdate,
each ad_mstr no-lock where....... use-index ad_addr,
each pt_mstr no-lock where..... use-index pt_part,
each ih_hist no-lock where ...... use-index ih_nbr,
each cm_mstr no-lock where .... use-index cm_addr
break by
(if sortby = "M" then pt_domain else
if sortby = "S" then xshd_domain else
if sortby = "P" then xshd_domain else
if sortby = "B" then pt_domain else
xshd_cust)
by
(if sortby = "M" then pt_site else
if sortby = "S" then xshd_line_site else
if sortby = "P" then xshd_part else
if sortby = "B" then pt_buyer else
xshd_cust)
with down frame b:
....
....
end.


Hope this helps
 
On reading the OP I was going to post a suggestion then noticed that Newprog had already posted exactly what I was going to suggest ^^, except for the fact that xshd_cust should only be included in the break by part not the by part.

I would also question the use of the USE-Index statements, use-index is BAD except in very rare circumstances.
 
Well spotted StuartT. I agree about the USE-INDEX.

That what you get for copying & pasting the code from the original. :blush:
 
Thanks everyone for the speedy reply and your help. It worked !! The use-index was put in the code for performance improvement. I tried commenting the use-index statements and ran the code. But it is taking more time to run when it is removed. Thanks again :)
 
You must log in or register to reply here.
Back
Top