convert a PLSQL to T_SQL for a function

Question

Post reply

convert a PLSQL to T_SQL for a function

sqlfriend

SSC Guru

Points: 52781
More actions
May 2, 2013 at 3:46 pm

#277073

I would like to convert a function from PL-SQL to T_SQL, not sure about the syntax,
Any one can help?, code are in attachment

Viewing 11 posts - 1 through 10 (of 10 total)

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

DAVNovak Hall of Fame Points: 3120 More actions · Answer 1

Try:

Create Function Get_XY_Coordinates (@i_Pupil_Number int)

Returns int

Begin

Declare @iXYCoord int

SELECT @iXYCoord = sab.address_id

From sps_address_bridge sab inner join sps_stud_assignment_address saa on sab.street_number = saa.street_number

And sab.full_street_name = saa.street_name

And sab.postal_code = saa.postal_code

Where saa.pupil_number = i_pupil_number

Order by CASE WHEN sab.modifier IS NULL THEN '0' ELSE 'Z' END, sab.x_coordinate, sab.y_coordinate

Return @iXYCoord

END

sqlfriend SSC Guru Points: 52781 More actions · Answer 2

Thanks, much, it is exactly like what I wrote too.

It looks like we don't need the cursor. Not sure why PLSQL uses that.

DAVNovak Hall of Fame Points: 3120 More actions · Answer 3

DAVNovak

Hall of Fame

Points: 3120

May 3, 2013 at 1:24 pm

#1612328

That is because oracle likes cursors

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

DAVNovak (5/3/2013)
That is because oracle likes cursors

Done right in Oracle it doesn't need cursors either.

sqlfriend SSC Guru Points: 52781 More actions · Answer 5

DAVNovak (5/3/2013)
Try:
Create Function Get_XY_Coordinates (@i_Pupil_Number int)
Returns int
Begin
Declare @iXYCoord int
SELECT @iXYCoord = sab.address_id
From sps_address_bridge sab inner join sps_stud_assignment_address saa on sab.street_number = saa.street_number
And sab.full_street_name = saa.street_name
And sab.postal_code = saa.postal_code
Where saa.pupil_number = i_pupil_number
Order by CASE WHEN sab.Housemodifier IS NULL THEN '0' ELSE 'Z' END, sab.x_coordinate, sab.y_coordinate
Return @iXYCoord
END

The above query runs very slow, i think the reason is in the order by there is the case statement,

can anyone help make this query more efficient, but still get the same result?

Thanks

Sergiy SSC Guru Points: 110208 More actions · Answer 6

sqlfriends (5/5/2013)
The above query runs very slow, i think the reason is in the order by there is the case statement,
can anyone help make this query more efficient, but still get the same result?
Thanks

You're gonna need provide DDL statements for the tables (including all indexes) in order to get any advice on that.

Actual query you run (how do you use the function) together with the actual execution plan would also help a lot.

But for the beginning I'd suggest to convert the function to a view:

Create VIEW Pupil_XY_Coordinates

AS

SELECT saa.pupil_number, sab.address_id,

CASE WHEN sab.Housemodifier IS NULL THEN '0' ELSE 'Z' END PrecedenceOrder, sab.x_coordinate, sab.y_coordinate -- to be used for ordering in outside queries

From sps_address_bridge sab

inner join sps_stud_assignment_address saa on sab.street_number = saa.street_number

And sab.full_street_name = saa.street_name And sab.postal_code = saa.postal_code

That would allow you to select coordinates for many pupils at once without compromising performance, and also search for all pupils within some coordinates range.

_____________
Code for TallyGenerator

sqlfriend SSC Guru Points: 52781 More actions · Answer 7

but how can I get the first record of addressID? I got duplicates.

Sergiy SSC Guru Points: 110208 More actions · Answer 8

sqlfriends (5/5/2013)
but how can I get the first record of addressID? I got duplicates.

Can you define "first record"?

And what do those duplicates mean - same pupil lives in multiple addresses at the same time?

_____________
Code for TallyGenerator

sqlfriend SSC Guru Points: 52781 More actions · Answer 9

Thanks much, I created the table script in the attachement. there are two tables, one is masterAddress table - is used for verifying address, the other is studentaddress table.

Now I think I should use function but use direct join: that is the second attachment.

I would like to join students table with master address table to get addressID, the problem is student table they don't have house modifier but master address does and some are have multiple hosue modifier, so when join with masteraddress table it will get duplicates, that is why they use those orders,

the goal is to get the first record returned by those columns ordered.

Thanks so much for taking look into this.

Sergiy SSC Guru Points: 110208 More actions · Answer 10

sqlfriends (5/5/2013)
the goal is to get the first record returned by those columns ordered.

Do you actually mean "the last record"?

If you're after the current address, than it must be the last modification recorded, not the first.

And how does that HouseModifier work?

When you are recording a change of address - what's actually happening?

P.S. The table sripts do not contain any indexing. Is that true - no indexing whatsoever?

_____________
Code for TallyGenerator