DECLARE @Tbl_AccountTABLE(

AccNoint,

Add_By_AccNoint,

Commdecimal(18,8),

lvl nvarchar(50))

INSERT @Tbl_Account VALUES(2,0,6,'MANAGER'),(3,2,4,'AGENT'),(5,3,3,'SALE PERSON'),(6,2,4,'AGENT')

DECLARE @Tbl_Transaction TABLE(

Transaction_AccNoint,

Commdecimal(18,8),

Amountdecimal(18,8))

DECLARE @PurchasePrice decimal(18,3)

set @PurchasePrice = 10.00;

WITH Dependencies AS(

SELECT * FROM @Tbl_Account WHERE AccNo = 5 UNION ALL

SELECT t.* FROM @Tbl_Account t

JOIN...

Tbl_Account

AccNo Commision Add_By_AccNo Level

2 6.000 0 MANAGER

3 4.000 2 SUPERVISOR

5 3.000 3 SALEPERSON

Tbl_Transaction //this is the example insert query success

AccNo Commision Amount

5 1% $0.10

3 2% $0.20

2 2% $0.20

This is the...

Msg 319, Level 15, State 1, Line 13

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause,...

Sean Lange (8/31/2012)

---

chinye2020 (8/31/2012)

---

Tbl_Account

AccNo Comm Add_By_AccNo Level

2 6.000 0 MANAGER

3 4.000 2 SUPERVISOR

5 3.000 3 SALEPERSON

Tbl_Transaction //this is the example insert query success

Transaction_AccNo Comm Amount

5 1% $0.10

3 2% $0.20

2 2%...

Tbl_Account

AccNo Comm Add_By_AccNo Level

2 6.000 0 MANAGER

3 4.000 2 SUPERVISOR

5 3.000 3 SALEPERSON

Tbl_Transaction //this is the example insert query success

Transaction_AccNo Comm Amount

5 1% $0.10

3 2% $0.20

2 2% $0.20 //how to...

Luis Cazares (8/31/2012)

---

Sean is right, DDL and sample data would have been a great help. As well as posting your whole problem.

You should pay more attention to your code to...

how to in select query save the last row Commision Value?have any idea?

Tbl_Account

AccNo Commision Add_By_AccNo Level

2 6.000 0 MANAGER

3 4.0002 SUPERVISOR

5 3.0003 SALEPERSON

Tbl_Transaction //this is the example insert query success

AccNo Commision Amount

5 1% $0.10

3 2% $0.20

2 2% $0.20

This is the Database with...

im using CTE read the most lowest member until top,

but until top,need to read back the second top Commision,this is the problem

AccNo Commision Add_By_AccNo

2 6.000 0

3 4.0002

5 3.0003

WITH Dependencies AS(SELECT * FROM Tbl_Account WHERE AccNo = 5 UNION ALL SELECT t.* FROM Tbl_Account t JOIN Dependencies d ON t.AccNo = d.Add_By_AccNo)

INSERT...

problem solved,should be like this

-- Insert statements for procedure here

WITH Dependencies AS(SELECT * FROM Tbl_Account WHERE AccNo = 8 UNION ALL SELECT t.* FROM Tbl_Account t...

USE [DB_Flexi]

GO

/****** Object: StoredProcedure [dbo].[UpdateUserAmount] Script Date: 08/30/2012 14:48:22 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[UpdateUserAmount]

@Topup_Amount decimal(18,2),

@AccNo int

--@Balance INT OUTPUT

AS

BEGIN

--...

thanks.understood~

Great job man ! that's what i want....

that's damn difficult....