Условия в запросе sql в аксапте

[user_ax]

Цитата:

Сообщение от Poleax

Приведите текст кода запроса.

Вы случаем не пытаетесь в запросе у LedgerJournalTable найти поле PaymPurpose_ZTR?
Она явно в LedgerJournalTrans.PaymPurpose_ZTR

    if (object_id('tempdb..#vendsettlement') IS NOT NULL) begin drop table #vendsettlement; end
    if (object_id('tempdb..#custsettlement') IS NOT NULL) begin drop table #custsettlement; end
    if (object_id('tempdb..#saldo') IS NOT NULL) begin    drop table #saldo; end
    if (object_id('tempdb..#saldoPr') IS NOT NULL) begin    drop table #saldoPr; end
    select transrecid, sum(settleamountcur) as settlecur, sum(settleamountmst) as settlemst, sum(exchadjustment) as exchadj
        into #vendsettlement
        from vendsettlement (nolock) where transdate < '2013.01.01' and ltrim(accountnum) like '%19265155'
            and dataareaid = 'zkz'
        group by transrecid

    select transrecid, sum(settleamountcur) as settlecur, sum(settleamountmst) as settlemst, sum(exchadjustment) as exchadj
        into #custsettlement
        from custsettlement (nolock) where transdate < '2013.01.01' and ltrim(accountnum) like '%19265155'
            and dataareaid = 'zkz'
        group by transrecid

    select 'Поставщик' as k,
        ltrim(LedgerAccount_ZTR) as LedgerAccount_ZTR,
        --isNull(CodeId_ZTR, '') as Dimension,
        (select CompanyGroup from Dimensions (nolock) where Dimensions.Num = custtrans.Dimension  and Dimensions.dataareaid = 'zkz' ) as Dimension,
        custtrans.RContractCode,
        custtrans.accountnum, substring(custtable.name,1, 100) as name,
        substring('                                                                                                                                                      ',1,140) as n,
        isnull(country.name, '') as name1,
        case
          ---> 3780 26.02.2013
          --   when (Journaltype = 10)
          --   then substring(LedgerjournalTrans.PAYMPURPOSE_ZTR,1,1000)
        --<-
            when SubjectOfAgreement_ZTR != '' then SubjectOfAgreement_ZTR
            when (LedgerjournalTrans.BankCentralBankPurposeText is not null and LedgerjournalTrans.BankCentralBankPurposeText != '' and  Journaltype = 4)
               then LedgerjournalTrans.BankCentralBankPurposeText
               when (LedgerjournalTrans.Txt is not null and LedgerjournalTrans.Txt != '' and (Journaltype = 0 or Journaltype = 45))
                then custtrans.Txt
            when (substring(rContractTable.RContractSubject,1,1000) is not null and substring(rContractTable.RContractSubject,1,1000) != '')  then substring(rContractTable.RContractSubject,1,1000)
            else ''
        end as RContractSubject,
        
        isnull(rContractTable.RCONTRACTNUMBER, '') as RCONTRACTNUMBER,
        sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) as mst,
        CustTrans.transdate,
        custtrans.DueDate,
      case
            when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 0 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 1 and day('2013.01.01') <= day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p1',
      case
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 1 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 1) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 3))
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') <= day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p2',
        case
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 3) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 36))
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') <= day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p3',
        case
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 36))
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p4',
        sum(-amountcur)-sum(-isnull(settlecur,0)) as cur,
        custtrans.currencycode, ' ' as f,

        isnull(rContractTable.RContractAccount, '') as RContractAccount,
        isnull(RContractPartnerType,'') as RContractPartnerType,
        case
            when (LedgerjournalTrans.PaymentStatusUserId_ZTR != '')
                then LedgerjournalTrans.PaymentStatusUserId_ZTR
            when  custtrans.CreatedBy != '' then custtrans.CreatedBy
            else ''
        end as CreatedBy , custtrans.Dimension3_, custtrans.Dimension7_
        into #saldo
        from vendtrans custtrans (nolock)
        left join #vendsettlement (nolock) on custtrans.recid = #vendsettlement.transrecid
        inner join vendtable custtable (nolock) on custtable.accountnum = custtrans.accountnum and custtable.dataareaid = 'zkz'left join AddressCountryRegion country (nolock) on country.CountryRegionId = custtable.CountryRegionId and country.dataareaid = 'zkz'left join rContractTable (Nolock) on rContractTable.RContractPartnerType = '1' and
                      rContractTable.RContractCode        = custtrans.RContractCode        and
                      rContractTable.RContractAccount     = custtrans.RContractAccount --and RContractStatus = '1'
                      and rContractTable.dataareaid = 'zkz'
        left join (select voucher, POSTINGPROFILE, offsetpostingprofile_ru, PaymentStatusUserId_ZTR,
                    LedgerjournalTrans.BankCentralBankPurposeText,LedgerjournalTrans.txt, Journaltype
        from LedgerjournalTrans (nolock)
         inner join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1
            and Journaltype in(0, 4,  45)  and LedgerjournalTrans.dataareaid = 'zkz'
            and LedgerjournalTrans.dataareaid = 'zkz'
            ) as LedgerjournalTrans
            on custtrans.voucher = LedgerjournalTrans.voucher
            and (LedgerjournalTrans.postingprofile in
            (select vendledgeraccounts.postingprofile from vendledgeraccounts (nolock)
            where (accountCode = 2 or (accountcode = 1 and num = vendgroup))
                and vendledgeraccounts.dataareaid = 'zkz' and
            ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)) or (offsetpostingprofile_ru in
            (select vendledgeraccounts.postingprofile from vendledgeraccounts (nolock)
            where (accountCode = 2 or (accountcode = 1 and num = vendgroup))
                and vendledgeraccounts.dataareaid = 'zkz' and
            ltrim(sumaccount) = ltrim(LedgerAccount_ZTR))))
        --left join RContractTypes (nolock) on RContractTypes.ContractCode = custtrans.RContractCode and RContractTypes.ContractPartnerType = 1
        where CustTrans.transdate < '2013.01.01' and (closed = '19000101' or closed >= '2013.01.01')
            and ltrim(LedgerAccount_ZTR) in ('6312100')
            and ltrim(custtrans.accountnum) like '%19265155' and custtrans.dataareaid = 'zkz'
            and LedgerAccount_ZTR not in(select accountnum from NotIncludeAccount_ZTR (nolock) where NotIncludeAccount_ZTR.dataareaid = 'zkz')

        group by LedgerAccount_ZTR, custtrans.RContractCode,
            custtrans.accountnum, custtable.name,
            country.name,
            substring(rContractTable.RContractSubject,1,1000),
            rContractTable.RCONTRACTNUMBER,
            CustTrans.transdate,
            custtrans.DueDate,custtrans.Dimension,
            custtrans.currencycode,rContractTable.RContractAccount,RContractPartnerType
            ,PaymentStatusUserId_ZTR, LedgerjournalTrans.BankCentralBankPurposeText ,custtrans.CreatedBy, --CodeId_ZTR,
            LedgerjournalTrans.txt, Journaltype, SubjectOfAgreement_ZTR ,custtrans.Txt,
            --Ledgerjournaltrans.PAYMPURPOSE_ZTR,
        --having sum(amountcur) != 0 or sum(amountmst) != 0
  custtrans.Dimension3_, custtrans.Dimension7_
    insert into #saldo
    select 'Клиент' as k ,/*' ' as g,*/ ltrim(LedgerAccount_ZTR) as LedgerAccount_ZTR,
        isNull(CodeId_ZTR, '') as Dimension,
        --(select CompanyGroup from Dimensions (nolock) where Dimensions.Num = custtrans.Dimension) as Dimension,
        custtrans.RContractCode,
        custtrans.accountnum, substring(custtable.name,1, 100),
        (select FinalDelvCustName_ZTR from SalesTable where SalesTable.SalesId = custtrans.DocId_ZTR) as n,
        isnull(country.name, '') as name1,
        case
         ---> 3780 26.02.2013
         when (Journaltype = 10)
         then substring(LedgerjournalTrans.PAYMPURPOSE_ZTR,1,500)
        
          --<-
            when SubjectOfAgreement_ZTR != '' then SubjectOfAgreement_ZTR
            /*when (LedgerjournalTrans.BankCentralBankPurposeText is not null and LedgerjournalTrans.BankCentralBankPurposeText != '' and  Journaltype = 7)
                then LedgerjournalTrans.BankCentralBankPurposeText*/
            when (LedgerjournalTrans.Txt is not null and LedgerjournalTrans.Txt != '' /*and (Journaltype = 0 or Journaltype = 45)*/)
                then custtrans.Txt
            when (substring(rContractTable.RContractSubject,1,1000) is not null and substring(rContractTable.RContractSubject,1,1000) != '')  then substring(rContractTable.RContractSubject,1,1000)

            else ''
        end as RContractSubject,
        isnull(rContractTable.RCONTRACTNUMBER, '') as RCONTRACTNUMBER,
        sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0)) as mst,
        Custtrans.transdate,
        custtrans.DueDate,
      case
            when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 0 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when dateDiff(month, CustTrans.Duedate, '2013.01.01') = 1 and day('2013.01.01') <= day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p1',
      case
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 1 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 1) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 3))
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') <= day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p2',
        case
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 3 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 3) and (dateDiff(month, CustTrans.Duedate,'2013.01.01') < 36))
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') <= day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p3',
        case
            when dateDiff(month, CustTrans.Duedate,'2013.01.01') = 36 and day('2013.01.01') > day(CustTrans.Duedate)
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            when ((dateDiff(month, CustTrans.Duedate,'2013.01.01') > 36))
            then sum(-amountmst)-sum(-isnull(settlemst,0))+sum(-isnull(exchadj,0))
            else 0
        end as 'p4',

        sum(-amountcur)-sum(-isnull(settlecur,0)) as cur,
        custtrans.currencycode, '' as f,
        isnull(rContractTable.RContractAccount,'') as RContractAccount,
        isnull(RContractPartnerType,'') as RContractPartnerType,
        case
            when (LedgerjournalTrans.PaymentStatusUserId_ZTR != '')  then LedgerjournalTrans.PaymentStatusUserId_ZTR
            when  custtrans.CreatedBy != '' then custtrans.CreatedBy
            else ''
        end as CreatedBy
 , custtrans.Dimension3_, custtrans.Dimension7_
        from custtrans  (nolock)
        left join #custsettlement on custtrans.recid = #custsettlement.transrecid
        inner join custtable (nolock) on custtable.accountnum = custtrans.accountnum and custtable.dataareaid = 'zkz'left join AddressCountryRegion country (nolock) on country.CountryRegionId = custtable.CountryRegionId and country.dataareaid = 'zkz'left join rContractTable (Nolock) on rContractTable.RContractPartnerType = '0' and
                      rContractTable.RContractCode        = custtrans.RContractCode        and
                      rContractTable.RContractAccount     = custtrans.RContractAccount --and RContractStatus = '1'
                      and rContractTable.dataareaid = 'zkz'
        left join LedgerjournalTrans (nolock) on custtrans.voucher = LedgerjournalTrans.voucher
            and LedgerjournalTrans.dataareaid = 'zkz'
            and (LedgerjournalTrans.postingprofile in
            (select custledgeraccounts.postingprofile from custledgeraccounts (nolock)
            where (accountCode = 2 or (accountcode = 1 and num = custgroup))
            and custledgeraccounts.dataareaid = 'zkz' and
            ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)) or (offsetpostingprofile_ru in
            (select custledgeraccounts.postingprofile from custledgeraccounts (nolock)
            where (accountCode = 2 or (accountcode = 1 and num = custgroup))
            and custledgeraccounts.dataareaid = 'zkz'  and
            ltrim(sumaccount) = ltrim(LedgerAccount_ZTR))))
        left join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1
            and Journaltype in(0, 7, 45) and LedgerjournalTable.dataareaid = 'zkz'
        left join RContractTypes (nolock) on RContractTypes.ContractCode = custtrans.RContractCode and RContractTypes.ContractPartnerType = 0
            and RContractTypes.dataareaid = 'zkz'
        where Custtrans.transdate < '2013.01.01' and (closed = '19000101' or closed >= '2013.01.01')
                    and ltrim(LedgerAccount_ZTR) in ('6312100')
                    and ltrim(custtrans.accountnum) like '%19265155'
                    and LedgerAccount_ZTR not in(select accountnum from NotIncludeAccount_ZTR (nolock) where NotIncludeAccount_ZTR.dataareaid = 'zkz')
                    and custtrans.dataareaid = 'zkz'

        group by custtrans.accountnum, custtrans.currencycode, custtrans.RContractCode, custtrans.Dimension,
            rContractTable.RContractCode,
            substring(rContractTable.RContractSubject,1,1000),
            LedgerAccount_ZTR, Custtrans.transdate,RContractPartnerType, rContractTable.RCONTRACTNUMBER,
            custtrans.DueDate,
            custtable.name, country.name,rContractTable.RContractAccount,RContractPartnerType,
            PaymentStatusUserId_ZTR, LedgerjournalTrans.BankCentralBankPurposeText, custtrans.CreatedBy, CodeId_ZTR,
            LedgerjournalTrans.txt, SubjectOfAgreement_ZTR, custtrans.Txt, custtrans.DocId_ZTR,
             substring(Ledgerjournaltrans.PAYMPURPOSE_ZTR,1,500),
            Ledgerjournaltable.JOURNALTYPE,
           
             --, Journaltype
        --having sum(amountcur) != 0 or sum(amountmst) != 0
  custtrans.Dimension3_, custtrans.Dimension7_
    select k,
        case when (mst < 0 or cur < 0) then 'Д' else 'К' end as pr,
        case when (mst < 0 or cur < 0) then BalSheetDt_ZTR
            when (mst > 0 or cur > 0) then BalSheetKr_ZTR
        end as bal,
        ltrim(LedgerAccount_ZTR) as LedgerAccount_ZTR,
        #saldo.Dimension,
        #saldo.accountnum,
        #saldo.name,
        n,
        name1,
        #saldo.RContractSubject,
        #saldo.RCONTRACTNUMBER,
        case when (mst < 0) then -mst else mst end as mst,
        transdate,
        #saldo.DueDate,
        case when (p1 < 0) then -p1 else p1 end as p1,
        case when (p2 < 0) then -p2 else p2 end as p2,
        case when (p3 < 0) then -p3 else p3 end as p3,
        case when (p4 < 0) then -p4 else p4 end as p4,
        case when (cur < 0) then -cur else cur end as cur,
        #saldo.currencycode,
        #saldo.CreatedBy
 , #saldo.Dimension3_, #saldo.Dimension7_
    into #saldoPr
    from #saldo
    inner join LedgerTable (nolock) on ltrim(LedgerTable.accountNum) = ltrim(LedgerAccount_ZTR) and LedgerTable.dataareaid = 'zkz'
    left join rContractTable (Nolock) on rContractTable.RContractPartnerType = #saldo.RContractPartnerType and
                      rContractTable.RContractCode        = #saldo.RContractCode        and
                      rContractTable.RContractAccount     = #saldo.RContractAccount --and RContractStatus = '1'
                      and rContractTable.dataareaid = 'zkz'
    where #saldo.CreatedBy like '%'
    order by #saldo.accountnum,#saldo.currencycode,ltrim(LedgerAccount_ZTR),transdate
