What SQL would I need to use to list all the stored procedures on an Oracle database? If possible I'd like two queries: list all stored procedures by name list the code of a stored procedure, given a name ...
Hi, I have a report that use a multi-value parameter into a "in" statement in a query. For example (with @areas as the multi-value parameter): select * from regions where areas in (@areas) It works perfectly but now I need to send the same parameter to a function in the SQL Server 2005 database: select name, myFunction(@areas) from r...
I want to group rows with SQL, my result set is following name size date data1 123 12/03/2009 data1 124 15/09/2009 data2 333 02/09/2010 data2 323 02/11/2010 data2 673 02/09/2014 data2 444 05/01/2010 I want to group result set like this one: data1 123 12/03/2009 124 15/09/2009 data2 333 02/09/2010 323 02/11/2...
I can write something like this with LINQ: var selection = from person in personList let initials = person.FirstName[0] + person.LastName[0] select initials; Can I do something similar with SQL, like maybe: SELECT @Initials FROM [Person] SET @Initials = SUBSTRING (Person.FirstName, 1, 1) + SUBSTRING (P...
Is there away to cause a script to prevent running if an if statement is true even with "GO"'s? For example I want to do something similar to the following: insert into table1 (col1, col2) value ('1', '2') GO if exists(select * from table1 where col1 = '1') BEGIN --Cause Script to fail END GO insert into table1 (col1, col2) val...
I have a database table structured like this (irrelevant fields omitted for brevity): rankings ------------------ (PK) indicator_id (PK) alternative_id (PK) analysis_id rank All fields are integers; the first three (labeled "(PK)") are a composite primary key. A given "analysis" has multiple "alternatives", each of which will have a "...
I'm trying to look up two pieces of metadata (volume and issue) on items which may have either a volume, issue, or both. The metadata is stored in a table with item ID, key (metadata field ID) and value. This does work, but it seems overly complex and repetitive: select volume.text_value as volume_value, issue.text_value as issue_valu...
Hey folks, Simple question here, and I know there are lots of kludgy ways to do it :) But I was hoping a SQL server expert had a script to easily script all the data out of a table? Or possibly all the tables in a DB? I'm getting tired of copying and pasting data over RDC! :) ...
Can someone please help me out with a query? In Microsoft SQL Server, I have the following query, which executes fine: SELECT * FROM ControlPoint INNER JOIN Project ON ControlPoint.ProjectID = Project.ProjectID INNER JOIN Site ON Site.SiteID = Project.SiteID WHERE Project.ProjectName LIKE '%Flood%' My problem is, when I try to execute...
Could I run the query: Select id, ( select count(*) from tableA as a where a.value < a.id ) from tableA as a where id < 5 and get the results I wanted. If not is there a way to do the same thing without having to do 2 querys? ...
I am trying to count the number of days of the current week that do not have an event associated with them, but I don't know how to do it. For example, I count the number of events happening this week with this query: SELECT COUNT(e.event_id) FROM cali_events e LEFT JOIN cali_dates d ON e.event_id = d.event_id WHERE YEARWEEK(d.date) = ...
I am a build engineer and responsible for our source control layout. We need to keep a version of all database objects and also group changes together with rollback scripts for pushes to production. We have development, QA, and Production environments and there are different versions of the database objects in each. One area we have t...
I have a query using a FREETEXTTABLE full text search which works perfectly for every column included in the index except for the primary key. The primary keys are in a format like abcdef123456 and when you search for abcdef123456 you get that one record returned. However, if you search for abcd or 12345 you get no results (unless that...
What's the best way implement MS SQL full-text search using all the normal things like wildcards and quotations. For example: If the search term the user inputs is Overdose of "Vitamin C" for child* I would like to treat "Vitamin C" as one phrase and would like to match "child" and "children" The documentation offers so many al...
I have 5 large XML files which I am keen to analyse. All of them are too large to open in a text editor and so I do not know their XML schemas. I have tried to import them into SQL server, however the process has given me an error even though I am pretty sure they are valid, as they were sourced from very reputable programmers. I have ...
I would like to have a column in my table that can store a variable amount of int values. What is the best way to do this? I would like to be able to select based on these ints so a csv list would not work. Basically I have a bunch of rows in my table that can belong to multiple different categories. I would like to store the category i...
Hi all, I am a fairly new MySQL developer and am starting on a project that I could do with a bit of initial advice on... I am creating a database that will primarily be holding a certain number of items (between 1-5k) and around 40 boolean variables associated with each one. Users will then be inputting their choice of these 40 value...
If say I need to run two separate SQL statements against two separate databases. Right now I do (pseudocode): Try{ declare variable connectionA to DatabaseA declare variable connectionB to DatabaseB connectionA.open() connectionB.open() declare variable SQLCmdA with ConnectionA and one SQL statement declare variable SQLCmdB with...
Hi, I've encountered a very, very strange situation in my application. I say strange because the errors are intermitent and I cannot find out why they occur. I've pass a sqlcommand to the DB (Sql 2005) trying to get a bigint (column ID_Facultate) from a single table. The problem is that I've got a string (the name of some lecture) that...
I want to make a small ticket-system for a project server which has several projects. So far the TicketID will be counted globally, i.e. there is a project A and a ticket with TicketID 1 and another project B and the ticket for this project will get TicketID 2 - like this: (TicketID, ProjectID) (1, 1) (2, 1) (3, 1) (4, 2) (5, 2) (6, 3) ...