Declare @mx int
Set @mx = (select max(score)
from ,,,
where ,,,,and
substring(city,1,1)='g')
select score,@mx
from ,,,,
where ,,,, and
substring(city,1,1)='g')
group by score
go
this seems to work for small tables
but when table is big and substring(city,1,1)='g')
condition is not met it takes aweful time like goes
in an infinit loop.
--
Sent by 3 from yahoo part of com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com
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 have a simple SSIS package running under SQL 2005 SP2a.
In the data flow, I have several lookup transforms with error outputs. Each error output links to its own audit transform and then writes data to its own flat file destination.
After the data flow is complete, my control flow will then use a ForEach file container to mail the flat files to the source system data owners with a message about incomplete/inconsistent source data. I am effectively providing feedback to the source owners so that we may improve the quality of data that gets sent to us.
My problem is that the flat file (which contains the offending rows) seems to get created everytime even when there were no errors in the lookup in question. Thus my ForEach container will always send a mail to the data source teams even if there were no errors as the error flat file will always exist albeit empty.
How can I stop this happening? How can I only create flat files when there really were errors? How can I prevent the source teams from receiving feedback emails when there is no reason to?
I have a sql server automatic job set up containing 2 steps.First step is an Operating System Command (CmdExec) type job whichruns a .bat file which copies across a backup file to this machine.The second step is a Transact -SQL Script (TSQL) type job which runsthe RESTORE DATABASE REPLACE command which restores the backup filecopied across in step 1.Step 1's advanced option says: On Success Action: Goto Step [2]Restore the database.Step 2's advanced option says: On Success Action: Quit the jobreporting success.On clicking ok I receive the error message:"WARNING: The following job step(s) cannot be reached with currentflow logic: [1] Transfer the Backup File. Are you sure this is whatyou want".The job fails to run.Both steps do run successfully when set up as two separate jobs - notlike this which has the two steps in one job.Anyone have any ideas as to what the flow logic problem is?Thiko!
every few days i'm getting an error in the application log , eventID 824, saying 'SQL Server detected a logical consistency-based i/o error; incorrect checksum. it then recommends completing a full database consistency check (dbcc checkdb).
i have run the dbcc checkdb ('databasename') and it is returning an error msg of:
Msg 8697, Level 16, State 215, Line 1 An internal error occurred in DBCC which prevented further processing Msg 8921, Level 16, State 1, Line 1 Check terminated. A failure was detected while collecting facts. Possibly tempdb out of space or a system table is inconsistent. Check previous errors
i have oodles of disk space left. any ideas on where to go from here.
I could deploy across my environment, which is a mix of 2008R2/2012 servers, to give some information on log files. Running into a silly issue right off the bat. The table that DBCC LogInfo() conjures out of magic is different between the two. In 2012 it gained the RecoveryUnitID column. So I'm trying to write some logic to create a temp table based on which version is running. I would like to avoid a global temp table if possible. Here's what I've tried:
sp_executesql creates a table outside of the scope of my session: DECLARE @PrVers NVARCHAR(128) , @PrVersNum DECIMAL(10,2) , @StageTable NVARCHAR(1024) = N''
Can anyone see where my logic may have gone a stray? A user would be attempting to update a gridview row, I'm storing the amount of an item and the qty they purchased. If the order was for $150 and the grid has two item 1 x $50 and 2 x $50 the order already totals $150, so if the user tries to change the first item to a qty of 2 x $50 the total would be $200 and this would be more than the total payment or $150. I want to then tell them they cannot do this. @AP_ID Int,@AI_ID int,@PurchaseAmount money,@PurchaseQTY int,@LastUpdate datetime,@LastUpdateBy nvarchar(50),@Receipt_ID intASdeclare @current_item_total decimal(18,2)declare @new_item_total decimal(18,2)declare @total_payments decimal(18,2)declare @current_purchase table(receipt_id int,purchase_amt money)INSERT INTO @current_purchase SELECT tblPurchase.Receipt_ID,(tblPurchase.PurchaseAmount * tblPurchase.PurchaseQty) as purchase_amtFROM tblPurchase INNER JOIN tblReceipts ON tblPurchase.Receipt_ID = tblReceipts.Receipt_IDWHERE tblReceipts.Receipt_ID=@receipt_id--Get total already saved to recordselect @current_item_total = sum(purchase_amt) from @current_purchase--Get total amount of payment saved to recordselect @total_payments = sum(tblReceipts.AmountPaid) from tblReceipts where tblReceipts.receipt_id=@receipt_id--Get total amount user is trying to save to recordset @new_item_total = (@PurchaseAmount * @PurchaseQTY)--If current total plus new total is greater than total payment, tell user they cannot do this.IF ((@current_item_total + @new_item_total) > @total_payments)BEGINSELECT 'You are attempting to add more than your total payment.' AS MESSAGEENDELSEBEGIN--UPDATE ROWUPDATE tblPurchase SET [AI_ID]=@AI_ID,[PurchaseAmount]=@PurchaseAmount,PurchaseQTY=@PurchaseQTY,LastUpdate=@LastUpdate,LastUpdateBy=@LastUpdateBy WHERE [AP_ID]=@AP_ID SELECT 'Item was updated.' AS MESSAGEEND
Trying to bind some data to a datalist for a report.
User selects an Industry from a dropdown list and then I dump all records for that industry. However in order to parse some of the record field values into names (I.E. from a 1 to the actual company name) for some records I have to read TABLE_ONE and for other records I might have to read TABLE_TWO depending on the value of FIELD_ONE.
If FIELD_ONE = "A" then I get the NAME from TABLE_ONE. If FIELD_ONE = "B" then I get the NAME from TABLE_TWO. If FIELD_ONE = "C" then I get the NAME from TABLE_THREE.
I'm lost at how to get started on this. I thought about adding IF statements to my query but these won't work because I'm not passing in the value of FIELD_ONE ahead of time - it's part of the query. So I thought maybe I could do a pre-read and store all FIELD_ONE values in an ArrayList and pass these in as parameters, but the stored proc is only being called once - so that won't work.
What i'm trying to do is if this column dt.IN_DIV_NO is populated take that value first if it's null than rr.AIQ_R_DIVISION_NO and if that column is null than rr.F_DIVISION_NO.
This is what i came up with, will it evaluate the way i need it to?
case When dt.IN_DIV_NO is not null then dt.IN_DIV_NO else case when rr.AIQ_R_DIVISION_NO is null then rr.F_DIVISION_NO end end as Current_DIV
Select * from TAB_A where state not in (1,4) and create_date < '2008-01-01' and ( (x != 2 and y != 1 and z != 4) or (x != 6 and y != 3 and z != 1) or (x != 8 and y != 0 and z != 9) )
then for example i am getting results where x,y and z is equal to one or more of above combinations ?
Now i vaguely remember that using or with a != messes up the logic ? if so can i use an NOR ? does it exist ?
I am looking for the best approach to update a table's column based on the results of two other different tables. My tables structure is as follows.
Table 1 has columns A and B (tblemployee has location and employeenumber ) Table 2 has columns C and A (tblocation has locationID (identity) and location) Table 3 has coumns C and B (tblcountry locationID (foreign key) and employeenumber.)
I want to update Table 3 (tblcountry) with the new locationID if and employee changes location or gets miscoded using an SSIS package.
I am having a little problem with my logic. i have a table simplehoursassignment that has a field named br. i need to get the value from simplehoursassigment & say if @mybit AND br > 0 set each day.
I have this query that is returning the same result twice and i cannot find why. I only have one record in Subquote. Does anyone know what the problem is?
Code Snippet SELECT s.* FROM Quote q INNER JOIN SubQuote s ON q.id = s.quoteID INNER JOIN TakeOffSheetItem t ON t.quoteID_takeoffitem = q.id AND t.subQuoteID_takeoffitem = s.subquoteid INNER JOIN PipeGroup p ON p.quoteID_PipeGroup = q.id AND p.subQuoteID_PipeGroup = s.subQuoteID WHERE (q.id = 1 AND q.deleted = 0 AND s.deleted = 0 AND t.deleted_takeoffitem = 0 AND p.deleted_PipeGroup = 0) ORDER BY s.subquoteid
I am trying to figure out how to set up this database. Basically, there are products with their associated fields. Each product can belong to multiple categories, and each category also has subcategories.So far I have the following, but not sure if this is the best way to set it up...TABLE Products:product_id (int) (1-many relationship to product_id in Table Product_Category)sku (int)descriptionpriceTABLE Categorycategory_id (int) (1-many relationship to category_id in Table Product_Category)nameTABLE SubCategorysubcategory_id (int) (1-many relationship to subcategory_id in Table Product_Category)category_id (int)nameTABLE Product_Categoryprodcat_id (int) product_id (int) (many-1 relationship to product_id in Table Products)category_id (int) (many-1 relationship to category_id in Table Category)subcategory_id (int) (many-1 relationship to subcategory_id in Table SubCategory) Thanks,Mick
Suppose in the table below the left column is called intNumber and right column is called strItem.I want to select the rows where the intNumber number appears for the first time(the ones marked with arrows), how do I do it?
I'm trying to figure out how to track what messages have been read by what users in a message board program we wrote. The users are identified by an ID number and each message has a unique number. Let's say there are 10000 users and 200,000 messages. What would be the database setup/logic to track every user as to what messages they had read or not read? Maybe I'm missing something but it does not seem to be a trivial task.
Hi there! Hope somebody got some better insight into this problem than i have. I'm struggling with some db logic. Overview: I have a client who is a publisher. They would like to post all their advert-size specs for each magazine to their web site. My first thought was that ill be able to build one table and populate it with the information for each magazine. I was wrong. Each magazine comes in a different size. Thus each magazine will have different advert-sizes as well. Layout for a magazine will look something like this: Size Trim Type Bleed Full Page 280x440 270x430 290x450 Half Page 140x220 130x210 150x230 etc... Some mags will not have values for Half pages since they dont print half pages and others will not have specs for Bleed. Because of this - as an easy way out I created a table per magazine. It works but i dont think its very smart.
Break it down! Ok so what im trying as a solution is to have three tables. The 1st table will hold the magID and Size values (e.g Half Page). The 2nd table will also have the magID and the Trim, Type, Bleed info. The 3rd table holds magID, sizeID, specID and the actual value (140x220). I thought that this would be better because within these three tables i can store the information for each magazine regardless of their differences. Brilliant!
The Problem. The problem comes when i have to put it all back together again. I need to represent this data in a table so i can bind it to a datagrid. I have NO idea how to do this. What i THINK i need to have is some temp table created on the fly. The row names for this temp table will come from the 1st table. The column names will come from the 2nd table and the values for each field will come from the 3rd table bound by foreign keys. I've somewhat managed to do this with INNER JOINS. But it doesn't give the desired result. I need to set row and column NAMES using tables 1 & 2 then populate the columns with table 3, then bind to a datagrid.
Any ideas on how i could manage this?
If you made it tis far through my question then thanks anyways! I hope you can help me out!
i want to write a query that takes any one specific person's schedule for an entire specific day, adds an arbitrary number of days to datStartTime, and finally inserts the PKSlotINDEX corresponding to the calcultated StartTime and fkPersonINDEX.
wow complicated isnt it...
the goal is to take the schedule of one day for one person and copy it to another day; i can scrap my current layout if necessary.
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.
The field [q5] has one of three values in it: "Happy", "Unhappy" or "Neither".
I have a list of about 50 session ID's. If a record in [tblQuiz] has a [sessionid] that matches one in this list, then: if [q5]= 'Happy' then change it to 'Unhappy' if [q5]= 'Unhappy' then change it to 'Happy'
The only way I can think of is to change all of one matching record type to some other value, then change all the other type, then change all the original type back to the other type. Which doesn't make much sense even when I've written it down, much less when I'm doing it and have to remember where I'm at. Is there a better way?
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.
Below is a sample data set. Each episode consists of several unique chart_component_key. I need to be able to pull the MAX status_date for the episode, but only if all deficiency_status is equal to 'C', which signifies that the entire episode has been completed. I thought either nested select statements or if/end logic might work but I am stuck.
1) Comparing 2 versions of same database. 2) Keeping track of schema versions of Tables & Stored Procedures so that I can rollback to previous schema.
I have started with Sys.Objects, Sys.Tables & Sys.Columns and I kept things rely on Object_ID but the problem started whenever I change some part of Table. It assigns new Object_ID to the table whenever I change it; moreover creationdate of object is also changed. So I assume that it drops and creates new table.
Please throw some light on following questions, 1) When and How Object_ID Changes? 2) Is there any other thing in database which is Unique & Not Changing with alteration of object? 3) What should be the preferred way to Compare 2 Database?
I hate readymade tools as they charge per user and my requirements are very small.
I would be recieving the vName as an input. I need to go through the table to make sure that the rules (Min and Max) are not intermingeled. Rules can be within another rule but can not cross.
Every time the SP is fired I need to be able to check if this rule is valid or not by comparing it to all the rules in the table. if it is not valid I need to fire an error back.
I am just getting started with the integration between SQL Server and ASP.NET 2. I am at a loss of how to move data in and out.
I need to track a vehicle inventory, properties to be track are, year, make, and model, color and so on. These have not presented me with much of a problem; however the vehicle options have left me totally confused.
I have an Options table and a Vehicle table. The Options table has a numeric primary key OptionsID, and a field for the option. The Vehicle table has a numeric primary key, VehicleID. I have tried using a join table named VehicleOptions with two fields, VehicleID and OptionsID. I have tried a join table (VehicleOptions) with a field for the VehicleID and separate fields for every OptionsID, inputting true or false, as needed.
I have tried passing a string with each OptionID separated with a comma, to a stored procedure, parsing the string, looping through setting the value to true or false as needed.
I have also tried matching the VehicleID with the OptionsID in the code for the web page then passing this to a separate stored procedure.
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?
