Need Query Help Re:Create &"Name&" Field & Populate
Jan 25, 2008
Guys,
New to SQL and I'm trying to create a new field, entitled “Name� and see all employee names from my "Employee" table using the following format:
LastName, FirstName, jobcode, emailaddress and hire date.
after much effort my code is as follows:
CREATE VIEW 'Name'
AS
SELECT LastName, FirstName, JobID, Email, Hiredate AS Relationship
FROM 'Name'
Create View 'Name' as Select LastName, FirstName, JobID, Email, Hiredate From EMPLOYEE
Group By LastName, FirstName, JobID, Email, Hiredate Order By LastName
But i'm still getting the following errors:
Msg 111, Level 15, State 1, Line 196
'CREATE VIEW' must be the first statement in a query batch.
Msg 102, Level 15, State 1, Line 199
Incorrect syntax near 'Name'.
Msg 111, Level 15, State 1, Line 200
'CREATE VIEW' must be the first statement in a query batch.
Any assistance is greatly appreciated
View 6 Replies
ADVERTISEMENT
Oct 31, 2006
Hi folks:
I have added a new field to an established table, and am having trouble figuring out how to populate its values:
Two tables are involved: Jobs and Parts
There is a one-to-one relationship between each JobID and its PartID
Each Part has a PartPrice. Now I have added to the Jobs table a JobPrice field. Whenever a new Job is created, JobPrice takes the current value of its Part's PartPrice. Each Job's JobPrice remains constant for historical purposes, while the PartPrice may fluctuate at my client's whim.
The trouble is that the Jobs table is 10k+ records large, and I need to fill the JobPrice values. I am at a loss. I know how to commit the update one record at a time:
UPDATE Jobs
SET JobPrice = (SELECT PartPrice FROM Parts WHERE (PartID = [the part in question]))
WHERE (JobID = [the job in question])
My SQL knowledge is limited to basic statements that I use in my .NET work, and I rarely create anything in Management Studio more elaborate than what you see above. Many thanks for your time,
Matt
View 3 Replies
View Related
May 20, 2008
the subject pretty much says it all, I want to be able to do the following in in VB.net code):
{[If [table with this name] already exists [in this sql database] then [ don't create another one] else [create it and populate it with these values]}
How would I do this?
View 3 Replies
View Related
Jul 20, 2005
I have two tables: TestA and TestB. Both tables have 3 fields: ID,Name, and RunDate. I need to create a query which will join the twotables first on Name but then I need to match up the RunDates eventhough the RunDates won't be the same.CREATE TABLE TestA (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)CREATE TABLE TestB (ID INT IDENTITY, Name VARCHAR(255), RunDateDATETIME)INSERT INTO TestA VALUES ('Account 1', '9/1/2004 12:00PM')INSERT INTO TestB VALUES ('Account 1', '9/1/2004 12:15PM')INSERT INTO TestA VALUES ('Account 1', '9/2/2004 1:00PM')INSERT INTO TestB VALUES ('Account 1', '9/2/2004 1:15PM')INSERT INTO TestA VALUES ('Account 1', '9/3/2004 3:00PM')INSERT INTO TestA VALUES ('Account 2', '9/5/2004 4:00PM')INSERT INTO TestB VALUES ('Account 2', '9/5/2004 4:15PM')Here's a common scenario:User updates TestA data for Account 1 on 9/1/2004 at 12:00pm. Thenthe user updates TestB data for Account 1, 15 minutes later. I wantthese two records to match. The user must always update TestA databefore they update TestB data. Therefore, there might be more rows inTestA then in TestBHere's what the results should look like for the above data.Name TestA Date TestB Date---- ---------- ----------Account 1 9/1/2004 12:00pm 9/1/2004 12:15PMAccount 1 9/2/2004 1:00pm 9/2/2004 1:15PMAccount 1 9/3/2004 3:00pm (NULL)Account 2 9/5/2004 4:00pm 9/5/2004 4:15PMAny help would be much appreciated!!!!
View 2 Replies
View Related
Dec 22, 2006
I'm very new to SQL server and can use some help. MyTable has ColumnA, which contains strings composed of 1 to 4 numeric characters (0 thru 9) followed by alphabetic characters. For example, "53ASDF". In my query, I need to create ColumnB, which takes the numeric prefix from ColumnA's string and prepends it with zeros, if necessary, to create a string of exactly 4 numeric characters. For example, I could get the following result:
ColA ColB
"6abc" "0006"
"457def" "0457"
"7232hij" "7232"
I have implemented a temporary solution using a CASE statement:
SELECT ColA, ColB =
CASE
WHEN ISNUMERIC(LEFT(ColA, 4)) = 1 THEN (LEFT(ColA, 4))
WHEN ISNUMERIC(LEFT(ColA, 3)) = 1 THEN '0' + (LEFT(ColA, 3))
WHEN ISNUMERIC(LEFT(ColA, 2)) = 1 THEN '00' + (LEFT(ColA, 2))
WHEN ISNUMERIC(LEFT(ColA, 1)) = 1 THEN '000' + (LEFT(ColA, 1))
ELSE ''
END
FROM MyTable
Because of additional complexities, I need to implement the solution with a loop instead of a CASE statement. Can someone please describe such a solution?
I'm very confused about how variables work in SQL Server, but made an attempt to implement a solution. Hopefully, someone can make corrections and describe how to use it with a SELECT statement. I would greatly appreciate any suggestions. This is what I started with:
DECLARE @ColBstring char(4)
DECLARE @num int
SET @ColBstring = ''
SET num = 1;
-- Get the numeric prefix from ColumnA's string
WHILE(isnumeric(substring(colA, 1, num)) = 1)
@ColBstring = (substring(colA, 1, num)
num = num + 1
-- Prepend the ColumnB string with zeros
WHILE(LEN(@ColBstring) < 4)
@ColBstring = '0' + @ColBstring
Thanks for any help,
Mike
View 1 Replies
View Related
Apr 25, 2006
I need to know who is entering data in my SQL database. I use Access Project as a front end and would like to know if it is possible to have an 'ID' field auto-populate with a user NT Login. For example, if user checks a check-box or enters data in field, I would like an ID field next to the checkbox or data field to populate with his user ID.
Any assistance is much appreciated!
Thanks.
View 5 Replies
View Related
Aug 20, 2007
I have 2 tables in a parent->child relationship. The primary key for the parent table is moving to a new column (that is an Identity). I'd like to relink the tables using the identity field.
Here's the structure:
Parent
refid int (Identity) - NEW PK
orderid char(9) - OLD PK
Child
orderdetailid int - PK
orderid char(9) - Old link to parent
refid int - New link to parent
I've populated refid in the parent. I just need to populate the refid column in the child. I have a VFP background. In VFP I would have looped through the child, found the matching record in the parent, then transfered the data over. I don' t have a clue how to do this in TSQL.
Brian
View 4 Replies
View Related
Aug 9, 2006
Hi,I have a 2 tables called 1.tblRisk which consists of Ref(pk), subject, status, staff & Dept(fk)2.tblDept which has Ref(Pk) & DepartmentHow do i get it to populate Department, when tblRisk Ref's Dept matches the Ref in tblDept i am using SQL Server 2000best regards
View 1 Replies
View Related
Aug 10, 2006
I am fairly new to SQL programming and would like to see if you can provide me with some direction on a small project I would like to accomplish. I have experience working with databases (mainly in with different versions of FoxPro) and reporting with Crystal reports, etc. but not much with SQL.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and delete the assigned codes from the storage DB or mark them as used possibly the book code.
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL) and date assigned. The original idea was to put a Y in the used field and insert the date the code was assigned to a book, but this is not entirely necessary, we have 90k unused codes and it really does not matter if they are simply deleted from the DB.
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine (something like the list below) but what I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB
Go to the books DB and update it with this classcode
Go back to the classcode DB and mark the used record with a Y in the Used field
Loop though the books DB and perform the same operation until no more records exist with an empty classcode field.
I will greatly appreciate your feedback you can provide.
Mike.
UPDATE [dbo].[Books]
SET dbo.Books.Classcode = [dbo].[classcode].[classcode]
From dbo.classcode
On [dbo].[Books].[classcode]= [NULL]
Populate DB field from list in “storage� DB
Mike Campos - CA
View 2 Replies
View Related
Mar 15, 2008
record.
For example. I have a table with fields named "From" and "To" which track drill hole measurements. When I add a new record I would like it to autopopulate the "To" field with the "From" value from the previous record.
Example Recordset
Record 1, From=0, To=500, Lithcode=AB1
Record 2, From=500, To=1000, Lithcode=CR4
Record 3, From=1000<- trying to figure out how to auto populate previous record's from value here
TIA
Todd
View 6 Replies
View Related
Mar 16, 2008
Was wondering if there is easy anyway to autopopulate a field by pulling a value from a field in the previous record?
For example, I have a table with fields name "Dist_From" and "Dist_To". When I add a new record I would like it to populate the "Dist_From" field with the "Dist_to" value from the previous record.
TIA
Todd
View 3 Replies
View Related
May 8, 2006
Sorry guys I know this is easy but I've been looking for about an hour for a straight forward explanation.
I want to store a user's wish list while they browse the site, then they can send me an enquiry populated with their choices.
Basically, a shopping cart!
I thought of using session variables and string manipulations but I am more comfortable with DB queries.
a simple 4 column table would cover everything.
SQL server and VBScript
Thanks
M
View 4 Replies
View Related
Jul 29, 2015
I'm looking to see if there is a way to populate starting number for the sequence from a max value of a table.
CREATE SEQUENCE test_seq
AS [int]
START WITH (select max(col1)+1000 from table1)
INCREMENT BY 1
MINVALUE 1
MAXVALUE 2147483647
CACHE
GO
View 3 Replies
View Related
Nov 27, 2006
Currently I have the following stored procedure which simply adds a new row in my SQL Express 2005. What I want is that -1). before inserting the record find out the new ID (primary key) value. (ID is automatically a sequential integer generated by SQL Server)2). and set COMPANY_ID = (new) ID Any thoughts? Thanks ALTER PROCEDURE usp_tbl_Company_Insert @Company_ID int, @Name varchar(200), AS<FIND THE NEW ID of the new row in the database> @Company_ID = (new ID) INSERT INTO tbl_Company (Company_ID, Name,)VALUES (@Company_ID, @Name)
View 1 Replies
View Related
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
View 2 Replies
View Related
Apr 9, 2014
Is this a correct syntax to populate a field name PHONES in my CUSTOMERS TABLE
case when(d.phone = (SELECT phone from CALLS where exists(select home_phone, mobile, toll_free from CALLS2) Then 1 END 'PHONES'
View 1 Replies
View Related
Nov 22, 2015
I am working with a vendor application called Cisco Unified Attendant Console - it operates on a Windows server with a SQL express database. The CUPs function of the application needs to reference a "contact" field with only the user portion of the contact's email address - generally, the contact's User ID will match the user portion of their email address, however, for this customer it does not (they use the employee number as the User ID and firstname.lastname as user portion of the email address.
Writing a script to accomplish the following:
The dbo.Contact_Properties table of the ATTCFG database has the following fields that we can work with:
Â
- First_Name
 - Last_Name
 - Email
 - User_Field_2
 - Contact_Unique_Ref (appears to be the field that ties all other contact tables together ?)
Is it possible to create a script that could run daily to either, combine the First_Name and Last_Name fields (with a period between) and populate the User_Field_2 field for each user, or populate the User_Field_2 field with everything before the @ symbol in the Email field for each user?
Also, by default the servers that this application is installed on does not have SQL Server Management Studio installed - is it possible to accomplish with PowerShell script triggered from the Windows Scheduler?
View 5 Replies
View Related
Jul 20, 2005
I am trying to populate a field in a SQL table based on the valuesreturned from using substring on a text field.Example:Field Name = RecNumField Value = 024071023The 7th and 8th character of this number is the year. I am able toget those digits by saying substring(recnum,7,2) and I get '02'. Nowwhat I need to do is determine if this is >= 50 then concatenate a'19' to the front of it or if it is less that '50' concatenate a '20'.This particular example should return '2002'. Then I want to take theresult of this and populate a field called TaxYear.Any help would be greatly apprecaietd.Mark
View 2 Replies
View Related
Jul 19, 2006
hi all, can anyone help me?I am a relative newbie to sql server and I am more familiar withEnterprise Manager than QA. I have made many many access databasesthough. I am making an asp.net application where by there are a setnumber of users, about 80, each one logs in and manages informationwithin their department.To get them started a manager has written 10 different hazards thatwill apply to all of the departments, and he has written consequencesand controls for the hazards. Each department must have thisinformation as each will manage and deal with them differentlyThe hazard information is stored in a main 'hazards' table, and theconsequences and controls are stored in related tables linked by the'hazardID' from the main table to a foreign key 'hazardID' in therelated tablesWhat i want to know is if there is a relatively simple way of using aquery to populate the 10 hazards to each department, and to alsoinclude the related table links, i dont mind renaming the departmentsnames to match each hazard, but i do not want to have to relink therelated tables manuallyIf anyone can give me any advice to get me started i will be incrediblygratefulthank youTable information is belowHazards------------HazardID - identity key fieldHazard - varcharDepartment - varcharConsequences----------------------ConsequenceID - identity key fieldHazardID - FKConsequence - varcharControls------------ControlID - identity key fieldHazardID - FKControl - varchardwight
View 3 Replies
View Related
Aug 25, 2006
Mike writes "I am trying to learn SQL programming and would like to see if you can provide me with some direction on a small project.
The project that I would like to work on has 2 SQL DBs and entails looking in a database for empty CLASSCODE fields, populate these fields from a “storage� database that contains un-used CLASSCODE codes and tag the assigned codes from the storage DB as used (“Y�)
The main DB called books has the following relevant fields, bookid, bookname, CLASSCODE (besides a number of other fields which are irrelevant to what I need to do). The “storage DB called classcode has 3 fields, CLASSCODE, Used (single character intended to contain either a Y or NULL).
My idea is to create a script to accomplish this that I can run periodically when new books have been added to the DB. I can setup the update routine I am stuck with is trying to do the following:
Pick the first record (classcode) from the classcode DB
Go to the books DB and update the first record that does not contain a code with the above classcode
Go back to the classcode DB and mark the used record with a Y in the Used field
Loop though the books DB and perform the same operation until no more records exist with an empty classcode field. The existing data would look something like:
dbo.Books
BookidClasscode
A123400001
B123400002
C123400003
D1234
E1234
dbo.classcode
classcodeUsedDate
00001Y5/17/2006 12:00:00 AM
00002Y5/17/2006 12:00:00 AM
00003Y5/17/2006 12:00:00 AM
00004
00005
00006
00007
00008
I need to pick the next classcode.classcode record that does not have a Y in the Used field and apply it to the first books.classcode record that has a NULL classcode record and then mark the used classcode.classcode record with a "Y"
Hopefully this will make some sense, I will greatly appreciate any feedback.
Mike."
View 1 Replies
View Related
Nov 29, 2006
How can I save the text of a rich text box in a sql express 2005 datatable ?
Of course I'd like to save the string and the format of the text (bold, color etc...).
Which column type I have to use for this RTF field ?
Thank you.
View 3 Replies
View Related
May 6, 2008
Are Primary Key fields automatically indexed, or do you have to create a seperate index for a PK in order for it to be indexed? I'm using SQL Server 2005.
View 7 Replies
View Related
Nov 10, 2005
I have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
TIA
View 1 Replies
View Related
Apr 11, 2006
I have the following fields in table A:
GL_ID|GL_Name_VC | Amount |Period_TI|Year_SI
===================================================
1000| Inventory| 8,000.00 | 01 | 2005
===================================================
1000| Inventory| -3,000.00 | 02 | 2005
===================================================
1000| Inventory| 5,000.00 | 02 | 2005
===================================================
the fields above have the following datatype:
Fields | Datatype
===================================
GL_ID | Integer
GL_Name_VC | Variable Character
Amount | Integer
Period_TI | TinyInteger
Year_SI | SmallInteger
The above database is running on Microsoft SQL Server 2000 and i would like to query
for a report that looks something as below:
GL_ID | GL_Name_VC |Op Bal|Period_Dr|Period_Cr|Period Bal|Closing Bal
======================================================================
1000 | Inventory |8,000 | 5,000 | -3,000 | 2,000 |10,000
The above report has an Op Bal column which is the sum of all amount in Period 01 in
Year 2005 carried forward as opening balance in Period 02, Period_Dr Column would contain
all positive amount in Period 02 & Period_Cr Column would contain all negative amount
in Period 02. Period Bal is the summation of both Period_Dr & Period_Cr and Closing Bal
column is the summation of Op Bal + Period Bal.
Guys, hope someone out there can help me with the sql command for the above report?
View 1 Replies
View Related
Jul 20, 2005
Hello,I would like to create more lines by concatenating values.When I use: <select 'This' + ' ' + 'is' + ' ' + 'an' + ' ' +'example'> the result is <This is an example> (on the same line).I woul like to get:<Thisisanexample> (each 'word' on a new line, but in 1 field)Whis SQL statement do i have to use?
View 2 Replies
View Related
Apr 10, 2008
Hi
In Sqlserve we can create dynmic field on sql like:
Select '' as Name from Employee.
It creates a string field.
I want to how to create a Bit field in this way.
Regards
Deepak
View 1 Replies
View Related
Aug 2, 2007
Hi -
I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.
Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.
Is there a way to do this with a trigger (or an otherwise elegant way)?
Thanks in advance for ANY help or ideas.
Oliver
View 3 Replies
View Related
May 30, 2004
Hi there, my situation is
I have a table x with 3 filed
a nvarchar(100), b smalldatetime, c text(16)
. I want to create a view like this:
select a + ' ' + b + ' ' + c as all_field from x where all_field like %my_str%
So, I always get a message error said wrong datatype, how can i do, please help me.
View 2 Replies
View Related
Oct 1, 2005
I would like to create a field that increments automatically by 1 - to use it as an identifier for a row. This is so dammed simple but I can not find how to do it !
Thank you very much for any help.
By the way I configure SQLExpress with VS2005 and SQL Express Manager.
Thank you very much,
Regards,
Fabian
my favorit hoster is ASPnix : www.aspnix.com !
View 5 Replies
View Related
Jun 9, 2006
In my SQL Database I insert a Google Map link as this
http://maps.google.com/maps?q=42.2362,-71.7439+(ESN=0-10073)
How can I mask it so that it appears in my Web page layout as a hyperlink, for my reports.
Something like: Map This
View 1 Replies
View Related
Jul 24, 2006
Now i m working in a existing project. I have to do some updations. Inthat project database table contain a table(usergroups) field name as'Name'. When i am trying to insert a new record in (usergoups) tablelike as follows'insert into UserGroups(Name,modusr,moddt) values ('sam',131,'7/23/200610:02:13 PM')The response ll come as follows,Invalid column name 'FirstName'.Invalid column name 'LastName'.The usergroups table structure is as follows,UserGroupId int 4 (PK field)Name varchar 50modusr int 4moddt datetime 8Plz tell the correct insert query to add records for the abovetable....Thanks in advance
View 3 Replies
View Related
Sep 12, 2006
I am between the "newbie" and "intermediate" stages of writing SQL code and I am wondering if there is a way to capture multiple results into one field so I can basically create a "set" for a unique identifier. Here is few result samples I receive from this code I am using now.
ReqNo ProcID
7102005 1409
7102005 1796
7139003 1411
7139003 6097
7261030 1409
7261030 1796
7268303 3998
7268303 4000
I would like to create a single row for each "ReqNo" and have a field that will an array of the "ProcID" results I receive. In other words, for the first "ReqNo" 7102005, can I create a field that will combine the 1409, 1796 into one field? I am trying to capture an array of integers used for that "ReqNo" so I can use that as a unique identifier in a join for another table.
So, ideally my result would be:
ReqNo ProcSet
7102005 1409, 1796
7139003 1411, 6097
7261030 1409, 1796
7268303 3998, 4000
Is this possible?
declare
@startdate smalldatetime,
@enddate smalldatetime ,
@month int,
@year int
select
@startdate = dateadd (dd, -7, getdate())
SELECT
@month = datepart (month, @startdate),
@year = datepart (year, @startdate)
SELECT
@startdate = convert (smalldatetime, convert(varchar(2), @month) + "/1/" + convert (varchar(4), @year))
select
@enddate = dateadd (dd, 1 , @startdate)
select distinct
pp_req_no as ReqNo,
pp_cproc_id_r as ProcID
from
risdb_rch08_stag..performed_procedure
(index pp_serv_time_r_ndx)
where
pp_service_time_r between @Startdate and @Enddate
and pp_status_v = 'CP'
and pp_rep_id > 0
order by
pp_req_no, pp_cproc_id_r
View 4 Replies
View Related
Mar 4, 2008
I'm want to store a time from a datetimepicker control(format = time) into a sql table. I'm using c# and windows forms with vs2008. Some example code would be nice. Thanks.
// dateTimePicker2
//
this.dateTimePicker2.Format = System.Windows.Forms.DateTimePickerFormat.Time;
this.dateTimePicker2.Location = new System.Drawing.Point(225, 21);
this.dateTimePicker2.Name = "dateTimePicker2";
this.dateTimePicker2.ShowUpDown = true;
this.dateTimePicker2.Size = new System.Drawing.Size(96, 20);
this.dateTimePicker2.TabIndex = 1;
Question?
1. What type should the time field be in the sql table? I want to be able to calculate with it at some time.
View 11 Replies
View Related