CODEWITHSUNDEEP
oracle-apex
Blob
Blob

Reputation: 541

Display '-' instead of '0' in Interactive report

I have the following sql query which is used to create a report to show revision history for a product. Intial record is revision 0, if something is edited it becomes revision 1, and so on. Instead of showing '0' in the report i would prefer to show '-', is there a way this can be achieved?

SELECT
product_id,
name,
description,
revision,
revision,reason,
'DETAILS' "VIEW DETAILS"
FROM product_table
WHERE product_id = :P2_product_id
ORDER BY REVISION DESC
--order by case when Revision is null then 1 else 0 end, revision desc

product_id - numeric
name - varchar2
description - varchar2
revision - numeric
revision_reason - varchar2

I did try the line which is commented out however the repor continues to show the 0 rather than a -.

Upvotes: 0

Views: 2094

Answers (3)

Scott
Scott

Reputation: 5035

You could also use nullif(revision,'-') and use the 'Show null values as' attribute.

Upvotes: 0

AgentRev
AgentRev

Reputation: 799

I know this question is a bit old and already answered, but I ran into the same problem today, and using DECODE for showing a hyphen instead of 0 causes the column type to switch to STRING, which prevents proper ordering of multi-digit numbers.

So, here's an alternative that keeps the column as NUMBER :

CASE WHEN column = 0 THEN NULL ELSE column END AS column

Upvotes: 1

Ro Milton
Ro Milton

Reputation: 2526

What you're after is the decode function.

Solution

SELECT
product_id,
name,
description,
decode(revision,0,'-',revision)revision,
revision_reason,
'DETAILS' "VIEW DETAILS"
FROM product_table
WHERE product_id = :P2_product_id
ORDER BY REVISION DESC
--order by case when Revision is null then 1 else 0 end, revision desc

Explanation

Replaced the column revision, with decode(revision,0,'-',revision)revision, . Make sure you read how decode works - it's very useful.

Note

Your SQL Select query includes this: revision,reason,. From looking at your column definitions, I think you mean revision_reason, instead. I updated my solution accordingly.

Upvotes: 1

Related Questions