ronitreddy Posted March 15, 2011 Report Posted March 15, 2011 Can anyone help me in understanding the below mentioned stored procedure.I got to migrate this SP into Informatica. sSc_hiding2 sSc_hiding2 sSc_hiding2INSERT INTO LOD.REPORT_SUMMARY (CARR_ACRONYM, ST_CD, REPORT_DTM, CLRTP_RF, CLRCD_RF, COUNT, LAST_UPDATE_NM ,LAST_UPDATE_DTM ) SELECT COALESCE(B.FILE_ACCRNM_TXT,''), COALESCE(B.INSURED_STATE_CD,''), V_REPORT_DTM, 'RPT_COUNT', 'INSGENM', SUM(COALESCE(C,0)), 'NEDB_RPT', CURRENT TIMESTAMP FROM ( SELECT CASE WHEN FILE_ACCRNM_TXT IN (SELECT FILE_ACCRNM_TXT FROM SESSION.CARR_ACRONYM ) THEN FILE_ACCRNM_TXT ELSE '' END CARR, CASE WHEN INSURED_STATE_CD IN (SELECT DISTINCT REF_INTERNAL_CD FROM REF.ARTREF2 WHERE REF_TYPE_CD = 'STACD' ) THEN INSURED_STATE_CD ELSE '' END ST, COUNT ( * ) C FROM NED.NED_525 WHERE PATIENT_RELATION_TO_INS_CD = '01' AND INSURED_SEX_CD = 'M' GROUP BY FILE_ACCRNM_TXT, INSURED_STATE_CD ) A RIGHT OUTER JOIN (SELECT FILE_ACCRNM_TXT, INSURED_STATE_CD FROM SESSION.CARR_ST_CD) B ON A.CARR = B.FILE_ACCRNM_TXT AND A.ST = B.INSURED_STATE_CD GROUP BY COALESCE(B.FILE_ACCRNM_TXT,''), COALESCE(B.INSURED_STATE_CD,'') WITH UR;Thanks in Advance
The QUEEN Posted March 15, 2011 Report Posted March 15, 2011 :surprised-038: :surprised-038: sorry2 dnw sHa_high5ing
vaasu Posted March 15, 2011 Report Posted March 15, 2011 2-3 tables ayithe kastapadi cheppochu. inni tables unte ardham chesukodanikey chaala time paduthundi baa. sSc_hiding2 sSc_hiding2
banti321 Posted March 15, 2011 Report Posted March 15, 2011 mama maree intha open ga vesavmee office vallu emanna chuste problem emo sCo_hmmthink sCo_hmmthink
Gachibowli_Diwakar Posted March 15, 2011 Report Posted March 15, 2011 just select statement ye kadha mama sCo_hmmthink sCo_hmmthink with a case statement in itdivide-n-conquer rule follow ayyi .. parts ga split cheyi .. it shudnt take that long (probably an hour or two)
ronitreddy Posted March 15, 2011 Author Report Posted March 15, 2011 [quote author=John Cena link=topic=167832.msg2026024#msg2026024 date=1300157744]2-3 tables ayithe kastapadi cheppochu. inni tables unte ardham chesukodanikey chaala time paduthundi baa. sSc_hiding2 sSc_hiding2[/quote]baa actually SP nalugu vela lines undi...nen dantlo oka part vesa...anni similar logic..idi ardam avthe veredi ardam avthadi ani oka chinna feeling sCo_hmmthink sCo_hmmthink sCo_hmmthink
ronitreddy Posted March 15, 2011 Author Report Posted March 15, 2011 [quote author=ChittinnaraNaidu link=topic=167832.msg2026031#msg2026031 date=1300157814]mama maree intha open ga vesavmee office vallu emanna chuste problem emo sCo_hmmthink sCo_hmmthink[/quote]maa office vallaa baa..chusthe job pothadi anthe kada..ee tension ee pressure kante ade better.. :( aina office vallu ela chustharu baa.. sCo_hmmthink sCo_hmmthink
vaasu Posted March 15, 2011 Report Posted March 15, 2011 [quote author=ronitreddy link=topic=167832.msg2026084#msg2026084 date=1300158172]baa actually SP nalugu vela lines undi...nen dantlo oka part vesa...anni similar logic..idi ardam avthe veredi ardam avthadi ani oka chinna feeling sCo_hmmthink sCo_hmmthink sCo_hmmthink[/quote] sHa_high5ing sHa_high5ing sHa_high5ing sHa_high5ingSP's anni anthe untayi ley.
ronitreddy Posted March 15, 2011 Author Report Posted March 15, 2011 [quote author=Musugu_Veerudu link=topic=167832.msg2026035#msg2026035 date=1300157856] just select statement ye kadha mama sCo_hmmthink sCo_hmmthink with a case statement in itdivide-n-conquer rule follow ayyi .. parts ga split cheyi .. it shudnt take that long (probably an hour or two)[/quote]baa manam computers lo bebebee..emi ardam avvatla..evarina telsina vallu koncham explain chesthe baguntadi baa..neku telisthe koncham cheppu baa fleezzz..
Gachibowli_Diwakar Posted March 15, 2011 Report Posted March 15, 2011 [quote author=ronitreddy link=topic=167832.msg2026120#msg2026120 date=1300158403]baa manam computers lo bebebee..emi ardam avvatla..evarina telsina vallu koncham explain chesthe baguntadi baa..neku telisthe koncham cheppu baa fleezzz..[/quote]niku ekkadi dhaaka ardham aindhi .. from which part u r facing difficulty ?
ramu Posted March 15, 2011 Report Posted March 15, 2011 [quote author=ronitreddy link=topic=167832.msg2025998#msg2025998 date=1300157457]Can anyone help me in understanding the below mentioned stored procedure.I got to migrate this SP into Informatica. sSc_hiding2 sSc_hiding2 sSc_hiding2[color=red][size=12pt] INSERTING INTO LOD.REPORT_SUMMARY[/size][/color]INSERT INTO LOD.REPORT_SUMMARY (CARR_ACRONYM, ST_CD, REPORT_DTM, CLRTP_RF, CLRCD_RF, COUNT, LAST_UPDATE_NM ,LAST_UPDATE_DTM ) SELECT COALESCE(B.FILE_ACCRNM_TXT,''), COALESCE(B.INSURED_STATE_CD,''), V_REPORT_DTM, 'RPT_COUNT', 'INSGENM', SUM(COALESCE(C,0)), 'NEDB_RPT', CURRENT TIMESTAMP FROM ( SELECT CASE WHEN FILE_ACCRNM_TXT IN (SELECT FILE_ACCRNM_TXT [size=12pt][color=red]-- FILE_ACCRNM_TXT is available in the list obtained from this query (SELECT FILE_ACCRNM_TXT FROM SESSION.CARR_ACRONYM) then use that value other wise null --same with the other case statement too, number of records (count(*))[/color][/size] FROM SESSION.CARR_ACRONYM ) THEN FILE_ACCRNM_TXT ELSE '' END CARR, CASE WHEN INSURED_STATE_CD IN (SELECT DISTINCT REF_INTERNAL_CD FROM REF.ARTREF2 WHERE REF_TYPE_CD = 'STACD' ) THEN INSURED_STATE_CD ELSE '' END ST, COUNT ( * ) C FROM NED.NED_525 WHERE PATIENT_RELATION_TO_INS_CD = '01' AND INSURED_SEX_CD = 'M' GROUP BY FILE_ACCRNM_TXT, INSURED_STATE_CD ) A [size=12pt][color=red] -- ee right outer join lo rasina query lo vunna state cd appropriate accrnm_txt ki link ayidhi .. aa 2 items meeda group chesi nee table populate ayidhi...[/color][/size] RIGHT OUTER JOIN (SELECT FILE_ACCRNM_TXT, INSURED_STATE_CD FROM SESSION.CARR_ST_CD) B ON A.CARR = B.FILE_ACCRNM_TXT AND A.ST = B.INSURED_STATE_CD GROUP BY COALESCE(B.FILE_ACCRNM_TXT,''), COALESCE(B.INSURED_STATE_CD,'') WITH UR;Thanks in Advance[/quote] naku ardham ayinattu raasa.. [size=12pt][color=red]each query seperate ga execute chesi chusukunte[/color][/size] neeku inka clear ga telisidhi... idi emina help ayidhemo chudu.... sHa_high5ing sHa_high5ing sHa_high5ing
Recommended Posts