Home » SQL & PL/SQL » SQL & PL/SQL » desired report
desired report [message #195905] Tue, 03 October 2006 02:09 Go to next message
arangu
Messages: 5
Registered: July 2006
Location: chennai
Junior Member
Sir,

We deal with taxation details. All the companies are suppose to file their tax report every month. I want to know the status of filing of tax report by the companies.

I constructed the query as follows :

Select distinct registration_no,
company_name,
location,
decode(filing_month,200604,nvl(filing_status,'NOT FILED')) "200604",
decode(filing_month,200604,nvl(filing_status,'NOT FILED')) "200605"
from return_mast
where filing_month >= 200604;

The report was generated as follows :

registration_no Company_name 200604 200605
--------------- ------------ ------ ------

F001 Ford India Y blank

F001 Ford India blank Y

A001 Axles India Y blank

A001 Axles India blank Y


But, I require the report as follows :

registration_no Company_name 200604 200605
--------------- ------------ ------ ------

F001 Ford India Y Y

A001 Axles India Y Y

Further, if no row is available for the particular month, either it should show the message "NOT FILED" or should be blank.

Advance thanks

Aranga

Re: desired report [message #195916 is a reply to message #195905] Tue, 03 October 2006 02:43 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You're getting blanks in the data because you haven't specified what the DECODE should return is if doesn't get a match. You've told it to return "NVL(filing_status,'NOT FILED')" if it does get a match, but if it doesn't it'll just return NULL.
You're also using '200604' on both lines in the DECODE.

Also - those results aren't from the query you posted. Your query has the 'LOCATION' column, but your posted results don't.

Also also - DON'T give the output columns names that require "" if you ever intend to use this query to return data to be used by something else. It just makes things very difficult

As to how to get the results on a single line,
Select registration_no,
       company_name,
       location,
       max(decode(filing_month,200604,nvl(filing_status,'NOT FILED'),null)) "200604",
       max(decode(filing_month,200605,nvl(filing_status,'NOT FILED'),null)) "200605"
from return_mast
where filing_month >= 200604
group by registration_no,
       company_name,
       location;
Previous Topic: INLINE QUERY
Next Topic: How to find the number of saturdays and Sundays?
Goto Forum:
  


Current Time: Thu Dec 08 20:26:48 CST 2016

Total time taken to generate the page: 0.25905 seconds