I have a little system of 3 tables Job, employees and times. This times table has the fields times_id, employee_id and job_id
I'm trying to have a query that pull of employees that don't have a certain job_id yet. I'm going to put this data in a table so the user knows they are available for that job. The code i have isn't working, and i'm not sure why.
SELECT
DISTINCT times.employee_id, employee.employee_name
FROM employee
INNER JOIN times ON employee.employee_id = times.employee_id
WHERE (times.job_id <> @job_id)
Thanks in advance for any help. I'm sure I missing someting silly, or maybe i need to have a stored procedure involved?... Thanks!
I am having a little trouble with something very simple:
I have a stored procedure with the following sql in it:
UPDATE mfsWS_CustNewCustomer SET Result_Id = 0 WHERE QueryGUID = @Guid
IF (@@ERROR <> 0)
BEGIN
RAISERROR ('Error setting Result_Id', 16, 1)
UPDATE mfsWS_CustNewCustomer SET Result_Id = 1 WHERE QueryGUID = @Guid
RETURN
END
To test if this works i SET the Result_Id = 'X' this is a smallint column so this should not work.
My issue is that all i get is the error:
cannot convert char to numeric etc etc
However the @@ERROR logic is not executed , my error is not raised and the update does nto happen, it si as though the entire sp aborts at the point where i try do the bogus update.
Is there something i should SET at the beginning of my sp or something ? How do i ensure that any error will be passed to the logic that checks @@ERROR and performs the appropriate actions ? This is SS2000, so i cannot use try catch.
I am need to create comma separated list in sql and I am using below query.
declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt select @ConcatenatedString
When I use the above code inside a function, i am not getting desired output.
create function GetConcatenatedValue AS ( declare @ConcatenatedString NVARCHAR(MAX) select @ConcatenatedString = COALESCE(@ConcatenatedString + ', ', '') + CAST(rslt.Number AS NVARCHAR) from ( select 1 Number union select 2 Number union select 3 Number )rslt return @ConcatenatedString )
I have 2 tables: First table: empID,PlanID,groupID Second: PlanID,groupID,EffectiveDate,TerminationDate,DeadlineDate I need to show only employee with in spesific group who is not enroll for the current month until deadline passed.
I have some table data and know how I want the results but I'm just having a bit of trouble in constructing the SQL logic to obtain the desired results. There's a site where visitors are able to select from a list of parts, and it will return a set of model/products that they can produce with the selected parts. Here's the data ...
tblModel tblPart ModelId ModelName PartId PartName ---------------------- ---------------------- 1 Alpha 1 CHOO1 Stem 2 Bravo 2 BH034 Rod 3 Bravo Pro 3 HRE Seat
I have two tables X,Y X empno....Sal.....Tax.....Returns...name 1.....4500....1050.... 750.......robert 2.....5750.....1560....900.......john 3.....4000.....900.....600.......keen 4.....6100....1200.....1000......stauton
Y empno....Sal.....Tax.....Returns...name 1.....4500....1000.... 000.......robert 2.....5750.....1200....900.......john 3.....4000.....900.....600.......keen 4.....6100....1000.....1000.......stauton
If you see the above tables I have data mismatch in X and Y tables for the same empno. I need to write a query which shows emp no and columns(name of col) where the data mismatch has occured. I came up with a query which I have to write for every individual column to get the mismatch. Since there 120 columns it is pretty hard task..i m looking for a logic where I can write a query which shows mismatched data in columns.
Expected Output table z col1..col2 1......tax 3......tax Appreciate your help.
Hi, sorry for a newbie question but I was wondering if the following is possible:
I have to select some information from a table which I have already created a query for. This information then has to be inserted into a new table but needs another column (not the promary key) with another unique custom identifer for each record in the format EX01 which is incremented by 1 for each record. I was wondering how is it possible to do this?
My approach was to create a view and then insert the values form the view into the new table but I still have no idea how to do the unique identifer. Was the first part of my approach correct or have been wrong from the start?
Hi All I have a sqlserver database with product, catagory and sub catagory format. Before I describe my problem, let me share whats I have in db. Their are two types of sinaros, either the products are directly assigned to a catagory or a product is placed in subcatagory that is in turn have a catagory. I use the following table struct for both of the scenarios: Product>>subcat bridge>>subcatagory Product>>catbridge>>catagory Here are the queries to get them: 1. If product assinged direct in catagory then select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,cat_bridge,category where product.id=cat_bridge.pid and cat_bridge.catid=category.catid; 2. If product assinged to sub cat then select product.pid, product.Prd_heading,product.[Description], product.Brand, product.img from product,subcat_bridge,subcategory where product.pid=subcat_bridge.pid and subcat_bridge.subcatid=subcategory.subcatid and subcategory.catid=category.catid; Now the problem is, I want to use a single query to download all the products to a CSV format and I need to combine both of the queries with a single one, probably with if else logic, but I am not getting it, I mean how to acheive. Can anyone help me sort this out? Thanks in Advance Regards Mykhan
I am dealing with two tables and I am trying to take one column from a table and match the records with another table and append the data of that column.
I used an update query that looks like this:
UPDATE Acct_table Set Acct_table.Score = (Select Score_tbl.Score from Score_tbl Where Acct_table.Acctnb = Score_tbl.Acctnb
This process has been running for over an hour and a half and is building a large log file. I am curious to know if there is a better command that I can use in order to join the tables and then just drop the column from one to the other. Both tables are indexed on Acctnb.
Hi, sorry for another newbie question but I was wondering if the following is possible:
I have to take some information from a table which I have already created a query for. This information then has to be inserted into a new table but needs another column (not the promary key) with another unique custom identifer for each record in the format EX01 which is incremented by 1 for each record. I was wondering how is it possible to do this?
My approach was to create a view and then insert the values form the view into the new table but I still have no idea how to do the unique identifer. Was the first part of my approach correct or have been wrong from the start?
I'd like to make a logic statement, that would take as arguments result of the sql select query. In more details: I would like to create a local Bool variable that would be false if some value is NULL in the table (or select query).Query example:select taskID from Users where Login=@usernameWhich classes/methods should i use to solve this problem? I use SqlDataSource to get access to database and i think i should use something like SqlDataSource.UpdateCommand and SqlDataSource.UpdateParameters but dont know how to build from this a logic statement.Thanks in advance
Hello, I have a requirement to select millions of rows from table and need do some parsing each row. I have identity column on each table. Here is the query logic I'm following. Logic A:Uisng While loop processing data row by row Logic B: Using Cursor Processing row by row
Here is the perormance on the above ran against .5 millions rows of data.
like this i need to populate. i need to show all the groupname and belonging systemName and belonging devicename and belonging sensorname
so please give me query for this complex operation please criteria's 1.GRoup can have systems and system can have devices and device can have sensors 2.GRoup can have systems and systems can have sensors[no device] 3.GRoup can have systems and systems can have devices [no sensor] 4.GRoup can only have system [no device, no sensor] 5.GRoup can have only sensor[no system, no device] so please give me query for this. not stored procedures.i need query for this
I have a perl program that is looping through a hash of a hash. I need to Update any existing records but also insert any new records in the table using collected data in the hash.
Life would be very simple if it was possible to use a Where Clause in an Insert statement but not does not work.
Here is some example code from my program: sub Test{ foreach my $table(keys %$HoH){ foreach my $field(keys %{$HoH->{$table}}){ if($table eq "CPU"){ my $CPUstatement = "INSERT INTO CPU(CPUNumber, Name, MaxClockSpeed, SystemNetName) Values ('$field', '$HoH->{CPU}{$field}{Name}', '$HoH->{CPU}{$field}{MaxClockSpeed}' , '$HoH->{Host}{SystemNetName}')"; print "$CPUstatement"; if ($db->Sql($CPUstatement)) { print "Error on SQL Statement"; Win32::ODBC::DumpError(); } else { print "successful"; } } }
have a SQL2K/VB.NET05 -based website that uses a complex search query, whose results will contain additional logic to be evaluated. There are thousands of records and growing, so it is not feasible to code this within the program...it must be evaluated inline or after the query, and it is also not feasible to set up additional fields and tables to handle the logic.
For a very general example: In the .NET code, the following variables are recognized: sex="M" Paid=4350.00 Outstanding=28000.50
One of the query result fields will contain the additional logic to evaluate and another will tell the type of expression..
EVAL EXPR BOOL Sex='F' and Paid/Outstanding < 27.50 BOOL Sex='M' and Paid/Outstanding < 38 or Sex='F' INT Paid*52.33
In other words..the thousands of records being returned have their own additional logic to evaluate. Is there a way this can be done by importing the variable into SQL server and testing it during the query?
If not, is there a way that I can run the code in the middle of .NET? I know I could run scripted code while in ASP, but ASP.NET is compiled, so I dont know if it can be done there....
I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?
I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?
INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'
Hello All, I am probably doing something small with my query that is causing me pain, but somehow the query is acting funky. What I am trying to do is do a search statement to find documents from a table. But the catch is it is taking three parameters. The searchString, Type and the Location (where the user who is searching belongs to). When I run my query I get all documents where the location and type is correct. But the searchstring does not even work.For example: Lets say I have 3 documents for a LocationID of '2' and the Type for all documents is '0'. Now imagine that the name of the documents as follow: Doc1 = a , Doc2 = b, Doc3 = c. So now a user wants to search for all docs that starts with 'a'. Remember, Loc ID = '2' and Type = '0'. The result of the query should be Doc1 and only Doc1. But somehow I am getting all three Docs b/c they belong and are the type of the give parameters. Any help would be greatfull. Query: SELECT Client.FirstName, Client.LastName, Client.MiddleName, Client.LocID, ClientDocuments.DocID, ClientDocuments.DirName, ClientDocuments.LeafName, ClientDocuments.Type, ClientDocuments.CreatedByUser, ClientDocuments.CreatedDate FROM Client INNER JOIN ClientDocuments ON Client.ClientID = ClientDocuments.ClientID WHERE ClientDocuments.Type = '0' AND Client.LocID = '3' AND ([ClientDocuments.LeafName] LIKE '%' + @SR + '%' OR [Client.SSN] LIKE '%' + @SR + '%' OR [Client.LastName] LIKE '%' + @SR + '%' OR [Client.FirstName] LIKE '%' + @SR + '%' OR [Client.MiddleName] LIKE '%' + @SR + '%' )
I've gotten spoiled with all these query builders. Now in SQL server management studio express its gone. I don't understand whats wrong with their query....any help would be appreciated.
SELECT [Products].myID, [ProductDetails].ShortName FROM [Products] INNER JOIN [ProductDetails] ON [Products].DetailID = [ProductDetails].myID
Parse comes back asCommand(s) completed successfully. Execute comes back asMsg 208, Level 16, State 1, Line 1Invalid object name 'Products'.
Hi, I'm using SQL SERVER 7.0. I'm trying to run the following query: ------------------------------------------------------------------------- DECLARE @DATABASENAME VARCHAR(255), @TABLENAME VARCHAR(255)
EXEC("SELECT @RUN_ID = (select run_id from " + @DATABASENAME + ".." + "t905_run_statistics where TABLE_NAME_NM = '" + @TABLENAME + "' and DATE_TABLE_LOAD_END_DTE is null)")
select @run_id, @dataBasename, @taBlename --------------------------------------------------------------------------- But I'm getting this error when I run it:
Server: Msg 137, Level 15, State 1, Line 0 Must declare the variable '@RUN_ID'.
I've been over this about 1,000,000 times but I can't figure out what I'm doing wrong. Can anyone help me out?
My problem is that I am not able to select a distinct set of results of all users and the ImageName if that user has an image which is approved and ProfileImage is 'yes' or '1'. Can anyone help me with writing the correct SQL for this with some basic explanation. Thanks!!
Hi;I'm here for many hours trying to do this but i couldn't find a way.I have a table whith a field called [DOB], where i have people's date of birth. Now, i need a SQL query to get people who's birthdays are in between two dates "BUT", what about the year on the date? I use to do this on Access: SELECT * FROM Members WHERE DATESERIAL(YEAR(NOW()), MONTH(DOB), DAY(DOB)) BETWEEN @startDate AND @endDate In the query above the year is not a problem because the DateSerial() function add the current year for all birthdates making it easyer to user parameters like: 06/01/2006 to 06/30/2006 Unfortunately, SQL Server does not support DateSerial() function. I appreciate any help on this. Thanks a lot.
I want to update Day_Status in Time_Sheet from Leave_Code (Leave) when P_Date in Time_Sheet between start date and End Date in Leave I am getting Msg 512, Level 16, State 1, Line 1Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.The statement has been terminated.Please help me.Thanks,Janaka
Hello, I m stuck here with a very nasty problem. I am selecting data from different tables based on the search criteria of the user. But when i run the following quesry it does not give any results in my script.
select s.* from my_profile m,signup s where s.gender='male' and s.age between '18' and '26' and eye_color in (7) and bodytype in (6) and languages in ('11, 15') and ethnicity in ('1, 3, 5') and religion in (12) and occupation in (4) and education in (7) and income_level in (4) and drinking in (4) and smoking in (1) and relationship in (5) and want_children in (2) and m.distance='10' and zip='9988' and s.sid=m.sid
The "languages in ('11, 15') and ethnicity in ('1, 3, 5') " part fails the query in the script. If I remove this part it works fine in both SQL and Asp script.
Please help me out!! This problem is pretty weird and cant make out why does this happens.. Any help will be greatly appreciated!
In my M/c the query analyser (SQL 2000) is not working when ever I try to run it by Using Enterprise manager / Explorer / Start menu - Program / Isqlw.exe.
When I Tried the task manager it shows the process isqlw is working. But I can't see any window coming up ..
Please help me otherwise I will go mad....using other tools to query . 50 % time i use this tool in work. U guys know how important it is .. Thank u
EntertainmentType typeId PK tinyint type varchar(30)
Entertainment id PK int typeId FK tinyint title varchar(35) description varchar(300) purchaseDate smalldatetime
CheckedOut recordId PK int id >> dunno if I should make this a foreign key - relates to Entertainment.id checkOutDate smalldatetime dueBackDate smalldatetime userId returned bit
It is actually has a relationship that is similar to a regular customers, orders set of tables.
I have a list of movies and every time a movie is checked out a record gets added to the checkedout table. So while there is 1 of each movie in the entertainment table ... the movie may be referred to in the checkedout table multiple times ...
The result set that I am trying to get, and that i've spent all day on, is - all the movies and an indication of whether they are currently available for checkout.
i have the following, which I also had help with ...
select * from entertainment where entId not in ( select entId from checkedout where -- checks if dates conflict, assume 2 days checkout ( checkOutDate > dateadd(d,2,getdate()) or dueBackDate < getdate() ) or -- checks if current booking returned and is now available ( checkOutDate < getdate() and dueBackDate > getdate() and returned = 'true') )
though this returns a list of all the movies that are currently available for checkout. I need to be able to show all the movies that I have, so that someone knows that I have it even if its not available right now. The relationship is very similar to a customers - orders set of tables and I suppose the equivalent would be asking for a list of all the customers indicating the lastest product they bought ...
If I replace not in with exists I get the desired result but it won't work with a join so I don't know how to indicate if its available or not. Does anyone have any suggestions ... I appreciate any help you can provide ...
I am unable to execute the following query against SqlCE 3.1. Could someone guide me what is wrong.
SELECT CASE WHEN ISNULL(MAX(CONTENT_NUMBER)) THEN 0 ELSE MAX(CONTENT_NUMBER) END + 1 FROM PRELIMINARY_CODES WHERE EXAMID = '38D990D322C94B189FF12AF158AD7B06';
Error Message: Major Error 0x80040E14, Minor Error 25501 > SELECT CASE WHEN ISNULL(MAX(CONTENT_NUMBER)) THEN 0 ELSE MAX(CONTENT_NUMBER) END + 1 FROM PRELIMINARY_CODES WHERE EXAMID = '38D990D322C94B189FF12AF158AD7B06' There was an error parsing the query. [ Token line number = 1,Token line offset = 46,Token in error = THEN ]
But when I execute foloowing queries: 1) select MAX(CONTENT_NUMBER)from PRELIMINARY_CODES; Result: NULL
Hi, The following INSERT query works in all aspects apart from the date value: String InsertCmd = string.Format("INSERT INTO [CommPayments] ([CommPaymentID], [Date], [InvestmentID], [Amount]) VALUES ({0},{1},{2},{3})", FormView1.SelectedValue, txtPaymentDate.Text, ddlInvestments.SelectedValue, txtAmount.Text); The value of txtPaymentDate.Text is "13/04/2006" but is inserted as a zero value (i.e. "01/01/1900"). In additon to replacing {1} with a string, I've tried changing {1} to both '{1}' and #{1}#, both of which are "caught" by my try/catch on the INSERT. What am I doing wrong? Thanks very much. Regards Gary
I am trying to move my application (asp.net) from a non-paged select to a paged query.
I am having a problem. Below is my Stored Procedure. The first Query in the procedure works...the rest (which are commented out ALL work interactively, but fail when the program tries to access....The ONLY THING I change is the stored procedure I switch the comment lines to the non-paged procedure and it works, I try to use the any of the paged procedures and it fails with the same error (included below)
I can't see where any of the queries are returning different results. I have also included the program abort that happens, it is the same for all of the paged queries.
ALTER PROCEDURE dbo.puse_equipment_GetAllTypedEquipment ( @EquipmentTypeId int, @StartRowIndex int, @MaximumRows int ) AS
-- ************************************************************************************************ -- Non-Paged OUTPUT -- THIS WORKS!!!!! SET NOCOUNT ON SELECT Equipment.*, EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId where Equipment.EquipmentTypeId = @EquipmentTypeId AND Equipment.IsDeleted = 0
/* -- ************************************************************************************************ -- Using a Temp Table --THIS WORKS INTERACTIVELY, But NOT When Called by the program SET NOCOUNT ON create table #PagedEquipment ( IndexId int IDENTITY(1,1) Not NULL, EquipId int )
-- Insert the rows from Equipment into the PagedEquipment table Insert INTO #PagedEquipment (EquipId) select EquipmentId From Equipment WHERE IsDeleted = 0
SELECT #PagedEquipment.IndexId, *,EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN #PagedEquipment with (nolock) on Equipment.EquipmentId = #PagedEquipment.EquipId INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId Where #PagedEquipment.IndexId Between (@StartRowIndex) AND (@StartRowIndex + @MaximumRows +1) */
/* -- ********************************************************************************************** --Using the With to create a temp table (in memory)..works interactively but fails when --called by the application.. --THIS WORKS INTERACTIVELY, But NOT When Called by the program Set NOCOUNT ON; With PagedEquipment AS ( SELECT EquipmentId, ROW_NUMBER() OVER (Order by Equipment.EquipmentId) AS RowNumber FROM Equipment WHERE EquipmentTypeId = @EquipmentTypeId AND IsDeleted = 0 )
SELECT RowNumber, Equipment.*, EquipmentCategory.Name as Category, EquipmentType.Name as EquipmentType FROM PagedEquipment INNER JOIN Equipment ON Equipment.EquipmentId = PagedEquipment.EquipmentId INNER JOIN EquipmentCategory ON Equipment.CategoryId = EquipmentCategory.CategoryId INNER JOIN EquipmentType ON Equipment.EquipmentTypeId = EquipmentType.EquipmentTypeId WHERE PagedEquipment.RowNumber Between (@StartRowIndex+1) AND (@StartRowIndex+1+@MaximumRows) return -- ******************************************************************************************* */
/* -- ******************************************************************************************** --nested selects --THIS WORKS INTERACTIVELY, BUT NOT WHEN CALLED FROM THE PROGRAM SET NOCOUNT ON Select * From ( Select Row_Number() OVER (Order By Equipment.EquipmentId) as RowNumber, Equipment.*, EquipmentType.Name as EquipmentType, EquipmentCategory.Name as Category FROM Equipment INNER JOIN EquipmentCategory ON EquipmentCategory.CategoryId = Equipment.CategoryId INNER JOIN EquipmentType ON EquipmentType.EquipmentTypeId = Equipment.EquipmentTypeId where Equipment.EquipmentTypeId = @EquipmentTypeId AND Equipment.IsDeleted = 0 ) equip Where equip.RowNumber between (@StartRowIndex+1) AND (@StartRowIndex + 1 + @MaximumRows ) -- ************************************************************************************************ */
Server Error in '/pUse' Application. --------------------------------------------------------------------------------
Arithmetic overflow error converting expression to data type int. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type int.
Source Error:
Line 148: { Line 149: List<EquipmentDetails> equipment = new List<EquipmentDetails>(); Line 150: while (reader.Read()) Line 151: equipment.Add(GetEquipmentFromReader(reader)); Line 152: return equipment;
Source File: c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs Line: 150
Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type int.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932 System.Data.SqlClient.SqlDataReader.HasMoreRows() +150 System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout) +212 System.Data.SqlClient.SqlDataReader.Read() +9 PredominantUse.DAL.Equipment.EquipmentProvider.GetEquipmentCollectionFromReader(IDataReader reader) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentEquipmentProvider.cs:150 PredominantUse.DAL.Equipment.SqlClient.SqlEquipmentProvider.GetTypedEquipmentList(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeDALEquipmentSqlClientSqlEquipmentProvider.cs:103 PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId, Int32 StartRowIndex, Int32 MaximumRows) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:259 PredominantUse.BLL.Equipment.GetTypedEquipment(Int32 EquipmentTypeId) in c:Documents and SettingsBrianDesktoppuseApp_CodeBLLEquipmentEquipment.cs:238
-------------------------------------------------------------------------------- Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
Hi folks,Hopefully this is a simple fix, but I keep getting Syntax error withthis statement in an MS SQL DTS statement and in Query Analyzer:Update A Set A.deptcode = A.deptcode,A.type = A.Type,a.TotalExpenseUnit = (a.LaborExpenseUnit + a.OtherExpenseUnit)Where a.Type in ('FYTD04', 'Prior Year','Budget')From Data_Unsorted A Join Data_Unsorted B OnA.deptcode = B.deptcode and A.type = B.TypeBelow is the error from Query Analyzer:Server: Msg 156, Level 15, State 1, Line 5Incorrect syntax near the keyword 'From'.Where do I place my Where..In statement since I only want to limit theUpdate to run for items where a.type is FYTD04, Prior Year, or Budget?Thanks,Alex.
The following query has been working for months and the other day it just stopped. I get no error, it just never finishes. It used to take 20 minutes. Nothing has changed that I know of.
The query is designed to insert the new records from the t_DTM_DATA_STAGING into t_DTM_DATA_STAGING2 using the t_DTM_DATA_1 as the outer join.
Average record count for t_DTM_DATA_STAGING is 2 Million Current record count in t_DTM_DATA_1 - 267 Million Both tables have clustered indexes made up of the 10 fields in the join below.
Any Ideas??
SET QUOTED_IDENTIFIER ON INSERT INTO [DTM].[dbo].[t_DTM_DATA_STAGING2] ([CP] , ,[MAJ] ,[MINR] ,[LOCN] ,[DPT] ,[YEAR] ,[PD] ,[WK] ,[TRDT] ,[SYSTEM] ,[AMOUNT] ,[DESCRIPTION] ,[GROUP] ,[VENDOR] ,[INVOICE] ,[IDAT] ,[PO_NUMBER] ,[DDAT] ,[RCV#] ,[RDAT] ,[RSP] ,[EXPLANATION] ,[UPLOAD_DATE] ,[UPLOAD_USER] ,[UPLOAD_NAME] ,[RELEASE_DATE] ,[RELEASE_USER] ,[RELEASE_NAME] ,[TRTM]) SELECT t_DTM_DATA_STAGING.CP, t_DTM_DATA_STAGING.CO, t_DTM_DATA_STAGING.MAJ, t_DTM_DATA_STAGING.MINR, t_DTM_DATA_STAGING.LOCN, t_DTM_DATA_STAGING.DPT, t_DTM_DATA_STAGING.YEAR, t_DTM_DATA_STAGING.PD, t_DTM_DATA_STAGING.WK, t_DTM_DATA_STAGING.TRDT, t_DTM_DATA_STAGING.SYSTEM, t_DTM_DATA_STAGING.AMOUNT, t_DTM_DATA_STAGING.DESCRIPTION, t_DTM_DATA_STAGING.[GROUP], t_DTM_DATA_STAGING.VENDOR, t_DTM_DATA_STAGING.INVOICE, t_DTM_DATA_STAGING.IDAT, t_DTM_DATA_STAGING.PO_NUMBER, t_DTM_DATA_STAGING.DDAT, t_DTM_DATA_STAGING.RCV#, t_DTM_DATA_STAGING.RDAT, t_DTM_DATA_STAGING.RSP, t_DTM_DATA_STAGING.EXPLANATION, t_DTM_DATA_STAGING.UPLOAD_DATE, t_DTM_DATA_STAGING.UPLOAD_USER, t_DTM_DATA_STAGING.UPLOAD_NAME, t_DTM_DATA_STAGING.RELEASE_DATE, t_DTM_DATA_STAGING.RELEASE_USER, t_DTM_DATA_STAGING.RELEASE_NAME, t_DTM_DATA_STAGING.TRTM FROM t_DTM_DATA_STAGING LEFT OUTER JOIN t_DTM_DATA AS t_DTM_DATA_1 ON t_DTM_DATA_STAGING.TRTM = t_DTM_DATA_1.TRTM AND t_DTM_DATA_STAGING.TRDT = t_DTM_DATA_1.TRDT AND t_DTM_DATA_STAGING.PD = t_DTM_DATA_1.PD AND t_DTM_DATA_STAGING.YEAR = t_DTM_DATA_1.YEAR AND t_DTM_DATA_STAGING.DPT = t_DTM_DATA_1.DPT AND t_DTM_DATA_STAGING.LOCN = t_DTM_DATA_1.LOCN AND t_DTM_DATA_STAGING.MINR = t_DTM_DATA_1.MINR AND t_DTM_DATA_STAGING.MAJ = t_DTM_DATA_1.MAJ AND t_DTM_DATA_STAGING.CO = t_DTM_DATA_1.CO AND t_DTM_DATA_STAGING.CP = t_DTM_DATA_1.CP WHERE (t_DTM_DATA_1.CP IS NULL)
I have a couple of queries that i run on my server but i need to automate them now. I have created a new job in the sql agent jobs and set up my steps accordingly.
My queries all run in a query window without trouble and they also parse in the command window of the sql agen job-step screen. When I run them manually, it fails with the first query moaning about my variables etc etc.
Is there some sort of limitation that i am not aware of or something?
