Home » Developer & Programmer » Application Express & MOD_PLSQL » Delete the Column Without Value (APEX 3.02)
Delete the Column Without Value [message #551557] Thu, 19 April 2012 05:35 Go to next message
cvs_1984
Messages: 133
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Hi,

I Developed the Report in APEX.

It Contain Lot of Columns (More then 25 Columns). Which show as long report.

During the Report Run time Some Column doesn't have the Data.

My Requirement is When Ever the Column have the Null Value then that column doesn't require to display in the apex report page.

If it possible then Kindly Guidance me.

Thanks & Regards,

C V S
Re: Delete the Column Without Value [message #551561 is a reply to message #551557] Thu, 19 April 2012 05:50 Go to previous messageGo to next message
Littlefoot
Messages: 19603
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
(I don't have Apex 3.02)

Try with conditional display. Here's how: create a report; for example, the one based on Scott's EMP table:
select ename, job, comm, sal
from emp

Suppose that COMM column might contain all NULL values, so you don't really want to display it. Therefore, go to COMM's properties, and under "Conditional Display" choose "Exists (SQL query returns at least one row)"; query:
select 'x'
from emp
where comm is not null

Run the report - COMM column will still be visible (because some employees have value in this column). For test, set it to null (perhaps you should back it up first):
update emp set comm = null
and run the report again - this time, the whole COMM column won't be displayed.

That should be all, I suppose.
Re: Delete the Column Without Value [message #551572 is a reply to message #551561] Thu, 19 April 2012 06:58 Go to previous messageGo to next message
cvs_1984
Messages: 133
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Dear Sir,

Thanks for Immediate Reply.

I Forget to Mention the Query status Fully.

The Query is develop through case which as convert the
Row Level Value to Column Level Data with summary of Value.

Attached file contain the Query.


Kindly Guidance me.

Regards,

C V S
  • Attachment: Querys.txt
    (Size: 1.12KB, Downloaded 64 times)
Re: Delete the Column Without Value [message #551573 is a reply to message #551572] Thu, 19 April 2012 07:02 Go to previous messageGo to next message
Littlefoot
Messages: 19603
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't understand what does the column source have to do with it? Its name won't be COMM but PUNJAB or CHENNAI or ..., but the rest of the story is still valid. Is it not?
Re: Delete the Column Without Value [message #551657 is a reply to message #551573] Fri, 20 April 2012 00:58 Go to previous messageGo to next message
cvs_1984
Messages: 133
Registered: August 2007
Location: Punjab, INDIA
Senior Member

Dear Sir,

In the Query I am Convert the Row Level value to Column Level Value.

Attached file contain the Output file and Output Require details.


Kindly Guidance me.


Regards,

C V S


Re: Delete the Column Without Value [message #551668 is a reply to message #551657] Fri, 20 April 2012 01:13 Go to previous messageGo to next message
Littlefoot
Messages: 19603
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This is contents of the file you attached, and I must admit that I don't understand it at all.

OUTPUT,,,,,,,,
,,,,,,,,Query :
FMR_REASON,PUNJAB,HARYANA,CHENNAI,,,,,
,,,,,,,,"select fmr_reason,sum(Punjab) Punjab,sum(Haryana) Haryana, sum(Chennai) Chennai"
For Want of Tyre,5,,6,,,,,From (
For Want of Load,5,,14,,,,,"select fmr_reason,"
For Fitness Certificate,2,,6,,,Change the Branch Name in the Row to Column,,"case when branch_name = 'TRK-Punjab' Then Vehi_cnt End Punjab,"
Accident Vehicle,1,,,,,,,"case when branch_name = 'TRK-Haryana' Then Vehi_cnt End Haryana,"
For Others,1,,,,,,,case when branch_name = 'TRK-Chennai' Then Vehi_cnt End Chennai
For Want of Drivers,2,,2,,,,,From (
For Maintenance,3,,,,,,,"select branch_name,fmr_reason,count(vehicle_number) VEHI_CNT"
,,,,,,,,From (
,,,,,,,,"select distinct xh.trans_date,xh.branch_name,xl.vehicle_number,xr.fmr_reason"
,,,,,,,,",xl.attribute1 others_reasons,xh.creation_date,xl.vehicle_type,"
REQUIRED TO DISPLAY WITHOUT HARYANA WHICH AS  NO TRANSACTION,,,,,,,,(select xth.logsheet_period_to 
,,,,,,,,from xxsc.xxsc_tls_header xth
FMR_REASON,PUNJAB,CHENNAI,,,,,,where trunc(xth.logsheet_period_to) = xl.trans_date
,,,,,,,,and xth.vehicle_number = xl.vehicle_number
For Want of Tyre,5,6,,,,,,and xth.organization_id = xl.organization_id
For Want of Load,5,14,,,,,,) logsheet_date
For Fitness Certificate,2,6,,,,,,"from xxsc.xxsc_tls_fleet_util_l xl,"
Accident Vehicle,1,,,,,,,"xxsc.xxsc_tls_fleet_util_h xh,"
For Others,1,,,,,,,xxsc.xxsc_tls_idle_halt_reasons xr
For Want of Drivers,2,2,,,,,,where xh.header_id = xl.header_id
For Maintenance,3,,,,,,,and xr.fmr_reason_id = xl.trans_type
,,,,,,,,--and xh.trans_date = :p_to_date
,,,,,,,,"and to_date(xh.trans_date,'dd/mm/rrrr') = '05-Dec-2011'"
,,,,,,,,"order by branch_name,vehicle_number,fmr_reason"
,,,,,,,,") group by branch_name,fmr_reason"
,,,,,,,,Order by Branch_name)
,,,,,,,,) Group by fmr_reason


Once again: what difference does "convert row level value to column level value" have to do with your problem? Imagine that you use this query for CTAS (Create Table As Select), for example:
create table my_test as
  select <your query goes here>;

When you describe the table, it will contain the following columns:
FMR_REASON, PUNJAB, HARYANA, CHENNAI

Create an Apex report that'll use this MY_TEST table. Query would be
select fmr_reason, punjab, haryana, chennai
from my_test


Now you'd set "Conditional display" for these columns (one by one) in your Apex report.

(Though, it might be that I don't understand the problem. Hopefully, someone else will be able to explain it better than me).

[Updated on: Fri, 20 April 2012 01:15]

Report message to a moderator

Re: Delete the Column Without Value [message #551703 is a reply to message #551668] Fri, 20 April 2012 04:06 Go to previous message
c_stenersen
Messages: 254
Registered: August 2007
Senior Member
That file is a horrible horrible way to post a question. I can see what you mean when I'm opening it in Excel (at least I think so), but everything you have in there should be posted in code tags in your post (with proper indentation) instead of in this manner. If I understand the file correctly you're dividing it in half horizontally and showing the output on one side and the query on the other.

To make it easier for others to interpret:
Query:
select fmr_reason,sum(Punjab) Punjab,sum(Haryana) Haryana, sum(Chennai) Chennai
From (
     select fmr_reason,
     case when branch_name = 'TRK-Punjab' Then Vehi_cnt End Punjab,
     case when branch_name = 'TRK-Haryana' Then Vehi_cnt End Haryana,
     case when branch_name = 'TRK-Chennai' Then Vehi_cnt End Chennai
     From (select branch_name,fmr_reason,count(vehicle_number) VEHI_CNT
          From (select distinct xh.trans_date,xh.branch_name,xl.vehicle_number,xr.fmr_reason
                       ,xl.attribute1 others_reasons,xh.creation_date,xl.vehicle_type,
                       (select xth.logsheet_period_to 
                               from xxsc.xxsc_tls_header xth
                               where trunc(xth.logsheet_period_to) = xl.trans_date
                               and xth.vehicle_number = xl.vehicle_number
                               and xth.organization_id = xl.organization_id
                       ) logsheet_date
               from xxsc.xxsc_tls_fleet_util_l xl,
               xxsc.xxsc_tls_fleet_util_h xh,
               xxsc.xxsc_tls_idle_halt_reasons xr
               where xh.header_id = xl.header_id
               and xr.fmr_reason_id = xl.trans_type
               --and xh.trans_date = :p_to_date
               and to_date(xh.trans_date,'dd/mm/rrrr') = '05-Dec-2011'
               order by branch_name,vehicle_number,fmr_reason
               ) 
          group by branch_name,fmr_reason
          Order by Branch_name)
) Group by fmr_reason


Current output:
FMR_REASON                  PUNJAB       HARYANA        CHENNAI
For Want of Tyre            5                           6
For Want of Load            5                           14
For Fitness Certificate     2                           6
Accident Vehicle            1
For Others                  1
For Want of Drivers         2                           2
For Maintenance             3


Desired output:
REQUIRED TO DISPLAY WITHOUT HARYANA WHICH AS  NO TRANSACTION

FMR_REASON                  PUNJAB       CHENNAI
For Want of Tyre            5            6
For Want of Load            5            14
For Fitness Certificate     2            6
Accident Vehicle            1	
For Others                  1	
For Want of Drivers         2            2
For Maintenance             3


First: This: "and to_date(xh.trans_date,'dd/mm/rrrr') = '05-Dec-2011'" is totally wrong! To_date is used to convert a string to a date. And if trans_date is actually a date data type you're just asking for trouble by applying to_date. And then you compare the result(which is a date) to a string. Which is also just asking for trouble. There will be implicit conversions all over, and as soon as a format setting doesn't match you will have exceptions. to_date is used for converting strings to dates, and to_char is used to convert dates to strings. Both should be used with a format mask.

Second: Using order by in the inner query (the one in your from clause) won't have any effect on what you're getting from the outermost query. You should order it in the outermost one.

But what Littlefoot gives you as advice is the best way of doing it. You won't need your whole query in the condition, just the part about whether or not the column in question has transactions. (I will let you try to find this out for yourself. Try to run all of the nested parts of your query and see where you don't get any entries for haryana Smile )
Previous Topic: How do I make password login case sensitive?
Next Topic: How to check apex version.
Goto Forum:
  


Current Time: Tue Sep 16 14:40:50 CDT 2014

Total time taken to generate the page: 0.06689 seconds