Outer Join

[Cognos_Jan2017]

I have lots of Microsoft Access query experience, but this is Cognos.

We have a Query pulling Answers (when they exist) of ...
Severe
Moderate
Minor

Our Manager wants to see Report rows for ...
Severe
Moderate
Minor
... even if there is no data, therefore zeros.

An Outer Join will do that.  Thinking Microsoft Access
("Make a Table") of only 3 entries of ..
Severe
Moderate
Minor

... can serve as an Outer Join to display those Answers even when
there is no data.

Gotta be simple ... how do I write a Cognos Query by defining
("Making A Table") of one query item w/ those 3 entries of ...
Severe
Moderate
Minor

TIA, Bob

[tjohnson3050]

Option 1 - Use a sql object as a source for your query:
     Syntax will vary based on your vendor - Oracle uses FROM DUAL in the from clause, SQL server leaves out the FROM clause all together.

     For example:     select 'Severe' as type union all select 'Moderate' as type union all select 'Minor' as type

Option 2 - Create a separate query for each value, hard code the value as a data item in each, then use a union object to union the values together.

[Cognos_Jan2017]

Thank you.

If I tried this correctly, I brought in a SQL Query object.

The database is SQL Server, so I entered ...
select 'Severe' as type union all select 'Moderate' as type union all select 'Minor' as type

Validate resulted in error message which included ...
No database name is specified for the SQL query of 'native' type

What have I done incorrectly?

UNION queries (used to write in Access) are wonderful.

[Cognos_Jan2017]

Option 2 - Create a separate query for each value, hard code the value as a data item in each, then use a union object to union the values together.

I made 4 separate queries, using 'ABC' as a common field, then using Microsoft Access SQL (I know it won't work w/ SQL Server, as you said FROM not used).
So my attempt at a SQL object was ...

SELECT ABC FROM A_Severe
UNION ALL
SELECT ABC FROM B_Moderate
UNION ALL
SELECT ABC FROM C_Minor
UNION ALL
SELECT ABC FROM D_MinorRelease;

Trying to Validate got the same error message as I listed before ...
No database name is specified for the SQL query of 'native' type

Getting close, but need suggestions.

TIA, Bob

[MFGF]

Hi,

I just put together an example using the Great Outdoors Sales (query) package. Here's what I did to create the SQL:

New Query - ProdQuery
SQL - Syntax = IBM Cognos, Data_Source = great_outdoors_sales, SQL = text below, Name = ProdQueryVirtualTable

Code Select Expand

select * from (
values
(     cast('Camping Equipment' as varchar(30))    ),
      (    'Mountaineering Equipment'   ),
      (    'Outdoor Protection'    ),
      (    'Personal Accessories'    ),
      (    'Golf Equipment'    )
)
MainProductNames (PLName )

You then join this to your main query 1..1 <--> 0..n

This then means you will see all product lines, regardless of whether they exist in your main query.

I'm attaching my report spec below.

Cheers!

MF.

[Cognos_Jan2017]

Thank you.

Awake too early.  Will try when at work later today, and report back results.

Very unskilled in the Report Spec HTML.  Used to use Access' Query by Example Grid, and modified that SQL as String in VB/ VBA modules running in subroutines.

Gotta learn the Cognos way.

[Cognos_Jan2017]

tjohnson3050 - Making 4 separate queries and then making part of a UNION works.  THANK you.

For those needing to learn more about Cognos' UNION queries, this is a good reference ...
ttps://www.youtube.com/watch?v=pjzhzv0LUwU

I don't know how 3, 4, or 5 areas can be used instead of the standard 2 areas UNION provides.

Don't know how to get Option 1 to work, but will continue studying that.

MFGF - I don't know the proper steps to take to utilize your code but will continue
studying that.  THANK you.

[tjohnson3050]

The union only appears to allow two queries.  You can drag more into place.

[tjohnson3050]

I made 4 separate queries, using 'ABC' as a common field, then using Microsoft Access SQL (I know it won't work w/ SQL Server, as you said FROM not used).
So my attempt at a SQL object was ...

SELECT ABC FROM A_Severe
UNION ALL
SELECT ABC FROM B_Moderate
UNION ALL
SELECT ABC FROM C_Minor
UNION ALL
SELECT ABC FROM D_MinorRelease;

Trying to Validate got the same error message as I listed before ...
No database name is specified for the SQL query of 'native' type

Getting close, but need suggestions.

TIA, Bob

Your SQL Object should look more like:

SELECT 'Severe' as TYPE
UNION ALL
SELECT 'Moderate' as TYPE
UNION ALL
SELECT 'Minor' as TYPE
UNION ALL
SELECT 'MinorRelease' as TYPE;

[Cognos_Jan2017]

THANK you for the info on dragging additional Queries into the Union.
Always good to learn.

Trying ...

Your SQL Object should look more like:

SELECT 'Severe' as TYPE
UNION ALL
SELECT 'Moderate' as TYPE
UNION ALL
SELECT 'Minor' as TYPE
UNION ALL
SELECT 'MinorRelease' as TYPE;

... in the SQL object I drag in, then apply the SQL, results in a Validate of ...
RQP-DEF-0500 No database name is specified for the SQL query of 'native' type.
RSV-SRV-0042 Trace back:
RSReportService.cpp(762): RQPException: CCL_CAUGHT: RSReportService::processImpl()
RSReportServiceMethod.cpp(259): RQPException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_Request
RSASyncExecutionThread.cpp(871): RQPException: RSASyncExecutionThread::checkException .....

What am I doing wrong?

TIA, Bob

[tjohnson3050]

When you are looking at the queries, and have the SQL object selected, look down in the properties screen for a datasource property.  Choose a data source.  It doesn't really matter which as long as it's a SQL server.  You won't query the actual data, you are asking the database server to 'create' the data set for you.

[Cognos_Jan2017]

Thank you tjohnson3050.

When selecting the SQL Object's properties, it displays ...
DATA
SQL syntax   Native
Data source  I selected 'CompanyDB'
SQL             ... displayed your code I added

Miscellaneous
Name          SQL1

When I added the code to a SQL Object, and when "Validate" it did NOT
report an error.  But viewing tabular data reseults in ...

An error occurred while performing operation 'sqlPrepareWithOptions' status='-56'.

UDA-SQL-0115 Inappropriate SQL request.UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Deferred prepare could not be completed.UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Statement(s) could not be prepared. (SQLSTATE=42000, SQLERRORCODE=8180)UDA-SQL-0564 [Microsoft SQL Server Native Client 11.0]Incorrect syntax near ';'. (SQLSTATE=42000, SQLERRORCODE=102)RSV-SRV-0042 Trace back:RSReportService.cpp(732): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(871): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(317): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(916): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestRSRequest.cpp(1629): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()RSQueryMgr.cpp(785): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(857): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(965): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(296): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(620): QFException: CCL_THROW: RSQueryMgrBasic::processExceptionRSQueryMgrExecutionHandlerImpl.cpp(168): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSFaultHandler.cpp(97): QFException: CCL_THROW: RSFaultHandler::handleNonCriticalExceptionQFSSession.cpp(1182): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1180): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1137): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1113): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(888): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(311): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4522): QFException: CCL_THROW: CoordinationPlanner

