Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Very frustrated with a concatenate Proc

Status
Not open for further replies.
bakershawnm

bakershawnm

Programmer
Apr 18, 2007
84
US
Hello all,

Yesterday the following SQL view and Proc worked fine. Today I only get a single pin in the field.

View used to call concatenate:
SELECT Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug, dbo.GetPins(Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug) AS Pins
FROM (SELECT DISTINCT Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug
FROM dbo.UnusedContacts
UNION
SELECT DISTINCT Drawing, ExtrctDate, RefDes, CavArrngID, [Sealing Plug2]
FROM dbo.UnusedContacts AS UnusedContacts_1
WHERE (NOT ([Sealing Plug2] IS NULL))) AS UnusdCntcs

In the above view the union is because there are two sealing plug fields as you could have up to 2 in any given pin slot.

Concatenate Proc
ALTER FUNCTION [dbo].[GetPins]
(
-- Add the parameters for the function here
@Drwng Varchar(max),
@DtExtrctd DateTime,
@RefDes varchar(max),
@PrtNm varchar(max),
@SlPlg varchar(max)

)
RETURNS Varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result Varchar(max)

-- Add the T-SQL statements to compute the return value here
Select @Result = IsNull(@Result + ', ', '') + RTRIM(Pin)
From (Select TOP (100) PERCENT Drawing, ExtrctDate, RefDes, CavArrngID, InsertID, SealingPlug, [Sealing Plug2], Pin,
LEFT(SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000) + 'X')-1) as ivalue
From dbo.UnusedContacts) as tmptbl
Where Pin Is Not NULL
And Drawing = @Drwng and RefDes = @RefDes and CavArrngID = @PrtNm
and ExtrctDate = @DtExtrctd and (SealingPlug = @SlPlg or [Sealing Plug2] = @SlPlg)
Order By replace(Pin, ivalue, ''), Cast(ivalue as int)

-- Return the result of the function
RETURN @Result

END

What could possibly cause this to no longer work? Any possibilities will be welcome no matter how unrealistic/improbable they may seem.

And thanks to all of those that contributed (without even knowing it) to the development of this view and Proc setup. I love this site because it is such a great source of information.

Thanks

 
Sort by date Sort by votes
First, you can remove the distincts from the derived table part because UNION is already doing that for you.

Code: 
SELECT     Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug, dbo.GetPins(Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug) AS Pins
FROM         (SELECT [!][s]DISTINCT[/s][/!] Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug
                       FROM          dbo.UnusedContacts
                       UNION
                       SELECT [!][s]DISTINCT[/s][/!] Drawing, ExtrctDate, RefDes, CavArrngID, [Sealing Plug2]
                       FROM         dbo.UnusedContacts AS UnusedContacts_1
                       WHERE     (NOT ([Sealing Plug2] IS NULL))) AS UnusdCntcs

Can you explain the problem a little better (because I get confused easily). Are you not returning the rows you are expecting, or is the problem with the function not concatenating all of the data you are expecting?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
I'm confused about something else.

In your query, you are combining results from 2 tables (UnusedContacts and UnusedContacts_1). In your function, you are only using UnusedContacts. It seems to me like this may be why you are missing some data. Of course, it's possible that I don't understand your data, either.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
The view is only showing 1 pin location instead of the (possibly hundreds) other pin locations. I am leaning towards the Proc not providing the data back to the view however it is possible the view is not providing the Proc with the right information. I still get all of the rows I got before it's the Pin field that is incorrect.
 
Upvote 0 Downvote
more info.

The Proc is only providing the last pin back to the view.
 
Upvote 0 Downvote
got lucky and solved it. I modified this:

From (Select TOP (100) PERCENT Drawing, ExtrctDate, RefDes, CavArrngID, InsertID, SealingPlug, [Sealing Plug2], Pin,
LEFT(SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000) + 'X')-1) as ivalue
From dbo.UnusedContacts) as tmptbl
Where Pin Is Not NULL
And Drawing = @Drwng and RefDes = @RefDes and CavArrngID = @PrtNm
and ExtrctDate = @DtExtrctd and (SealingPlug = @SlPlg or [Sealing Plug2] = @SlPlg)
Order By replace(Pin, ivalue, ''), Cast(ivalue as int)

To this:
From dbo.SortedUnusdCntx
Where Pin Is Not NULL
And Drawing = @Drwng and RefDes = @RefDes and CavArrngID = @PrtNm
and ExtrctDate = @DtExtrctd and (SealingPlug = @SlPlg or [Sealing Plug2] = @SlPlg)