o

Я добавил свой код в условие case в 3 селекте и последнем (комментарии --->3780 26.02.2013)
Но в первом случае он ругается, что не знает такого поля, хотя выбираю его из выпадающего списка, я так понимаю, что если решить проблему с первым case-ом, тогда будет отображаться то, что нужно.

[Poleax]

Цитата:

Сообщение от user_ax

Я добавил свой код в условие case в 3 селекте и последнем (комментарии --->3780 26.02.2013)

Там есть вложенный запрос на 94 строке.

        left join (select voucher, POSTINGPROFILE, offsetpostingprofile_ru, PaymentStatusUserId_ZTR,
                    LedgerjournalTrans.BankCentralBankPurposeText,LedgerjournalTrans.txt, Journaltype
					from LedgerjournalTrans (nolock)
					inner join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1
					 	and Journaltype in(0, 4,  45)  and LedgerjournalTrans.dataareaid = 'zkz'
						and LedgerjournalTrans.dataareaid = 'zkz'
						) as LedgerjournalTrans

Для прикола он назван также as LedgerjournalTrans
там как раз и не хватает поля PAYMPURPOSE_ZTR
Добавьте его во вложенный запрос . На пример:

        left join (select voucher, POSTINGPROFILE, offsetpostingprofile_ru, PaymentStatusUserId_ZTR, PAYMPURPOSE_ZTR,
                    LedgerjournalTrans.BankCentralBankPurposeText,LedgerjournalTrans.txt, Journaltype
					from LedgerjournalTrans (nolock)
					inner join LedgerjournalTable on LedgerjournalTrans.journalnum = LedgerjournalTable.journalnum and posted = 1
					 	and Journaltype in(0, 4,  45)  and LedgerjournalTrans.dataareaid = 'zkz'
						and LedgerjournalTrans.dataareaid = 'zkz'
						) as LedgerjournalTrans

