Home » Developer & Programmer » Application Express, ORDS & MOD_PLSQL » Delete the Column Without Value (APEX 3.02)
| Delete the Column Without Value [message #551557] |
Thu, 19 April 2012 05:35  |
|
|
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  |
 |
Littlefoot
Messages: 21823
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
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 |
|
|
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 2574 times)
|
|
|
|
|
|
|
|
| Re: Delete the Column Without Value [message #551668 is a reply to message #551657] |
Fri, 20 April 2012 01:13 |
|
Littlefoot
Messages: 21823
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 |
c_stenersen
Messages: 255
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  )
|
|
|
|
Goto Forum:
Current Time: Wed Dec 11 16:30:08 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
