Help::: Convertng Sql(T-Sql) Stored Proc To Oracle(Pl-Sql)

[mtkr]

evaraina sql(t-sql) stored proc ni oracle(pl-sql) ki convert cheyyagalaraaa.....[CODE]


CREATE PROCEDURE [dbo].[rsp_Application_Audit_Process]
@Channel Varchar(40),
@StartDate datetime
As
BEGIN
select *
from (
select
'Application Not Received from Siebel' as Problem,
aa.Channel,
aa.HIC,
se.medicare_no,
aa.ReceiveDate,
aa.LastName,
se.last_name as SMM_LastName,
aa.FirstName,
se.first_name as SMM_FirstName,
aa.Address,
aa.City,
aa.State,
aa.Zip,
aa.Phone,
aa.SSN,
aa.DOB,
se.date_of_birth as SMM_DOB,
aa.EffectiveDate,
se.application_id,
se.subscriber_id,
se.hcc_id,
se.cip_id,
SUBSTRING(aa.filename,69,20) as filename,
aa.confirmation_number,
'' AS DCN,
aa.Application_Source,
'MA' as product_type
from
appaudit aa
left join vw_stage_enrollment_cms se on aa.confirmation_number = se.confirmation_number
where se.confirmation_number is null
and aa.channel = 'CMS'
and aa.channel IN (SELECT * FROM dbo.fn_SplitList2(@Channel,','))
and aa.Invalid = 'N'
and( (@StartDate is null) or (aa.ReceiveDateTime >= @StartDate))
and aa.HIC not in (select HIC from AppAudit aa1 inner join vw_stage_enrollment_cms se1 on aa1.HIC = se1.medicare_no where se1.medicare_no is null and aa1.channel='CMS')
)a
UNION
select
'Application Not Received from Siebel' as Problem,
aa.Channel,
aa.HIC,
se.medicare_no,
aa.ReceiveDate,
aa.LastName,
se.last_name as SMM_LastName,
aa.FirstName,
se.first_name as SMM_FirstName,
aa.Address,
aa.City,
aa.State,
aa.Zip,
aa.Phone,
aa.SSN,
aa.DOB,
se.date_of_birth as SMM_DOB,
aa.EffectiveDate,
se.application_id,
se.subscriber_id,
se.hcc_id,
se.cip_id,
SUBSTRING(aa.filename,76,50) as filename,
aa.confirmation_number,
'' AS DCN,
aa.Application_Source,
CASE
WHEN CHANNEL ='INT' THEN
CASE
WHEN CMS_CONT_NO = 'MIPPA' THEN 'MS'
ELSE 'MA'
END
END AS product_type
from
appaudit aa
left join vw_stage_enrollment_cms se on aa.confirmation_number = se.confirmation_number
where se.confirmation_number is null
and aa.channel = 'INT'
and aa.channel IN (SELECT * FROM dbo.fn_SplitList2(@Channel,','))
and aa.Invalid = 'N'
and( (@StartDate is null) or (aa.ReceiveDateTime >= @StartDate))
end
[/CODE]



and the function which it uses is


[CODE]



CREATE FUNCTION [dbo].[fn_SplitList2]
(
@sList varchar(4000),
@sDelim varchar(1)
)
RETURNS @ParsedList TABLE(value varchar(4000) not null)
AS
BEGIN
DECLARE @sValue varchar(4000), @Pos int

SET @sList = RTRIM(@sList) + @sDelim
SET @Pos = CHARINDEX(@sDelim,@sList,1)
IF REPLACE(@sList,@sDelim,'') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @sValue = LTRIM(RTRIM(LEFT(@sList,@Pos - 1)))
INSERT INTO @ParsedList(value) VALUES(@sValue)
SET @sList = RIGHT(@sList, LEN(@sList) - @Pos - LEN(@sDelim) + 1)
SET @Pos = CHARINDEX(@sDelim,@sList,1)
END
END
RETURN
END

[/CODE]

[karna11]

neeke doubt antee compulsory gaa LTT cheyalsindheeee

[minnu]

[iTeachSAP]

bayya.. online converter tools untayi chudu .. they convert TSQL to PLSQL .. all thbest.. let me know if you need any more information ..

[mtkr]

[quote name='karna11' timestamp='1355161239' post='1302924953']
neeke doubt antee compulsory gaa LTT cheyalsindheeee
[/quote]
vammo antha cinema led mama ikkada.... eado pilla P bacha developer ganni...
sql aithe ato ito manage chesevanni..... kani oracle assal touch led..


[quote name='minnu' timestamp='1355161275' post='1302924956']

