SELECT - 1 AS OrganisationID, '--Please Select--' AS OrganisationName UNION ALL SELECT OrganisationID, OrganisationName FROM tblOrganisation ORDER BY OrganisationName
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'"
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
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)
Hi,Say I have a table Job with columns name, date, salary . I want to getthe name ,date and salary for the date when that person earned maximumsalary. I am using something likeSELECT X.name,X.date,X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);The problem is ; if a person earns maximum salary on two dates, both ofthe dates are printed. I just want to get any one of those two rows.I triedSELECT X.name,Min(X.date),X.salaryFROM job XWHERE X.salary IN(SELECT MAX(Y.salary) FROM job Y where Y.name= X.name);but it gives error.Can anybody please suggest a solution?Regards,Aamir
I have an sql data source..select command is something like this. Select password from users where username = Username.Text how can i retrieve the password value and save it into a string?! to make a comparison between password value and a textbox Password.Text i know its a silly question but i took me long time and i still can't find the ans
If I want to select certain values from a table where date is equal to something and the second field is null,would I go.
select * from table where date = 'something' and Sent = null or something else because if I do it this way I get nothing as a result but if I scroll down I can see that I have null values.
This is more of a basic SQL question rather than anything specific for T-SQL, but I assume someone here can help me...
I have a problem with properly combining a lot of AND and ORs in a SELECT statement in a stored procedure in order to get the desired results. The problem is that I want to have all results that fullfill all of the supplied conditions: InstitutionCode, CollectionCode, ScientificName, Locality (unless they are null, hence 'coalesce') and the Parentid, that can be in one of eight columns.
SELECT * FROM QueryView WHERE InstitutionCode = COALESCE(@museum, InstitutionCode) AND CollectionCode = COALESCE(@collection, CollectionCode) AND ScientificName LIKE '%' + @binomen + '%' AND Locality LIKE '%' + @locality + '%' AND ParentID1 = COALESCE(@taxparent, ParentID3) OR ParentID2 = COALESCE(@taxparent, ParentID2) OR ParentID3 = COALESCE(@taxparent, ParentID3) OR ParentID4 = COALESCE(@taxparent, ParentID4) OR ParentID5 = COALESCE(@taxparent, ParentID5) OR ParentID6 = COALESCE(@taxparent, ParentID6) OR ParentID7 = COALESCE(@taxparent, ParentID7) OR ParentID8 = COALESCE(@taxparent, ParentID8)
The current construction, however, gives me all results that fullfill either on of the four conditions, or the parentid in one of the columns. putting parentheses around parentid part gives me zero query results. I understand that the ORs should be restricted to the parentids and not the rest, but putting parentheses around parentid part gives me zero query results.
Has anyone got a good tip to help me resolve this puzzle?
I'm new to SQL server. I want to add or append a unique set of rows to a destination table from a source table, they are essentially the same table by definition. The source table is updated every hour via DTS, all rows deleted and new set added. Both tables have the same primary key. Approximately 40 unique rows are created each hour and I would think the best approach would be to append the new rows to the destination table. I think an Append query will run into a primary key conflict.
In Access, I did this within VB by checking the max value of the primary key and then running the append for any values greater than that.
In SQL, I'm not sure if this should be done as a stored procedure or if there is an easier approach altogether.
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
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 was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Hello,I am trying to update records in my database from excel data using vbaeditor within excel.In order to launch a query, I use SQL langage in ADO as follwing:------------------------------------------------------------Dim adoConn As ADODB.ConnectionDim adoRs As ADODB.RecordsetDim sConn As StringDim sSql As StringDim sOutput As StringsConn = "DSN=MS Access Database;" & _"DBQ=MyDatabasePath;" & _"DefaultDir=MyPathDirectory;" & _"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" &_"PWD=xxxxxx;UID=admin;"ID, A, B C.. are my table fieldssSql = "SELECT ID, `A`, B, `C being a date`, D, E, `F`, `H`, I, J,`K`, L" & _" FROM MyTblName" & _" WHERE (`A`='MyA')" & _" AND (`C`>{ts '" & Format(Date, "yyyy-mm-dd hh:mm:ss") & "'})"& _" ORDER BY `C` DESC"Set adoConn = New ADODB.ConnectionadoConn.Open sConnSet adoRs = New ADODB.RecordsetadoRs.Open Source:=sSql, _ActiveConnection:=adoConnadoRs.MoveFirstSheets("Sheet1").Range("a2").CopyFromRecordset adoRsSet adoRs = NothingSet adoConn = Nothing---------------------------------------------------------------Does Anyone know How I can use the UPDATE, DELETE INSERT SQL statementsin this environement? Copying SQL statements from access does not workas I would have to reference Access Object in my project which I do notwant if I can avoid. Ideally I would like to use only ADO system andSQL approach.Thank you very muchNono
It appears to update only the first qualifying row. The trace shows a row count of one when there are multiple qualifying rows in the table. This problem does not exist in JDBC 2000.
I'm writing a fairly involved stored procedure. In this Stored Procedure, I have an update statement, followed by a select statement. The results of the select statement should be effected by the previous update statement, but its not. When the stored procedure is finish, the update statement seemed to have worked though, so it is working.
I suspect I need something, like a GO statement, but that doesnt seem to work for a stored procedure. Can anyone offer some assistance?
Hello How can i say this I would like my if statement to say: if what the client types in Form1.Cust is = to the Select Statement which should be running off form1.Cust then show the Cust otherwise INVALID CUSTOMER NUMBER .here is my if statement. <% If Request.Form("Form1.Cust") = Request.QueryString("RsCustNo") Then%> <%=Request.Params("Cust") %> <% Else %> <p>INVALID CUSTOMER NUMBER</p> <% End If%> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RsCustNo %>" ProviderName="<%$ ConnectionStrings:RsCustNo.ProviderName %>" SelectCommand="SELECT [CU_CUST_NUM] FROM [CUSTOMER] WHERE ([CU_CUST_NUM] = ?)"> <SelectParameters> <asp:FormParameter FormField="Cust" Name="CU_CUST_NUM" Type="String" /> </SelectParameters></asp:SqlDataSource> any help would be appreciated
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS SELECT VendorName, InvoiceNumber, InvoiceTotal From Vendors JOIN Invoices ON Vendors.VendorID = Invoices.VendorID
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 trying to format the Sys field (which is Int) in case the first byte is zero. But I'm getting an error msg; Msg 156, Level 15, State 1, Procedure sp_Pull_Cancels, Line 43 Incorrect syntax near the keyword 'Right'.
CASE WHEN substring(PCRACT, 5, 1) = '4' THEN 3641 WHEN substring(PCRACT, 5, 1) = '5' THEN 2012 WHEN substring(PCRACT, 5, 1) = '6' THEN 9694 END as Right('0000' + Cast(Sys as varchar (4)),4)
Any ideas how I construct this CASE statement to get the desired results.
Hi, I have three tables: 1 Order Details 2 Product Info 3 Received Qty
The query is pretty simple however I'm getting incorrect results.
The query grabs a load of information (including a SUM statement) from the order details tbl.. this is INNER JOIN ed to the products tbl to match an ID field which grabs some extra info from the product info tbl.
all fairly straightforward and works without a problem..
However I now need to add in the received qty tbl (as the name suggests this keep a track of the qty's of items received)
I need to join this tbl to the order details tbl based on a unique ID from the orders tbl and then SUM together all of the entries in that received qty tbl for that item.
Result is I get a single record which shows the qty ordered (from the order details tbl), and the total received (from the received qty) tbl
These are held in a seperate tbl as an order can be deliveryed in several stages.
Here is my statement:
SELECT PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem, dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived, SUM(dbo.Suppliers_POs_Details.Qty * dbo.Suppliers_POs_Details.PricePerItem) AS LineTotal, SUM(dbo.Suppliers_POs_Details_Received.Qty) AS QtyReceived FROM dbo.Suppliers_POs_Details INNER JOIN PDS_Login.Supplier_Products ON dbo.Suppliers_POs_Details.ProductID = PDS_Login.Supplier_Products.ProductID LEFT OUTER JOIN dbo.Suppliers_POs_Details_Received ON dbo.Suppliers_POs_Details.PODetailsID = dbo.Suppliers_POs_Details_Received.PODetailsID WHERE (dbo.Suppliers_POs_Details.POID = @POID) GROUP BY dbo.Suppliers_POs_Details_Received.Qty, PDS_Login.Supplier_Products.ProductName, PDS_Login.Supplier_Products.ProductCode, dbo.Suppliers_POs_Details.POID, dbo.Suppliers_POs_Details.PODetailsID, dbo.Suppliers_POs_Details.Qty, dbo.Suppliers_POs_Details.PricePerItem, dbo.Suppliers_POs_Details.PoDetailsStatus, dbo.Suppliers_POs_Details.PoDateExpected, dbo.Suppliers_POs_Details.PODateReceived, dbo.Suppliers_POs_Details_Received.Qty
The problem is, if there is more than one relating record in teh Received Qty tbl instead of that being added together as I want, two results appear...
It's obviously something simple but I can't seem to figure it out - anyone point me in the right direction?
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???
I'm trying to calculate some leagues for a website i run usuing some quite complicated querys. As the leagues are calculated with such somplicated criteria, i've had to build the leagues inot a temporary table.
What i need to be able to do is update the temp table with the results of a group by query. This gives me an error and the only way i have found to achieve this is to insert the results of a group by into another temp table, and then use that to perfom the update.
Is there something i'm missing, or is this the only way to achieve my goal ?
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 have a query that pulls a list of organizations. I'm trying to use a CASE statement to add a column of names based on the relationship of the individual to that organization. It is possible for two people at the organization to both have that relationship to the organization. Currently, when I pull my results, I get all the individual's names for all the organizations, but I'm also getting a row where there is no name, something like:
Some of the organizations, they have no one with the relationship, but for all the organizations that do have someone in the position I want to not see the blank row. My case statement is:
CASE WHEN EXISTS (Select ind_customer.cst_key WHERE ixo_rlt_code = 'Relationship' ) THEN ind_customer.cst_ind_full_name_dn ELSE '' END AS 'FULL NAME'
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
