Create date from day/month

[teresa.danna@gmail.com]

Hi,

I have the following in a table:

Season Start Date
1/15
2/30
3/19
5/30

which I would like to use as part of my join, however the date syntax requires a year be added (which I'm pulling from the expense year). I've used the following Expression for the join however I'm getting an error which I believe comes down to the addition of the year which I must not be adding correctly.


[Join_Final].[State] = [Qry_PerDiem].[State Name] and
[Join_Final].[Expense Date] > = [Qry_PerDiem].[Effective Date] and
[Join_Final].[Expense Date] < = [Qry_PerDiem].[Terminate Date] and

cast(
[Qry_PerDiem].[Season Start Date]+'/'+[Join_Final].[Expense Year],
date) <= [Join_Final].[Expense Date]

Any thoughts?

Thanks again!

Teresa

[Ammus1234]

What is the error you get? Can you post it?

[teresa.danna@gmail.com]

The error appears when I try to run the tabular data for the query of the join:

QE-DEF-0261 QFWP - Parsing text: [Join_Final].[Country] = [Qry_PerDiem].[Country Name] and [Join_Final].[State] = [Qry_PerDiem].[State Name] and [Join_Final].[City] = [Qry_PerDiem].[City Name] and [Join_Final].[County] = [Qry_PerDiem].[County Name] and [Join_Final].[Expense Date] > = [Qry_PerDiem].[Effective Date] and [Join_Final].[Expense Date] < = [Qry_PerDiem].[Terminate Date] and cast( [Qry_PerDiem].[Season Start Date]+'/'+[Join_Final].[Expense Year], date) <= [Join_Final].[Expense Date] and cast( [Qry_PerDiem].[Season End Date]+'/'+[Join_Final].[Expense Year], date) >= [Join_Final].[Expense Date] QE-DEF-0260 Parsing error before or near position: 253 of: "[Join_Final].[Country] = [Qry_PerDiem].[Country Name] and [Join_Final].[State] = [Qry_PerDiem].[State Name] and [Join_Final].[City] = [Qry_PerDiem].[City Name] and [Join_Final].[County] = [Qry_PerDiem].[County Name] and [Join_Final].[Expense Date] > ="QE-DEF-0261 QFWP - Parsing text: [Join_Final].[Country] = [Qry_PerDiem].[Country Name] and [Join_Final].[State] = [Qry_PerDiem].[State Name] and [Join_Final].[City] = [Qry_PerDiem].[City Name] and [Join_Final].[County] = [Qry_PerDiem].[County Name] and [Join_Final].[Expense Date] > = [Qry_PerDiem].[Effective Date] and [Join_Final].[Expense Date] < = [Qry_PerDiem].[Terminate Date] and cast( [Qry_PerDiem].[Season Start Date]+'/'+[Join_Final].[Expense Year], date) <= [Join_Final].[Expense Date] and cast( [Qry_PerDiem].[Season End Date]+'/'+[Join_Final].[Expense Year], date) >= [Join_Final].[Expense Date]QE-DEF-0260 Parsing error before or near position: 253 of: "[Join_Final].[Country] = [Qry_PerDiem].[Country Name] and [Join_Final].[State] = [Qry_PerDiem].[State Name] and [Join_Final].[City] = [Qry_PerDiem].[City Name] and [Join_Final].[County] = [Qry_PerDiem].[County Name] and [Join_Final].[Expense Date] > ="QE-DEF-0261 QFWP - Parsing text: [Join_Final].[Country] = [Qry_PerDiem].[Country Name] and [Join_Final].[State] = [Qry_PerDiem].[State Name] and [Join_Final].[City] = [Qry_PerDiem].[City Name] and [Join_Final].[County] = [Qry_PerDiem].[County Name] and [Join_Final].[Expense Date] > = [Qry_PerDiem].[Effective Date] and [Join_Final].[Expense Date] < = [Qry_PerDiem].[Terminate Date] and cast( [Qry_PerDiem].[Season Start Date]+'/'+[Join_Final].[Expense Year], date) <= [Join_Final].[Expense Date] and cast( [Qry_PerDiem].[Season End Date]+'/'+[Join_Final].[Expense Year], date) >= [Join_Final].[Expense Date]QE-DEF-0260 Parsing error before or near position: 253 of: "[Join_Final].[Country] = [Qry_PerDiem].[Country Name] and [Join_Final].[State] = [Qry_PerDiem].[State Name] and [Join_Final].[City] = [Qry_PerDiem].[City Name] and [Join_Final].[County] = [Qry_PerDiem].[County Name] and [Join_Final].[Expense Date] > ="QE-DEF-0261 QFWP - Parsing text: [Join_Final].[Country] = [Qry_PerDiem].[Country Name] and [Join_Final].[State] = [Qry_PerDiem].[State Name] and [Join_Final].[City] = [Qry_PerDiem].[City Name] and [Join_Final].[County] = [Qry_PerDiem].[County Name] and [Join_Final].[Expense Date] > = [Qry_PerDiem].[Effective Date] and [Join_Final].[Expense Date] < = [Qry_PerDiem].[Terminate Date] and cast( [Qry_PerDiem].[Season Start Date]+'/'+[Join_Final].[Expense Year], date) <= [Join_Final].[Expense Date] and cast( [Qry_PerDiem].[Season End Date]+'/'+[Join_Final].[Expense Year], date) >= [Join_Final].[Expense Date]RSV-SRV-0042 Trace back:RSReportService.cpp(826): QFException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(259): QFException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(789): QFException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(250): QFException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(842): QFException: CCL_RETHROW: RSASyncExecutionThread::processCommand(): asynchRunSpecification_RequestExecution/RSRenderExecution.cpp(625): QFException: CCL_RETHROW: RSRenderExecution::executeAssembly/RSDocAssemblyDispatch.cpp(290): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSLayoutAssembly.cpp(79): QFException: CCL_RETHROW: RSLayoutAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSReportPagesAssembly.cpp(179): QFException: CCL_RETHROW: RSReportPagesAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSPageAssembly.cpp(303): QFException: CCL_RETHROW: RSPageAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableRowAssembly.cpp(175): QFException: CCL_RETHROW: RSTableRowAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(307): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchAssemblyAssembly/RSTableCellAssembly.cpp(137): QFException: CCL_RETHROW: RSTableCellAssembly::assembleAssembly/RSDocAssemblyDispatch.cpp(357): QFException: CCL_RETHROW: RSDocAssemblyDispatch::dispatchChildrenAssemblyForwardAssembly/RSAssembly.cpp(660): QFException: CCL_RETHROW: RSAssembly::createListIteratorAssembly/RSAssembly.cpp(712): QFException: CCL_RETHROW: RSAssembly::createListIteratorRSQueryMgr.cpp(1088): QFException: CCL_RETHROW: RSQueryMgr::getListIteratorRSQueryMgr.cpp(1164): QFException: CCL_RETHROW: RSQueryMgr::getResultSetIteratorRSQueryMgr.cpp(1324): QFException: CCL_RETHROW: RSQueryMgr::createIteratorRSQueryMgr.cpp(1624): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgr.cpp(1614): QFException: CCL_RETHROW: RSQueryMgr::executeRsapiCommandRSQueryMgrExecutionHandlerImpl.cpp(189): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()RSQueryMgrExecutionHandlerImpl.cpp(173): QFException: CCL_RETHROW: RSQueryMgrExecutionHandlerImpl::execute()QFSSession.cpp(1147): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1145): QFException: CCL_CAUGHT: QFSSession::ProcessDoRequest()QFSSession.cpp(1102): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSSession.cpp(1078): QFException: CCL_RETHROW: QFSSession::ProcessDoRequest()QFSConnection.cpp(788): QFException: CCL_RETHROW: QFSConnection::ExecuteQFSQuery.cpp(213): QFException: CCL_RETHROW: QFSQuery::Execute v2CoordinationQFSQuery.cpp(4452): QFException: CCL_THROW: CoordinationPlanner

[teresa.danna@gmail.com]

Actually I figured this out. The join didn't like this section:

[Join_Final].[Expense Date] > = [Qry_PerDiem].[Effective Date] and
[Join_Final].[Expense Date] < = [Qry_PerDiem].[Terminate Date] and

So I switched it to a between function and everything seems to be working for the moment...

Thanks all!