How to avoid the Self-Reference Error in SQL View?

1

Hi,

I am building a report for a client using SQL views.
I need to create 4 column, which are dependent:

  1. Balance Held
  2. Total Number of units
  3. Average Rate
  4. Realized P&L

Average Rate = Balance Held/Total Number of units

Realized P&L = Number of Units * ( Previous Average Rate - Dealt Rate)

Balance Held = Previous Balance Held + Realized PnL + Dealt Rate * Units

UNITS | DEALT RATE | AVERAGE RATE | BALANCE HELD | TOTAL UNITS | REALIZED P&L

1 | 4 | 4 | 4 | 1 | 0

3 | 2 | 4 | 16 | 4 | 6

2 | 3 | 4 | 24 | 6 | 2

2 | 3 | 4 | 32 | 8 | 2

2 | 3 | 4 | 40 | 10 | 2

In Excel it is all fine, however When I am trying to incorporate the above logic into SQL views (I calculate Realized PnL in View1, Balance Held in View 2 and Average Rate in View 3 ), I get the error message about self - referencing of the views. :

View or function vw_PnLReport_by_deal4 contains a self-reference. Views or functions cannot reference themselves directly or indirectly.

Any hint on how to apply this logic in SQL ? which function to use ?

Thank you !

2

Pleaae show us the view

3

hi

hope this helps :slight_smile: :slight_smile:

you can use ALIASES ...
like when same column appears twice in select not allowed then we give another alias
select name , name as name123 from table

this link below talks about self referencing ... some clue might be there !!!