[Cognos_Jan2017]

tjohnson305o ...

I realized the SQL syntax probably needed to be IBM Cognos,
and selecting that resulted in a new error ...

An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.

UDA-SQL-0358 Line 2: Syntax error near "UNION".RQP-DEF-0149 The query specification is incorrect.RQP-DEF-0457 Referenced Query 'Q_tjohnson' is not defined or its query items contain unresolved references.RSV-SRV-0042 Trace back:RSReportService.cpp(732): QFException: CCL_CAUGHT: RSReportService::processImpl()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(871): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(317): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(916): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestRSRequest.cpp(1629): QFException: CCL_THROW: RSRequest::executeInteractivePrompting()RSQueryMgr.cpp(785): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(857): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(965): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgrBasic.cpp(296): QFException: CCL_RETHROW: RSQueryMgrBasic::executeRsapiCommandRSQueryMgrBasic.cpp(620): QFException: CCL_THROW: RSQueryMgrBasic::processExceptionRSQueryMgrExecutionHandlerImpl.cpp(168): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSFaultHandler.cpp(97): QFException: CCL_THROW: RSFaultHandler::handleNonCriticalExceptionQFSSession.cpp(1182): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1180): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1137): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1113): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(888): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(311): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4522): QFException: CCL_THROW: CoordinationPlanner

[Cognos_Jan2017]

MFGF ..

I just put together an example using the Great Outdoors Sales (query) package. Here's what I did to create the SQL:

New Query - ProdQuery
SQL - Syntax = IBM Cognos, Data_Source = great_outdoors_sales, SQL = text below, Name = ProdQueryVirtualTable


Code: [Select]

select * from (
values
(     cast('Camping Equipment' as varchar(30))    ),
      (    'Mountaineering Equipment'   ),
      (    'Outdoor Protection'    ),
      (    'Personal Accessories'    ),
      (    'Golf Equipment'    )
)      
MainProductNames (PLName )

You then join this to your main query 1..1 <--> 0..n

This then means you will see all product lines, regardless of whether they exist in your main query.

I'm attaching my report spec below.

Cheers!

MF.

* Virtual SQL and outer join

After I clicked properties, and based on what tjohn3050 and you recommended, it DOES Work !!!

THANK you, and have a good weekend.