VBA/SQL, Недопустимое использование Nulls
Я использую пользовательскую форму в excel для запуска некоторых внутренних запросов между моими электронными таблицами. Однако я получаю Invalid Use of Null и мне известно о синтаксисе nz null (SQL MS Access - недопустимое использование Null), однако мои запросы могут быть довольно большими, и мне интересно, есть ли что-нибудь, что я могу добавить к моему коду vba, чтобы Разрешить null.
вот мой запрос,
select [EUC_ID],
RiskReportProductType,
[Source Currency of the CUSIP that is denominated in],
[EnterpriseCurrency],[EnterpriseSector],
[RatingCombo],
iif(RiskReportProductType in ('Equity|Private','Equity|Public','Seed Capital','Pref Shares','GLadjustment','OtherAdjustment'),'n/a',
switch(
[EnterpriseRating] = 'No Rating','BBB',
[EnterpriseRating] <> 'No Rating',[EnterpriseRating],
[EnterpriseRating] is null,'Unmapped - no rating mapping for ' & [RatingCombo],
)) as [EnterpriseRating],
[Issuer],
iif(RiskReportProductType in ('Bond|Corporate','Hybrid','CMBS|CAD','MBS|CAD','Loan Substitute securities'),
switch(
[EnterpriseCurrency] is null, 'Unmapped - no currency mapping for ' & [Source Currency of the CUSIP that is denominated in],
[EnterpriseRating] is null,'Unmapped - no rating mapping for ' & [RatingCombo],
[EnterpriseSector] is null,'Unmapped - no sector mapping for ' & [BbgSector],
[EnterpriseCurrency] is not null and [EnterpriseRating] is not null and [EnterpriseSector] is not null,'CORPZERO'&'|'&[EnterpriseCurrency]&'|'&[EnterpriseRating]&'|'&[EnterpriseSector],
true,'Unmapped'
),'n/a') as [CorporateBond_DiscountCurve],
iif(RiskReportProductType in ('Bond|Sovereign'),
switch(
[Risk free curve] is null, 'Unmapped - no risk free curve for ' & [Source Currency of the CUSIP that is denominated in] & '|' & [EnterpriseCurrency],
[Risk free curve] is not null, [Risk free curve],
true, 'Unmapped'
),'n/a') as [SovereignBond_DiscountCurve],
iif(RiskReportProductType in ('Bond|Agency','Bond|Special Purpose Entity'),
switch(
[EnterpriseRating] is null,'Unmapped - no rating mapping for ' & [RatingCombo],
[EnterpriseRating] is not null, 'CORPZERO'&'|'&[EnterpriseCurrency]&'|'&[EnterpriseRating]&'|'&'AGN',
true,'Unmapped'
),'n/a') as [AgencyBond_DiscountCurve],
iif(RiskReportProductType in ('Bond|Regional Authority'),
switch(
[Province] is null,'Unmapped - no issuer mapping for ' & [Issuer],
[EnterpriseCurrency] is null, 'Unmapped - no currency mapping for ' & [Source Currency of the CUSIP that is denominated in],
[EnterpriseRating] is null, 'Unmapped - no rating mapping for ' & [RatingCombo],
[Province]='AGN', 'CORPZERO'&'|'&[EnterpriseCurrency]&'|'&[EnterpriseRating]&'|'&'AGN',
[EnterpriseCurrency] in ('USD','CAD'),'PROVZERO'&'|'&[EnterpriseCurrency]&'|'&[Province]&'|'&[EnterpriseRating],
true,'Unmapped'
),'n/a') as [RegionalAuthorityBond_DiscountCurve],
iif(RiskReportProductType in ('Bond|Supra'),
switch(
[SupraCurve] is null,'Unmapped - no supra mapping for ' & [Issuer],
[EnterpriseRating] is null, 'Unmapped - no rating mapping for ' & [RatingCombo],
[SupraCurve] is not null and [EnterpriseRating] is not null, [SupraCurve]&'|'&iif([EnterpriseRating]='No Rating','B',[EnterpriseRating])&'|'&'SUP',
true,'Unmapped'
),'n/a') as [SupraBond_DiscountCurve],
iif(RiskReportProductType in ('Bond|Muni'),
switch(
[MuniRating] is null, 'Unmapped - no muni rating mapping for ' & [EnterpriseRating],
[MuniRating] is not null, 'MUNIZERO'&'|'&'USD'&'|'&[MuniRating]&'|'&'MID',
true, 'UnMapped'
),'n/a') as [MuniBond_DiscountCurve],
iif(RiskReportProductType in ('ABS','ARS','CLO','CMBS|USD','MBS|USD','MBS|GBP','MBS|CMO|Agency|USD','MBS|CMO|USD','MBS|Agency|USD'),
'SWAPZERO'&'|'&[EnterpriseCurrency]&'|'&'|MID','n/a') as [LIBORPRODUCTS_DiscountCurve],
switch(
[RiskSource] = 'No risk','n/a',
[LeafNodeName] is null,'Unmapped - no leaf node for transit '& [Transit # (not rollup)],
[LeafNodeName] is not null, [LeafNodeName]) as [EnterpriseNode],
PolyMaturity,
null as Combined_DiscountCurve
from (
select distinct
master.[EUC_ID],map_rate.[EnterpriseRating],
master.[Moodys External Rating# Input NR if the security is not rated o]&'|'&
master.[S&P External Rating# Input NR if the security is not rated or W]&'|'&
master.[Fitch External Rating Input NR if the security is not rated or ] as RatingCombo,map_poly.RiskReportProductType,map_poly.PolyMaturity,
map_risksource.RiskSource,map_curr.[EnterpriseCurrency],map_prod.[EnterpriseSector],
map_supracurr.[SupraCurve],map_munirate.[MuniRating],map_rfcurve.[Risk free curve],
map_issuer.[Province],map_transit.[Transit],map_transit.LeafNodeName,master.[Source Currency of the CUSIP that is denominated in],
map_prod.[BbgSector],master.[Issuer],master.[Transit # (not rollup)]
from
((((((((((
(select *
from [masterbankingbookdb$]) as master
left join
(select [FinanceDatabaseRatingCombo],[EnterpriseRating] from [mappings$]
where [EnterpriseRating] is not null
) as map_rate
on map_rate.[FinanceDatabaseRatingCombo] =
(master.[Moodys External Rating# Input NR if the security is not rated o]&'|'&
master.[S&P External Rating# Input NR if the security is not rated or W]&'|'&
master.[Fitch External Rating Input NR if the security is not rated or ]
))
left join
(select [poly_EUC_ID],RiskReportProductType,PolyMaturity from [mapped_polytypes$]
where [poly_EUC_ID] is not null
) as map_poly
on map_poly.poly_euc_id=master.euc_id)
left join
(select [EUC_ID],risksource from [mapped_risksource$]
where [EUC_ID] is not null
) as map_risksource
on map_risksource.euc_id=master.euc_id)
left join
(select [FinanceCurrency],[EnterpriseCurrency] from [mappings$] where [FinanceCurrency] is not null
) as map_curr
on map_curr.FinanceCurrency=master.[Source Currency of the CUSIP that is denominated in])
left join
(select [euc_id],[BbgType_IndustrySector] from [mapped_product$] where [euc_id] is not null
) as bbg_prod
on bbg_prod.euc_id=master.euc_id)
left join
(select [BbgSector],[EnterpriseSector] from [mappings$] where [BbgSector] is not null
) as map_prod on map_prod.[BbgSector]=iif(bbg_prod.[BbgType_IndustrySector] is null,'All',bbg_prod.[BbgType_IndustrySector])
)
left join
(select [SupraCurrency],[SupraCurve] from [mappings$] where [SupraCurrency] is not null
) as map_supracurr
on map_supracurr.SupraCurrency=master.[Source Currency of the CUSIP that is denominated in])
left join
(select [CorpRating],[MuniRating] from [mappings$] where [CorpRating] is not null
) as map_munirate
on map_munirate.[CorpRating]=map_rate.[EnterpriseRating])
left join
(select [Currency],[Risk free curve] from [mappings$] where [Currency] is not null
) as map_rfcurve
on cstr(map_rfcurve.[Currency])=cstr(map_curr.[EnterpriseCurrency]))
left join
(select [Issuer],[Province] from [mappings$] where [Issuer] is not null
) as map_issuer
on iif(map_issuer.[Issuer] is not null,map_issuer.[Issuer],'All')=iif(master.[Issuer] is null,'All',master.[Issuer]))
left join
(select [Transit],[LeafNodeName] from [mappings$] where [Transit] is not null
) as map_transit
on cstr(map_transit.[Transit])=cstr(master.[Transit # (not rollup)])
) as X
Ответы
Ответ 1
Функции CStr, такие как CStr, очень подвержены запуску Недействительное использование null. Не должно быть необходимости использовать CStr в объединениях, так как это приведет к получению равенства.
Удалите CStr в следующих местах:
on cstr(map_transit.[Transit])=cstr(master.[Transit # (not rollup)])
а также
on cstr(map_rfcurve.[Currency])=cstr(map_curr.[EnterpriseCurrency]))
Обратите внимание, что, в то время как вы фильтровали с помощью Is Not Null в этих подзапросах, оптимизатор может сначала выполнить соединение, а затем выбросить Invalid Use Of Null перед фильтрацией. См. Этот ответ для примера, где оптимизатор сделал что-то подобное.
Кроме того, если вы действительно хотите использовать строку, вы можете использовать следующее:
on (map_transit.[Transit] & '' = master.[Transit # (not rollup)] & '')
Добавление пустой строки к чему-то приводит свое значение к строке, даже если она Null, не выбрасывая ошибку.
