Big Challange

hi all

i need to write a function to replace one field from another field in strode procedure .

as a example

SELECT
profc.SURNAME + ', ' + profc.FORENAME AS ProfCarer,
SPECS.DESCRIPTION AS Speciality,
steams.STEAM_REFNO_NAME AS StaffTeam,
REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,
COUNT(REFS.REFRL_REFNO) AS NoOfOpenReferrals
FROM REFS
LEFT OUTER JOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO
LEFT OUTER JOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO
LEFT OUTER JOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO
INNER JOIN PrimaryCareTrust pct ON REFS.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

i need to replace REFS.PCT_CODE FROM GEOGAREA.PCT_CODE THIS FUNCTION NEED ONE INPUT PARAMETER CALLED REFS.REFTOPROCA_REFNO

Any Idea
Thank's

I'm a little confused.

You want to translate the REFS.PCT_CODE in order to use it in the joing on PrimaryCareTrust?

|||ya i need to replace refs.pct_code from another cord in all the strode procs|||

OK, what is the the code you want as the replacement?
Where does it come from?

Where does it go within the query?

Please provide more info.

|||the code is GEOGAREA.PCT_CODE its from GeographicArea table
it just replase that REFS.PCT_CODE
|||

This doesn't make sense, but anyway:

Code Snippet

SELECT

profc.SURNAME +', '+ profc.FORENAME AS ProfCarer,

SPECS.DESCRIPTION AS Speciality,

steams.STEAM_REFNO_NAME AS StaffTeam,

REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,

COUNT(REFS.REFRL_REFNO)AS NoOfOpenReferrals

FROM REFS

INNERJOIN GEOGAREA

ON REFS.PCT_CODE = GEOGAREA.PCT_CODE

LEFTOUTERJOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO

LEFTOUTERJOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO

LEFTOUTERJOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO

INNERJOIN PrimaryCareTrust pct ON GEOGAREA.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL

|||ya i know this mate. i have lots of procedures so what i want to do is i need create a function that i execute that it'll go and search that REFS.PCT_CODE in each and every procedures and replace it by GEOGAREA.PCT_CODE

can we do this?|||

You bet.

REFS.PCT_CODE is the input

GEOGAREA.PCT_CODE is the output

Now, how are REFS and GEOGAREA related?
How does REFS.PCT_CODE find the correct entry in GEOGAREA?

|||

Spend a little bit time to change your quires(even it is on multiple stored procedures).

If you use function it may decrease the performance.

|||PCT_CODE STANDS geographic area code this are same but refs.pct_code is going to be change thats why.|||

Niranga,

Please provide some details and specifics.

What is the current DDL/schema?

How is it changing?

Provide some sample table data and your expected results.