Cursor within sql query

Question

Cursor within sql query

sunsilk10

Old Hand

Points: 336
More actions
July 15, 2020 at 3:13 pm

#3770242

Hi could anyone please shed some light how this sql works. i.e. how the cursor is being used in the following:
select * from
table (ts_summary ( cursor (
select sd.USRN ,
sd.special_desig_source_ha,
sd.street_special_desig_num ,
sd.STREET_SPECIAL_DESIG_CODE,
sd.Street_Descriptor ,
sd.special_desig_description ,
sd.special_desig_location_text ,
sd.special_start_x ,
sd.special_start_y ,
sd.special_end_x ,
sd.special_end_y
from TNSG_streets_special_desig sd
where sd.STREET_SPECIAL_DESIG_CODE = 2
order by sd.usrn

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

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

Are you sure that is SQL Server code and not Oracle or some other RDBMS? This is a SQL Server site, so you're likely to get better help somewhere else.

John

frederico_fonseca SSCoach Points: 16017 More actions · Answer 2

that is Oracle SQL - Pipeline or Parallel Table Function - see https://docs.oracle.com/cd/B19306_01/appdev.102/b14289/dcitblfns.htm

and if you crosspost to other forums at least have the courtesy of posting the link to the other forum - this was also posted on stackoverflow.

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 3

p.s. SQL <> SQL so always identify which RDBMS engine you're using.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 4

Just to be sure, are you trying to convert Oracle to SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sunsilk10 Old Hand Points: 336 More actions · Answer 5

Yes I was intending to convert to sqlserver.

I managed to figure this out. Ts_summary was a custom function. All sorted.

Thanks.

frederico_fonseca SSCoach Points: 16017 More actions · Answer 6

for other readers benefit it may be worthwhile posting the T-SQL version of the above code.

I'm sure many will have some difficulties with similar constructs

Jeff Moden SSC Guru Points: 1003864 More actions · Answer 7

sunsilk10 wrote:

Yes I was intending to convert to sqlserver.
I managed to figure this out. Ts_summary was a custom function. All sorted.
Thanks.

Awesome... can you post the code so we can do a comparison and maybe learn something new? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)