I am creating a view in SQL using the View tab in the database and adding the table and the fields I need. For criteria I need to have 28 'or' s . How do I insert more columns on the end of my view ?
View 2 RepliesPrior to insert (into #JobListTable) I need to change the value of the 't2.plant_id' column (from #EquentialJobListTable). This column will have a value of 110, 300 or 320. If 110 then value should be SBGB2, if 300 then values should be RGWP and if 320 value should be RGWP. Is this possible to do?
INSERT#JobListTable
(
job_date,
job_number,
job_phase,
qty_received,
plant_id
)
SELECTt2.job_date,
t2.job_number,
t2.job_phase,
t2.qty_received,
t2.plant_id
FROM#EquentialJobListTable AS t2
LEFT JOIN#JobListTable AS t1 ON t1.job_date = t2.job_date
AND t1.job_number = t2.job_number
AND t1.job_phase = t2.job_phase
WHEREt1.job_date IS NULL
RESOLVED RESOLVED RESOLVED
DOH! When I rebuilt the OS, I changed the data partition ID's, and didn't remember to change them in the connetion strings
Not great, but better than continuing to bang head against wall.
Would like to say to MS appreciators that the VS2005 reinstall actually went quite well, considering.
===========================================================================
I built an app, works fine. Had the development environment working fine (VS2005 (SP0) and SQL Server Express). It had been stable for months, no problem.
Then I had to reinstall XP Pro...(finally threw too many conflicting programs into it, I guess...)
I managed to get VS2005 reinstalled ok. However, when I opened and compiled my existing app in VS2005, using Cassini, I got the dreaded error:
An attempt to attach an auto-named database for file <file string> failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
I also get this error for the compiled copy of the app on the same machine, running on IIS. (Where it also previously worked fine).
The Connection strings are all of the form
connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=(specific file string).MDF;Integrated Security=True;User Instance=True"
I appreciate that there are zillions of posts on this issue. I'm hoping that someone might know why this should happen in this particular case.
The thing that's different about this case is that the app was working fine; I have the app deployed on a demo machine and also on a remote server as well as on the development machine.
The only thing that changed is that there was this fresh install of SQL Server Express.
I tried using >> sp_configure user instances enabled','1';RECONFIGURE <<< in SSMSEE, which took, but didn't help.
So something is different in SSE. Since the app is stable everywhere else, I REALLY don't want to go through and adjust the connection strings...MUCH better to figure out what SSE needs to be happy with the existing connection strings...as it was before.
Any suggestions would be appreciated.
Thanks!
EDIT----TRIED SOME THINGS:
Changed folder and file permissions to allow ASPNET full access to the .mdf's and log files (though never had to do this before on the development machine.) No change...
Can see the table contents within the .mdf through SSMSE, and also through Server Explorer in VS2005.
So because the problem happens with both IIS and Cassini, I'm assuming it's got to be an issue between ASPNET and SSE.
EDIT --- TRIED SOME MORE THINGS:
I dimly remember that when I had this problem before, it was that the error message was too "dumb". It wasn't my file that had the permissions problems, it was that the SSE System Databases had to have permissions with respect to ASPNET.
I tried to set that up but I'm a complete newb on this area, so I don't know if I did it right. In SSMSE I created a Login for MyComputer/ASPNET, and gave it all permissions and roles, and then made it an owner for the four system databases...but no joy.
Any pointers on this last piece would be helpful.
EDIT - MORE INFO
The error details
[SqlException (0x80131904): An attempt to attach an auto-named database for file <filestring>.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739123
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +170
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +349
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +181
I have two tables, one a data table, the other a product table. I want to perform a join on the two tables with values distributed into columns based on the value in the month field.
data_table
product_code month value
350 1 10
350 2 20
350 3 30
product_table
product_code profit_center
350 4520
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 20 30
My current query gives the following result
result_view
product_code profit_center mon1 mon2 mon3
350 4520 10 0 0
350 4520 0 20 0
350 4520 0 0 30
Any direction toward a solution would be appreciated. Am using SS2005.
For an ASP.net project, I have had a DropDownList with a static ArrayList.The ArrayList will be defined from a View, where there is no Identity PK.
I also have used cbxDropDownListName.SelectedIndex to add new data toa table, where an Indetity PK is used to reference the ArrayList.
I am wondering how can I add an identity PK to my view?
TIA,Jeffrey
I have a SQL table that consists of columns A, B and C. I am trying to construct a view consisting of all columns (i.e. A, B, C) and a computed column. This computed column has the following logic:
If B is blank or null then NewColumn = A + ' - ' C
else
NewColumn = A + ' - ' + B
I am just wondering how the SQL statement should look like....
Is there a way of changing the value of a column in a view? For example, I have a table with defect code and a description (other columns as well). In a view, I would like to see only the defect code and the description, but if the code is > 90, I would like to define what the description is.
TIA
Jennifer
I have a view that has 2 columns. The first column is associated with a function for the Units. The second column calculates the Market Value:
View
====
Col1: Unit = get_number_of_units()
Col2: MV = get_number_of_units() * get_unit_value()
I need to call get_number_of_units() twice in the view. Is it possible to changes Col2 to something like: MV = Col1 * get_unit_value()?
Is get_unit_value() being called if I do Select Col1 from View?
Thanks.
I have just started learning PHP and MS SQL. I was having a problem viewing the database (column entries) in the MS SQL server managemnet studio. I can see the column names but I cannot see the entries that I save in it.
Ne help pls !!!
Any yes the data is saved because when I use the "select" command I can print the data.
*first issue
how to add a calculated column in a view such that this calculated column will be calculated from the oraginal columns
create view vw1
as
select tab.col1,tab.col2 from
from tab
and i want to add a column that contains the result of a comparison between col1 and col2 (col1<col2) such that the values of the column will be true,false
thanx
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
how can i reference a column in a view.
My duplicate check runs against the entire column. There might be another test field that has the same value, and that might be valid, so im trying to make sure that when i do the check, i am checking only against the serial number field and not all test fields.
Not sure if I am approaching this correctly. I can get name, address, zip code from a table via a VIEW. But the problem is I need to create another column called "status" in the same view that is based on "customer_end_date" if end_date> GETDATE() then 'P'(pick-up) and end_date< GETDATE () then 'I'(install). Using a stored procedure this is easy...but how would I do this in a VIEW?
View 6 Replies View RelatedHello all,
I'd like to create a view which shows all specific column values
(null values for example)
as calculated values from another column,
if for example i have the table:
---------------
| col1 | col2 |
|------|------|
| 123 | null |
| 126 | 9 |
---------------
i would like the view to be:
---------------
| col1 | col2 |
|------|------|
| 123 | 6 | ----> 6, since it's the sum of col1 digits 1+2+3
| 126 | 9 |
---------------
I don't really know how to do that, although i'm quite sure it's possible, any help will be appreciated,
Best Regards,
pitt
I want to find columns name associated with view. For example; can you please help me to write query? I need to write for several tables.
TableName Column View
---------- -------- -------
Employee EmpId v_EmployeeDetails
Employee Firstname v_EmployeeDetails
Employee Lastname --
Employee SSN v_PersonalD
Employee DOB --
___________________________________________________
--just example
go
create view v_EmployeeDetails
as
select EmpId,Firstname from Employee
go
go
create view v_PersonalD
as
select SSN from Employee
Hi,I have a view that looks something like this -CREATE VIEW myview AS SELECTmyudf(col1) as col1, col2, col3FROM mytableThe view has an 'INSTEAD OF' trigger on it to do the correct thing oninsert. When I run the bcp, it runs successfully, but the valueinserted into col1 is always NULL. I modified my trigger to get abetter idea of what was happening, and when I look at the values ofINSERTED.col1, they are all null. It appears that bcp is setting thecolumn value to null, presumable because the view definintion for thiscolumn is a derived column.Does any one know a way around this?Thanks!
View 5 Replies View Related
Hello,
I am going to change a column name YYZ in a VIEW because of typo. It should be named as YYY.
What is best scenario?
Thanks
Hi,
I keep running into the following Error message when I try to execute the query below. Can anyone please give me an idea of how I can solve this issue and keep my data??
CREATE VIEW [dbo].[view_mCover]
AS
SELECT dbo.tProduct.intProductId AS dblCoverId, dbo.tProduct.strProductName AS strCoverName,strProductCode AS strCoverCode, dbo.tProduct.strProdPictUrl AS strCoverThumbnailSrc,
dbo.tProduct.intSubCategoryId AS dblCoverCategoryId, dbo.tProduct.strProdPictUrl AS strCoverSrc, dbo.tProduct.floatPrice AS floatCoverCost,
dbo.tProduct.intStockQuantity AS intProductStock, dbo.tProduct.isInDesignTool as isInDesignTool
FROM dbo.tProduct INNER JOIN
dbo.tCategory ON dbo.tProduct.intCategoryId = dbo.tCategory.intCategoryId
WHERE (dbo.tCategory.intCategoryId = 6) AND (dbo.tProduct.isDeleted = 0)
ERROR message
===============
Error Message: Column Name 'StrProdPictURL' appears more than once in the result column list
Thanks in Advance...
DollarJunkie
I have a view that shows payment amount, payment date. I need to add a column to the view that shows what fiscal year the payment belongs to.
How can this be done?
I do have a table that has the fiscal start and end in it, tblGlobal with fields FiscalYearStart and FiscalYearEnd.
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.
I have a view where the results would be like this.(userid,name,rolekey are my col names with data)
userid name rolekey
test1 tname rolekey1
test1 tname rolekey2
test1 tname rolekey3
is this possible to retireve data from view where i need only userid with rolekey1.? tried with a function but its taking more time? any options in doing it in the view itself?
Hi all!
I'm trying to script a view that does a simple query to some tables.
The catch is 2 of the columns are created on run time.
The first column is filled with a calculation with values of other columns. The 2nd column I would like to fill with the ranking of this calculated column
Example: (imagine caculatedcol is Sum(Col1+Col2+Col3)
Col1 Col2 Col3 CalculatedCol Ranking
10 10 10 30 1
9 9 9 27 2
8 8 8 24 3
7 7 7 21 4
How can I get the Ranking column filled based on the calculated column?
I'm desperate.
Thanks for any help.
---
Mário Ramos
Hey everyone,
I have a view that shows the email address and first name of a Membership table. I need to add a column to this view that doesn't have a corresponding column in the original table that has some default value.
i.e. the view currently shows:
John john@something.com
I need it to say:
John john@something.com EmailBlast
The third column will always have that value.
Anyone have a suggestion?
Thanks in advance!
--J
Dear All,I'm facing a problem with creating a view.I have a table withfollowing descriptionTab1------------------------------------Col1 col2---- -----Val1 TomVal2 CatVal3 JackVal4 JimNow I'm trying to create a view with following information fromTab1.The output should beTab1View--------------------------------------Col1 Col2 Col3---- ---- -----1 Val1 Tom2 Val2 Cat3 Val3 Jack4 Val4 Jim---------------------------------------So the create view statement add this column dynamically in thisresult set.If any body has any doubt on this query please let me inform.RegardsArijit Chatterjee
View 1 Replies View RelatedUsing SQL Server 2005. Defined partitioned view with computed column. Computed column was a constant varchar. Ran a SELECT. According to Query Execution Plan, SQL did recognize the computed column as the partitioning column and used it to optimize the query.
However MSDN says a computed column cannot be used as the partitioning column.
Could someone from MS clarify?
Hello,
please enlighten me regarding an issue with partitioned view... There are 3 tables in my DB of a similar structure:
CREATE TABLE Table1 (value1 varchar(1))
CREATE TABLE Table2 (value1 varchar(1))
CREATE TABLE Table3 (value1 varchar(1))
INSERT INTO Table1 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
INSERT INTO Table2 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
INSERT INTO Table3 (value1)
SELECT 'a' UNION SELECT 'b' UNION SELECT 'c'
As sometimes we need to access all data from these tables, a view has been created:
CREATE VIEW AllData AS
SELECT value1, '1' as table_id from Table1
UNION ALL
SELECT value1, '2' as table_id from Table2
UNION ALL
SELECT value1, '3' as table_id from Table3
The problem is that while running a query like
SELECT * from AllData WHERE value1 = 'a' and table_id = '3'
I see a table scan being performed on all 3 tables, not just table3 - i.e optimisation engine doesn't care for my table_id computed column and for that fact that required data is located ONLY in Table3.
Is there any way to force optimiser to consider this column andrrebuild a plan? If not - how can I rebuild a view (I can't modify tables) to achieve that? Maybe create an index for a view?
Thanks in advance. RTFM and search don't seem to clarify this for me...
Thanks a lot, Greg!
Well, I have another question.
In SQL-2005, till SP2 version, there is a bug, dialed with treating an identity column in a view as an ordinary int column, when the compatibility verison of the DB is set to 80: http://support.microsoft.com/kb/920974
In SQL Server 2005 Express Edition CTP (since version 9.00.3027), which is available to download from Microsoft site, this bug was fixed.
Please tell if you know, how can I get an evaluation copy of any other edition of this version, where merge publication feature could be tested?
Is there any way to get the base table/column from a view by supplying the view name/column name_in_view? I know about INFORMATION_SCHEMA.VIEW_COLUMN_USAGE, but it doesn't always work. Try it on a regular database, not pubs. Any help is appreciated!
View 1 Replies View RelatedHi Guys
I'm trying to create a view in SQL Server to display only one copy of all records in a table, where some of the records are exact matches to other records (except for an ID autoincrement field)
I have the following code that displays distinct titles:
sql Code:
Original
- sql Code
SELECT DISTINCT DocTitle
FROM dbo.TBL_TABLE_NAME
SELECT DISTINCT DocTitleFROM dbo.TBL_TABLE_NAME
But when i use this code (adding one field to query), it displays all the duplicate records also, which I do not want:
sql Code:
Original
- sql Code
SELECT DISTINCT DocTitle, ID
FROM dbo.TBL_TABLE_NAME
SELECT DISTINCT DocTitle, IDFROM dbo.TBL_TABLE_NAME
I want to get the distinct results for DocTitle, with 5 other columns added to the query so they can be displayed in a web page list.
The query I want is something like this, but I can not get it to work:
sql Code:
Original
- sql Code
SELECT DISTINCT DocTitle, ID, FirstName, LastName, Company, DocDescription, DocFile
FROM dbo.TBL_TABLE_NAME
ORDER BY DocTitle
SELECT DISTINCT DocTitle, ID, FirstName, LastName, Company, DocDescription, DocFileFROM dbo.TBL_TABLE_NAMEORDER BY DocTitle
I know this query works from an MS Access DB, but I need it to work from SQL Server.
I am using SQL Server 2000
Any help is greatly appreciated.
Alex
Hi,
I have a table of results for various measured quantites and i need to turn this into a view. Only problem is i need to seperate the measured quantities and their respective values into seperate columns.
At the moment I have something like:
Quantity : Value
--------------------
Quantity 1 : 0.12
Quantity 1 : 0.56
Quantity 2 : 2.36
Quantity 2 : 5.34
Quantity 2 : 4.13
Quantity 3 : 10
Quantity 3 : 15
and I need a view that looks like:
Quantity 1 : Quantity 2 : Quantity 3
-------------------------------------
0.12 : 2.36 : 10
0.56 : 5.34 : 15
null(?) : 4.13 : null(?)
I've tried using pivots but they don't seem to help
Any ideas?
I have a view. When I select data, I have to use double quotation on selected column name, for instance:
select "assy_no" from myView;
if I try
select assy_no from myView
I will get error: ORA-00904: "ASSY_NO": invalid identifier
When I query on table, double quotation is not required.
Why the column name on view requires double quotation?
I am creating a view for the table:
bellus=# select * from host_application_definition;
id | type_value | connection_value | group_value | application_value | host
----+------------+------------------+-------------+-------------------+----
From the table meta_host_types;
id | value | types | name
----+-------+-----------+--------
1 | agm | host-type | Rencid
I would like to seperate value into type_value and connection_value, because it holds both values.
Any tip?
bellus=# d host_application_definition;
Table "public.host_application_definition"
Column | Type | Modifiers
-------------------+---------+--------------------------------------------------------------------------
id | integer | not null default nextval('host_application_definition_id_seq'::regclass)
type_value | integer |
connection_value | integer |
group_value | integer |
application_value | integer |
host | integer |
Indexes:
"host_application_definition_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"host_application_definition_application_value_fkey" FOREIGN KEY (application_value) REFERENCES appsmarteye(id)
"host_application_definition_connection_value_fkey" FOREIGN KEY (connection_value) REFERENCES meta_host_types(id)
"host_application_definition_group_value_fkey" FOREIGN KEY (group_value) REFERENCES meta_host_grouptypes(id)
"host_application_definition_host_fkey" FOREIGN KEY (host) REFERENCES master_hosts(id)
"host_application_definition_type_value_fkey" FOREIGN KEY (type_value) REFERENCES meta_host_types(id)
d meta_host_types;
Table "public.meta_host_types"
Column | Type | Modifiers
-------------+---------+---------------------------------------------------------------------
id | integer | not null default nextval('meta_types_application_id_seq'::regclass)
application | integer |
value | text |
comments | text |
types | text |
Indexes:
"meta_types_application_pkey" PRIMARY KEY, btree (id)
"meta_host_types_id_key" UNIQUE, btree (id)
"meta_host_types_value_key" UNIQUE, btree (value, application)
"meta_host_types_value_key1" UNIQUE, btree (value)
Foreign-key constraints:
"meta_types_application_application_fkey" FOREIGN KEY (application) REFERENCES appsmarteye(id)
Hi All,I have two tables, one is about member infomations, the other is thecatergoriesmember_info(id,name,email,phone)member_categories(id,category)how can create a view like this (id, name, category1, category2,category3) with high performance?Thanks in advance.Joshua
View 2 Replies View Related