OraFAQ Forum: Reports & Discoverer » Mailing labels report

Home » Developer & Programmer » Reports & Discoverer » Mailing labels report

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Mailing labels report [message #385795]

Wed, 11 February 2009 01:24

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

djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS 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

Report message to a moderator

Re: Another crappy "please help" title [message #386356 is a reply to message #385938]

Fri, 13 February 2009 22:26

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

Report message to a moderator

Mailing labels report [message #386745 is a reply to message #385795]

Mon, 16 February 2009 22:49

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.

Report message to a moderator

Re: Mailing labels report [message #386774 is a reply to message #386745]

Mon, 16 February 2009 23:58

Littlefoot
Messages: 21808
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>

Report message to a moderator

Re: Mailing labels report [message #386778 is a reply to message #386774]

Tue, 17 February 2009 00:23

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

Sad

Thnaks and regards.
Charu.

Report message to a moderator

Re: Mailing labels report [message #386784 is a reply to message #386778]

Tue, 17 February 2009 00:32

Littlefoot
Messages: 21808
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.

Report message to a moderator

Re: Mailing labels report [message #386793 is a reply to message #386784]

Tue, 17 February 2009 00:47

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

Report message to a moderator

Re: Mailing labels report [message #386806 is a reply to message #386793]

Tue, 17 February 2009 01:05

Littlefoot
Messages: 21808
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.

Report message to a moderator

Re: Mailing labels report [message #386809 is a reply to message #386806]

Tue, 17 February 2009 01:09

charuamit
Messages: 121
Registered: January 2009

Senior Member

I m using
Oracle 10g
Developer Suite 10g

Windows XP

Report message to a moderator

Re: Mailing labels report [message #386815 is a reply to message #386809]

Tue, 17 February 2009 01:36

Littlefoot
Messages: 21808
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).

/forum/fa/5731/0/

/forum/fa/5731/0/

Attachment: books.PNG
(Size: 8.23KB, Downloaded 1595 times)

Report message to a moderator

Re: Mailing labels report [message #386816 is a reply to message #386815]

Tue, 17 February 2009 01:45

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

Sad

Charu

Report message to a moderator

Re: Mailing labels report [message #386819 is a reply to message #386816]

Tue, 17 February 2009 02:02

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

How many records does your table have?

Report message to a moderator

Re: Mailing labels report [message #386820 is a reply to message #386819]

Tue, 17 February 2009 02:06

charuamit
Messages: 121
Registered: January 2009

Senior Member

it had 4000 books

perhaps thts y it is hanging.

Report message to a moderator

Re: Mailing labels report [message #386894 is a reply to message #386820]

Tue, 17 February 2009 03:55

Littlefoot
Messages: 21808
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;

Report message to a moderator

Re: Mailing labels report [message #386904 is a reply to message #386894]

Tue, 17 February 2009 04:10

charuamit
Messages: 121
Registered: January 2009

Senior Member

Hi

I did it Smile

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.

Report message to a moderator

Re: Mailing labels report [message #386918 is a reply to message #385795]

Tue, 17 February 2009 04:51

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 22:11:20 CDT 2024