bad syntax...LOOP in STORED PROC?

[LMCRYER]

I know what I am trying to do, but not HOW to do it, exactly!

I have a stored procedure in SQL that does this:
*********************************
CREATE PROCEDURE [DBO].[BOM_SEARCH]

@PART_NO VARCHAR(25),
@1_PART_DESC VARCHAR(25),
@2_PART_DESC VARCHAR(25),
@3_PART_DESC VARCHAR(25)

AS

SELECT
distinct PART.PART_NBR,
PART.PART_TYPE,
PART.BOM_UM,
PART.PART_DESC,
TXDT.COMMENT_ONE,
TXDT.COMMENT_TWO,
TXDT.TEXT_ID,
TXDT.PREFIX_ID,
TXDT.SEQ_NBR

FROM
PART_ELK PART,
TXDT_ELK TXDT

WHERE
PART.PART_NBR*=TXDT.TEXT_ID AND
PART.USER_FIELD_3='ELK' AND
TXDT.PREFIX_ID = 'TXT' AND

UPPER(PART.PART_NBR) LIKE UPPER(@PART_NO) + '%'
GO
*********************************

However, what I really need to do is a function on the side, which will put together all of the TXDT.COMMENT_ONE and TXDT.COMMENT_TWO fields (see below) and then join that into the above stored procedure....

Or maybe this needs a subquery?

Please note - the symtax below is shown for CONCEPT only, it is from ORACLE (which I did get to work correctly =)
*********************************

BEGIN
FOR i IN (SELECT T.COMMENT_ONE,T.COMMENT_TWO
FROM TXDT_ELK T, PART_ELK P
WHERE T.TEXT_ID = P.PART_NBR and T.PREFIX_ID = 'TXT' AND

LOOP


concat_strg :=
substr(
concat_strg ||
Ltrim(Rtrim(i.comment_one)) || ' ' ||
Ltrim(Rtrim(i.comment_two)) || ' '
,1,4000);
END LOOP;
RETURN concat_strg;
END;
/
*********************************

Can any one help guide me, please?

LMC

LMC

 

[mrdenny]

If I'm understanding you correctly you want to take the comment_one and comment_two fields for every row, put them into a large variable, and return that variable with each row of your result set.

CREATE PROCEDURE [DBO].[BOM_SEARCH] 

@PART_NO VARCHAR(25), 
@1_PART_DESC VARCHAR(25),
@2_PART_DESC VARCHAR(25),
@3_PART_DESC VARCHAR(25)

AS

declare @comment_one varchar(1000)
declare @comment_two varchar(1000)
declare @master_comment varchar(8000) 
/*if this isn't big enought to hold all your data, let me know, and I'll show you how to do with a temp table.*/


declare cur CURSOR for select comment_one, Comment_two FROM 
PART_ELK PART,
TXDT_ELK TXDT

WHERE 
  PART.PART_NBR*=TXDT.TEXT_ID AND
  PART.USER_FIELD_3='ELK' AND
  TXDT.PREFIX_ID = 'TXT' AND
  
  UPPER(PART.PART_NBR) LIKE UPPER(@PART_NO) + '%'
/*You might need to edit the joins and where as needed*/
open cur
fetch next from cursor into @comment_one, @comment_two
while @@FETCH_STATUS = 0
BEGIN
     set @master_comment = @master_comment + @comment_one + @comment_two
     fetch next from cursor into @comment_one, @comment_two
END
close cur
deallocate cur


SELECT 
  distinct PART.PART_NBR,
  PART.PART_TYPE,
  PART.BOM_UM,
  PART.PART_DESC,
  TXDT.TEXT_ID,
  TXDT.PREFIX_ID,
  TXDT.SEQ_NBR,
  @Master_comment AllComments
FROM 
PART_ELK PART,
TXDT_ELK TXDT

WHERE 
  PART.PART_NBR*=TXDT.TEXT_ID AND
  PART.USER_FIELD_3='ELK' AND
  TXDT.PREFIX_ID = 'TXT' AND
  
  UPPER(PART.PART_NBR) LIKE UPPER(@PART_NO) + '%'
GO

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[SQLSister]

Suggest you never use the *= construction again. It is not a good construction for SQL Server. From Books Online:

"In earlier versions of Microsoft® SQL Server™ 2000, left and right outer join conditions were specified in the WHERE clause using the *= and =* operators. In some cases, this syntax results in an ambiguous query that can be interpreted in more than one way."

Sometimes SQL Server will interpet this as a cross join rather than a left join. Which is very bad if you have large tables as well as giving you incorrect results. YOu need to start using the join systax not this ambiguous type of join. Read about left Joins in Books online for the correct sysntax to use.

Questions about posting. See faq183-874

 

[mrdenny]

Now if I had actually read the where clause I would have noticed that. That'll teach me.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[LMCRYER]

OK, that makes sense about the *= problem, I will read up on that.

Let me see if I can explain myself better =)

For every record in the PART table, there may or may not be a record in the TXDT table. However, the TXDT table can have multiple rows in it for that part (defined by a sequence number field).

What I currently get is this:

Part.ID Part.Desc TXDT.comment1 TXDT.comment2

A This is a weird way of showing an exa
A This is a mple but I ho pe that it is
A This is a clearer than trying to put
A This is a it in words.

Notice that I get multiple lines for Part A, and the PART desc is showing multiple times. The TXDT stuff really needs to be grouped together by the seq number and then come in nicely. The idea is to get this:

Part.ID Part.Desc Comments
A This is a weird way of showing an example
but I hope that it is clearer than
trying to put it in words.

If I get really lucky I am going to figure out how to do
this (which I can do in the Crystal side but would rather learn the right way in SQL first!)

Part.ID @COMMENTS (FORMULA or whatever)

A This is a weird way of showing an example
but I hope it is clearer than trying to put
it in words.

Is that any better? I think I need a function to go and get the TXDT stuff, wrap it all together nicely and spit it back out (like what I have in the example in the first post)?
Somebody kindly correct me if I am WAY off mark?



LMC

 

[mrdenny]

A function should work here. You could create a function that accepts the PartID as input and returns all the text that goes with that. Then you could call that function inline. Not sure what sort of performance hit you would take doing it that way.

Denny

--Anything is possible. All it takes is a little research. (Me)