i have created a dynamic sql have checked my outputs as well as my connections which are fine … gives me error Unable to invoke adapter service colt01.common.adapters.jdbc:selectCOLTLaborHours

Any ideas? Here is the SQL …

LD.DLY_DT,LW.WRK_RQST_NO,LW.LBR_EXP_IND,LD.DLY_QTY,CHP.ALT_ID_NO,LW.CHRG_AU
FROM
DMP.LBR_TM_EXP_WKLY_LI LW,DMP.LBR_TM_EXP_RPTG LR,DMP.PRSN_DMS DMS,DMP.LBR_TM_EXP_DLY_LI LD,CHP.UPMSA1SG CHP
WHERE
LW.XACTN_TMSTMP >= ‘2005-05-02 00.33.34.000000’ AND LW.XACTN_TMSTMP <=> ’ ’ AND LR.LBR_EXP_XACTN_ID = LW.LBR_EXP_XACTN_ID AND LR.CURR_WK_EDT = LW.CURR_WK_EDT AND LD.CURR_WK_EDT = LW.CURR_WK_EDT AND LD.WKLY_LI_NO = LW.WKLY_LI_NO AND LD.XACTN_TMSTMP >= ‘2005-05-02 00.33.34.000000’ AND LD.XACTN_TMSTMP <=> 0 ORDER BY LW.XACTN_TIMESTAMP

also hve tried using SELECT DISTINCT …

Leon,

The dynamic SQL adapter service template is intended to let you pass in a portion of the SQL statement in an input variable. For example, you can pass in the where clause, the columns list or even the entire SQL statement. See the docs in \packages\WmJDBCAdapter\docs for the syntax.

Is it possible that you are not passing in part of the SQL statement, but instead providing the entire SQL statement with no substitution variables?

Mark

Thanks Mark but actually i have a service that generates SQL for LaborHour & VehicleHour, it generates fine but when i runs the next service which is retrieveCOLTHours it fails at the selectCOLTLaborHour JDBC connection … so i tried to manually run the connection with the SQL for LaborHour … my dynamicSQL has SELECT $(SQL) which will take the entire sql statment. I have also set the right outPut fields …

more help needed on this…
Thanks,
Leon

Leon,

What is the specific error message from the logs? Is it possible that this JDBC service uses a different adapter connection and that that connection is not enabled?

We can play 20 questions all day (well, not really, I’m stopping after this one). Provide more detail on the error and I’ll see what I can do. I’m done guessing.

Mark