Home A unique key to use in query operations for the view
Reply: 0

A unique key to use in query operations for the view

user1445
1#
user1445 Published in July 22, 2018, 6:18 pm

I have this view table in sql server and I want to make it a unique key for each record in a separate column.

how I can make it a unique key for each record in a separate column? like index for example

This is the code created the view

SELECT     'order' AS type, id AS id, id_customer, amount AS debit, 0 AS credit, order_date AS date, '....' AS description
FROM         dbo.Orders
UNION ALL
SELECT     'receipt' AS type, id AS id, id_customer, 0 AS debit, amount AS credit, receipt_date AS date, 'cash' AS description
FROM         dbo.receipts

I am asking this presentation using this code But I have a problem when there is an invoice and receipt similar id number is repeated each one of them twice so I want a unique key to do the comparison in the following code

declare @id_customer int 
;with initial as(
  select *
  from result
  where id_customer= @id_customer
),report as(
  select r.id,[balance]=isnull((select sum(b.debit-b.credit)
               from initial b
               where b.[date]<r.[date]) + r.debit - r.credit ,r.debit-r.credit)
  from initial r
)

 select [Operation type] = type,
        reference_no = r.id,
        [description],
        [Debit] = debit,
        [Credit] = credit,
        [Balance] = b.balance
 from result r
 inner join report b on b.id = r.id
 where r.id_customer = @id_customer
 order by r.[date]
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.265295 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO