[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Creating a new column in a view

this may sound like a stupid question but within a view if i wanted to add a column that would contain numbers how would i do this, is it a cast function or something?

john
0
pepps11976
Asked:
pepps11976
  • 3
  • 3
  • 3
  • +1
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
where do those "numbers" come from?
0
 
pepps11976Author Commented:
it would be something i would manually add
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
well, let's start with this "explanation".

say your view is like this:
create view your_view
as
select t.col1, t.col2 
from yourtable 

Open in new window


you could add a new column like this:
create view your_view
as
select t.col1, t.col2 
, 123 as new_col
from yourtable 

Open in new window


if this does not solve your problem, please try to explain with some data samples what you are trying to achieve
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
pepps11976Author Commented:
Ok this is my View
SELECT     TOP (100) PERCENT SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) 
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total it_total_gbp], dbo.[Anal Code].ss_prod
FROM         dbo.[Anal Code] INNER JOIN
                      dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.itran.it_anal <= 'ZZZ') AND (dbo.itran.it_doc <= 'WOR') AND (dbo.ihead.ih_sorder <> ' ') AND (dbo.ihead.ih_orddate >= '07/01/2010') 
                      AND (dbo.ihead.ih_orddate <= '06/30/2011')
GROUP BY dbo.[Anal Code].ss_prod

Open in new window


and i would like to add another column call Forecast that would accept number values
0
 
Roman GhermanCommented:
I think you want to return something like a RowID,
here is a link that could help you: http://extremedev.blogspot.com/2011/03/ms-sql-rowid.html

You will need to use ROW_NUMBER() OVER(ORDER BY SalesYTD DESC)

where inside (...) you will put the column(s) on which to generate that rowid
0
 
sachinpatil10dCommented:
try this

this will create a serial number column in the view
create view vw_ViewName
as
select col1, col2
row_number() over (order by col1,col2) srno
from TableName

Open in new window

0
 
Roman GhermanCommented:

if you just want a column of type INT then:


SELECT     TOP (100) PERCENT SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) 
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total it_total_gbp], dbo.[Anal Code].ss_prod,
CONVERT(INT, NULL) AS Forecast 
FROM         dbo.[Anal Code] INNER JOIN
                      dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.itran.it_anal <= 'ZZZ') AND (dbo.itran.it_doc <= 'WOR') AND (dbo.ihead.ih_sorder <> ' ') AND (dbo.ihead.ih_orddate >= '07/01/2010') 
                      AND (dbo.ihead.ih_orddate <= '06/30/2011')
GROUP BY dbo.[Anal Code].ss_prod

Open in new window

0
 
sachinpatil10dCommented:
change in you view
check if it works
SELECT     TOP (100) PERCENT SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) 
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total it_total_gbp], dbo.[Anal Code].ss_prod
row_number() over (order by dbo.[Anal Code].ss_prod) newIdColumn                      
FROM         dbo.[Anal Code] INNER JOIN
                      dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.itran.it_anal <= 'ZZZ') AND (dbo.itran.it_doc <= 'WOR') AND (dbo.ihead.ih_sorder <> ' ') AND (dbo.ihead.ih_orddate >= '07/01/2010') 
                      AND (dbo.ihead.ih_orddate <= '06/30/2011')
GROUP BY dbo.[Anal Code].ss_prod

Open in new window

0
 
sachinpatil10dCommented:
Column type with int
SELECT     TOP (100) PERCENT SUM(CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) 
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS [Total it_total_gbp], dbo.[Anal Code].ss_prod
,0 newIdColumn                      
FROM         dbo.[Anal Code] INNER JOIN
                      dbo.itran ON dbo.[Anal Code].ss_acode = dbo.itran.it_anal RIGHT OUTER JOIN
                      dbo.ihead ON dbo.itran.it_doc = dbo.ihead.ih_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.itran.it_anal <= 'ZZZ') AND (dbo.itran.it_doc <= 'WOR') AND (dbo.ihead.ih_sorder <> ' ') AND (dbo.ihead.ih_orddate >= '07/01/2010') 
                      AND (dbo.ihead.ih_orddate <= '06/30/2011')
GROUP BY dbo.[Anal Code].ss_prod

Open in new window

0
 
pepps11976Author Commented:
sachinpatil10d

you code does allow me to add a column but i cannot manually add numbers into it
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot put values into a view's data.
you can only do that in some table, and query that into your view ...
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 3
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now