How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT? For example how do a SELECT like: SELECT foo, bar FROM foobar; Hint : BEGIN SELECT foo, bar FROM foobar; END; doesn't work. ...
Does anyone know of a SQL library in ASP.NET that can be used to manage tables? Eg SQLTable table = new SQLTable(); table.AddColumn(“First name”, varchar, 100); table.AddColumn(“Last name”, varchar, 100); if(table.ColumnExists(“Company”)) table.RemoveColumn(“Company”); The operations I am looking for are adding, editing and deleti...
I have the following Transact-Sql that I am trying to convert to LINQ ... and struggling. SELECT * FROM Project WHERE Project.ProjectId IN (SELECT ProjectId FROM ProjectMember Where MemberId = 'a45bd16d-9be0-421b-b5bf-143d334c8155') Any help would be greatly appreciated ... I would like to do it with Lambda expressions, if possible....
The following SQL separates tables according to their relationship. The problem is with the tables that sort under the 3000 series. Tables that are part of foreign keys and that use foreign keys. Anyone got some clever recursive CTE preferably or a stored procedure to do the necessary sorting?? Programs connectiong to the database are no...
I have been researching audit triggers for some of my sql tables. I came across this article on simple-talk. Now I know that the COLUMNS_UPDATED() function used here is only supposed to be available on SQL Server 2005 and later but it appears to work under 2000 as well. Is this perhaps a classic Microsoft undocumented feature in 2000? O...
We have this set of data that we need to get the average of a column. a select avg(x) from y does the trick. However we need a more accurate figure. I figured that there must be a way of filtering records that has either too high or too low values(spikes) so that we can exclude them in calculating the average. ...
Hello, I have this code http://www.nomorepasting.com/getpaste.php?pasteid=22580 which is part of a small ajax application. I would like to know a better, more efficient way to assign $query, instead of copying the sql each time with a different query or a bunch of if clauses. Basically the query will be dependant on the link clicked...
Master table contains ID and PersonName. Course table contains ID, CourseName. Detail table contains ID, MasterID, CourseID, StartDate,EndDate I want to create report that shows list of persons (PersonName) and the only last course they took (so every person is listed only once): PersonName - CourseName - StartDate - EndDate ...
What are your set of best practices when working with PostgreSQL? I'm interested in every aspect of postgres-management/development. Configurations, rules, functions, users, ... There are a lot of documentation about PostgreSQL, but I haven't found any clear best-practice/cookbook source yet. Just to clarify: I know about views, transa...
My table has a large number of columns. I have a command to copy some data - think of it as cloning a product - but as the columns may change in the future, I would like to only select everything from the table and only change the value of one column without having to refer to the rest. Eg instead of: INSERT INTO MYTABLE ( SELECT NEW_I...
I have four tables containing exactly the same columns, and want to create a view over all four so I can query them together. Is this possible? (for tedious reasons I cannot/am not permitted to combine them, which would make this irrelevant!) ...
Using JDBC (with jt400 driver / connection, naming=system) I'm running these SQL statements: "CREATE ALIAS QTEMP/SOURCETEMP FOR " + library + "/" + file + " (" + member + ")" "SELECT SRCDTA FROM QTEMP/SOURCETEMP" "DROP ALIAS QTEMP/SOURCETEMP" This works. However, when the member String has a . in it this confuses everthing. Is there...
I would need to create a temp table for paging purposes. I would be selecting all records into a temp table and then do further processing with it. I am wondering which of the following is a better approach: 1) Select all the columns of my Primary Table into the Temp Table and then being able to select the rows I would need OR 2) Sel...
I want to list (a sorted list) all my entries from an attribute called streetNames in my table/relation Customers. eg. I want to achieve the following order: Street_1A Street_1B Street_2A Street_2B Street_12A Street_12B A simple order by streetNames will do a lexical comparision and then Street_12A and B will come before Street_2A/B...
Is it possible to generate a list of all source members within an iSeries source file using SQL? Might be similar to getting table definitions from SYSTABLES and SYSCOLUMNS, but I'm unable to find anything so far. ...
Hi there, I have a Stored Procedure called spGetOrders which accepts a few parameters: @startdate and @enddate. This queries an "Orders" table. One of the columns in the table is called "ClosedDate". This column will hold NULL if an order hasn't been closed or a date value if it has. I'd like to add a @Closed parameter which will ta...
I'm working on a database that tracks files and dependencies in projects. Briefly, I have two main tables; the PROJECTS table lists project names and other properties, the FILES table lists files. Every file entry points to a project as a foreign key set to CASCADE, so if I delete a project record from the database, all the file records ...
Data table structure is: id1,id2,id3,id4,... (some other fields). I want to create summary query to find out how many times some ID value is used in every column. Data 1,2,3,4,2008 2,3,5,1,2008 1,3,2,5,2007 1,2,3,6,2007 3,1,2,5,2007 For value 1, the result should be 1,0,0,1,2008 2,1,0,0,2007 How to accomplish this with one query (in M...
When I do this manually public class AdventureWorks : DataContext { public AdventureWorks(string connection) : base(connection) { } public Table<Contact> Contacts; } [Table(Name = "Person.Contact")] public class Contact { [Column(DbType = "int not null", IsPrimaryKey = true, IsDbGenerated = true)] public int ContactID;...
Hey.. I'm trying to concatenate 3 [char(32)] fields:title1title2title3 into one field, but the catch is that I'm using an older version of SQL and it DOES NOT support the CONCAT() subroutine or the + operatorfor example:CONCAT(title1, title2, title3)(title1 + title2 + title3) DON'T WORK!!!!Is there another way? ...