Home » Developer & Programmer » Reports & Discoverer » How to control Repeating Row
How to control Repeating Row [message #202506] Fri, 10 November 2006 01:53 Go to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
I defined query in report 6i
SELECT DISTINCT SUM(SUB_FABRIC_DELIVERED.QUANTITY) qty, FABRIC_DELIVERED.FDNO, 
SUB_FABRIC_DELIVERED.COLOR,
SUB_PROGRAM.COLOR, 
SUB_PROGRAM.REQ_KGS,
FABRIC_DELIVERED.PPNO
FROM SUB_FABRIC_DELIVERED,
     FABRIC_DELIVERED,
     PROGRAM, 
     SUB_PROGRAM
WHERE ((SUB_FABRIC_DELIVERED.FDNO = FABRIC_DELIVERED.FDNO)
 AND (SUB_PROGRAM.JOBNO = PROGRAM.JOBNO)
 AND (FABRIC_DELIVERED.PPNO = PROGRAM.PPNO))
GROUP BY FABRIC_DELIVERED.FDNO, 
         FABRIC_DELIVERED.PPNO, 
         FABRIC_DELIVERED.SUBPP, 
         SUB_FABRIC_DELIVERED.COLOR, 
         PROGRAM.PPNO, PROGRAM.SUBPP, 
         SUB_PROGRAM.COLOR,
         SUB_PROGRAM.REQ_KGS
ORDER BY SUB_FABRIC_DELIVERED.COLOR 


problen is that when I run report row are repeating many times it seeks record in sub_fabricc_delivered and repeat record as found in this table.
Re: How to control Repeating Row [message #202509 is a reply to message #202506] Fri, 10 November 2006 02:12 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I'm not sure I understood what you were saying, but let me try: DISTINCT keyword is unnecessary when query has an aggregate function (because GROUP BY distincts retrieved records).

GROUP BY clause has far too many columns; perhaps it is a reason for too many retrieved records? It is obligatory to group results by columns in the selectable column list.

Also, consider using table aliases - it improves reading.

So, what happens if you run something like this:
SELECT   SUM (s.quantity) qty, f.fdno, s.color, sp.color, sp.req_kgs, f.ppno
    FROM sub_fabric_delivered s, fabric_delivered f, program p,
         sub_program sp
   WHERE s.fdno = f.fdno
     AND sp.jobno = p.jobno
     AND f.ppno = p.ppno
GROUP BY f.fdno, s.color, sp.color, sp.req_kgs, f.ppno
ORDER BY s.color;
Re: How to control Repeating Row [message #202519 is a reply to message #202509] Fri, 10 November 2006 03:13 Go to previous messageGo to next message
kamran.it
Messages: 265
Registered: September 2005
Location: Karachi
Senior Member
Please see attached file . Query resut.

You can see PPno 388 and color, color2 cloumn now you can understand.

I hope.

*************************
Thank You Sir I have dont this...............

But still I am waiting your kind suggestion.
  • Attachment: report.bmp
    (Size: 1.15MB, Downloaded 181 times)

[Updated on: Fri, 10 November 2006 03:25]

Report message to a moderator

Re: How to control Repeating Row [message #202639 is a reply to message #202519] Fri, 10 November 2006 11:54 Go to previous messageGo to next message
Littlefoot
Messages: 20900
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I saw the image you attached. Is it result of your query or mine? I suppose it is not correct (i.e. you'd like it to look different); could you post (using [code] tags) desired result? Also, it would probably help if you provide CREATE TABLE statements along with sample data.
Re: How to control Repeating Row [message #203556 is a reply to message #202519] Wed, 15 November 2006 09:09 Go to previous messageGo to next message
DiscoUser
Messages: 9
Registered: November 2006
Junior Member
Hello,
I saw ur attached output. It sure is getting distinct rows. no 2 rows have same values for all columns. Please take a close look, the kgs column has distinct values for Ppno 388. You are gruoping on all the columns you are using. So it is gouping on Kgs column too. as you are using aggregate function - SUM, you have to group every column you are using in select.

Hope this helps.
DU.
Re: How to control Repeating Row [message #203560 is a reply to message #202506] Wed, 15 November 2006 09:21 Go to previous messageGo to next message
DiscoUser
Messages: 9
Registered: November 2006
Junior Member
Hello Guys,

This message is not pertaining to this thread, but in general.
I am a new member of this forum.
I have noticed on this forum that people post questions and get some responses, but never get back to let us know if they could solve the problem, if the responses were useful etc. To my knowledge, the idea of a forum is to share information. Unless you let other people know how you could solve your problem, if somebody runs into same problem, they don't know if those responses for your questions work. So they keep posting the same question in a different thread. Please make it better by sharing your solution and a simple thank you note if you could solve your problem with someone's response.
Hope you take this in a positive manner and try to respond.

Thank You,
DU.
Re: How to control Repeating Row [message #203804 is a reply to message #202506] Thu, 16 November 2006 06:52 Go to previous message
ab_trivedi
Messages: 460
Registered: August 2006
Location: Pune, India
Senior Member
hi,

You qty,fdno and ppno are same for the records so I think you have to add another repeating frame for your desired output.

Bye
Ashu
Previous Topic: Japanese Lables in Reports 6i
Next Topic: Reprts Generating in the specified format... PLZ Help
Goto Forum:
  


Current Time: Thu Dec 08 12:34:11 CST 2016

Total time taken to generate the page: 0.20413 seconds