converting oracle code to sql server code

Question

converting oracle code to sql server code

leodec

SSC Enthusiast

Points: 132
More actions
October 8, 2010 at 10:20 am

#239505

Hi guys
Please help me to convert below mentioned oracle query into sql server. want to retrieve all those records where
new offers should not be made before the cancel date of the last offer. In data provides off_order 1 is the latest record
SELECT *
FROM ( SELECT tab1.key
,tab1.dt_admin cur_offer_dt
,tab1.dt_resp cur_resp_dt
,LAG( tab1.RECORD_TYPE, 1 ) OVER (PARTITION BY tab1.key ORDER BY tab1.off_order DESC) prev_offer_exist
,LAG( tab1.non_att_dt, 1 ) OVER (PARTITION BY tab1.key ORDER BY tab1.off_order DESC ) prev_non_att
,LAG( tab1.dt_resp, 1 ) OVER (PARTITION BY tab1.key ORDER BY tab1.off_order DESC) prev_resp_dt
,LAG( tab1.dt_admin, 1 ) OVER (PARTITION BY tab1.key ORDER BY tab1.off_order DESC ) prev_offer_dt
FROM tab1
WHERE FILE_ID = p_file_id)
WHERE prev_offer_exist IS NOT NULL
AND prev_non_att IS NOT NULL
AND (prev_resp_dt <> cur_resp_dt OR prev_offer_dt <> cur_of_dt)
AND is_valid_date(prev_non_att,'YYYY-MM-DD') > is_valid_date(cur_of_dt,'YYYY-MM-DD')
Data given below
keyoff_order dt_admindt_respnon_att_dt
1656522010-01-282010-01-282010-012-04
1656512010-01-292010-02-04null
16585 2 2010-05-05 2010-06-07 2010-06-07
16585 2 2010-06-04 2010-06-04 null
i have tried one solution
with tt (key, dt_admin,dt_resp seq)
as (select tab1.key, tab2.dt_admin, tab1.dt_resp, row_number() over (order by off_order desc) as seq from tab1, tab2 where tab1.key =tab2.key)
select distinct l.* from tt l, tt n where l.key = n.key and and l.seq -1 = n.seq
but this query is returning different rows after every run... may be because of some seq error.... and other thing it is returning the latest records as well
let me know if you need more infor
thanks & regards

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

leo it looks like you have two custom functions;

one is an aggregate function named LAG, and the other is a function which returns a date based on some business rules: is_valid_date

your query would work in TSQL if those two functions exist, and the also the keyword "key" is delimited with brackets:

SELECT *

FROM ( SELECT tab1.

,tab1.dt_admin cur_offer_dt

,tab1.dt_resp cur_resp_dt

,LAG( tab1.RECORD_TYPE, 1 ) OVER (PARTITION BY tab1. ORDER BY tab1.off_order DESC) prev_offer_exist

,LAG( tab1.non_att_dt, 1 ) OVER (PARTITION BY tab1. ORDER BY tab1.off_order DESC ) prev_non_att

,LAG( tab1.dt_resp, 1 ) OVER (PARTITION BY tab1. ORDER BY tab1.off_order DESC) prev_resp_dt

,LAG( tab1.dt_admin, 1 ) OVER (PARTITION BY tab1. ORDER BY tab1.off_order DESC ) prev_offer_dt

FROM tab1

WHERE FILE_ID = p_file_id) X

WHERE prev_offer_exist IS NOT NULL

AND prev_non_att IS NOT NULL

AND (prev_resp_dt <> cur_resp_dt OR prev_offer_dt <> cur_of_dt)

AND is_valid_date(prev_non_att,'YYYY-MM-DD') > is_valid_date(cur_of_dt,'YYYY-MM-DD')

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

LAG() is actually a PL/SQL analytic function.

I agree IS_VALID_DATE() is a custom function usually coded like:

create or replace FUNCTION IS_VALID_DATE(pStr VARCHAR2, pFmt VARCHAR2) RETURN NUMBER

IS

vRet NUMBER := 0;

vDate DATE := NULL;

BEGIN

IF pStr IS NOT NULL THEN

BEGIN

vDate := TO_DATE(pStr, pFmt);

--

IF vDate IS NOT NULL THEN

vRet := 1;

END IF;

EXCEPTION

WHEN OTHERS THEN

NULL;

END;

END IF;

--

RETURN vRet;

END IS_VALID_DATE;

/

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

leodec SSC Enthusiast Points: 132 More actions · Answer 3

hi

thanks for the reply but if i am not wrong then Sql server don't have lag function could you please help me how to achieve same in sql server if possible with simple select query.

with kind regards

leodec SSC Enthusiast Points: 132 More actions · Answer 4

hi guys

could you please help me how to retrieve previous records in sql server as there is no lag function in sql server

thanks & Regards

grahamc SSCertifiable Points: 6762 More actions · Answer 5

What does the LAG function in Oracle do? There might be an equivalent SQL function, with a different name.

leodec SSC Enthusiast Points: 132 More actions · Answer 6

hi

lag function in oracle basically brings the record from the previous row and in sql server we don't have such function to do that ... so in that case i have tried using ROWNUMBER() function with self join which is giving strange result after every run... sometimes it brings 4 records sometimes it brings 32 records or so on... could you please tell me if this is the right approach to do that then what is wrong with the sql..

with tt (key, dt_admin,dt_resp seq)

as (select tab1.key, tab2.dt_admin, tab1.dt_resp, row_number() over (order by off_order desc) as seq from tab1, tab2 where tab1.key =tab2.key)

select distinct l.* from tt l, tt n where l.key = n.key and and l.seq -1 = n.seq

thanks & regards