put the sorting of the pin locations into a view.

For the life of me I cannot figure out why that would make a difference. Specifically it was the 'Order By' clause that was causing the problem.

This is the new view:
SELECT TOP (100) PERCENT Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug, [Sealing Plug2], Pin, LEFT(SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin),
8000), PATINDEX('%[^0-9.-]%', SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000) + 'X') - 1) AS ivalue
FROM dbo.UnusedContacts
ORDER BY Drawing, RefDes, REPLACE(Pin, LEFT(SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000), PATINDEX('%[^0-9.-]%',
'SUBSTRING(Pin, PATINDEX(%[0-9.-]%, Pin), 8000) + X') - 1), ''), CAST(LEFT(SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000), PATINDEX('%[^0-9.-]%',
SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000) + 'X') - 1) AS int)


Any other thoughts on why the Order By clause was suddenly causing problems when it worked fine yesterday would still be welcome.

Thanks
 
Upvote 0 Downvote
ah.... you have an order by in a view. That's a no-no. Actually, there are hacks to make it work, but it's still a hack. Your best approach would be to remove the order by from inside the view and order the data outside the view (where you use the view) instead. For more reading on this....


The Top 100 Percent trick for sorting views worked in SQL2000 but not in SQL2005. Top 99.999999 percent is an even worse hack that is not guaranteed to work in future versions of SQL server.

To determine how many views you have in your database with Order By, run this.

Code: 
Select Distinct sysobjects.name as ViewName 
from   sysobjects 
       inner join syscomments 
         On sysobjects.id = syscomments.id 
Where  xtype = 'v' 
       and syscomments.text like '%order by%' 
Order By sysobjects.name

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
But having the order by clause in the Proc is where the problem was. I took out the order by clause from the Proc and I was able to get all of the pins in the field. The only issue was that they were not in the sorted order I needed but at least they were there.

The Top 100 percent clause seems to be set as a default for my SSMS and I just haven't turned it off. And the Top 100 Percent never worked for me as a hack. In the past I have had to us the Top (<max 32bit int value>) to get it to propagate the sort. However in most cases I have gotten away from that (there might be some leftovers out there that I haven't cleaned up.)
 
Upvote 0 Downvote
In the end I had to do this (and get rid of the newest view) for the sake of speed.

ALTER FUNCTION [dbo].[GetPins]
(
-- Add the parameters for the function here
@Drwng Varchar(max),
@DtExtrctd DateTime,
@RefDes varchar(max),
@PrtNm varchar(max),
@SlPlg varchar(max)

)
RETURNS Varchar(max)
AS
BEGIN
-- Declare the return variable here
DECLARE @Result Varchar(max)

-- Add the T-SQL statements to compute the return value here
Select @Result = IsNull(@Result + ', ', '') + RTRIM(Pin)
From (SELECT Top 2147483648 Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug, [Sealing Plug2], Pin, ivalue
FROM (SELECT Drawing, ExtrctDate, RefDes, CavArrngID, SealingPlug, [Sealing Plug2], Pin,
LEFT(SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000),
PATINDEX('%[^0-9.-]%', SUBSTRING(Pin, PATINDEX('%[0-9.-]%', Pin), 8000) + 'X') - 1) AS ivalue
FROM dbo.UnusedContacts) as tmptbl_1
Where Pin Is Not NULL
And Drawing = @Drwng and RefDes = @RefDes and CavArrngID = @PrtNm
and ExtrctDate = @DtExtrctd and (SealingPlug = @SlPlg or [Sealing Plug2] = @SlPlg)
ORDER BY REPLACE(Pin, ivalue, ''), CAST(ivalue AS int)) as tmptbl_2

-- Return the result of the function
RETURN @Result

END

Because the pins had to be in sorted order in the field otherwise the user of the data would complain.

Thanks for your help and advice.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gussy81
  • Locked
  • Question
Decimal Datatype with negative value
Replies
5
Views
1K
Chris Miller
Chris Miller
SashaBuilder3
  • Locked
  • Question
select a single row based on conditions in multiple rows 1
Replies
3
Views
571
SashaBuilder3
SashaBuilder3
Cpreston
  • Locked
  • Question
Update with a month name 1
Replies
9
Views
637
gmmastros
gmmastros
Billz66
  • Locked
  • Question
need to udate table entry with number containing a *
Replies
11
Views
277
Chris Miller
Chris Miller
Cpreston
  • Locked
  • Question
How to add a Letter to datepart
Replies
5
Views
500
Andrzejek
Andrzejek

Part and Inventory Search

Sponsor

Back
Top