Hello All Cognos Gurus,
I hope you all are doing great...
Hello All Cognos Gurus,
I hope you all are doing great...
I have a question..,
I need to find count of null values e.g:
I have two tables eg: ABC,EFG in this
one column is ABC.123 here ABC is Table Name and 123 is Column Name
Other column is EFG.123 here EFG is Table Name and 123 is Column Name
if (ABC.123=EFG.123) and when this condition returns null value then
I need to count all this null values because this null values represents empty locations.. I need to calculate all this empty location.
My question is what function do need to use..
I hope its clear...Please send your responses ASAP
Quote
ABC.123=EFG.456 if it returns null value
Can you be more specific on this?
I'm a little confused about how you are joining your two tables.
But to count nulls on a join between two tables I normally use a left outer join between the tables and then;
case when ([column] is null) then (1) else (0) end as null_count,
Then I can just total the null_count column.
(The syntax on the case might be wrong, but you get the idea)
Quote from: cognoshelp@ymail.com on 15 Jul 2008 10:35:21 PM
Hello All Cognos Gurus,
I hope you all are doing great...
I have a question..,
I need to find count of null values e.g:
I have two tables eg: ABC,EFG in this
one column is ABC.123 here ABC is Table Name and 123 is Column Name
Other column is EFG.123 here EFG is Table Name and 123 is Column Name
if (ABC.123=EFG.123) and this condition returns null value then
I need to count all this null values because this null values represents empty locations.. I need to calculate all this empty location.
My question is what function do need to use..
I hope its clear...Please send your responses ASAP
Quote from: blom0344 on 16 Jul 2008 04:19:56 AM
Can you be more specific on this?
Here is formula for my calculation..
if(([Presentation Layer].[ABC].[123]=[Presentation Layer].[DEF].[123] or [Presentation Layer].[ABC].[456]=[Presentation Layer].[DEF].[123]) is null)
then
('1')
else
('0')
What I want is, is there any other method which helps me out to calculate null values or which resolves this error msg....Please ASAP
I am getting this error
"An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'"
Details of the error:
"UDA-SQL-0358 Line 5: Syntax error near "NULL". RSV-SRV-0042 Trace back: RSReportService.cpp(595): QFException: CCL_CAUGHT: RSReportService::process() RSReportServiceMethod.cpp(177): QFException: CCL_RETHROW: RSReportServiceMethod::process(): promptPagingForward_Request RSASyncExecutionThread.cpp(562): QFException: RSASyncExecutionThread::checkException RSASyncExecutionThread.cpp(179): QFException: CCL_CAUGHT: RSASyncExecutionThread::run(): promptPagingForward_Request RSASyncExecutionThread.cpp(612): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): promptPagingForward_Request Execution/RSRenderExecution.cpp(584): QFException: CCL_RETHROW: RSRenderExecution::execute Execution/RSRenderExecution.cpp(856): QFException: CCL_RETHROW: RSRenderExecution::processActiveDocuments Assembly/RSDocAssemblyDispatch.cpp(235): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssembly Assembly/RSLayoutAssembly.cpp(135): QFException: CCL_RETHROW: RSLayoutAssembly::assemble Assembly/RSDocAssemblyDispatch.cpp(295): QFException: CCL_RETHROW: "
Quote from: cognoshelp@ymail.com on 16 Jul 2008 09:52:12 AM
if(([Presentation Layer].[ABC].[123]=[Presentation Layer].[DEF].[123] or [Presentation Layer].[ABC].[456]=[Presentation Layer].[DEF].[123]) is null)
then
('1')
else
('0')
I think the two bold faced brackets are unnecessary. Further the 'is null' after the second bold bracket is a condition that has no comparitive data item. Either remove the 'is null' or add a data item to test its 'nullness'. Did I just coin a new term?
8)
Your logic looks odd.
I'll strip out the second part after the 'or' to give us a criteria of;
[Presentation Layer].[ABC].[123]=[Presentation Layer].[DEF].[123] is Null
It makes no sense to me.
You could say
[Presentation Layer].[ABC].[123] is null
or
[Presentation Layer].[ABC].[123]+[Presentation Layer].[DEF].[123]is null
but you can't put an '=' between the two items and then ask is null.
Also, you can's write
(A or B) is null
The correct logic is
(A is null) or (B is null)
Quote
Did I just coin a new term?
Yep, 'nullability' is the common term, though that describes one aspect of a table column. So you may have introduced a novelty ;D
Quote from: Robl on 16 Jul 2008 10:50:52 AM
Your logic looks odd.
I'll strip out the second part after the 'or' to give us a criteria of;
[Presentation Layer].[ABC].[123]=[Presentation Layer].[DEF].[123] is Null
It makes no sense to me.
You could say
[Presentation Layer].[ABC].[123] is null
or
[Presentation Layer].[ABC].[123]+[Presentation Layer].[DEF].[123]is null
but you can't put an '=' between the two items and then ask is null.
Also, you can's write
(A or B) is null
The correct logic is
(A is null) or (B is null)
Thanks for your time!!
Here I need to compare two tables and three columns..
if it returns null then it represents open location for our business (Warehouse). if it is not null then the location is full.I hope this time its bit clear pls see my top comments!!
I need it very urgent...Please respond ASAP
Quote from: blom0344 on 16 Jul 2008 04:28:53 PM
Yep, 'nullability' is the common term, though that describes one aspect of a table column. So you may have introduced a novelty ;D
Hey can you explain in more detail way...Please
It was sort of a joke. When you design a database table you specify for each column the nullability.
A column can be allowed to store null values or not.
However, the outcome of a query may yield null values as well, which is not the same thing ;)