SELECT - 1 AS OrganisationID, '--Please Select--' AS OrganisationName
UNION ALL
SELECT OrganisationID, OrganisationName
FROM tblOrganisation
ORDER BY OrganisationName
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
I am new to stored procedures and T-SQL so please stick with me. I have a table that holds information about companies. I am trying to write a stored procedure that when run will query that table and find out if there are more than one entry of that company. All company names in that table must be unique (they can only occur once), if they occur more than once I need to flag it for reporting.
So what is the best way to go about this? Essentially what i was thinking was doing a select * on the table and then going from the first entry to the last and at each entry running a select * from table where companyname = @nameofcompany. @nameofcompany would be the name for that entry. If the select statement revealed more than one entry then i would know there was a problem.
Like I said I am new and this is probably very simple but i need a little help getting started
Hey all - VERY new to SQL so I apologize if I butcher normally trivial things :)
Looking to run a query that will retrieve the number of results returned from a select statement...
Currently have a LicenseID table with a Software column...the statement that works on it's own that i've got is:
SELECT * FROM Software WHERE LicensesID = 2
Currently when I run that with the data so far I get 4 results returned to me...how can I add to that statement so that instead of displaying the results themselves, I just get the number 4 returned as a total number of results?
I have a table with three columns. Name, book#, category.
I need to write a select statement that looks at the book# and category and returns all the names that match that into one joined our combined result. So if my name column had john, Jane, Jim, jack, and Steph in unique rows that matched it would bring back a resulted formatted as. John, Jane, Jim, jack, Steph. So I could place that in a field for reference.
I am using the following conditional select statement but it returns no results.
Declare @DepartmentName as varchar Set @DepartmentName = null Declare @status as bigint Set @status = 4 IF (@DepartmentName = null) BEGIN
SELECT CallNumber AS [Call Number], Problem, Solution, Note FROM AdminView WHERE (Status = @status) ORDER BY CallLoggedDT END ELSE IF (@DepartmentName <> null) Begin
SELECT CallNumber AS [Call Number], Problem, Solution, Note FROM dbo.AdminView WHERE (Status = @status) AND (DepartmentName = @DepartmentName) ORDER BY CallLoggedDT end
when i run the 2nd half by itself it tells me to declare @status but not @departmentname. whats going on???
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
I have the following SELECT statement attached to a dropdown box: SELECT [Workshop] FROM [Workshops] WHERE Workshop <> (SELECT Workshop FROM Workshop_Registration WHERE FullName = @FullName AND Completed = @Completed) ORDER BY [Workshop] I am trying to get all workshops (50 or more) from the WORKSHOPS table that the logged in user is not already registered for. This works perfectly as long as the student is registered for at least 1 class. It populates the dropdown with all of the other classes. If they aren't registered for a class then it doesn't list any classes. The problem is definitely the subquery, but how do I make it to where if the subquery doesn't return any results (student not registered for anything), I get all of the workshops in the dropdown? Any help is appreciated! MikeD
Hello,I have written a stored procedure where I prompt the user for the Year and the Month (see below)How do I take the variable @TheMonth and find out how many days is in the month and then loop to display a total for every day in the selected month.Can someone please point me in the right direction. CREATE PROCEDURE crm_contact_frequency_report @TheYear varchar(4),@TheMonth varchar(2) AS SELECT /* EMAILS (B) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 1)) AS Total_EmailOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode = 0)) AS Total_EmailImconing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth) AND (B.directioncode IS NULL)) AS Total_EmailNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Email B ON A.subject = B.subject WHERE (YEAR(B.CreatedOn) = @TheYear) AND (MONTH(B.CreatedOn) = @TheMonth)) AS Total_All_Emails, /* PHONE CALLS (C) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 1)) AS Total_CallOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode = 0)) AS Total_CallIncoming, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth) AND (C.directioncode IS NULL)) AS Total_CallNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN PhoneCall C ON A.subject = C.subject WHERE (YEAR(C.CreatedOn) = @TheYear) AND (MONTH(C.CreatedOn) = @TheMonth)) AS Total_All_Calls, /* FAXES (D) */(SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 1)) AS Total_FaxOutgoing, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode = 0)) AS Total_FaxIncoming, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth) AND (D.directioncode IS NULL)) AS Total_FaxNotListed, (SELECT COUNT(*) FROM dbo.CampaignResponse A INNER JOIN Fax D ON A.subject = D.subject WHERE (YEAR(D.CreatedOn) = @TheYear) AND (MONTH(D.CreatedOn) = @TheMonth)) AS Total_All_Faxes FROM CampaignResponse AGO
I need to insert data into a table based on the results returned by a select statement. Basically, the select statement below gives me a list of all the work orders created in the last hour.
select worknumber from worksorderhdr where date_created > DATEADD(HOUR, -1, GETDATE())
This might return anywhere between 5 and 50 records each time. What I then need to do is use each of the work numbers returned to create a record in the spec_checklist_remind table. The other details in the insert statement will be the same for each insert, it's just the worknumber from the select statement that needs to be added to the insert where the ?? are below:
I need to run a stored procedure on all the results of a select query.For a simplified example, I'd like to be able to do something likethis:SELECT JobID, QtyToAddFROM JobsWHERE QtyToAdd > 0Then for the results of the above:EXEC sp_stockadd @JobID, @QtyToAddWhere the above line ran for every result from the above select query.Anyone able to shed some light on how to do this?Many thanks,James
I'm trying to delete the selected data from a table colum from a select statement.
This is the select statement
SELECT RFC822 FROM SQLGOLDMINE.DBO.MAILBOX MB WHERE ((MB.CREATEON >= '2014-07-24' AND MB.CREATEON <= '2015-07-24') OR (MB.MAILDATE >= '2014-07-24' AND MB.MAILDATE <= '2015-07-24')) AND (MB.MAILREF LIKE '%auction notification & invitation%')
What the delete statement would look like. I've tried replacing Select with delete from but get a sytax error.
I have a simple update statement (see example below) that when runs, I expect to see the number of records updated in the Results tab. This information shows up in the Messages tab; however, what is displayed in the Results tab is (No column name) 40. From where the 40 is being generated. I have tried restarting SSMS 2012, restarting my computer, turning NOCOUNT on and off.
"UPDATE TableA SET Supervisor = 'A123' WHERE PersonnelNumber = 'B456'"
I am fairly new with SQL and still learning. I have used a case statemtent for a column in my select list and want to use the results of that statement's field in my WHERE clause but it is not working for me. Here is the code I have so far:
SELECT l.loanid, p.investorid, l.duedate, case when pc.duedate >= l.duedate then pc.duedate end as RateDueDate, pc.interestrate FROM loan l inner join participation p on p.loanid = l.loanid inner join paymentchange pc on pc.loanid = l.loanid where p.investorid = '12345' and RateDueDate is not null order by l.loanid, pc.duedate
I want to put the results of this case statment in my where clause like highlighted above but it is not working because RateDueDate is not an actual column in the table. Any help would be greatly appreciated.
SQL Ver: 2008 (not r2) Problem: The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records. The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL. I only got this message when running inside the sp, not when running standalone. I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.
SQL CODE: DECLARE @WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y') select @WIPMonthCurrent as WIPMonthCurrent
I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table. Here is my select statement: SELECT employee.emp_id, DATEDIFF(mm, employee.emp_begin_accrual, GETDATE()) * employee.emp_accrual_rate - (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft, employee.emp_lname + ', ' + employee.emp_fname + ' ' + employee.emp_minitial + '.' AS emp_name, department.department_name, location.location_name FROM employee INNER JOIN request AS request_1 ON employee.emp_id = request_1.emp_id INNER JOIN department ON employee.emp_department = department.department_id INNER JOIN location ON department.department_location = location.location_id GROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_name ORDER BY location.location_name, department.department_name, employee.emp_lname
The section below is the part that may or may not contain information: SELECT (SELECT SUM(request_duration) AS daystaken FROM request) AS daysleft
So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!! TIA
I have a stored procedure on a SQL Server 2008 database. The stored procedure is very simple, just a SELECT statement. When I run it, it returns 422 rows. However, when I run the SELECT statement from the stored procedure, it returns 467 rows. I've tried this by running both the stored procedure and the SELECT statement in the same SSMS window at the same time, and the behavior is the same. The stored procedure is:
USE [REMS] GO /****** Object: StoredProcedure [mobile].[GetAllMobileDeviceUsers] Script Date: 12/04/2014 */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When I do this in the same SSMS window:
exec mobile.GetAllMobileDeviceUsers
SELECT ee.EmployeeID, EmployeeName = LastName + ', ' + FirstName FROM EmployeeInvData ee --UNION
[Code] ....
I get two result sets. The first is 422 rows; the second is 467 rows. Why?
We are getting unexpected results from the following update statement when it is executed on SQL Server 2005.
The strange thing is that we get duplicated values for QM_UID (although when run under SQL Server 2000 we don't get duplicated values)
Can anyone explain why this happens or suggest another way of populating this column without using a cursor or adding a temporary autoincrement column and copying the values over?
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
-- NB: The number of rows that must be added to tmp before this problem will occur is machine dependant
-- 100000 rows is sufficient on one of our servers but another (faster) server doesn't show the error
-- at 100000 rows but does at 1000000 rows.
-- Create a table
CREATE TABLE tmp ( [QM_ADD_OP] [char](6) DEFAULT '' NOT NULL, [QM_ADD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ADD_TIME] [int] DEFAULT -1 NOT NULL, [QM_EDIT_OP] [char](6) DEFAULT '' NOT NULL, [QM_EDIT_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EDIT_TIME] [int] DEFAULT -1 NOT NULL, [QM_LOCK_OP] [char](6) DEFAULT '' NOT NULL, [QM_QUOTE_JOB] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_NUM] [char](12) DEFAULT '' NOT NULL, [QM_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PRJ_NUM] [char](12) DEFAULT '' NOT NULL, [QM_NUMBER] [char](12) DEFAULT '' NOT NULL, [QM_REV_NUM] [char](6) DEFAULT '' NOT NULL, [QM_REV_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REV_TIME] [int] DEFAULT -1 NOT NULL, [QM_REV_OPR] [char](6) DEFAULT '' NOT NULL, [QM_STYLE_CODE] [char](4) DEFAULT '' NOT NULL, [QM_REP_JOB_NUM] [char](12) DEFAULT '' NOT NULL, [QM_REP_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_REP_PART] [char](6) DEFAULT '' NOT NULL, [QM_REP_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_REP_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_MODEL_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_RUN_NUM] [int] DEFAULT 0 NOT NULL, [QM_SOURCE_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_SOURCE_QTY] [char](12) DEFAULT '' NOT NULL, [QM_SOURCE_PART] [char](6) DEFAULT '' NOT NULL, [QM_SOURCE_MODEL] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_QTY] [char](12) DEFAULT '' NOT NULL, [QM_ORIG_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_JOB] [char](12) DEFAULT '' NOT NULL, [QM_COPY_COLUMN] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_J_PART] [char](6) DEFAULT '' NOT NULL, [QM_COPY_QUOTE] [char](12) DEFAULT '' NOT NULL, [QM_COPY_VAR] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_QTY] [char](12) DEFAULT '' NOT NULL, [QM_COPY_Q_PART] [char](6) DEFAULT '' NOT NULL, [QM_JOINT_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_QUOTE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_JOB_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_LIVE_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_USER_STATUS] [smallint] DEFAULT 0 NOT NULL, [QM_DEL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_IS_CONVERTED] [smallint] DEFAULT 0 NOT NULL, [QM_PRINTED] [smallint] DEFAULT 0 NOT NULL, [QM_COPY_RATES] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_UPDATE] [smallint] DEFAULT 0 NOT NULL, [QM_CRED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CRED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CRED_AMT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CRED_OP] [char](6) DEFAULT '' NOT NULL, [QM_HELD] [smallint] DEFAULT 0 NOT NULL, [QM_PROOF] [char](12) DEFAULT '' NOT NULL, [QM_DELIV_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_ART_METHOD] [char](12) DEFAULT '' NOT NULL, [QM_DES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_REC_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REC_TIME] [int] DEFAULT -1 NOT NULL, [QM_OWN_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RESP_TIME] [int] DEFAULT -1 NOT NULL, [QM_RESP_OP] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_1] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_2] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_3] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_4] [char](6) DEFAULT '' NOT NULL, [QM_RESP_OP_5] [char](6) DEFAULT '' NOT NULL, [QM_RECONTACT] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQ_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_ORIG_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_ORIG_TIME] [int] DEFAULT -1 NOT NULL, [QM_PREF_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PREF_TIME] [int] DEFAULT -1 NOT NULL, [QM_LATE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_LATE_TIME] [int] DEFAULT -1 NOT NULL, [QM_TITLE] [char](72) DEFAULT '' NOT NULL, [QM_DELIV_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CLT_SPEC] [char](12) DEFAULT '' NOT NULL, [QM_TAX_REF] [char](22) DEFAULT '' NOT NULL, [QM_CONTACT] [char](36) DEFAULT '' NOT NULL, [QM_PHONE] [char](22) DEFAULT '' NOT NULL, [QM_FAX] [char](22) DEFAULT '' NOT NULL, [QM_ORDER] [char](20) DEFAULT '' NOT NULL, [QM_ORDER_CFM] [smallint] DEFAULT 0 NOT NULL, [QM_ORDER_REL] [char](6) DEFAULT '' NOT NULL, [QM_REP] [char](12) DEFAULT '' NOT NULL, [QM_REP_1] [char](12) DEFAULT '' NOT NULL, [QM_REP_2] [char](12) DEFAULT '' NOT NULL, [QM_REP_3] [char](12) DEFAULT '' NOT NULL, [QM_REP_4] [char](12) DEFAULT '' NOT NULL, [QM_REP_5] [char](12) DEFAULT '' NOT NULL, [QM_COORDINATOR] [char](12) DEFAULT '' NOT NULL, [QM_PRIORITY] [smallint] DEFAULT 0 NOT NULL, [QM_TYPE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_GRADE] [smallint] DEFAULT 0 NOT NULL, [QM_FIN_SIZE_CODE] [char](12) DEFAULT '' NOT NULL, [QM_FIN_WID] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_LEN] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_DEP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GUSS] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_GSM] numeric(26,8) DEFAULT 0 NOT NULL, [QM_FIN_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_ORIENT] [smallint] DEFAULT 0 NOT NULL, [QM_PROD_CODE] [char](22) DEFAULT '' NOT NULL, [QM_FIN_GOOD] [char](22) DEFAULT '' NOT NULL, [QM_CUST_CODE] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_1] [char](12) DEFAULT '' NOT NULL, [QM_CUST_CODE_2] [char](12) DEFAULT '' NOT NULL, [QM_CUST_PROS] [smallint] DEFAULT 0 NOT NULL, [QM_REQD_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_REQD_TIME] [int] DEFAULT -1 NOT NULL, [QM_FOLIO] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_1] [char](12) DEFAULT '' NOT NULL, [QM_FOLIO_2] [char](12) DEFAULT '' NOT NULL, [QM_PACK_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_USAGE] numeric(26,8) DEFAULT 0 NOT NULL, [QM_REORDER] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_EACH_WGT] numeric(26,8) DEFAULT 0 NOT NULL, [QM_WGT_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_RFQ_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_RFQ_TIME] [int] DEFAULT -1 NOT NULL, [QM_RFQ_OPR] [char](6) DEFAULT '' NOT NULL, [QM_SALES_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_SALES_SRC] [char](12) DEFAULT '' NOT NULL, [QM_SALES_RSN] [char](12) DEFAULT '' NOT NULL, [QM_PROFILE] [char](12) DEFAULT '' NOT NULL, [QM_JOB_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_PREV_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_JOB_UNIT] [char](12) DEFAULT '' NOT NULL, [QM_PO_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_PO_TIME] [int] DEFAULT -1 NOT NULL, [QM_PO_OP] [char](6) DEFAULT '' NOT NULL, [QM_DLY_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_DLY_TIME] [int] DEFAULT -1 NOT NULL, [QM_QTY_DESP] numeric(26,8) DEFAULT 0 NOT NULL, [QM_TOTAL_DLY] [int] DEFAULT 0 NOT NULL, [QM_SCHED_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_SCHED_TIME] [int] DEFAULT -1 NOT NULL, [QM_CLOSE_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CLOSE_TIME] [int] DEFAULT -1 NOT NULL, [QM_INV_NUM] [char](12) DEFAULT '' NOT NULL, [QM_PACK_NUM] [char](12) DEFAULT '' NOT NULL, [QM_DOWN_LOAD] [smallint] DEFAULT 0 NOT NULL, [QM_TRACK_CODE] [char](4) DEFAULT '' NOT NULL, [QM_TAX_TYPE] [smallint] DEFAULT 0 NOT NULL, [QM_TAX_CODE] [char](6) DEFAULT '' NOT NULL, [QM_CURR] [char](6) DEFAULT '' NOT NULL, [QM_EXCH_RATE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_UNIT_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_UNIT_FLAG] [smallint] DEFAULT 0 NOT NULL, [QM_RUNON_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_SPEC_QTY] numeric(26,8) DEFAULT 0 NOT NULL, [QM_CHARGEABLE] [smallint] DEFAULT 0 NOT NULL, [QM_NC_REASON] [char](22) DEFAULT '' NOT NULL, [QM_CUST_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_JOB_MKUP] numeric(18,8) DEFAULT 0 NOT NULL, [QM_BROKERAGE] numeric(18,8) DEFAULT 0 NOT NULL, [QM_CUST_DISC] numeric(18,8) DEFAULT 0 NOT NULL, [QM_INVOKED_BTNS] [int] DEFAULT 0 NOT NULL, [QM_IMPORTED] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_RECALC] [smallint] DEFAULT 0 NOT NULL, [QM_IMPORT_CONVERT] [smallint] DEFAULT 0 NOT NULL, [QM_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_CODE] [char](36) DEFAULT '' NOT NULL, [QM_TEMPLATE] [smallint] DEFAULT 0 NOT NULL, [QM_REPEAT_PERIOD] [int] DEFAULT 0 NOT NULL, [QM_REOPEN_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_CAT_OPTION] [char](16) DEFAULT '' NOT NULL, [QM_UNIT_ID] [char](10) DEFAULT '' NOT NULL, [QM_PROD_BRANCH] [char](6) DEFAULT '' NOT NULL, [QM_UID] [int] DEFAULT 0 NOT NULL, [QM_AVAIL_DATE] [smalldatetime] DEFAULT ('1900-01-01') NOT NULL, [QM_AVAIL_TIME] [int] DEFAULT -1 NOT NULL ) ON [PRIMARY]
GO
-- Create an index on the table
CREATE unique INDEX [QM_NUMBER_ORDER] ON tmp([QM_QUOTE_JOB], [QM_NUMBER]) ON [PRIMARY]
GO
-- Populate the table
declare @Counter as int
SET NOCOUNT ON
set @Counter = 1
while @Counter < 100000
begin
insert into tmp (QM_ADD_TIME, QM_NUMBER) values (1,@Counter);
set @Counter = @Counter + 1
end
GO
-- Update QM_UID to a sequential value
declare @NextID int;
set @NextID = 1;
update tmp set QM_UID=@NextID, @NextID = @NextID + 1;
-- Find rows with a duplicate QM_UID (there should be no duplicate)
select QM_UID, count(*) from tmp group by QM_UID having count(*) > 1 order by QM_UID
--drop table tmp
-- output from select @@VERSION
-- Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I have created a table(T1) from select query result, that Select query is parameterised. Now I need to update the select query table(T1) based on the result every time.
Below is my Query:
ALTER PROCEDURE [dbo].[RPT_Cost_copy] SELECT MEII.*, SIMM.U_SP2DC, UPPER(SIMM.U_C3C2) AS GRP3,sb.cost, PREV.Z1, PREV.Z3, SB.Z2, SB.Z4,SIMM.U_C3DC1 AS FAM INTO T1 FROM (SELECT a.meu, a.mep2, SUM(a.mest) as excst FROM mei as A WHERE a.myar=@yr and a.mprd=@mth AND LTRIM(A.MCU) <> '' AND LTRIM(A.MRP2) <> ''
I have a table which I need to obtain data from but am having a problem with select statement. Specifically, I have a table that has multiple records for a particular hostName where the name of the host is either a shortname (say "Larry") or a long name (say "Larry's"). I need to display only the long names (NOT THE SHORT NAME records with the similar hostName). Select winsHostName, len(winsHostName) from winsData where winsClientIPAddress IN (SELECT winsClientIPAddress from winsData Group By winsClientIPAddress Having count(winsClientIpAddress) > 1) Order by winsHostName Which returns data Name Length ATVDDR 6 ATVDDR1 7 This is a s far as I can get but, Now, I need to list all remaining table fields based on the Name with the longer length and this is where I run into trouble. The output should read per below with the remaining table fields which I cannot seem to extract with nested select statement Name IP Addr Location ATVDDR1 1.1.1.1 2ndFloor I tried adding another GROUP BY by this gets me no where because I do not need to execute another aggregate in select statement. Maybe I need to use INNER JOIN Please advise any assistance.
Would it be possible to retrieve a "dynamically" named field from a table by using an input parameter?
For example, if a table has fields named Semester1, Semester2, Semester3, Semester4, and I was lazy and only wanted to create one stored procedure for all semesters could I do the following...
ALTER PROCEDURE u_sp_x @semester int AS Select Semester@semester From ThisTable
Update ed_abcdeeh set category = case when name_of_school = '' then category = 'No Facility' else '' end,status = case when name_of_school = '' then status = 'Non-Compliant' else 'Compliant' end.
How to make this query right.. when name of school is blank i want to update my category to No facility, but if the name of school has data it will just make it blank. same to the status..
I have the following list of fields in a table named call.CallID, CallGroupID, PersonID, ParentID,I have two records.23, 45, John, Null26, <Incorrect Value>, Jane, 23Now, I'm wanting to set the CallGroupID of record two, to that of theCallGroupID of record one.This is what I came up with, but it doesn't work.update call set CallGroupID = (Select CallGroupID from call whereCallID = ParentID) where ParentID is not nullAs you know the Select statment I have embedded is trying to pull up alist CallGroupIDs from records where the CallID = ParentID. What Iwant it to do is pull up the CallGroupID of the record where the CallID= ParentID.The CallID is a unique field, so there is only one such record in thetable.Any help?Thanks
hi myself avii am developing one appliacaion in which i am using vb 6 as front end,adodb as database library and sql sever 7 as backend.i want to update one table for which i required data from other table. andiretrive data from second table by giving some condition. when i get data,then to update first table i need to use do while loop. instead of that iwant to use select statement directly in update query.plz give me some help.following is the my queries and its out putStrSql = ""StrSql = "Select * From SalesVchMaterialDesc where TransactionID=" &txtTransactionID.text & ""rsMName.Open StrSql, Conn, adOpenKeysetDo While Not rsMName.EOFStrSql = ""StrSql = "Update StockTable Set Outward=Outward - " &rsMName("Netweight") & ",OutwardQty=OutwardQty - " & rsMName("Qty") & "Where MaterialId=" & rsMName("Material_Name") & " and VoucherDate='" &Format(rsMName("VoucherDate"), "mm/dd/yyyy") & "'RsAdd.Open StrSql, Conn, adOpenStaticrsMName.MoveNextLooprsMName.Closeout put***main querySelect * From SalesVchMaterialDesc where TransactionID=848do while not loopUpdate StockTable Set Outward=Outward - 8.06,OutwardQty=OutwardQty - 1Where MaterialId=221 and VoucherDate='04/01/2004' and SMID=0loop
I am trying to write an update t-SQL statement from the following select statement:
SELECT EduNextContacts.ssn FROM EduNextContacts Left JOIN EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND EduNextContacts.campaign_code = EduContactsAuditChanges.campaign_code GROUP BY EduNextContacts.ssn HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) + SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2)
I tried many versions of writing my update statement with no luck. My most recent version is as follows:
UPDATE EduNextContacts
SET EduNextContacts.overLMLimit = 'Y'
FROM EduNextContacts Left JOIN
EduContactsAuditChanges ON EduNextContacts.ssn = EduContactsAuditChanges.ssn AND
HAVING(SUM(CASE EduContactsAuditChanges.release_code WHEN '103' THEN 1 ELSE 0 END) +
SUM(CASE EduContactsAuditChanges.release_code WHEN '102' THEN 1 ELSE 0 END) >= 2))
And gives the following error:
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Can anyone help shed some light on how to make an update statement from my SELECT query above?
