return 1 row only with union all

Question

Post reply

return 1 row only with union all

dave platt-208496

SSC Eights!

Points: 862
More actions
October 30, 2006 at 10:46 am

#173277

can i limit to one row the number of rows returned when i must use several union all between select statements? i am constrained to using either a function or stored procedure. i understand set rowcount cannot be used in a UDF. is it possible to use top 1 with the results from the stored procedure? if so could you suggest the syntax for me - thanks for your help

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

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

Ram Ram Ten Centuries Points: 1079 More actions · Answer 1

I do not know this is what u r looking for

SELECT TOP 1 *

FROM (

SELECT 'A' AS COL1, 'B' AS COL2 UNION

SELECT 'AA' AS COL1, 'BB' AS COL2 UNION

SELECT 'AAA' AS COL1, 'BBB' AS COL2 UNION

SELECT 'AAAA' AS COL1, 'BBBB' AS COL2 UNION

SELECT NULL AS COL1, 'B' AS COL2 UNION

SELECT 'A' AS COL1, NULL AS COL2

&nbsp AS A

John Rowan SSC Guru Points: 56440 More actions · Answer 2

I would recommend using an ORDER BY clause if you want your query to return the same TOP 1 each time it is run. The only way to guarantee your results is to order them yourself.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

dave platt-208496 SSC Eights! Points: 862 More actions · Answer 3

thank you both for your kind advice. i tried the select top 1 from (select .. union all select ...) as A

looks good to me - did not know i could use several select statements after the from clause. works fine in query analyser but i get following error when placed in a UDF

Server: Msg 444, Level 16, State 2, Procedure fnLineAddrType, Line 25

Select statements included within a function cannot return data to a client.

i have not had time to troubleshoot the message - i'll try a SP and return

regards

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

It should look something like this :

CREATE FUNCTION dbo.FnName (@param AS int)

RETURNS TABLE

AS

RETURN SELECT Col1, Col2 FROM dbo.TableName where ColA = @param

GO

SELECT * FROM dbo.FnName(8)

GO

dave platt-208496 SSC Eights! Points: 862 More actions · Answer 5

thank you for the suggestion. this led me to reading up on your inspired table-valued function incorporating the union all selects -

i can now limit the result as i wanted using: select top 1 * from dbo.fnName

is it possible to test if no rows are returned and substitute something more friendly like "no information available"? - preferably the test would be performed by the: select top 1 * from dbo.fnName (rather than within the the table valued function) as it would give me more flexibility.

before i would use an inline UDF with a select case statement to test the result and substitute a more friendly phrase if null was returned.

Chris Chilvers SSCrazy Points: 2260 More actions · Answer 6

> is it possible to test if no rows are returned and substitute something more friendly like "no information available"?

Would this be something better handled by the application?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

I'm tempted to say that you should reconsider the whole operation. I never saw the need for continuous calls of the same function to do selects.

Can you post some sample data from you tables (even if fake). and the required output from that data. That way we might think about an alternative solution!

dave platt-208496 SSC Eights! Points: 862 More actions · Answer 8

it is true when i say i don't have any control of the application - the developer does not support (although they do allow) the use of select sql statements by the user (me) although they say the facility is there if i want to use it.

the developer provides the sql backend and also allows me to merge info from sql direct into ms word using sql select statements. they have their own merge commands but in this case they lack functionality.

i want a one line address - in format: name, street1, town - to be merged into the word document

i have used concatenation to achieve the formatting.

i was going to explain the underlying table structure but my explanation got so complicated / lengthy i thought i would save you from likely injury when you fell from your chair - due to sleep or laughter.

it may be evident that to obtain a "default" address i have to use a union all

it was the icing to have sql provide an alternative to the one line address where, for whatever reason, a person did not have an address associated with them. but it is not strictly necessary. i am happy with the cake. thanks

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I would suggest you go back to the developpers to see if they can find a better solution.. if they can't and you have a working one, then use it... but they might not like it of it takes too much ressources on the server.

Good luck with that!