[/quote]

thnx

[mtkr]

[quote name='iTeachSAP' timestamp='1355163520' post='1302925157']
bayya.. online converter tools untayi chudu .. they convert TSQL to PLSQL .. all thbest.. let me know if you need any more information ..
[/quote]

ya konni chusa mama...
kani chalaa varaki database connections adugutunnai... n naa personal laptop lo oracle ledhu...
n off wrk stn lo instal cheyyadam risk ani aalochistunna....

inka kudarakapothe eve na laptop lo oracle instal chesukovaalii...

[iTeachSAP]

[quote name='mtkr' timestamp='1355163699' post='1302925170']
ya konni chusa mama...
kani chalaa varaki database connections adugutunnai... n naa personal laptop lo oracle ledhu...
n off wrk stn lo instal cheyyadam risk ani aalochistunna....

inka kudarakapothe eve na laptop lo oracle instal chesukovaalii...
[/quote]


no need re.. nuvvu code isthe adhi convert chesthundhi .. ledhante nenu manual gaa cheyagalanu .. but i need time mama.. running with office work ..

[Ranga_The_Donga]

ltt..

[mtkr]

[quote name='iTeachSAP' timestamp='1355163779' post='1302925180']


no need re.. nuvvu code isthe adhi convert chesthundhi .. ledhante nenu manual gaa cheyagalanu .. but i need time mama.. running with office work ..
[/quote]
emo mama nen chusinavvani alane unnai...neeku veelainappudu oka sari aa link post chei...
n if possible eve r repu cheyyagalavaa??

[quote name='Jeedeelu' timestamp='1355163818' post='1302925184']
ltt..
[/quote]
thnx jeedi

[mtkr]

google lo vethukuthe aaa split function ki dorikindhiii...


CREATE TYPE tbl_array AS table OF VARCHAR2(2000);

[CODE]
CREATE OR REPLACE FUNCTION fn_SPLITLIST(I_STRING IN VARCHAR2, I_DELIM IN VARCHAR2)
RETURN TBL_ARRAY PIPELINED PARALLEL_ENABLE
AS
O_VALUE NUMBER ;
O_POS NUMBER ;
O_STRING VARCHAR2(2000);
O_START NUMBER := 0;
O_END NUMBER := 0;
BEGIN
O_VALUE := LENGTH(I_STRING) - LENGTH(REPLACE(I_STRING,I_DELIM,''));
FOR O_POS IN 1..O_VALUE LOOP
O_END := INSTR(I_STRING,I_DELIM,1, O_POS);
O_STRING := SUBSTR (I_STRING, O_START + 1 , O_END - O_START - 1);
O_START := O_END ;
PIPE ROW(TO_CHAR(O_STRING));
END LOOP;
O_STRING := SUBSTR (I_STRING, - (LENGTH(I_STRING) - O_END));
PIPE ROW(TRIM(O_STRING));
RETURN;
END FN_SPLITLIST;

[/CODE]

SELECT * FROM TABLE(fn_splitLIST('ONE,TWO,THREE',','));

[cherlapalli_jailer]

lot of tools vunnayi
pl try on google

if not i will check in the evening i sis from Pl/SQL to T-SQL

[cherlapalli_jailer]

lot of tools vunnayi
pl try on google

if not i will check in the evening i sis from Pl/SQL to T-SQL

[mtkr]

[quote name='cherlapalli_jailer' timestamp='1355164232' post='1302925233']
lot of tools vunnayi
pl try on google

if not i will check in the evening i sis from Pl/SQL to T-SQL

[quote name='mtkr' timestamp='1355163699' post='1302925170']
ya konni chusa mama...
kani chalaa varaki database connections adugutunnai... n naa personal laptop lo oracle ledhu...
n off wrk stn lo instal cheyyadam risk ani aalochistunna....

inka kudarakapothe eve na laptop lo oracle instal chesukovaalii...
[/quote]


[/quote]



neeek telisina/chusina links post chei mama..thnx

[iTeachSAP]

[quote name='mtkr' timestamp='1355164021' post='1302925204']
emo mama nen chusinavvani alane unnai...neeku veelainappudu oka sari aa link post chei...
n if possible eve r repu cheyyagalavaa??


thnx jeedi
[/quote]


sure.. evening ala .. koncham sepu kurchoni rastha PLSQL ... thx..

[mtkr]

[quote name='iTeachSAP' timestamp='1355164421' post='1302925253']


sure.. evening ala .. koncham sepu kurchoni rastha PLSQL ... thx..
[/quote]
thnx a lot mama... ltt chesthu untaaa