Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

convert a PLSQL to T_SQL for a function Expand / Collapse
Author
Message
Posted Thursday, May 2, 2013 3:46 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 1,792, Visits: 3,262
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


  Post Attachments 
SQL.txt (21 views, 732 bytes)
Post #1449001
Posted Friday, May 3, 2013 1:08 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:57 PM
Points: 406, Visits: 210
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



Post #1449333
Posted Friday, May 3, 2013 1:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 1,792, Visits: 3,262
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.
Post #1449341
Posted Friday, May 3, 2013 1:24 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:57 PM
Points: 406, Visits: 210
That is because oracle likes cursors


Post #1449344
Posted Friday, May 3, 2013 1:28 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 2:01 PM
Points: 20,861, Visits: 32,889
DAVNovak (5/3/2013)
That is because oracle likes cursors


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



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1449346
Posted Sunday, May 5, 2013 7:38 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 1,792, Visits: 3,262
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
Post #1449562
Posted Sunday, May 5, 2013 9:24 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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.
Post #1449567
Posted Sunday, May 5, 2013 9:39 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 1,792, Visits: 3,262
but how can I get the first record of addressID? I got duplicates.
Post #1449569
Posted Sunday, May 5, 2013 9:54 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, November 3, 2014 4:30 PM
Points: 4,574, Visits: 8,366
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?
Post #1449574
Posted Sunday, May 5, 2013 10:08 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 12:10 PM
Points: 1,792, Visits: 3,262
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.


  Post Attachments 
table script.txt (4 views, 3.15 KB)
query to use to get addressID.txt (4 views, 987 bytes)
Post #1449575
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse