Results 1 to 1 of 1

Thread: Queries

  1. #1
    Join Date
    Jul 2006
    Posts
    157

    Unanswered: Queries

    I am trying to debug an old program. There are queries which passess in the property method. I am unable to understand these queries if any one please help me in here?

    Thanks.


    Code:
    Public Property Let LookupType(ByVal RHS As ClientLookupTypeEnum)
      Dim DebtorSelect As String
      Dim DebtUDFSelect As String
      mLookupType = RHS
      mDebtIdNoTableName = ""
      Select Case mLookupType
      Case ClientLookupTypeEnum.lDebts
        'mBaseSQL = "SELECT *,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT ,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST, (SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO from debt_subview DS WHERE {WHERE}"
        mBaseSQL = "SELECT {SELECT}DS.*,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST,(SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO {ADDITIONALSELECTFIELDS} from debt_subview DS join DBTR DR on (DR.DEBTOR_ID = DS.PRIMARY_DEBTOR_ID,0) WHERE {WHERE}"
        mBaseSQLDebtor = "SELECT {SELECT}DS.*,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST,(SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO {ADDITIONALSELECTFIELDS} from debt_subview DS join " & "lcltbl{LOCALEXPORTTABLE}" & " CE on (ds.PRIMARY_DEBTOR_ID = CE.CE_PRIMARY_DEBTOR_ID,0) join DBTR DR on (DR.DEBTOR_ID = CE.CE_PRIMARY_DEBTOR_ID,0) WHERE {WHERE}"
        mBaseSQLCltRefNo = "SELECT {SELECT}DS.*,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST,(SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO {ADDITIONALSELECTFIELDS} from debt_subview DS join " & "lcltbl{LOCALEXPORTTABLE}" & " CE on (ds.CLT_REF_NO = CE.CE_CLT_REF_NO,0) join DBTR DR on (DR.DEBTOR_ID = ds.PRIMARY_DEBTOR_ID,0) WHERE {WHERE}"
        mBaseSQLCltRefNoWithCltId = "SELECT {SELECT}DS.*,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST,(SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO {ADDITIONALSELECTFIELDS} from debt_subview DS join " & "lcltbl{LOCALEXPORTTABLE}" & " CE on ((ds.CLT_REF_NO = CE.CE_CLT_REF_NO,0) and (ds.CLT_ID=CE.CE_CLT_ID,0)) join DBTR DR on (DR.DEBTOR_ID = ds.PRIMARY_DEBTOR_ID,0) WHERE {WHERE}"
        mBaseSQLDebt = "SELECT {SELECT}DS.*,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST,(SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO {ADDITIONALSELECTFIELDS} from debt_subview DS join " & "lcltbl{LOCALEXPORTTABLE}" & " CE on ((ds.PRIMARY_DEBTOR_ID = CE.CE_DEBT_ID,0) and (ds.DEBT_NO=CE.CE_DEBT_NO)) join DBTR DR on (DR.DEBTOR_ID = CE.CE_PRIMARY_DEBTOR_ID,0) WHERE {WHERE}"
        mDebtIdNoTableName = "ds"
      Case ClientLookupTypeEnum.lPayments
        mBaseSQL = "SELECT *{ADDITIONALSELECTFIELDS} FROM PMT_VIEW P JOIN DBTR DR on (P.DEBTOR_ID=DR.DEBTOR_ID) WHERE {WHERE}"
        mBaseSQLDebtor = "SELECT * {ADDITIONALSELECTFIELDS}  FROM PMT_VIEW P JOIN lcltbl{LOCALEXPORTTABLE} CE on (P.DEBTOR_ID = CE.CE_PRIMARY_DEBTOR_ID,0) WHERE {WHERE}"
        mBaseSQLCltRefNo = "SELECT * {ADDITIONALSELECTFIELDS}  FROM PMT_VIEW P JOIN lcltbl{LOCALEXPORTTABLE} CE on P.CLT_REF_NO = CE.CE_CLT_REF_NO WHERE {WHERE}"
        mBaseSQLCltRefNoWithCltId = "SELECT * {ADDITIONALSELECTFIELDS}  FROM PMT_VIEW P JOIN lcltbl{LOCALEXPORTTABLE} CE on ((P.DEBT_CLT_REF_NO = CE.CE_CLT_REF_NO,0) and P.CLT_ID = CE.CE_CLT_ID) WHERE {WHERE}"
        mBaseSQLDebt = "SELECT * {ADDITIONALSELECTFIELDS}  FROM PMT_VIEW P JOIN lcltbl{LOCALEXPORTTABLE} CE on ((P.DEBT_ID = CE.CE_DEBT_ID,0) and P.DEBT_NO = CE.CE_DEBT_NO) WHERE {WHERE}"
        mDebtIdNoTableName = "P"
        TableNameToJoinWithCE = "P"
      Case ClientLookupTypeEnum.lLetters
    '    mBaseSQL = "SELECT DS.CLT_ID,DS.CLT_REF_NO,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT ,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST, (SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO,sentltr.* from dbtr join sentltr on dbtr.debtor_id = sentltr.debtor_id join debt ds on (ds.primary_debtor_id = dbtr.debtor_id,0) where {WHERE}"
    '    mBaseSQL = "SELECT DS.CLT_ID,DS.CLT_REF_NO,(select count(*) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_COUNT ,(select list( convert(char ( 20 ),PMT.PMT_DATE, 111) + ' ' + string(PMT.PRINC_AMT) + ' ' + PMT.PMT_CODE ) from PMT where (PMT.DEBT_ID=DS.DEBT_ID AND PMT.DEBT_NO=DS.DEBT_NO)) as PMT_LIST, (SELECT MAX(string(DEBT_ID) + '-'+ string(DEBT_NO)) FROM DEBT D3 WHERE D3.CLT_REF_NO=DS.CLT_REF_NO and D3.STATUS_CODE=(Select min(STATUS_CODE) From DEBT D2 where D2.CLT_REF_NO=DS.CLT_REF_NO)) as CUR_DEBT_ID_NO,sentltr.* from debt ds join lcltbl{LOCALEXPORTTABLE} CE on (ds.PRIMARY_DEBTOR_ID=CE.CE_PRIMARY_DEBTOR_ID,0) join SENTLTR on (SENTLTR.sent_ltr_id = CE.CE_SENT_LTR_ID,0) join dbtr DR on (DR.debtor_id = CE.CE_PRIMARY_DEBTOR_ID,0) where {WHERE}"
      Case Else
        mBaseSQL = vbNullString
      End Select
    End Property
    Last edited by loquin; 02-05-07 at 17:13. Reason: add [code] [/code] tags for readability.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •