Search Results

Search found 28930 results on 1158 pages for 'sql ce'.

Page 345/1158 | < Previous Page | 341 342 343 344 345 346 347 348 349 350 351 352  | Next Page >

  • Sql Trigger Trouble

    - by SImon
    Hey guys i cant get this trigger to work, ive worked on it for an hour or so and cant see to figure out where im going wrong, any help would be appreciated CREATE OR REPLACE TRIGGER allergy BEFORE INSERT ON DECLARE med VARCHAR2(20); BEGIN SELECT v.medication RCD.specify INTO med FROM visit v, relcondetails RCD WHERE :new.medication = v.medication AND RCD.specifiy = 'allergies'; IF med = allergies THEN RAISE_APPLICATION_ERROR(-20000, 'Patient Is alergic to this medication'); END IF; END allergy; When put into oracle ERROR at line 6: ORA-04079: invalid trigger specification

    Read the article

  • SQL not yielding expected results

    - by AnonJr
    I have three tables related to this particular query: Lawson_Employees: LawsonID (pk), LastName, FirstName, AccCode (numeric) Lawson_DeptInfo: AccCode (pk), AccCode2 (don't ask, HR set up), DisplayName tblExpirationDates: EmpID (pk), ACLS (date), EP (date), CPR (date), CPR_Imported (date), PALS (date), Note The goal is to get the data I need to report on all those who have already expired in one or more certification, or are going to expire in the next 90 days. Some important notes: This is being run as part of a vbScript, so the 90-day date is being calculated when the script is run. I'm using 2010-08-31 as a placeholder since its the result at the time this question is being posted. All cards expire at the end of the month. (which is why the above date is for the end of August and not 90 days on the dot) A valid EP card supersedes ACLS certification, but only the latter is required of some employees. (wasn't going to worry about it until I got this question answered, but if I can get the help I'll take it) The CPR column contains the expiration date for the last class they took with us. (NULL if they didn't take any classes with us) The CPR_Imported column contains the expiration date for the last class they took somewhere else. (NULL if they didn't take it elsewhere, and bravo for following policy) The distinction between CPR classes is important for other reports. For purposes of this report, all we really care about is which one is the most current - or at least is currently current. If I have to, I'll ignore ACLS and PALS for the time being as it is non-compliance with CPR training that is the big issue at the moment. (not that the others won't be, but they weren't mentioned in the last meeting...) Here's the query I have so far, which is giving me good data: SELECT iEmp.LawsonID, iEmp.LastName, iEmp.FirstName, dept.AccCode2, dept.DisplayName, Exp.ACLS, Exp.EP, Exp.CPR, Exp.CPR_Imported, Exp.PALS, Exp.Note FROM (Lawson_Employees AS iEmp LEFT JOIN Lawson_DeptInfo AS dept ON dept.AccCode = iEmp.AccCode) LEFT JOIN tblExpirationDates AS Exp ON iEmp.LawsonID = Exp.EmpID WHERE iEmp.CurrentEmp = 1 AND ((Exp.ACLS <= #2010-08-31# AND Exp.ACLS IS NOT NULL) OR (Exp.CPR <= #2010-08-31# AND Exp.CPR_Imported <= #2010-08-31#) OR (Exp.PALS <= #2010-08-31# AND Exp.PALS IS NOT NULL)) ORDER BY dept.AccCode2, iEmp.LastName, iEmp.FirstName; After perusing the result set, I think I'm missing some expiration dates that should be in the result set. Am I missing something? This is the sucky part of being the only developer in the department... no one to ask for a little help.

    Read the article

  • SQLServer - Test the result of a stored procedure

    - by Melursus
    In Microsoft SQLServer, it is possible to test the result of a stored procedure to know if the result return rows or nothing ? Example : EXEC _sp_MySp 1, 2, 3 IF @@ROWCOUNT = 0 BEGIN PRINT('Empty') END ELSE BEGIN PRINT(@@ROWCOUNT) END But @@ROWCOUNT always return 0 so maybe is there another way of doing this ?

    Read the article

  • Keeping Linq to SQL alive when using ViewModels (ASP.NET MVC)

    - by Kohan
    I have recently started using custom ViewModels (For example, CustomerViewModel) public class CustomerViewModel { public IList<Customer> Customers{ get; set; } public int ProductId{ get; set; } public CustomerViewModel(IList<Customer> customers, int productId) { this.Customers= customers; this.ProductId= productId; } public CustomerViewModel() { } } ... and am now passing them to my view instead of the Entities themselves (for example, var Custs = repository.getAllCusts(id) ) as it seems good practice to do so. The problem i have encountered is that when using ViewModels; by the time it has got to the the view i have lost the ability to lazy load on customers. I do not believe this was the case before using them. Is it possible to retain the ability of Lazy Loading while still using ViewModels? Or do i have to eager load using this method? Thanks, Kohan.

    Read the article

  • shreding xml column

    - by csetzkorn
    Hi, I have a XML column which contains XML like this: <Set> <Element> <ID> 1 </ID> <List> <ListElement> <Part1> ListElement 1 </Part1> </ListElement> <ListElement> <Part1> ListElement2 </Part1> </ListElement> </List> </Element> <Element> <ID> 2 </ID> <List> <ListElement> <Part1> ListElement3 </Part1> </ListElement> <ListElement> <Part1> ListElement4 </Part1> </ListElement> </List> </Element> </Set> I would like to shred this into a relation table containing this: ID, ListElement 1, ListElement1 1, ListElement2 2, ListElement3 2, ListElement4 I am able to obtain the content of the Parts using something like this: select List.value('(Part1/text())[1]', 'varchar(max)') as test from Table CROSS APPLY xml.nodes('// Element/List/ListElement') AS List(List) but I have not yet achieved to keep the ‘foreign key’ (the ID value). Thanks. Best wishes, Christian

    Read the article

  • Get top 2 rows from each unique field in a column

    - by Sai
    I have a table named tblItemResources in which I want to get the only 2 rows from each unique field in a column named effectiveDate (order by: ascending): tblItemResources Table |   empID   |   effectiveDate    |    Company   |    Description |   0-123    |    2014-01-23     |   DFD Comp  |   Analyst |   0-234    |    2014-01-23     |   ABC Comp   |  Manager |   0-222    |    2012-02-19     |   CDC Comp  |  Janitor |   0-213    |    2012-03-13     |   CBB Comp  |  Teller and so on. Any help would be much appreciated.

    Read the article

  • sql insert statement with a lot of same where clause and one different where cluase

    - by william
    I m sry if the title is not clear. Here's my proble. I created a new table which will show total, average and maximum values. I have to insert the results into that table. That table will have only 4 rows. No Appointment, Appointment Early, Appointment Late and Appointment Punctual. So.. I have sth like.. insert into newTable select 'No Appointment' as 'Col1', avg statement, total statement, max statement from orgTable where (general conditions) and (unique condition to check NO APPOINTMENT); I have to do that same thing for another 3 rows.. where only the unique condition is different to check early, punctual or late.. So..the statement is super long. I wanna reduce the size.. How can I achieve that?

    Read the article

  • SQL HAVING COUNT and JOIN

    - by user1833274
    I have tried to this query: What are the doctors that work on less than 2 Hospitals. I have these tables: CREATE TABLE Hospital ( hid INT PRIMARY KEY, name VARCHAR(127) UNIQUE, country VARCHAR(127), area INT ); CREATE TABLE Doctor ( ic INT PRIMARY KEY, name VARCHAR(127), date_of_birth INT, ); CREATE TABLE Work ( hid INT, ic INT, since INT, FOREIGN KEY (hid) REFERENCES Hospital (hid), FOREIGN KEY (ic) REFERENCES Doctor (ic), PRIMARY KEY (hid,ic) ); I tried with this: SELECT DISTINCT D.ic FROM Doctor D, Work W JOIN Hospital H ON (H.hid = W.hid) WHERE D.bi = W.bi GROUP BY (D.ic) HAVING COUNT(H.hid) < 2 ;

    Read the article

  • need help with a simple SQL update statement.

    - by Tony
    There's a field with type of varchar. It actually stores a float point string. Like 2.0 , 12.0 , 34.5 , 67.50 ... What I need is a update statement that remove the ending zeros of fields like 2.0 , 12.0 , change them to their integer representation , that is 2 , 12 ...,and leave 3.45 , 67.50 unchanged . How should I do this ? I am using oracle 10.

    Read the article

  • SQL query: Last but one rank for user

    - by Derk
    My table structure looks like this: create table rankings ( id IDENTITY NOT NULL, user_id INT NOT NULL, game_poule_id INT NOT NULL, rank INT NOT NULL, insertDate DATETIME NOT NULL, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (game_poule_id) REFERENCES game_poules(id) ON DELETE CASCADE ); All old rankings of users per game are saved in this table. Now I want to have the last but one rank in the table for all users in a gamepoule. Has someone an idea how to achive this? Thanks

    Read the article

  • JOIN in SQL with one-to-many relationship

    - by Kristopher Ives
    I'm making a tool to track calls to house/senate reps, and I have 2 tables of importance here: reps rep_id rep_name # and more info comments rep_id status # enum about result of contact comment I want to query for all reps joining the most recent associated comments and in some cases joining comments of a specific status, but there might not be any comments associated with that rep yet. THANKS!

    Read the article

  • Multi table Triggers SQL Server noob

    - by Chin
    I have a load of tables all with the same 2 datetime columns (lastModDate, dateAdded). I am wondering if I can set up global Insert Update trigger for these tables to set the datetime values. Or if not, what approaches are there? Any pointers much appreciated

    Read the article

  • How to remove duplicate records in a table?

    - by Mason Wheeler
    I've got a table in a testing DB that someone apparently got a little too trigger-happy on when running INSERT scripts to set it up. The schema looks like this: ID UNIQUEIDENTIFIER TYPE_INT SMALLINT SYSTEM_VALUE SMALLINT NAME VARCHAR MAPPED_VALUE VARCHAR It's supposed to have a few dozen rows. It has about 200,000, most of which are duplicates in which TYPE_INT, SYSTEM_VALUE, NAME and MAPPED_VALUE are all identical and ID is not. Now, I could probably make a script to clean this up that creates a temporary table in memory, uses INSERT .. SELECT DISTINCT to grab all the unique values, TRUNCATE the original table and then copy everything back. But is there a simpler way to do it, like a DELETE query with something special in the WHERE clause?

    Read the article

  • SQL Query Question ROW_CONCAT

    - by DaveC
    Hello Guy, I have been stuck on this problem for quite awhile... I hope someone out there can give me a hand. The following table is in my database: Product_ID Color Type 1 Red Leather 1 Silver Metal 1 Blue Leather 2 Orange Metal 2 Purple Metal I am trying to get the following output: Product_ID Type Color 1 Leather Red, Blue 1 Metal Silver 2 Metal Orange, Purple I know it has to do with some kind of double group by and a group_concat.... have been looking at this for an hour without figuring it out. Any help is much appreciated!!!

    Read the article

  • converting date data type into varchar

    - by Sheetal Inani
    I have some dates fields in table. These columns contain dates in the following format: mmddyy For example: 31/12/2010 00:00:00:0000 I need to import these values into a table which is set to varchar and numeric and formats dates like this: monthName varchar Year numeric(4,0) currently I'm using INSERT INTO [School].[dbo].[TeacherAttendenceDet] ([TeacherCode], [MonthName], [Year]) (SELECT MAX(employeecode), Datename(MONTH, dateofjoining) AS MONTH, Datepart(YEAR, dateofjoining) AS DATE FROM employeedet GROUP BY dateofjoining) but datename() gives result in date format.. I have to save it in varchar format How can I do this? this is employeemast table: EmployeeCode numeric(5, 0) PayScaleCode numeric(7, 0) DesignationCode varchar(50) CityCode numeric(5, 0) EmployeeName varchar(50) FatherName varchar(50) BirthDate varchar(50) DateOfJoining varchar(50) Address varchar(150) this is TeacherAttendenceDet table TeacherCode numeric(5, 0) Unchecked Year numeric(4, 0) Unchecked MonthName varchar(12) Unchecked i have to insert in teacherattendencedet table the monthname and year from employeemast

    Read the article

  • SQL Server db_owner

    - by andrew007
    Hi, in my SQL2008 I have a user which is in the "db_datareader", "db_datawriter" and "db_ddladmin" DB roles, however when he tries to modify a table with SSMS he receives a message saying: You are not logged in as the database owner or system administrator. You might not be able to save changes to tables that you do not own. Of course, I would like to avoid such message, but until now I did find the way... Therefore, I try to modify the user by adding him to the "db_owner" role, and of course I do not have the message above. My question is: Is it possible to keep the user in the "db_owner" role, but deny some actions like alter user or ? I try "alter any user" securable on DB level, but it does not work... THANKS!

    Read the article

  • sql rowlock on select statement

    - by David
    I have an ASP.Net webpage where the user selects a row for editing. I want to use the row lock on that row and once the user finishes the editing and updates another user can edit that row i.e. How can I use rowlock so that only one user can edit a row? Thank you

    Read the article

  • How to prompt user input parameters for SQL Queries in Access 2010

    - by user1848907
    SELECT Transactions.TransactionNumber FROM Transactions WHERE (((Transactions.Date)>=#11/23/12#)) AND (((Transactions.Date)<=#11/23/12#)); The above code returns all the transaction that happened between the specified dates. But I want those dates to be defined by the user every time the query is executed. I tried using the [] operators to have the user define the criteria in the WHERE, something like this: WHERE (((Transactions.Date)>=[Input a Date])) AND (((Transactions.Date)<=[Input a Date])); But microsoft Access throws a Syntax error message. The same happens when I include the # (date operators) like this WHERE (((Transactions.Date)>=#[Input a Date]#)) AND (((Transactions.Date)<=#[Input a Date]#)); Is there anopther way to manage dates that I'm not aware of or is asking a user for dates in a query out of the question

    Read the article

  • performing auditing in java with sql server DB - before and/or after do not get audited

    - by Domingos
    When auditing, sometimes the before value does not get audited, other times the after value does not get audited, other times both values do not get audited at all. After researching, I found out that only values from a specific codes table get audited. the code was: compareCodesTableInteger(audit, int, int, objectBefore, objectAfter, stringDescription, stringCodesTable); I then changed it to: compareCodesTableInteger(audit, int, int, objectBefore, objectAfter, stringDescription, booleanCheck ? stringCodesTableIfTrue : stringCodesTableIfFalse); Description: if objectBefore AND objectAfter are both from stringCodesTableIfTrue OR from stringCodesTableIfFalse, auditing takes place as expected. The problem is: most of the times, objectBefore is from stringCodesTableIfTrue, and objectAfter is from stringCodesTableIfFalse, or vice-versa. In this scenario auditing fails. How do I go around this? Please assist

    Read the article

  • Test the sequentiality of a column with a single SQL query

    - by LauriE
    Hey, I have a table that contains sets of sequential datasets, like that: ID set_ID some_column n 1 'set-1' 'aaaaaaaaaa' 1 2 'set-1' 'bbbbbbbbbb' 2 3 'set-1' 'cccccccccc' 3 4 'set-2' 'dddddddddd' 1 5 'set-2' 'eeeeeeeeee' 2 6 'set-3' 'ffffffffff' 2 7 'set-3' 'gggggggggg' 1 At the end of a transaction that makes several types of modifications to those rows, I would like to ensure that within a single set, all the values of "n" are still sequential (rollback otherwise). They do not need to be in the same order according to the PK, just sequential, like 1-2-3 or 3-1-2, but not like 1-3-4. Due to the fact that there might be thousands of rows within a single set I would prefer to do it in the db to avoid the overhead of fetching the data just for verification after making some small changes. Also there is the issue of concurrency. The way locking in InnoDB (repeatable read) works (as I understand) is that if I have an index on "n" then InnoDB also locks the "gaps" between values. If I combine set_ID and n to a single index, would that eliminate the problem of phantom rows appearing? Looks to me like a common problem. Any brilliant ideas? Thanks! Note: using MySQL + InnoDB

    Read the article

  • SQL Syntax for Complex Scenario (Deals)

    - by Yisman
    hello everyone i have a complex query to be written but cannot figure it out here are my tables Sales --one row for each sale made in the system SaleProducts --one row for each line in the invoice (similar to OrderDetails in NW) Deals --a list of possible deals/offers that a sale may be entitled to DealProducts --a list of quantities of products that must be purchased in order to get a deal now im trying to make a query which will tell me for each sale which deals he may get the relevant fields are: Sales: SaleID (PK) SaleProducts: SaleID (FK), ProductID (FK) Deals: DealID (PK) DealProducts: DealID(FK), ProductID(FK), Mandatories (int) for required qty i believe that i should be able to use some sort of cross join or outer join, but it aint working here is one sample (of about 30 things i tried) SELECT DealProducts.DealID, DealProducts.ProductID, DealProducts.Mandatories, viwSaleProductCount.SaleID, viwSaleProductCount.ProductCount FROM DealProducts LEFT OUTER JOIN viwSaleProductCount ON DealProducts.ProductID = viwSaleProductCount.ProductID GROUP BY DealProducts.DealID, DealProducts.ProductID, DealProducts.Mandatories, viwSaleProductCount.SaleID, viwSaleProductCount.ProductCount the problem is that it doesnt show any product deals that r not fullfiled (probably because of the productid join). i need that also sales that dont have the requiremnets show up, then i can filter out any saleid that exists in this query "where AmountBought thank you for your help

    Read the article

  • Incorrect syntax near the keyword 'select' while execuing query

    - by sam
    I am getting Incorrect syntax near the keyword 'select' after executing the following code. declare @c int SELECT @c = COUNT(*) FROM (select id, max(date_stored) from table B INNER JOIN table P ON B.id = P.id where id = 3) select @c I want to select total no of records having max stored dates in database. Can any one plz tell what I am doing wrong

    Read the article

< Previous Page | 341 342 343 344 345 346 347 348 349 350 351 352  | Next Page >