SQL Query to get data from various databases

Question

I wrote the below query to pull the data from different databases. I have created two temp tables to pull the data from two different databases and finally a select statement from the original database to join all the tables. My query is getting executed but not getting any data.(Report is blank). I tried executing the two temp tables separately. it is giving the correct data. But when I execute the whole query, the result is blank. Below is the query. Please help.

"set fmtonly off

use GODSDB

IF object_id('tempdb..#CISIS_Call_Log') IS NOT NULL DROP TABLE #CISIS_Call_Log
select * 
into #CISIS_Call_Log
from OPENQUERY (CSISDB,
'select
ccl.ContractOID,
ccl.db_insertdate,
ccl.ContractCallLogStatusIdentifier,
ccl.db_UpdateDate,
ccp.ContractCallLogPurposeOID,
ccp.ContractCallLogPurposeIdentifier,
ccp.Description
from csisdb.dbo.ContractCallLog CCL
inner join csisdb.dbo.ContractCallLogPurpose CCP on ccl.ContractCallLogPurposeIdentifier = ccp.ContractCallLogPurposeIdentifier
where JurisdictionShortIdentifier = ''ON'' 
AND  ContractCallLogStatusIdentifier IN (''DNR'', ''NR'')
')

IF object_id('tempdb..#CMS_Campaign') IS NOT NULL DROP TABLE #CMS_Campaign
select * 
into #CMS_Campaign
from OPENQUERY (BA_GBASSTOCMS, '
Select
SystemSourceIdentifier,
ContractOID,
OfferSentDate,
CampaignOfferTypeIdentifier,
CampaignContractStatusIdentifier,
CampaignContractStatusUpdateDate,
DeclineDate,
CampaignOfferOID,
CampaignOID,
CampaignStartDate,
CampaignEndDate,
Jurisdiction,
CampaignDescription
from CMS.dbo.vw_CampaignInfo
where Jurisdiction = ''ON''
and CampaignOfferTypeIdentifier = ''REN''
')

select mp.CommodityTypeIdentifier as Commodity
,c.RtlrContractIdentifier as ContractID
,cs.ContractStatusIdentifier as ContractStatus
,c.SigningDate
,cf.StartDate as FlowStartDate
,cf.EndDate as FlowEndDate
,datediff(day, getdate(), c.RenewalDate) as RemainingDays
,c.RenewalDate
,l.ContractCallLogStatusIdentifier as CallLogType
,Substring (l.Description, 1, 20) as CallPurpose
,l.db_insertDate as CallLogDate
,cms.CampaignOfferOID as OfferID
,cms.CampaignContractStatusIdentifier as OfferStatus
,cms.CampaignContractStatusUpdateDate as StatusChangeDate
,cms.DeclineDate
from Contract c
inner join contractstate cs on cs.contractoid = c.ContractOID
and cs.ContractStatusIdentifier in ('ERA', 'FLW')
and datediff(day, getdate(), c.RenewalDate) > 60
inner join SiteIdentification si on si.SiteOID = c.SiteOID
inner join MarketParticipant mp on mp.MarketParticipantOID = si.MarketParticipantOID
inner join Market m on m.MarketOID = mp.MarketOID
inner join Jurisdiction j on j.JurisdictionOID = m.JurisdictionOID
and j.CountryCode = 'CA'
and j.ProvinceOrStateCode = 'ON'
inner join ContractFlow cf on cf.ContractOID = c.ContractOID
inner join #CISIS_Call_Log l on convert(varchar(15), l.ContractOID) = c.RtlrContractIdentifier
inner join #CMS_Campaign cms on convert(varchar(15), cms.ContractOID) = c.RtlrContractIdentifier


set fmtonly on"

Because one of the join is failing. A field that you think matches, doesn't match. Eliminate each join one by one and see which one is the problem. — Nick.Mc, Dec 07 '17 at 05:46

score 0 · Answer 1 · answered Dec 07 '17 at 06:21

IF the data in each temp table is verified, then:

Try a smaller, less complex, query to test your temp tables with. Also try them using a LEFT join as well e.g.:

select
      c.RtlrContractIdentifier as ContractID
    , c.SigningDate
    , datediff(day, getdate(), c.RenewalDate) as RemainingDays
    , c.RenewalDate
    , l.ContractCallLogStatusIdentifier as CallLogType
    , Substring (l.Description, 1, 20) as CallPurpose
    , l.db_insertDate as CallLogDate
    , cms.CampaignOfferOID as OfferID
    , cms.CampaignContractStatusIdentifier as OfferStatus
    , cms.CampaignContractStatusUpdateDate as StatusChangeDate
    , cms.DeclineDate
from Contract c
LEFT join #CISIS_Call_Log l on convert(varchar(15), l.ContractOID) = c.RtlrContractIdentifier
LEFT join #CMS_Campaign cms on convert(varchar(15), cms.ContractOID) = c.RtlrContractIdentifier

Does this return data? Does it return data from both joined tables?

If neither temp table is returning data then those join conditions need to be changed.

If both temp tables do return data from that query, then try INNER joins. If that still works, then add back more joins (one at a time) until you identify the join that causes the overall fault.

Without data for every table it just isn't possible for us to pinpoint the exact reason for a NULL result. Only you can, so you need to trouble-shoot the problem one step at a time.

SQL Query to get data from various databases

1 Answers1