SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


convert a PLSQL to T_SQL for a function


convert a PLSQL to T_SQL for a function

Author
Message
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3893 Visits: 4025
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
Attachments
SQL.txt (25 views, 732 bytes)
DAVNovak
DAVNovak
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 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



sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3893 Visits: 4025
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
DAVNovak
SSC-Addicted
SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)SSC-Addicted (474 reputation)

Group: General Forum Members
Points: 474 Visits: 210
That is because oracle likes cursors



Lynn Pettis
Lynn Pettis
SSC-Dedicated
SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)SSC-Dedicated (39K reputation)

Group: General Forum Members
Points: 39912 Visits: 38564
DAVNovak (5/3/2013)
That is because oracle likes cursors


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

Cool
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)
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3893 Visits: 4025
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
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10524 Visits: 11964
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.
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3893 Visits: 4025
but how can I get the first record of addressID? I got duplicates.
Sergiy
Sergiy
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10524 Visits: 11964
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?
sqlfriends
sqlfriends
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3893 Visits: 4025
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.
Attachments
table script.txt (14 views, 3.00 KB)
query to use to get addressID.txt (21 views, 987 bytes)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search