What function which you suggest to use

[cognoshelp@ymail.com]

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

[blom0344]

ABC.123=EFG.456 if it returns null value

Can you be more specific on this?

[Robl]

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)

[cognoshelp@ymail.com]

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

[cognoshelp@ymail.com]

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: "

[MDXpressor]

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)

[Robl]

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)

[blom0344]

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

[cognoshelp@ymail.com]

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

[cognoshelp@ymail.com]

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

[blom0344]

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  ;)