Please HELP

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

Quote:

Originally Posted by gggram2000

I wrote a UDF in SQL server 2005 that looks like this:

CREATE FUNCTION InvoiceNum ()
RETURNS INT
AS
BEGIN
DECLARE @.inv int
SET @.inv = @.inv + 1
SET @.inv = (SELECT InvoiceNumber FROM Sales)

IF @.inv IS NULL
SET @.inv = '1'
ELSE if @.inv IS NOT NULL and @.inv > ''
INSERT INTO Sales(InvoiceNumber
RETURN @.inv
END

Which gives me this error:

Msg 156, Level 15, State 1, Procedure InvoiceNum, Line 16
Incorrect syntax near the keyword 'RETURN'.

I would like help on that error...and after that, I'm using Visual Studio 2005...I would like to know how to call the function from within a command in MSVS. Your help will be greatly appreciated!

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)|||

Quote:

Originally Posted by amitpatel66

You need to close the bracket.

INSERT INTO Sales(InvoiceNumber)

Yea that's a mistake i did when i pasted it here...but in the actual server it has the bracket.

A question would be how to insert a value (eg. Name) into a table row block. Like: Insert Into Table (ClolumnName) Value(@.ColumnName) But I want to enter it into and existing column/row to replace the value of that specific column with "Name". Let's say for that column it has "None"...I want to change it to "Name"...how do i do that?

PL/SQL help - split an address

I have a single address column that i want to split.
For example I have an address with carriage returns like:

address
----------
Administration Tech Services

1234 Elm Avenue

West Building

I would like a SELECT query to split this address column into 3 like:
address address2 address3
---- ---- ----
Administration Tech Services 1234 Elm Avenue West BuildingYou may need to create a function like this:
Create Or Replace Function Get_Addr
(P_Line Number, P_Addr Varchar2)
Return Varchar2 Is
Type Addr_Typ Is Table Of Varchar2(1000);
V_Addr Addr_Typ;
V_Tmp Varchar2(1000);
I Pls_Integer;
J Pls_Integer;
K Pls_Integer;
L Pls_Integer;
Begin
V_Tmp:=Rtrim(P_Addr)||Chr(10);
For I In 1..P_Line Loop
V_Addr(I):='';
End Loop;
I:=0;
L:=Length(V_Tmp);
While (L > 0)
Loop
K:=Instr(V_Tmp,Chr(10));
If K = 0 Then
Exit;
End If;
J:= K-1;
If J > 0 Then
I:=I+1;
V_Addr(I):= Substr(V_Tmp,1,J);
V_Tmp:=Rtrim(Substr(V_Tmp||' ',K+1));
End If;
L:=Length(V_Tmp);
End Loop;
Return V_Addr(P_Line);
End;
/
And use it like:

UPDATE MyAddrTab
SET Address2=Get_Addr(2,Address)
, Address3=Get_Addr(3,Address);
COMMIT;
UPDATE MyAddrTab
SET Address=Get_Addr(1,Address);
COMMIT;
:eek:

PS: You will need to validate parameters!

PIVOT Query

Hi,
I have a requirement for a query which returns as many columns as many
rows are present in the table.
Table looks like this
Parent_Assembly_id Child_Assembly_id
1 2
2 3
3 4
3 5
I need to transform the data to look like
Level1 Level2 Level3 Level4 .....
1 2 3 4
1 2 3 5
.Hi
I'd prefer to do such kind of things on the client side
CREATE TABLE #Test
(
Parent_Assembly_id INT NOT NULL,
Child_Assembly_id INT NOT NULL
)
INSERT INTO #Test VALUES (1,2)
INSERT INTO #Test VALUES (2,3)
INSERT INTO #Test VALUES (3,4)
INSERT INTO #Test VALUES (3,5)
SELECT T2.Parent_Assembly_id AS Level1,
T2.Child_Assembly_id AS Level2,
T.Parent_Assembly_id AS Level3,
T.Child_Assembly_id AS Level4
FROM #Test T JOIN #Test T1
ON T.Parent_Assembly_id=T1.Child_Assembly_id JOIN #Test T2 ON
T1.Parent_Assembly_id=T2.Child_Assembly_id
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:OANptPUUFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a requirement for a query which returns as many columns as many
> rows are present in the table.
> Table looks like this
> Parent_Assembly_id Child_Assembly_id
> 1 2
> 2 3
> 3 4
> 3 5
>
> I need to transform the data to look like
> Level1 Level2 Level3 Level4 .....
> 1 2 3 4
> 1 2 3 5
> .
>|||Hi,
Thanks for the quick reply.
The problem I am facing is that I would not know before hnd the number
of levels that are present in the database.
The number of levels can be N levels. Is there any way of achieving
this?
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OtLIzWUUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'd prefer to do such kind of things on the client side
> CREATE TABLE #Test
> (
> Parent_Assembly_id INT NOT NULL,
> Child_Assembly_id INT NOT NULL
> )
> INSERT INTO #Test VALUES (1,2)
> INSERT INTO #Test VALUES (2,3)
> INSERT INTO #Test VALUES (3,4)
> INSERT INTO #Test VALUES (3,5)
> SELECT T2.Parent_Assembly_id AS Level1,
> T2.Child_Assembly_id AS Level2,
> T.Parent_Assembly_id AS Level3,
> T.Child_Assembly_id AS Level4
> FROM #Test T JOIN #Test T1
> ON T.Parent_Assembly_id=T1.Child_Assembly_id JOIN #Test T2 ON
> T1.Parent_Assembly_id=T2.Child_Assembly_id
>
>
> "Nishanth" <cvnishanth@.hotmail.com> wrote in message
> news:OANptPUUFHA.3152@.TK2MSFTNGP12.phx.gbl...
many
>|||Hi
Well , thus I said that it will done better on the client side
You will have to calculate how many leveles you have and according to the
number to build a query.
I'd suggest you to do some searching on internet for examples of Itzik
Ben-Gan to deal with hierarchy data
"Nishanth" <cvnishanth@.hotmail.com> wrote in message
news:eGNFbEVUFHA.2892@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Thanks for the quick reply.
> The problem I am facing is that I would not know before hnd the number
> of levels that are present in the database.
> The number of levels can be N levels. Is there any way of achieving
> this?
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:OtLIzWUUFHA.4092@.TK2MSFTNGP12.phx.gbl...
> many
>