и раскоментите в 26 стр. ---> 3780 26.02.2013



В 141 строке приведенного кода, нормально. Там просто выборка из таблицы JOIN на 208 строке

        left join LedgerjournalTrans (nolock) on custtrans.voucher = LedgerjournalTrans.voucher
            and LedgerjournalTrans.dataareaid = 'zkz'
            and (LedgerjournalTrans.postingprofile in
            (select custledgeraccounts.postingprofile from custledgeraccounts (nolock)
            where (accountCode = 2 or (accountcode = 1 and num = custgroup))
            and custledgeraccounts.dataareaid = 'zkz' and
            ltrim(sumaccount) = ltrim(LedgerAccount_ZTR)) or (offsetpostingprofile_ru in
            (select custledgeraccounts.postingprofile from custledgeraccounts (nolock)
            where (accountCode = 2 or (accountcode = 1 and num = custgroup))
            and custledgeraccounts.dataareaid = 'zkz'  and
            ltrim(sumaccount) = ltrim(LedgerAccount_ZTR))))

[Poleax]

Цитата:

Сообщение от user_ax

Но в первом случае он ругается, что не знает такого поля, хотя выбираю его из выпадающего списка, я так понимаю, что если решить проблему с первым case-ом, тогда будет отображаться то, что нужно.

При выборе из списка, вы выбираете из таблицы из БД. Т.е. вообщем из таблицы LedgerJournalTrans. Это редактор кода SQL выдает список всех полей таблицы, а не подзапроса.

Но у Вас там влож. запрос УРЕЗАННЫМ списком полей. При выполнении и вываливается с ошибкой. нюансы

[user_ax]

Цитата:

Сообщение от Poleax

При выборе из списка, вы выбираете из таблицы из БД. Т.е. вообщем из таблицы LedgerJournalTrans. Это редактор кода SQL выдает список всех полей таблицы, а не подзапроса.

Но у Вас там влож. запрос УРЕЗАННЫМ списком полей. При выполнении и вываливается с ошибкой. нюансы

Так вот почему вываливалось неполный перечень полей, эво как, благодарю за разъяснение