Adding complex Where Clause to addData() or Insert SQL

[tece321]

Current version: SmartClient Version: v11.1p_2018-07-19/PowerEdition Deployment (built 2018-07-19)

The client is executing an addData( record, new DSCallback () {}, DSRequest ); using an Operation ID in the DSRequest to vector to this operation binding 'cloneWaiverRuns'

    <operationBinding operationType="add" operationId="cloneWaiverRuns">
        <customSQL> 

INSERT INTO PNR_Waiver_Run(
   Waiver_Comment
  ,WMode
  ,WSubMode
  ,WModeType
  ,WStageName
  ,WVoltage
  ,WCorner
  ,WTemp
  ,WBlock
  ,WProjName
  ,WRevision
  ,PNR_WaiverRunState
  ,FK_BCG_UIWaiverMaster
)
SELECT 
  WR.Waiver_Comment
,WR.WMode
,WR.WSubMode
,WR.WModeType
,WR.WStageName
,WR.WVoltage
,WR.WCorner
,WR.WTemp
,WR.WBlock
,$values.tgtProjName
,$values.tgtRevision
,'Pending'
,PK_TGT
FROM PNR_Waiver_Run as WR
JOIN dbo.BCG_WaiverMaster as WM_Src  ON (WM_Src.PK_BCG_Waiver_Master = WR.FK_BCG_UIWaiverMaster AND WM_Src.FK_idProject = $values.pkSrcProj)
JOIN ( SELECT  WM2_Tgt.PK_BCG_Waiver_Master as PK_TGT, * FROM BCG_WaiverMaster as WM2_Tgt 
LEFT JOIN PNR_Waiver_Run as WR_Tgt ON (WM2_Tgt.PK_BCG_Waiver_Master = WR_Tgt.FK_BCG_UIWaiverMaster)
WHERE  WM2_Tgt.FK_idProject = $values.pkTgtProj )
AS Target ON (Target.FK_WaiverRequestor = WM_Src.FK_WaiverRequestor  
AND Target.Waiver_Table = WM_Src.Waiver_Table
AND Target.NameOfWaiverSet = WM_Src.NameOfWaiverSet
AND Target.Public_Set = WM_Src.Public_Set)
WHERE Target.PNR_WaiverRunState is NULL 

 </operationBinding>

I need to ADD a clause to the final WHERE of the form:

AND (WR.WBLOCK LIKE 'foo\_far' OR WR.WBLOCK LIKE '%pcie%' OR WR.WBLOCK LIKE 'Another%Example')

Note there are SQL LIKE widecards in the clause.

I have tried setting an AdvancedCriteria on the DSRequest of the addData() but this causes the data of the passed record to be dropped. Something comes to my memory of adding a Criteria to an addData() call changes it into the fetchData() call ?


In that case I modified the last line of the operation to:

WHERE Target.PNR_WaiverRunState is NULL AND $defaultWhereClause

hoping the passed Adv. Criteria would be added but no success.

Anyway I have tried adding the complex Where clause as a String type to the record, as an attribute called blockCrit, and accessing it's value by:

WHERE Target.PNR_WaiverRunState is NULL AND $rawValue.blockCrit

but that failed.

So is this doable? Is there a way to use velocity in the Operation binding to add the complex criteria - which is a long list of OR clauses, and I don't know how long the clause may be ahead of time. The user selects from a large list to sub-select the WBlock values.

Can you point me in the right direction?

[Isomorphic]

Where clauses are not generated for “add” operations since a normal SQL insert has no where clause.

You can, at any point, get any of the generated SQL clauses for any operation via SQLDataSource.getSQLClause(). So if you need a where clause you could construct a “fetch” DSRequest and call getSQLClause() with that.

[tece321]

Thanks.

I think you may be sending me down a much bigger change path than I want to make.

Let me ask this another way. I have an Operation Binding for an addData() call. I wish to add a velocity construct that will allow me to add a complex SQL condition, as the INSERT is with a SELECT clause.

I have tried to use $rawValue.blockCrit and I get an exception

....SQLServerException: Invalid pseudocolumn "$rawValue".

Is $rawValue not available for addData() ? It is available on fetch operations.

I have tried to use $value.blockCrit and I have have problems with the velocity modifying the passed string and changing escaped characters and SQL matching expressions.

Or maybe there is no 'simple' way to do what I want?

[tece321]

Well I found this construct worked:

WHERE Target.PNR_WaiverRunState is NULL AND ( $rawValue.advancedCriteria.blockCrit )

The velocity substitution results in this:

WHERE Target.PNR_WaiverRunState is NULL AND (WR.WBlock LIKE 'foo\_far' OR WR.WBlock LIKE '%pcie%' )

Will this 'hack', using $rawValue.advancedCriteria.variableName continue to work in future releases.

Something from the back of my mind believes that I've asked this same question in the past.

[Isomorphic]

Setting AdvancedCriteria on an "add" request is definitely something that might be rejected outright in the future, since it's invalid for the operationType.

But there is no need to jam this data through inappropriate APIs when you have dsRequest.addToTemplateContext() as a means of making whatever you want available to the Velocity template. So just take your "blockCrit" string and add it that way, and that's simpler anyway.