Home » Developer & Programmer » Reports & Discoverer » Mailing labels report
Mailing labels report [message #385795] Wed, 11 February 2009 01:24 Go to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
Hi

Do u have any idea how i can write a for loop in my report for serial numbers.
For ex... we have 5 boooks of IT....we want to label them as copy 1/5 ....copy 2/5 etc...
so loop will be from 1 to taotal_no_of_copies for the book

but where do i put it ??

Thanks in advance.
Charu

[SPLIT by DJM, then by LF, then MERGED by LF]

[Updated on: Tue, 17 February 2009 00:00] by Moderator

Report message to a moderator

Re: Another crappy "please help" title [message #385938 is a reply to message #385795] Wed, 11 February 2009 23:38 Go to previous messageGo to next message
djmartin
Messages: 10180
Registered: March 2005
Location: Canberra ACT Australia
Senior Member
Account Moderator
Is this a report or a form?

Do you want to store the 1, 2, etc in the database when the row is added or on the screen only?

David
Re: Another crappy "please help" title [message #386356 is a reply to message #385938] Fri, 13 February 2009 22:26 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
It is a report....I don't want to store the values in the database.

Right now in our report we have mentioned :
C.1/&total_no_of_copy....

We have manually mentioned C1,c2 etc....
but now we want to run a loop to count the values...

But i dont know where to call the loop.....

please help me sir

Regards.
Charu
Mailing labels report [message #386745 is a reply to message #385795] Mon, 16 February 2009 22:49 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
Hi

I need your help in creating a report with mailing labels....
I have 1 record of a book named 'ABC' ...this book has 5 copies

so the structure z like : book_id,book_name,.....total_copy
ex....1,ABC,....5

Now when i generate a report for this book id...it will show only 1 label but i want it to show 5 labels as

1
ABC
1/5

1
ABC
2/5

etc etc till 5/5

so it means we want to spilt 1 record into 5 labels with everything same except 1/5,2/5.....5/5

Any suggestions....
Can u mail me some sample report on repeating frame .

Thanks In Advance.
Charu.
Re: Mailing labels report [message #386774 is a reply to message #386745] Mon, 16 February 2009 23:58 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What will you do when you buy another copy of the same book, so that the ABC book won't have "total = 5" but "total = 6"? What will you do when someone forgets to return a borrowed book so total will be 4?

Do you plan to unstick old labels, print new ones and spend your life doing that sticky job?

What I am saying is: your model is - in my opinion - wrong.

However, if you insist on that, perhaps something like this might help:
SQL> select * from books;

   BOOK_ID BOOK_NAME            TOTAL_COPY
---------- -------------------- ----------
         1 Harry Potter                  5
         2 Lord of the Rings             2
         3 Oracle for dummies            3

SQL> select distinct b.book_id, b.book_name, level ||'/'|| b.total_copy result
  2  from books b
  3  connect by level <= b.total_copy
  4  order by 1, 3;

   BOOK_ID BOOK_NAME            RESULT
---------- -------------------- ----------
         1 Harry Potter         1/5
         1 Harry Potter         2/5
         1 Harry Potter         3/5
         1 Harry Potter         4/5
         1 Harry Potter         5/5
         2 Lord of the Rings    1/2
         2 Lord of the Rings    2/2
         3 Oracle for dummies   1/3
         3 Oracle for dummies   2/3
         3 Oracle for dummies   3/3

10 rows selected.

SQL>
Re: Mailing labels report [message #386778 is a reply to message #386774] Tue, 17 February 2009 00:23 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
Thanks for your reply...
the query is hanging....y ?
select distinct b.book_id, b.book_name, level ||'/'|| b.total_copy result
from lib_books b
connect by level <= b.total_copy
order by 1, 3

when I exceute this query it goes into hanging Sad

Thnaks and regards.
Charu.
Re: Mailing labels report [message #386784 is a reply to message #386778] Tue, 17 February 2009 00:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't know; you have provided too few (i.e. none at all) information.
Re: Mailing labels report [message #386793 is a reply to message #386784] Tue, 17 February 2009 00:47 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
My table structure is :
Name Null? Type
----------------------------------------- -------- ----------------------------
BOOK_ID NOT NULL NUMBER(10)
GLOBAL_NO VARCHAR2(25)
BOOK_NAME VARCHAR2(200)
AUTHOR_NAME VARCHAR2(50)
PUBLISH_DATE NUMBER(4)
CLASS_ID NUMBER(10)
TOTAL_COPY NUMBER(4)
CURRENT_COPY NUMBER(4)
REMARKS VARCHAR2(250)
PUBLISHER VARCHAR2(50)
CLASS_TYPE VARCHAR2(10)
BOOK_TYPE CHAR(2)
PARTS VARCHAR2(10)
PART2 VARCHAR2(10)
SUB_NAME VARCHAR2(200)
CREATE_USER VARCHAR2(10)
MODIFY_USER VARCHAR2(10)
CREATE_DATE DATE
MODIFY_DATE DATE
BARCODE VARCHAR2(30)
SERIAL_NO VARCHAR2(6)
SERIAL NUMBER(10)
COLOUR VARCHAR2(200)
REP_INV VARCHAR2(100)

and the query that i m running is :


select distinct b.book_id, b.book_name, level ||'/'|| b.total_copy result
from lib_books b where book_id=4003
connect by level <= b.total_copy
order by 1, 3

This book_id(4003) will be given as a parameter in the report.

What else i need to tell u??
Thanks for your help.

Charu
Re: Mailing labels report [message #386806 is a reply to message #386793] Tue, 17 February 2009 01:05 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Quote:
What else i need to tell u??

For example, database version, Reports Builder version, operating system you use.
Re: Mailing labels report [message #386809 is a reply to message #386806] Tue, 17 February 2009 01:09 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
I m using
Oracle 10g
Developer Suite 10g

Windows XP
Re: Mailing labels report [message #386815 is a reply to message #386809] Tue, 17 February 2009 01:36 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, nothing strange here.

I have the same software combination and report runs just fine (see the screenshot).

./fa/5731/0/
  • Attachment: books.PNG
    (Size: 8.23KB, Downloaded 401 times)
Re: Mailing labels report [message #386816 is a reply to message #386815] Tue, 17 February 2009 01:45 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
not working yet......

first i was just trying at sql prompt...now i tried directly in report builder bt still hangs....

Sad
Charu

Re: Mailing labels report [message #386819 is a reply to message #386816] Tue, 17 February 2009 02:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
How many records does your table have?
Re: Mailing labels report [message #386820 is a reply to message #386819] Tue, 17 February 2009 02:06 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
it had 4000 books

perhaps thts y it is hanging.
Re: Mailing labels report [message #386894 is a reply to message #386820] Tue, 17 February 2009 03:55 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Well, yes - that is a drawback.

In that case, try with such a query; it should be much faster:
select book_id, book_name, level ||'/'|| total_copy result
from books
connect by level <= total_copy
  and prior book_id = book_id
  and prior dbms_random.value is not null
order by 1, 3;
Re: Mailing labels report [message #386904 is a reply to message #386894] Tue, 17 February 2009 04:10 Go to previous messageGo to next message
charuamit
Messages: 121
Registered: January 2009
Senior Member
Hi

I did it Smile
i created a temporary global table and inserted records in it after parameter form trigger in report......

It is now working fine......bt one prob...
it show the labels on 1st time execution...again if u execute it won display anything...And u will have to exit report builder thn re-open .....thn it will display the labels...

Any idea ?
Thanks.
Charu.
Re: Mailing labels report [message #386918 is a reply to message #385795] Tue, 17 February 2009 04:51 Go to previous message
m_golam_hossain
Messages: 89
Registered: August 2008
Location: Uttara, Dhaka, Bangladesh
Member

Alternatively you might like to use SUMMERY COLUMN in your report builder with COUNT function in conjuction with a FORMULA COLUMN having total count. Add the two columns with concatenation and a slash.

[Updated on: Tue, 17 February 2009 04:53]

Report message to a moderator

Previous Topic: Dynamic Report Size in report
Next Topic: Unable to Parse Query
Goto Forum:
  


Current Time: Sat Dec 10 10:43:30 CST 2016

Total time taken to generate the page: 0.09188 seconds