sql

How do you select a stored procedure's return value?

It seems like this would would be trivial, but my SQL is not up to par and I can't figure it out. I just want to select a returned scalar from a stored procedure. Basically like this: select dbo.sProcedure @param But that throws an error. It's not really an option to return a result set from within the procedure. Is it possible to do ...

Library to parse SQL statements.

I would like to be able to parse an arbitrary SQL SELECT statement and retrieve the various component parts (columns, relations, JOIN conditions, WHERE conditions, ORDER BY columns), ideally using Delphi. A quick google search shows several different freeware products, but it's not clear if they are complete and/or under active developm...

SQL Latest date

I thought I had it but it seemed I was wrong. I have a query select PrestatieCode, Uitvoerdatum, Identificatie, Foutmelding from bam_Zending_AllInstances as zAll left outer join bam_Zending_CompletedRelationships as zRel on zAll.ActivityID = zRel.ActivityID left outer join bam_Prestatie_AllInstances as pAll on zRel.ReferenceData = p...

Efficient implementation of faceted search in relational databases

I am trying to implement a Faceted search or tagging with multiple-tag filtering. In the faceted navigation, only not-empty categories are displayed and the number of items in the category that are also matching already applied criteria is presented in parenthesis. I can get all items having assigned categories using INNER JOINs and ge...

Are there any built in SQL Server 2005 functions to serialize / deserialize string parameters to a table?

The big question is are there any "built in" SQL Server 2005 functions to serialize / deserialize string parameters to a table variables? The rest explains my need further, but may not be necessary. I have a SQL SP I would like to refactor. It currently accepts five customers ids and five sets of order ids relevant to those customers....

How to create calculated field in Access Database using SQL during runtime in Delphi

I am creating a table during runtime. This is how I do it: AdoCommand1.Connection:=AdoConnection1; cs:='CREATE TABLE '+edname.text+' (' + 'ID Integer IDENTITY(1,1) NOT NULL UNIQUE PRIMARY KEY,' + '[Date Added] DATETIME,'+ '[Name] TEXT(255))'; ADOCommand1.CommandText:=cs; ADOCommand1.Execute; I need to add a field "age" which should be...

Invalid Number Error! Can't seem to get around it.

Oracle 10g DB. I have a table called s_contact. This table has a field called person_uid. This person_uid field is a varchar2 but contains valid numbers for some rows and in-valid numbers for other rows. For instance, one row might have a person_uid of '2-lkjsdf' and another might be 1234567890. I want to return just the rows with valid...

How to bind in PDO a string with %

I have the following query, (which don't work). How do I bid strings inside an existing string with % (I believe the % is not the problem, but really not sure). $sql="SELECT * FROM T WHERE f LIKE '%:bindParamString%'"; ...

SQL - Find missing data / negative search

Hi - following on from my other post... I have a table that uses two fields to make a primary key... TravelEventID RemarkNo Keyword Text 0001 1 TVL LOWCOST BOOKING 0001 2 TVL CREDIT CARD USED 0001 3 PSG COST CENTRE REQUIRED 00...

SQL joins and logic

I am working with a SharePoint web part that I am designing in C#. I need to query a SQL Server 2005 database to return the names of machines that are associated with a particular customer, selected by the user. The problem is that there are different types of machines, and the names themselves are stored in several other tables. Here i...

SQL - Select Fields of the Max ID

The last InvoiceID, and corresponding fields needs to be selected. The entire sql contains several inner joins. SELECT max(InvoiceID), InvoiceEndDate FROM Invoices WHERE TransactionOrderItemID = '000831' Right now, I am getting the InvoiceID, and have to fetch the InvoiceEndDate again. Is there an efficient way of d...

MySQL get rows but prefer one column value over another

A bit of a strange one, I want to write a MySQL query that will get results from a table, but prefer one value of a column over another, ie id name value prioirty 1 name1 value1 NULL 2 name1 value1 1 3 name2 value2 NULL 4 name3 value3 NULL So here name1 has two entries, but one has a prioirty of 1. I wan...

Storing XSLT in SQL Server 2005 with xml type?

I have a lot of XSL files in my ASP.NET web app. A lot. I generate a bunch of AJAX HTML responses using this kind of generic transform method: public void Transform(XmlDocument xml, string xslPath) { ... XslTransform myXslTrans = new XslTransform(); myXslTrans.Load(xslPath); myXslTrans.Transform(xml,null, HttpContext.Cu...

SQL Update from a Select

Hey! I want to update two fields of table (Store) from a select of others tables, but i don´t know how i can do it. The SQL system is in AS/400, so doesn´t have SQL Server or Oracle tricks :( Here is the SELECT, i want the price and amount of an order (article table join for select only existing articles) SELECT OrderDetails.Price, Ord...

TSQL Query for analyzing Text.

I have a table that has ordernumber, cancelled date and reason. Reason field is varchar(255) field and it was written by many different sales rep and really hard to group by the reason category I need to generate a report to categorize cancelation reasons. What is the best way to analyse the reasons with TSQL? Sample of reasons entered ...

Remove dupes from recordset excluding columns from dupe condition.

I'm up against a mssql database, having a SQL query like... SELECT id, type, start, stop, one, two, three, four FROM a UNION ALL SELECT id, type, start, stop, one, two, three, four FROM b UNION ALL SELECT id, type, start, stop, one, two, three, four FROM c ORDER BY type ASC Resulting in... row | id type start stop on...

How do I rename primary key values in Oracle?

Our application uses an Oracle 10g database where several primary keys are exposed to the end user. Productcodes and such. Unfortunately it's to late to do anything with this, as there are tons of reports and custom scripts out there that we do not have control over. We can't redefine the primary keys or mess up the database structure. ...

MySQL Table with TEXT column

Hello. I've been working on a database and I have to deal with a TEXT field. Now, I believe I've seen some place mentioning it would be best to isolate the TEXT column from the rest of the table(putting it in a table of its own). However, now I can't find this reference anywhere and since it was quite a while ago, I'm starting to think...

How would I do this JOIN in Rails?

Here's my SQL statement: SELECT * FROM `message_users` LEFT JOIN `messages` ON message_users.message_id = messages.id WHERE (message_users.user_id = 1 AND message_users.hidden = 0) AND message_users.last_read_at > messages.updated_at ORDER BY messages.updated_at DESC LIMIT 0, 20 How would I pull that off with proper ...

Is it possible to insert data in MySQL from a SQL Server trigger?

A fun question for a Friday afternoon, no? As the question states, is it possible to write a SQL Server trigger that inserts data into a MySQL table? EDIT: Yes, I know this is basically a dumb idea and that the application should be doing it and so on. But this is a legacy Classic ASP application which we're transitioning to MySQL; a...