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
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.
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.
QuoteOption 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
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
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.
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.
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.
The union only appears to allow two queries. You can drag more into place.
Quote from: Cognos_Jan2017 on 14 Jun 2017 06:37:35 PM
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;
THANK you for the info on dragging additional Queries into the Union.
Always good to learn.
Trying ...
QuoteYour 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
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.
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
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
MFGF ..
Quote
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.