Can I delete the DLL-File C:\PATH\TO\DLL\FILE.DLL after creating an assembly with CREATE ASSEMBLY MyAssemblyName FROM 'C:\PATH\TO\DLL\FILE.DLL' ? Everything seems to work fine after deleting the file. But SELECT * FROM sys.assembly_files shows the path of the file in the name column. Can this cause problems? (or is the content of...
I want to create a stored procedure. If the parameter is -1 then there should not be a where clause on that column else there should be a WHERE clause. What's the best way to do it without a lot of IF branching? I checked the archive. There are a few similar questions but not exactly the same. CREATE PROCEDURE report ( @site int, ...
Hello, I am working with 3 tables, trying to pull a list that match certain criteria. I need a list of companies from the companies table, that have an active opportunity (iactive is 1 in opportunities table) AND have a record in the notes table of type order or order2. Companies Table +------+------------------+ | cid | ccyname ...
One of the aspects of computer science/practical software engineering I am weaker at is actually doing significant work in database systems. That is to say, I can do simple queries on smaller datasets, no problem. However, working with complex queries on large datasets invokes a level of understanding of databases beyond me right now. Fo...
I have a field with a date/time value like this: 2009-11-17 18:40:05 It's in UTC. In the query how can I convert this to EST? I'm trying something like this but it throws an error. // datetime is the field name SELECT FROM_TZ(TIMESTAMP TO_DATE(datetime, 'yyyy-mm-dd hh24miss'), 'EST') AS DT FROM db_name ...
The SQL is: execute ('delete from HttpRequests where Date < ''2009-08-' + convert(nvarchar(max), 0) + '''') The error is Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'convert'. Commenting out the convert part removes the error. Whats wrong? ...
I have a coworker who came across DBMS_SQL.to_refcursor which would be a great solution for him to pass back the refcursor he needs, however we are running Oracle 10g and this feature is only available in 11g. Is there an easy equivalent to this in Oracle 10g? We have developed an alternate way of coding our solution but it would be ...
I have values being cut off and would like to display the full values. Sqlite3 -column -header locations.dbs " select n.namelist, f.state, t.state from names n left join locations l on l.id = n.id left join statenames f on f.st = l.st left join statenames t on t.st = l.stto where n.timing > 200601 and count(n.timing)<=15" Which gives ...
I'm accessing a data context object that is auto-generated by using LINQ to SQL. The SQL database is a SQL Server 2000 box. The class I'm working with is a SQL View. I have a statement that is similar to this: query = _context.OrderDetails .Where(w => w.Product == "TEST") .OrderBy(o => o.DateCompleted) .ThenBy(t => t.LineIte...
I have a set of string & numbers that I'd like to use between databases. the idea is that table A has a row of data with 2 values acting as a single primary key Table B has the same 2 values of data in a single row, or it doesn't... So... Id like to find out how many values from Table A (2 column values in a single row) match in colu...
Is there a best practice when it comes to using Datasets in SSRS? Should I be writing a query for the dataset that pulls directly from tables, or should I be creating a view in the source database and then querying the view via the Dataset? I understand there are some scenarios where the view would be better (centralizing a view for use...
Currently, I'm peppering form submissions to account for single quotes & other garbage. $form_field_value= str_replace("'", "''", stripslashes($form_field_value)); It is to prep the value for insertion using: $insert_sql = "insert into table (field) values ('".$form_field_value."')"; odbc_exec($conn, $insert_sql); Essentially,...
I have a table(offer) with three columns, id, product_id and price. offer ----- id (integer) product_id (integer) price (decimal) I wanted to fire a SQL query which will return the number of offers between a price range. range should be like 0-1, 1-2, 2-3 etc price_lower price_upper number_of_offers ------------------------...
I have a select query in MySQL shown below: This query executes and produces results in the form of a table Current | Past1 | Past2 | Past 3 | Past4 200 600 800 000 88 I would like to transpose these results to get information in the form: Therefore I would like the reults to be (transposed) Current 2...
I have 3 tables, one which represents a "supertype", with an ID column. Two other tables are each subtypes, with an ID column that is a foreign key to the supertype table, plus a subtype-specific column. I want a query that returns all the data, as well as a column I can use as a discriminator that tells me what table the row came from...
I have a select query in MySQL shown below: This query executes and produces results in the form of a table Current | Past1 | Past2 | Past 3 | Past4 200 600 800 000 88 ----------------------------------------------- I would like to transpose these results to get information in the form: Therefore I would l...
I want to select *, and not have to type out all individual columns, but I also want to include a custom column with a case statement. I tried the following: select *, (case when PRI_VAL = 1 then 'High' when PRI_VAL = 2 then 'Med' when PRI_VAL = 3 then 'Low' end) as PRIORITY from MYTABLE; But...
private void BuildGridView2() { GridView1.DataSource = new Select() .From("NewsReleases") .Where("RelMonth").IsEqualTo(this.ddlAward.SelectedValue) .And("RelYear").IsEqualTo(this.ddlYear.SelectedValue) .OrderAsc("RelDate") .ExecuteDataSet(); } The SQL statement above is not working for some reason. Is there a ...
I have a table T with columns X, Y and Z. I need to retrieve non-group by column X value of the first row of every group, group by column Y value, and MIN of column Z value in a SQL single query. Please could you help me out. ...
I have a simple SQLite table called "message": sequence INTEGER PRIMARY KEY type TEXT content TEXT I want to get the content of the last message of each type (as determined by its sequence). To my surprise, the following simple query works: SELECT MAX(sequence), type, content FROM message GROUP BY type Surprise, because I know that...