SQL queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL queries

Author

Topic

patelakush
Starting Member

6 Posts

Posted - 2011-09-08 : 09:54:43

I am suing an open query and want to join with the existing 2 queries. How can I do that. I do not want to use temp table

USE AF_Data
declare @SQLString varchar(max)
declare @SQLOpenQuery varchar(max)
declare @EOM_Date datetime

SET @EOM_Date = (SELECT rpt_dt_date FROM tblRpt_Dt)

SET @SQLOpenQuery = 'SELECT CLCL_ID, GRGR_CK, SBSB_CK, SGSG_CK, SBSB_ID, CLCL_INPUT_DT FROM FACETS.NCAV_CLCL_BASE
INNER JOIN FACETS.NCAV_SBSB_BASE ON CLCL.GRGR_CK = SBSB.GRGR_CK and CLCL.SBSB_CK = SBSB.SBSB_CK
WHERE
AND CLCL_INPUT_DT <= TO_DATE(''' + CONVERT(VARCHAR(30),2011-03-01,120) + ''',''YYYY-MM-DD HH24:MI:SS'')
AND CLCL_INPUT_DT >= TO_DATE(''' + CONVERT(VARCHAR(30),2011-08-31,120) + ''',''YYYY-MM-DD HH24:MI:SS'')'

PRINT @SQLOpenQuery

SET @SQLString = N'SELECT CLCL_ID, SBSB_ID, CLCL_INPUT_DT FROM OPENQUERY(FACETSPRODRPTW, ''' + REPLACE(@SQLOpenQuery,'''','''''') + ''')'

EXEC (@SQLString)

---------------------------------------------------------------

----First run the below query which convert CLCL_LOW_SVC_DT to report and stores in a Temop table----------

SELECT
DEV_ARP.dbo.wrkClaims.clcl_low_svc_dt,CAST(100*YEAR(clcl_low_svc_dt) + month(clcl_low_svc_dt) as CHAR(6))as RTP,
DEV_ARP.dbo.wrkClaims.CLCL_ID,
DEV_ARP.dbo.wrkClaims.rpt_dt,
DEV_ARP.dbo.wrkClaims.GRGR_ID,
DEV_ARP.dbo.wrkClaims.PROD_4_JUR,
DEV_ARP.dbo.wrkClaims.PROD_5_Leg_Ent,
DEV_ARP.dbo.wrkClaims.CLM_AGE,
DEV_ARP.dbo.wrkClaims.Source_Data,
DEV_ARP.dbo.wrkClaims.PDPD_ID,
DEV_ARP.dbo.wrkClaims.SBSB_ID,
DEV_ARP.dbo.wrkClaims.SGSG_ID,
DEV_ARP.dbo.wrkClaims.CLCL_RECD_DT,
DEV_ARP.dbo.wrkClaims.load_dt,
SUM(dbo.wrkClaims.CLCL_TOT_CHG)as Total_charge
into ##claim
FROM
DEV_ARP.dbo.wrkClaims
GROUP BY
DEV_ARP.dbo.wrkClaims.clcl_low_svc_dt,CAST(100*YEAR(clcl_low_svc_dt) + month(clcl_low_svc_dt) as CHAR(6)),
DEV_ARP.dbo.wrkClaims.CLCL_ID,
DEV_ARP.dbo.wrkClaims.GRGR_ID,
DEV_ARP.dbo.wrkClaims.RPT_DT,
DEV_ARP.dbo.wrkClaims.PROD_4_JUR,
DEV_ARP.dbo.wrkClaims.PROD_5_Leg_Ent,
DEV_ARP.dbo.wrkClaims.CLM_AGE,
DEV_ARP.dbo.wrkClaims.Source_Data,
DEV_ARP.dbo.wrkClaims.SBSB_ID,
DEV_ARP.dbo.wrkClaims.PDPD_ID,
DEV_ARP.dbo.wrkClaims.SGSG_ID,
DEV_ARP.dbo.wrkClaims.CLCL_RECD_DT,
DEV_ARP.dbo.wrkClaims.load_dt
-----------------------------------------------------------------------------------
-------Once the conversion of CLCL_LOW_SVC_DT been done run the below query which put the data in the table tblFacets_Pended_Claims------

INSERT INTO tblFacets_Pended_Claims(mkt_segment,cja_cd,risk_type,CLCL_ID,PROD_4_JUR,
prod_5_leg_ent,CLM_AGE,CLCL_LOW_SVS_DT,CLCL_TOT_CHG,GRGR_ID,SGSG_ID,PDPD_ID,CLCL_RECD_DT,Source_Data,load_dt,RPT_DT)

SELECT
mkt_segment,cja_cd,risk_type,
##claim.CLCL_ID,
##claim.PROD_4_JUR,
CASE
WHEN ##claim.prod_5_leg_ent IS NULL THEN med_rpt_org_cd
ELSE ##claim.prod_5_leg_ent
END as org_cd,
##claim.CLM_AGE,
##claim.clcl_low_svc_dt,
##claim.Total_Charge,
##claim.GRGR_ID,
##claim.SGSG_ID,
##claim.PDPD_ID,
##claim.CLCL_RECD_DT,
##claim.Source_Data,
##claim.load_dt,
##claim.RPT_DT
FROM
##claim left JOIN
(SELECT enr.rpt_dt,Enr.subscriber_id, grp_id,enr.mkt_segment,enr.cja_cd,ref.risk_type,Enr.med_coverage_type,Enr.med_rpt_org_cd
FROM AF_Data.dbo.tblFacets45_Member_Enr as Enr INNER JOIN AF_Data.dbo.XREF_Market_Segment as Ref
ON Enr.grp_type=Ref.grp_type
AND Enr.af_segment_size=Ref.af_segment_size
AND Enr.med_pln_fund_arrangement=fund_arrangement
WHERE enr.mbr_suffix=0) as temp
ON ##claim.sbsb_id=temp.subscriber_id
AND ##claim.RPT_DT=temp.rpt_dt
AND ##claim.grgr_id=temp.grp_id

Kristen
Test

22859 Posts

Posted - 2011-09-08 : 10:12:51

"I do not want to use temp table"

Some reason why not?

We always use ##TEMP tables for the results from OPENQUERY before we join to anything else, because in our experience the performance can be terrible otherwise.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources