Delete the data

[jpraveen]

Hi
can i write the Delete SQL directly in ODBC stage
my query is

DELETE FROM imp_ProviderFacility FROM imp_ProviderFacility
inner join
( SELECT DISTINCT mosg.StateCode, mosg.PCPGroup, pf.PRVDR_FAC, rtrim(pf.PRVDR_FAC_LONG_NAME) 'PRVDR_FAC_LONG_NAME'

FROM
mo_SpecialistGroups AS mosg
INNER JOIN HCP.dbo.PROVIDER_FACILITY AS pf ON mosg.PCPGroup = pf.PRVDR_GRP

WHERE
pf.END_EFF_DATE Is Null) A
On A.StateCode=imp_ProviderFacility.StateCode
and A.PRVDR_FAC_LONG_NAME=imp_ProviderFacility.PRVDR_FAC_LONG_NAME
and A.PCPGroup =imp_ProviderFacility.PRVDR_GRP
and A.PRVDR_FAC=imp_ProviderFacility.PRVDR_FAC.

thers is INNER JOIN in it.

i am taking the Source and Target Same Table(imp_ProviderFacility)
and i had written this SQL in Upsert mode DELETE ONLY.
am i doing right ot not?

Thanks,
Praveen

[ray.wurlod]

INNER JOIN is perfectly OK, but you need at least one parameter marker so that a value can be passed from DataStage to trigger the SQL.

[jpraveen]

Hi
can you tell me briefly what is menat by parameter marker,for the below query what are the changes i should made?
i am taking all the columns from source table and just i am writing the Delete statement,i had no idea on parameter marker.can anyone expalain on this.

Thanks
Praveen