oracle function converted to SQL function (syntax help?)

[LMCRYER]

I have a function that works like a charm in Oracle, and I need it to be in SQL so that a stored procedure in SQL can get to it...I dont understand what I am doing wrong -- definitely need syntax help. Can someone please, please swith this to SQL for me and then explain it so I learn how to do it next time?
*********************************************************
CREATE OR REPLACE FUNCTION concat_part_desc_LMC082304 RETURN VARCHAR2 AS
concat_desc VARCHAR2(4000) := NULL;
BEGIN
FOR i IN (SELECT T.COMMENT_ONE,T.COMMENT_TWO
FROM CSIOWNER.txdt T, CSIOWNER.PART_ELK PART
WHERE T.TEXT_ID = PART.PART_NBR and T.prefix_id = 'TXT'
AND PART.USER_FIELD_3 = 'ELK')
LOOP
-- l_string := l_string || Ltrim(Rtrim(i.comment_one)) ||
-- ' ' || Ltrim(Rtrim(i.comment_two)) || ' ';

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


LMC

 

[SQLBill]

Most of those commands don't exist in SQL Server. Oracle uses PL/SQL and SQL*Plus. SQL Server uses Transact-SQL. Basically they are the same, but there are many differences. It's like English. There's British English and American English. If you know one, you basically know the other, but there are some words/phrases that will mess you up. For example, (this isn't meant to insult anyone) what is a fag? In British english it means cigarette, but it's not something polite in American English.

Your whole script will need to be re-written. If I have time I'll give it a try. I suggest you checking out the Microsoft BOL or if you are going to do a lot of SQL Server programming - you should take a Microsoft SQL Server Transact-SQL class.

-SQLBill

BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

Posting advice: FAQ481-4875

 

[LMCRYER]

Well, let me provide more info on what I am really trying to do - it's entirely possible I am using the wrong approach from the beginning?

I have 2 tables - PART, and TXDT.

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?


LMC

 

[SQLBill]

Much better. Let me see what I can do with it.

-SQLBill

 

[vongrunt]

This function crawls through fixed query and concatenates comments. Try someting like:

if exists(select * from sysobjects where xtype='FN' and name = 'concat_part_desc_LMC082304')
	drop function concat_part_desc_LMC082304
go

create function concat_part_desc_LMC082304()
returns nvarchar(4000)
as
begin
	declare @concat_desc nvarchar(4000)
	set @concat_desc = ''

	select @concat_desc = substring( ltrim(rtrim(T.COMMENT_ONE)) + ' ' + ltrim(rtrim(T.COMMENT_TWO)) + ' ', 1, 4000)
		FROM CSIOWNER.txdt T INNER JOIN CSIOWNER.PART_ELK PART ON T.TEXT_ID = PART.PART_NBR
		WHERE T.prefix_id = 'TXT'  AND PART.USER_FIELD_3 = 'ELK'

	return @concat_desc
end
go

 

[vongrunt]

Damn... Friday afternoon

select @concat_desc = substring( [b]@concat_desc + [/b]ltrim(rtrim(T.COMMENT_ONE)) + ' ' + ltrim(rtrim(T.COMMENT_TWO)) + ' ', 1, 4000)
FROM ...

 

[LMCRYER]

OK, I got the function to not throw any errors. Syntax looks good. Now, how the heck do I tie this function back into the stored parameter so that the stored parameter calls the function based on :

IF the part_nbr is in the stored proc, and
IF that part_nbr is in the TXDT table, then get the data from the function, otherwise, don't get anything....

???

LMC

LMC

 

[vongrunt]

> IF the part_nbr is in the stored proc, and

Don't understand this part...

> IF that part_nbr is in the TXDT table,

Typical construct for that is

if exists(select * from table where field=@value)
begin
...
end

To call function, syntax is:

declare @variable nvarchar(4000)
...
set @variable = dbo.concat_part_desc_LMC082304()

What confuses me a bit is that Oracle function you posted seems to have no input arguments.