OraFAQ Forum: Reports & Discoverer » decode with reports

Home » Developer & Programmer » Reports & Discoverer » decode with reports

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

decode with reports [message #512876]

Wed, 22 June 2011 11:29

hassan08
Messages: 123
Registered: June 2011
Location: egypt

Senior Member

i have table in database contains name it company names
first column contains company name such as hassan
datatype in column is varchar2

i want when user when insert the data in this column show only
name of the company name it the report
but the column maybe contains one or more then values
i want show only first record when any update done in the table

example

first column contains company name such as "hassan"
when make any update or insert new value in the column such as "hassan1" show only first record inserted
i used this code but not working

select decode (company_name,0,company)
from company names
order by company_name desc
i write this code in
formula column but not working
i hope you understand meaning

Report message to a moderator

Re: decode with reports [message #512877 is a reply to message #512876]

Wed, 22 June 2011 11:37

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

1) What does decode have to do with anything? Your decode says: If company_name is 0 return company else return null. I do not see any relationship between that logic and your problem description.
2) I'm not sure how in your tables you determine which company name was there first but I would assume it's not the one that's last alphabetically - so why are you ordering by company_name desc?
3) If the user updates the company name where are you going to get the previous value from?

We know nothing about your tables and data other than what you tell us.
How exactly do you determine in your tables which company name was there first?
How do you tell which company names belong to the same company?

A test case is probably required here - Create table statements and insert statements for data. That way we can recreate your situation.

Report message to a moderator

Re: decode with reports [message #512883 is a reply to message #512877]

Wed, 22 June 2011 12:35

hassan08
Messages: 123
Registered: June 2011
Location: egypt

Senior Member

create table company_names(

company_name varchar2(150),
company_address varchar2(150);

this is the table
data insert in the company_name is
global company,global
okay this data inserted in column company_name

i want show only global company in the report but not show global

when user make any update on table and change global company and chenged name it to global software

show only global software in the report

im used the privious code in the formuls column faced many error
such as the cloumn contains more than data in this column

Report message to a moderator

Re: decode with reports [message #512904 is a reply to message #512883]

Wed, 22 June 2011 14:15

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

If you have a row in that table with a value for company_name of "global company" and someone updates that row so that company_name is now "global software", where exactly are you going to get the value "global company" from since it is no longer in the database?

Quote:

im used the privious code in the formuls column faced many error
such as the cloumn contains more than data in this column

That's not an oracle error. Do you mean you got a too_many_rows error?

Report message to a moderator

Re: decode with reports [message #512915 is a reply to message #512904]

Wed, 22 June 2011 16:49

hassan08
Messages: 123
Registered: June 2011
Location: egypt

Senior Member

YES I MEAN that but i hope you understand my meaning
this table contains only names of company and names only one in first row
second row contains the address i want show only first row but second row don't show it and when user update this first row show it in the report
when used the code faced an error because the column contains more than value name company in first row , and second row contains the address
i used formula column in report builder

Report message to a moderator

Re: decode with reports [message #512918 is a reply to message #512915]

Wed, 22 June 2011 17:07

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

And by what criteria do you tell which row is the first row?

Report message to a moderator

Re: decode with reports [message #512933 is a reply to message #512918]

Wed, 22 June 2011 20:05

hassan08
Messages: 123
Registered: June 2011
Location: egypt

Senior Member

the first row is global company

Report message to a moderator

Re: decode with reports [message #512977 is a reply to message #512933]

Thu, 23 June 2011 03:00

cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester

Senior Member

If the first row always has the value global company, then just return the text "global company" and skip doing a query altoghter.
I suspect that's not the case though.

Rows in a table are not stored in any order. If you want to get the first row you need a column that defines which row is first - like say a date column that records when the record was created. Without such a column there is no concept of first row.

Read this and see if it makes things clearer: http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:912210644860

When you've done that come back here and post a complete test case as already asked and we should be able to answer your question.

Report message to a moderator

Previous Topic:	Oracle Report Generating in Excel
Next Topic:	clob in report 6i (split from hijacked thread about problem with varchar2 declaration)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 06 18:46:01 CST 2025