Ravi_Jaganathan
asked on
Need to print null if the query doesn't return any value
Hi,
I have crystal report with sub report (sum of open balance). the query associated with sub query return result. But the query associated with main report doesn't return any result. So the sub report also doesn't show up in the report even though the query returned the results.
Is there a way to print the subreport even if the main report query doesn't return any values.
Note: sub report query just get the parameter from main report query, that is the reason why it brings results.
Ravi
I have crystal report with sub report (sum of open balance). the query associated with sub query return result. But the query associated with main report doesn't return any result. So the sub report also doesn't show up in the report even though the query returned the results.
Is there a way to print the subreport even if the main report query doesn't return any values.
Note: sub report query just get the parameter from main report query, that is the reason why it brings results.
Ravi
Does the main report sometimes return results or does it never return results and is only a holder for the subreport(s)? If it never returns results then just simplify the main query:
select 'dummy' as dummy from dual
frodoman
select 'dummy' as dummy from dual
frodoman
ASKER
most of the time main report retrun the result
You should be trying the query I have suggested.
select* from
(
<your main report query>
) main_query,
(select 'dummy' as dummy from dual) b
where main_query.dummy(+) = b.dummy
Incase your main query is blanck the modified query brings just one record something like below which is suffiecient to print the subreport. You can always supress the record using detail supression formula
Field1 Field 2 Field3....Dummy Dummy
Null Null Null Null Dummy
hth..
Gary
select* from
(
<your main report query>
) main_query,
(select 'dummy' as dummy from dual) b
where main_query.dummy(+) = b.dummy
Incase your main query is blanck the modified query brings just one record something like below which is suffiecient to print the subreport. You can always supress the record using detail supression formula
Field1 Field 2 Field3....Dummy Dummy
Null Null Null Null Dummy
hth..
Gary
Gary is correct - I was pointing out a simpler approach *IF* your main report was intentionally blank. If there is supossed to be data sometimes then use the approach that he suggested.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for your help
select* from
(select acct_id, 'dummy' as dummy from
ci_acct
where acct_id = '13434'
) main_query,
(select 'dummy' as dummy from dual) b
where main_query.dummy(+) = b.dummy
hth..
Gary