Creating A View With Detailed Informations

Mar 30, 2006

Hello,
I have a code for creating view in T-SQL. I want to ask you guys, i want to make this result set should grouped by DepoAdi column and StokKodu (this is an alias sure you can get it from code). Did i make it on group by line? My second problem is i want to add 2 columns to this query. This 2 column will calculate some values with SUM function and - operator. At CRM.Depolar.DepoBilgileri table i have a column named Miktar (this one stores int type datas) and i have a column named islemturu(this one stores 1 or 0). I want to calculate Miktar values which rows has islemturu column 0 and subtract them from which rows has islemturu column 1 value and this computing action must be based on Grouped columns.

DepoAdi | StokKodu | Miktar | IslemTuru
ABS SK101 5 0
ABS SK101 3 1
ABS SK102 4 0
ABS SK102 3 1

This is the table and i'm imaging view what i want now...

DepoAdi | StokKodu | Miktar
ABS SK101 2
ABS SK102 1

How can i add this resultset to my code..

Thanks for reading... Waiting your answers.. Happy coding...



CREATE VIEW [CRM.Depolar.DepoDurumlari]

AS

SELECT [CRM.Depolar.DepoBilgileri].DepoAdi,

[CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu + [CRM.Objeler.ObjeKodlari].ObjeKodu AS StokKodu

FROM [CRM.Depolar.DepoBilgileri], [CRM.Objeler.TemelGruplar.TureyenGruplar], [CRM.Objeler.ObjeKodlari], [CRM.Depolar.DepoHareketleri]

WHERE [CRM.Depolar.DepoBilgileri].Id IN (SELECT DepoBilgileri

FROM [CRM.Depolar]

WHERE Id IN (SELECT Depo

FROM [CRM.Depolar.DepoHareketleri]))

AND [CRM.Objeler.TemelGruplar.TureyenGruplar].Id IN (SELECT ObjeGrubu

FROM [CRM.Objeler]

WHERE Id IN (SELECT Id

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

AND [CRM.Objeler.ObjeKodlari].Id IN (SELECT StokKodu

FROM [CRM.StokKartlar.KartBilgileri]

WHERE Id IN (SELECT KartBilgileri

FROM [CRM.StokKartlar]

WHERE Id IN (SELECT StokKart

FROM [CRM.Depolar.DepoHareketleri])))

GROUP BY [CRM.Depolar.DepoBilgileri].DepoAdi, [CRM.Objeler.TemelGruplar.TureyenGruplar].GrupKodu, [CRM.Objeler.ObjeKodlari].ObjeKodu

View 3 Replies


ADVERTISEMENT

Detailed View In C#

Apr 19, 2007

Hi there,
I was wondering if there is a detailed view control (that can be linked to grid view control) in ordinary C# applications as we have in ASP.NET applications? Please if anyone has information regarding that let me know.

Regards

View 1 Replies View Related

Creating Index On A View To Prevent Multiple Not Null Values - Indexed View?

Jul 23, 2005

I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?

View 3 Replies View Related

Calling A Stored Procedure From A View OR Creating A #tempTable In A View

Aug 24, 2007

Hi guys 'n gals,

I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....

I tried:

CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc


and unfortunately, it does not let this run.

Anybody able to help me out please?

Cheers!

View 3 Replies View Related

Error Authentication User Informations

Jul 20, 2005

Hey Gang, I get a error message "Authentication Failed for SQL Serverinstance: local with friendly name local SQL Server does not exist oraccess denied". I have a Just loaded SQL Server on my virtual box and loadedMicrosofts bpa and microsoft security anlyzer. The security scan work but thebpa scan doesnot. I also look at the database,I can get access to and notice a newdatabase schema(sqlbpa). Any help would be appreciated.ThanksGarry D

View 2 Replies View Related

Creating A View Help

Aug 6, 2007

i have a table which has 2 columns 1 'report'  2 'part'
now in my 'report' cloumn i have # with 6 digits ex. '111111' and 'part' has '1, 2, 3, 4,..to 50'
i want to create a view that will put them together in format like this:
1111110001
1111110002
1111110003 .. and on
it needs to be in 10 digits.
is there anyway i can create a View or may be a column in the table which can create the #'s in this format.

View 2 Replies View Related

Creating A View

Dec 5, 2005

What are some possible purposes of creating a view and how can it be used to reinforce data security. What description of circumstances can be used for a view to save re-programming?

View 1 Replies View Related

Help With Creating View

Apr 16, 2008

Hi All,

I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 2 Replies View Related

Help With Creating View

Apr 16, 2008

Hi All,

I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with ‘#’ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 2 Replies View Related

Need Help With Creating View

Jul 23, 2005

I need to create a view that scores a research assessment. So I havequestions 1 through 10, but the problem is that Q3 and Q5-10 have to berecoded so that if the response is 3, it becomes 0, 2=1, 1=2 and 0=3.So this is what I have. I keep getting an error message that there is"incorrect syntax near the keyword THEN". I don't know which "THEN" itis referring to (or all of them)?? HELP! I am new to this.CREATE VIEW name ASSELECT ID, DATE, TOTAL=Q1+Q2+CASE WHEN (Q3=0 THEN 3 WHEN Q3=1 THEN 2 WHEN Q3=2 THEN 1 WHEN Q3=3 THEN0)+Q4+CASE WHEN (Q5=0 THEN 3 WHEN Q5=1 THEN 2 WHEN Q5=2 THEN 1 WHEN Q5=3 THEN0)+CASE WHEN (Q6=0 THEN 3 WHEN Q6=1 THEN 2 WHEN Q6=2 THEN 1 WHEN Q6=3 THEN0)+CASE WHEN (Q7=0 THEN 3 WHEN Q7=1 THEN 2 WHEN Q7=2 THEN 1 WHEN Q7=3 THEN0)+CASE WHEN (Q8=0 THEN 3 WHEN Q8=1 THEN 2 WHEN Q8=2 THEN 1 WHEN Q8=3 THEN0)+CASE WHEN (Q9=0 THEN 3 WHEN Q9=1 THEN 2 WHEN Q9=2 THEN 1 WHEN Q9=3 THEN0)+CASE WHEN (Q10=0 THEN 3 WHEN Q10=1 THEN 2 WHEN Q10=2 THEN 1 WHEN Q10=3THEN 0) ENDFROM tablename

View 2 Replies View Related

Help With Creating View

Apr 16, 2008



Hi All,


I have a sql command with temporary tables, but whenever I am trying to create a view with temporary tables
I am getting an error as below:

"Views or functions are not allowed on temporary tables. Table names that begin with €˜#€™ denote temporary tables."


Please anybody let me know is it possible to create a view with temporary tables in SQL Server 2005.If not, then is their any way how I can create a view with temporary tables.



Thank You

View 3 Replies View Related

Question About Creating A View.

May 19, 2007

I currently have a website with a page that displays the flags/ keys of the entire roster of guilded characters.  Some more background is that I run a website for my Everquest guild, users can log in , create characters , and update their flags / keys.
 There are 4 associated tables that are used in displaying the flags.
 the Characters , Flags, Expansion, and Char_Flags tables.   The char_flags table consists of 2 foreign keys and a bit field for enabled / disabled. 
Char_flags = char_fk, flag_fk, enabled (bit)
The select statement I'm currently using to get information is as follows.
SELECT Expansion.ExpansionName, Flags.Flag_Name, Characters.Char_Name, char_flags.enabled FROM char_flags INNER JOIN Flags ON char_flags.Flag_FK = Flags.Flag_PK INNER JOIN Expansion ON Flags.Expansion_FK = Expansion.ExpansionPK INNER JOIN Characters ON char_flags.Char_FK = Characters.Char_PK WHERE (Expansion.ExpansionPK = @expansion)
 That displays the information in a format like
Expansion Name,  FlagName, CharacterName, Enabled.   And there are usually 10 -15 flags per expansion so this lists the expansion 10 times, each flag name, then the character name 10-15 times, and wether or not the flag is enabled.
 
I would like to display the information a little differently and I'm not sure how to.   I would like to display the inormation like this
             Flag Name 1     Flag Name 2,  Flag name 3, Flag Name 4, etc...
Char 1          X                                            X                  X
Char2                                  X                     X                  X
Char 3         X                      X                      X                 X
Char 4
Char 5
etc
where the characters make up the first column, the flag names make up the first row(headers) and wether or not the flag is enabled is in the column under the corresponding flag.
This way the name of the flag, and the character name are only displayed one time instead of the flags and character names being repeated over and over.  
If anyone can help me on how to display the data I would appreciate it.   Here is a link to the page to show how it looks now if it helps www.shattereddestinies.net/flagstest.aspx
Thanks
Brad Fortner

View 3 Replies View Related

Problems Creating A View

Jan 19, 2005

I have a query on one of my pages that is just too large to keep in the page, so I need to reference a stored view in sql. I'm kind of new to this, so I'm having trouble getting the syntax right. The query is just a simple select statement using the results of several textboxes as parameters. I know how to do the query inside an asp.net page, but when I move it to sql, I don't know how to reference the textbox value i.e. @textbox. Here's what I have so far:
USE [Maindb]
GO
CREATE VIEW [tblMain_view] (@textbox nvarchar(30)) ????
AS SELECT dbo.tblMain.Field1, ....
FROM dbo.tblMain
WHERE dbo.tblMain.Field1 = @textbox and ....


First of all, I know that where I declare @textbox is wrong, so where is the right place to declare it? Also, how do I reference the view from the webpage and do I still use:
cmd.SelectCommand.Parameters.Add . . .
in the page to establish the value. Anyone know a good tutorial on this. All the ones I've found were either in C# or didn't really apply. I need to know how to do this in VB. Thanks

View 1 Replies View Related

Creating View On Another Database

May 23, 2006

Hi I have two SQL server 2000 machine in my corporateHow to create a SQL View if the database table is located on another SQL server?Example :I am currently working at SQL Server "S01", but need to create a sql view to table customer on "Marketing" database located on SQL Server "S02"if the table is reside on the same machine then it's simple for me, but this customer table is not located on "S01" but located on different machine "S02"how to do this? any tutorial?thanks a lotYonscun

View 2 Replies View Related

Creating View Through Sproc

Jun 15, 2001

Hi guys,
I am trying to create a view through SP
as follows, but I am getting as error as Invalid syntax near view.
Please let me know where I am doing wrong.

Thanks,Rau
CREATE PROCEDURE PurgeReport
@intJob as int
AS
if @intjob=1
begin
CREATE VIEW Purge AS SELECT Btch_id AS Batch_ID, DLN, Process,End_DLN, Job,
Tcode
FROM Batch_HDR
end

View 2 Replies View Related

Creating Indexes On View

Nov 9, 2000

Hi

I want to know if we can create indexes on Views in SQL 7.0. If yes, then how

Thanks

alsi

View 1 Replies View Related

Help Needed For Creating View

Sep 2, 2004

Hi

Need help in writing a query. I have a table contains details about an item. Each item belongs to a group. Items have different status. If any one of the item in a group is not "Completed", then the itemgroup is in state incomplete. if all the item under the group is completed then the item group itself is completed. Now I need to create a view with itemgroup and itemstatus.
Suppose I have five records

item itemgroup status
1 1 complete
2 1 Xyz
3 2 complete
4 2 complete
5 2 complete

my view should be

itemgroup status
1 incomplete
2 complete

All the Statuses are not predefined...they get added as and when required........

Right now I am using a function. But dont want to use it for performance reasons. Would appriciate any help.


Thanks

View 6 Replies View Related

Creating A View With A New Column

Jan 31, 2014

I'm using SQL Server 2008 to solve the following exercise.

I have three Tables:
Table CUSTOMER with columns: CustomerID,Company,ContactName,Phone
Table ORDER with columns: OrderID,OrderDate,ShippedDate,ShipperID,Freight,CustomerID
Table Shipper with columns:ShipperID,Company,Phone

I need to Create a view listing the shipper and the number of unshipped orders. I have been trying to use joins or possibly subqueries, but I'm not getting a correct result for a ShipperID with NO unshipped orders.My view should looks like: ShipperID, Company, Phone, UnshippedOrders

View 4 Replies View Related

Creating A View Using A Union?

Jan 14, 2015

I am looking to create a new view by combining two tables, but i would like to create a new column in the view that identifies what table the data came from. For example I have a Table A and Table B, using a union i combined the two table but i want a new column titled Source which could be populated by A or B.

This is my code so far:

Create View Table_AB As
Select *From Table_A
Union All
Select*From Table_B

View 1 Replies View Related

Creating Columns In A View

May 10, 2006

Hi im trying to create a view that creates another column for a table and then adds the value but i cant get it could sum1 help

Create View BONUS_PAYOUTS As

Alter Table Employee
Add EMP_BONUS Decimal(6,2)

Update Employee
Set EMP_BONUS = EMP_PCT / 500000

Select Employee.EMP_LNAME, Employee.EMP_FNAME, Employee.EMP_PCT, Position.POS_TITLE, Employee.EMP_BONUS
From Employee
Inner Join Position On Employee.POS_ID = Position.POS_ID

View 5 Replies View Related

Error Creating View

Jul 25, 2007

i tried running the statement :

(1)
create view qcostcentre
as
select * from dbo.costcentre.dtblcostcentre

>ERR: Msg 208, Level 16, State 1, Procedure qcostcentre, Line 4
Invalid object name 'dbo.costcentre.dtblcostcentre'.

(2)
create view qcostcentre
as
select * from costcentre.pcusers.dbo.dtblcostcentre

>ERR: Msg 7202, Level 11, State 2, Procedure qcostcentre, Line 4
Could not find server 'costcentre' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.


note: pcusers is a user of 2 database (costcentre & datamaster) with dbo_datareader owned & role schema .

View 3 Replies View Related

Creating An Indexed View

Nov 15, 2006

I am trying to create an indexed view, on a date from a date dimension table...I am new to SQL, and I am at a loss of ideas on this one. Any help would be greatly appreciated!

Here is the Error I am given

"Msg 4513, Level 16, State 2, Procedure VEW_F_MZT_ORDER_HEADER_DAY, Line 3

Cannot schema bind view 'JJWHSE.VEW_F_MZT_ORDER_HEADER_DAY'. 'JJWHSE.VEW_F_INVC_SHIP_TO' is not schema bound.

Msg 1939, Level 16, State 1, Line 1

Cannot create index on view 'VEW_F_MZT_ORDER_HEADER' because the view is not schema bound."

Here is my code..

CREATE VIEW [JJWHSE].[VEW_F_MZT_ORDER_HEADER_DAY] WITH SCHEMABINDING

AS

SELECT TEW_D_DT.DT_KEY AS DATE_KEY,

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY AS LOC_KEY,

TEW_D_LOC.LOC_DESC AS LOC_DESC ,

TEW_D_LOC.RGN_DESC AS REGION_DESC,

TEW_D_LOC.DISTRICT_DESC AS DISTRICT_DESC,

ISNULL(SUM(VEW_F_INVC_PAY_EXT.PRORATED_NET_PRICE),0) AS CONCIERGE_FLASH,

COUNT_BIG(*) AS COUNT

FROM

JJWHSE.VEW_F_INVC_SHIP_TO VEW_F_INVC_SHIP_TO

INNER JOIN

JJWHSE.VEW_F_INVC_PAY_EXT VEW_F_INVC_PAY_EXT

ON

VEW_F_INVC_SHIP_TO.DATE_KEY = VEW_F_INVC_PAY_EXT.DATE_KEY

AND VEW_F_INVC_SHIP_TO.ORDER_NUMBER = VEW_F_INVC_PAY_EXT.ORDER_NUMBER

AND VEW_F_INVC_SHIP_TO.INVOICE_NUMBER = VEW_F_INVC_PAY_EXT.INVOICE_NUMBER

AND VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER = VEW_F_INVC_PAY_EXT.SHIP_TO_NUMBER

INNER JOIN

JJWHSE.VEW_F_INVC_DTL VEW_F_INVC_DTL

ON

VEW_F_INVC_DTL.DATE_KEY = VEW_F_INVC_SHIP_TO.DATE_KEY

AND VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_INVC_SHIP_TO.ORDER_NUMBER

AND VEW_F_INVC_DTL.INVOICE_NUMBER = VEW_F_INVC_SHIP_TO.INVOICE_NUMBER

AND VEW_F_INVC_DTL.SHIP_TO_NUMBER = VEW_F_INVC_SHIP_TO.SHIP_TO_NUMBER

AND VEW_F_INVC_DTL.LINE_NUMBER = VEW_F_INVC_PAY_EXT.LINE_NUMBER

AND VEW_F_INVC_DTL.SEQUENCE_NUMBER = VEW_F_INVC_PAY_EXT.SEQUENCE_NUMBER

AND VEW_F_INVC_DTL.NON_INVENTORY = 'N'

AND VEW_F_INVC_DTL.GIFT_CARD = 'N'

INNER JOIN

JJWHSE.VEW_F_MZT_ORDER_HEADER VEW_F_MZT_ORDER_HEADER

ON

VEW_F_INVC_DTL.ORDER_NUMBER = VEW_F_MZT_ORDER_HEADER.ORDER_NUMBER

AND VEW_F_MZT_ORDER_HEADER.ACTIVE_FLAG = 1

INNER JOIN

JJWHSE.TEW_D_DT TEW_D_DT

ON

VEW_F_INVC_DTL.DATE_KEY = TEW_D_DT.DT_KEY

INNER JOIN

JJWHSE.TEW_D_LOC TEW_D_LOC

ON

VEW_F_MZT_ORDER_HEADER.LOCATION_KEY = TEW_D_LOC.LOC_KEY

WHERE VEW_F_INVC_SHIP_TO.CHANNEL = 'I'

GROUP BY TEW_D_DT.DT_KEY , VEW_F_MZT_ORDER_HEADER.LOCATION_KEY , TEW_D_LOC.LOC_DESC ,

TEW_D_LOC.RGN_DESC , TEW_D_LOC.DISTRICT_DESC

GO

CREATE UNIQUE CLUSTERED INDEX IX_VEW_F_MZT_ORDER_HEADE_DAY ON JJWHSE.VEW_F_MZT_ORDER_HEADER ( DATE_KEY )

View 1 Replies View Related

Creating View Problem

Dec 12, 2006

Hi all. I'm new to SQL Server and I've run into a tough problem. I currently have a SQL Server database with 54 US states/territories tables. I'm working on creating a view so that I can view all the states data in a single table-like structure. I've mapped all the columns from all the different tables to their new names and made sure that all the new attributes match. When I use the Management Express tool to open the view, it executes but fails because for some reason it's chosen to map a field called 'id' into an int, rather than a varchar. Is there a way to force the view to treat the 'id' as a varchar and not an int? I can't see a way to change its type through the GUI tools.

View 7 Replies View Related

Creating Table From A View!!!

Jan 15, 2008



Hi folks!!

I have been working on a view and now I want to create a table which has not only similar columns and datatypes but also same data as I have fetched in the view.
How can I do that? Do I have to use a cursor or there is a simpler way to do it?
Please advice me here..

Can I use
Create table <tablename> as ( Select * from <viewname>) ?????

Thanks in advance for reading this.
Tanya

View 7 Replies View Related

Creating A View From ASP.Net Page (Problems)

Oct 6, 2006

Hello,I am having an error  when using the CREATE VIEW statement and trying to execute it from an ASP.net page. I get an error that says something along the lines of 'dbo' schema does not exist or you do not have permissions to access it. I have checked and the user has been granted every permission I can think of (including setting it to the DB owner), but it does not work. I am able to execute all the Select, Update, Insert statements that I wish to, and when I copy the statement into the SQL 2005 Management studio query menu it works perfectly, it just gives the error message from the ASP.net page.Here is an example (or as close as I can remember at this point) of some code I tried:myCommand.Connection = myConnectionmyCommand.Open()myCommand.CommandText = "Create View TestView as Select 1, 2"myCommand.ExecuteNonQuery()myCommand.Close() Any help would be appreciated.Thanks,StuporMan 

View 7 Replies View Related

Creating A VIEW With Multiple DATATABLES

Oct 24, 2007

I am still confounded with how to create a VIEW involving multiple DATATABLES in a DATASET.  Could someone point out a MSDN page where I can see this working and how I go about doing this?  Let me re-iterate - I want to create a VIEW that uses JOIN type statements that involves multiple DATATABLES in my loaded DATASET.

View 1 Replies View Related

Creating An Identity Column In A View

Mar 25, 2004

I need a view that will:
1) select the distinct values in a column (the easy part)
2) assign the equivalent of an identity column to each row containing the distinct value it returns.

For example TableA contains 25 records but one of the columns only contains three unique values (say, 'A','B', & 'C').

This is what I want my view to return:

1 A
2 B
3 C

In other words, I need the view to assign the 1, 2 and 3 to the individual rows.

Thanks in advance for any pointers on this one.

View 4 Replies View Related

Creating A Large Dynamic View

Oct 11, 2007

I have a procedure that creates a large dynamic view of several tables. The view is a union view of up to 15 tables. The table names are all <name>_DDMM where name is the standard table name and ddmm is the day and month of the tables data. The tables are created by a software supplied by another company, so I can not ensure that the tables will always have exactly the same fields or number of fields. Sometimes the company will add more fields to the tables in thier updates. So, I have to include the field names in the SQL exec command to create the query. This makes for a very long exec command and depending on the number of tables it needs to include, it can require upwards of a 16,000 character string. Obviously, this can't work, so I had to break up the variable in order to create the procedure. However, I'm wondering if there isn't a better method than creating three different 8000 varchar variables and having overflow write to the next variable in line. Especially if the number of tables needs to be expanded, it could be a problem. Is there a better way to run a create view exec command on a large number of characters?

EDIT: Changed the title to read Procedurally generating a large view.

View 14 Replies View Related

Problem Creating View For Cube

Jan 30, 2004

Anyone see a problem with creating this view for a cube??

The error I get seems to be in line 8 with the sum function, but I see nothing wrong with it.

CREATE VIEW CarCube AS
SELECT CASE WHEN (GROUPING(CarType) = 1) THEN 'ALL'
ELSE ISNULL(CarType, 'Not Known')
END AS CarType,
CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'
ELSE ISNULL(Color, 'Not Known')
END AS Color
SUM(Amount) AS TotalAmt
FROM Inventory
GROUP BY CarType, Color WITH CUBE

View 3 Replies View Related

Problem Creating TRIGGER On A VIEW

Feb 11, 2004

Hello,

I have a problem that definitely has me stumped.

I have a view that looks at data in a different database. Some of the fields in the view are updateable and some are not. I am trying to create a trigger against the view that will allow me to audit the updates into an audit table. I am having problems when trying to execute the CREATE TRIGGER statement.

I keep getting the message...

Server: Msg 208, Level 16, State 4, Procedure updDocInfo, Line 1
Invalid object name 'vwDC_DocInfo'.

Where vwDC_DocInfo is the name of the view.

Does anyone have any idea why I might be getting this error? The VIEW definitely does exist and I am executing the script in the same database as the view.

The script is included below...


CREATE TRIGGER updDocInfo
ON [vwDC_DocInfo]
FOR UPDATE AS

DECLARE @ModifiedDate AS DATETIME
SELECT @ModifiedDate = GETDATE()

-- Audit OLD record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM DELETED DEL


-- Audit NEW record.
INSERT tblAudit_DC_DocInfo
SELECT
0 AS AuditType,
ItemID,
Comment,
VersionComment,
CheckedOut,
Title,
BaseParagonDocumentNumber,
Author,
ClientDocumentNumber,
ClientDocumentType,
ClientJobNumber,
[Module],
Unit,
SequenceNumber,
RevisionDate,
ApprovedBy,
CheckedDepartmentManager,
CheckedLeadEngineerDesigner,
IssueType,
RevisedByDesigner,
RevisedByEngineer,
RevisionCode,
HSECheck,
CurrentVersionNumber,
CurrentVersionDate,
USER AS ChangedByUser,
@ModifiedDate AS DateChanged
FROM INSERTED INS

View 4 Replies View Related

Creating A View In A Stored Procedure

Apr 2, 2004

Hi Everyone

Im trying to create a view from within a stored procedure and are having problems. Is it possible to do this? And if so, how? I've been trying with the code below.

CREATE PROC upProcName AS

DECLARE @Variable varchar(50)

CREATE VIEW vwName AS

SELECT DISTINCT Table1.*, Table2.*
FROM dbo.Table1
INNER JOIN dbo.Table2 AS BUG
ON Table1.Col1 = Table2.Col1
WHERE LI.accname = @Variable

GO

Any Thoughts ideas would be great

Cheers

View 7 Replies View Related

Creating SQL View - Syntax Is Wrong

Apr 14, 2004

We are creating a view in SQL to format data to DTS to another database. Here is the section of the view which does not work -- eliminating this section from the query allows the view to be created:

CASE APTran.LineNbr
WHEN < '0' THEN 'O'
--Offsetting Document (or transaction line as we have (AP Liab. AP Cash Entry).
WHEN > '0' THEN 'D'
--Regular Document (or transaction line as we have (This is either a Debit or a Credit line not AP Liab. AP Cash Entry).
ELSE 'E'
END AS JED_DIST_OR_OFF,


I am attaching the whole query stmt here.

Thanks very much for all assistance.

View 6 Replies View Related

SQL 2012 :: Creating View With Calculations?

Aug 11, 2014

The view I am trying to create (crvKCLPRInsJob) is using another view (brvPRInsJob). I can provide a sample data, that's easy. I couldn't figure out how to get all the other info required.

I created the view below.
I created the following custom fields.
IncCode723Hours
IncCode748Hours
GrossEarn723
GrossEarn748

[Code]....

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved