MACRO to extract data from HP ALM

[shre0047]

We have the following code as a macro to extract data from the database of ALMQC.

SQLquery = " SELECT " _
    & " COALESCE ( " _
    & " (       tsetfold10.CF_ITEM_NAME " _
    & " + '\' + tsetfold9.CF_ITEM_NAME " _
    & " + '\' + tsetfold8.CF_ITEM_NAME " _
    & " + '\' + tsetfold7.CF_ITEM_NAME " _
    & " + '\' + tsetfold6.CF_ITEM_NAME " _
    & " + '\' + tsetfold5.CF_ITEM_NAME " _
    & " + '\' + tsetfold4.CF_ITEM_NAME " _
    & " + '\' + tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), "


SQLquery = SQLquery & " (       tsetfold9.CF_ITEM_NAME " _
    & " + '\' + tsetfold8.CF_ITEM_NAME " _
    & " + '\' + tsetfold7.CF_ITEM_NAME " _
    & " + '\' + tsetfold6.CF_ITEM_NAME " _
    & " + '\' + tsetfold5.CF_ITEM_NAME " _
    & " + '\' + tsetfold4.CF_ITEM_NAME " _
    & " + '\' + tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), "


SQLquery = SQLquery & " (       tsetfold8.CF_ITEM_NAME " _
    & " + '\' + tsetfold7.CF_ITEM_NAME " _
    & " + '\' + tsetfold6.CF_ITEM_NAME " _
    & " + '\' + tsetfold5.CF_ITEM_NAME " _
    & " + '\' + tsetfold4.CF_ITEM_NAME " _
    & " + '\' + tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), " _
    & " (       tsetfold7.CF_ITEM_NAME " _
    & " + '\' + tsetfold6.CF_ITEM_NAME " _
    & " + '\' + tsetfold5.CF_ITEM_NAME " _
    & " + '\' + tsetfold4.CF_ITEM_NAME " _
    & " + '\' + tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), "


SQLquery = SQLquery & " (       tsetfold6.CF_ITEM_NAME " _
    & " + '\' + tsetfold5.CF_ITEM_NAME " _
    & " + '\' + tsetfold4.CF_ITEM_NAME " _
    & " + '\' + tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), " _
    & " (       tsetfold5.CF_ITEM_NAME " _
    & " + '\' + tsetfold4.CF_ITEM_NAME " _
    & " + '\' + tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), " _
    & " (       tsetfold4.CF_ITEM_NAME " _
    & " + '\' + tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), "


SQLquery = SQLquery & " (       tsetfold3.CF_ITEM_NAME " _
    & " + '\' + tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), " _
    & " (       tsetfold2.CF_ITEM_NAME " _
    & " + '\' + tsetfold1.CF_ITEM_NAME " _
    & " ), " _
    & " (       tsetfold1.CF_ITEM_NAME " _
    & " ) " _
    & " ) as 'Test Set Path' " _
    & " ,RELEASES.REL_NAME as 'Release' "


SQLquery = SQLquery & " , test.TS_TEST_ID As 'Test ID' " _
    & " ,test.TS_NAME As 'Test Name' " _
    & " ,parent.RQ_REQ_NAME as '(L6) Req Parent' " _
    & " ,req.TPR_NAME as 'Req Type' " _
    & " ,REQ.RQ_REQ_ID As 'Req ID' " _
    & " ,REQ.RQ_REQ_NAME As 'Req Name' "


SQLquery = SQLquery & " FROM td.CYCLE tset " _
    & " Left Join td.CYCL_FOLD tsetfold1  on tsetfold1.CF_ITEM_ID  = tset.CY_FOLDER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold2  on tsetfold2.CF_ITEM_ID  = tsetfold1.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold3  on tsetfold3.CF_ITEM_ID  = tsetfold2.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold4  on tsetfold4.CF_ITEM_ID  = tsetfold3.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold5  on tsetfold5.CF_ITEM_ID  = tsetfold4.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold6  on tsetfold6.CF_ITEM_ID  = tsetfold5.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold7  on tsetfold7.CF_ITEM_ID  = tsetfold6.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold8  on tsetfold8.CF_ITEM_ID  = tsetfold7.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold9  on tsetfold9.CF_ITEM_ID  = tsetfold8.CF_FATHER_ID " _
    & " Left Join td.CYCL_FOLD tsetfold10 on tsetfold10.CF_ITEM_ID = tsetfold9.CF_FATHER_ID " _
    & " LEFT JOIN td.RELEASE_CYCLES ON RELEASE_CYCLES.RCYC_ID = tset.CY_ASSIGN_RCYC " _
    & " LEFT JOIN td.RELEASES ON RELEASES.REL_ID = RELEASE_CYCLES.RCYC_PARENT_ID " _
    & " LEFT JOIN td.REQ_TYPE typ on typ.TPR_TYPE_ID = typ.RQ_TYPE_ID " _
    & " LEFT JOIN req.REQ_COVER ON REQ.RQ_REQ_ID = REQ_COVER.RC_REQ_ID " _
    & " LEFT JOIN TEST ON REQ_COVER.RC_ENTITY_ID = TEST.TS_TEST_ID "

I get the error 'Invalid column name RQ_TYPE_ID' but I can't figure out how to resolve this simple error.