?
Solved

Related to Oracle Reports 6i Formula column

Posted on 2006-05-20
5
Medium Priority
?
1,806 Views
Last Modified: 2008-02-01
My requirement is to display the records with the first column having prefixed with '*' depending on some conditions which are specified in the functional specification.
After i was able to achive this, the new requirement came as to take the parameter as boolean. If suppose the parameter value is Yes then i have to display the records with the first column which is prefixed with '*' only. If i select as No then i have to display all the records independent of '*'.
Now i'll explain this requirement with example using employee table.
I want to dispaly the records with empno which is prefixed with '*' depending on the condition commission is null.
Here i have written the formula column which mentioned below.

function CF_1Formula return Char is
begin
 if(:comm is null) then
       return('*'||:empno);
  else
       return(:empno);
  end if;
 
end;

Now at this moment i can see the records which are prefixed with '*' with the empno.
Now how i should display the records with only '*' when i choose the parameter value as Yes.

Pass a parameter to the function:

function CF_1Formula( p_option in varchar2 ) return Char is
begin
   
If upper(p_option) = 'YES' then
   
   if(:comm is null) then
     return ('*'||:empno);
   else
     return ( null );
   end if;

Else

   if(:comm is null) then
     return ('*'||:empno);
   else
     return (:empno);
   end if;

End if;
end;

This is doing good, but this makes the output as :

 
     empno          ename          salary            deptno
-----------------------------------------------------------------
    *1234           JAMES           20000              10
                        JONES           30000              20
    *2345           JOSEPH         40000              30
                        BILL               50000             10
                        BOTHAM        20000             40
    *3456           EAN               10000             20

-------------------------------------------------------------------

Here in this output , its showing null values for the empno.But we dont want the complete record which doesnt match the crieteria.

Even this could be possible even with a single query,using DECODE or CASE, but it shows the similar result as above.

Pls help me out in this problem.
Thanx
0
Comment
Question by:samisyed
  • 2
3 Comments
 
LVL 4

Expert Comment

by:HKComputer
ID: 16724347
What are you programming in?  You've posted in the MS Access section.  The concept of what you are trying to do makes sense to me but your code is very foreign.

In Access we use SQL statements and achieving what you want there would be a rather simple thing.
0
 
LVL 4

Expert Comment

by:HKComputer
ID: 16724353
Oops, my bad.  I thought I was in the Access section.  I am most sorry.
0
 
LVL 4

Accepted Solution

by:
abd00n earned 500 total points
ID: 16727959
Hi,
The first CF you did here is just doing the following:
IF :comm is null (No Commission?): PRINT '*' as a prefix for the EmpNo
ELSE (i.e. is not NULL): PRINT the EmpNo without a prefix

Then you added the p_option = 'YES'  OR = 'NO'!
It did nothing but the following:

IF :comm is null (No Commission?): PRINT '*' as a prefix for the EmpNo
ELSE (i.e. is not NULL): PRINT the EmpNo without a prefix

I mean to say your script is just Vanishing the EmpNo or concatenating it to a '*'

What you need to do is the following:

1- Delete your CF_1Formula (No need for it)
2- Put the Fields (empno, ename, salary & deptno) in a frame (by default it will be named as M_1)
3- Add this script to your Format Trigger:

function M_1FormatTrigger return boolean is
begin

  If upper(p_option) = 'YES' then
      if(:comm is null) then
          return (FALSE);
      else
          return (TRUE);
      end if;
  Else
          return (TRUE);
  End if;
end;

I am sure this is OK! But..
.. I am a human bieng ;)
If any mistake don't hesitate to correct or ask!
Bye
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question