sql

ActiveRecord::Relation join, how to add a column of a join table to the query result with a new name?

To set the stage, I'm using rails 3 and I have these tables and relationships: user has_many lists list has_many tasks task has_many stints I would like to build a query that allows me to select all of the current users stints, and to have the list.id available as an attribute on each stint in the result. I would need to rename list.i...

Best practice? open and close multi connections, or one large open connection for ado.net

I am using ADO.Net for some database connectivity and I was just hoping I was doing it the right way. I am opening and closing multiple connections for each stored procedure. Or should I be wrapping this up in just one open connection (less resource on the database maybe?) thanks, and if there is anything odd or something I could be do...

sqlDecimal to decimal clr stored procedure Unable to cast object of type 'System.Data.SqlTypes.SqlDecimal' to type 'System.IConvertible'.

Hi, I'm new to this. I'm writing a clr stored procedure that calls another stored procedure to get a value that's used in a calculation. A stored procedure returns int (I guess SqlInt32? the type of the value in the table is int) that is then needed to be converted to decimal. Here's the code I have: public static int CalculateMyV...

MySQL Combine multiple rows

I have a table similar to the following (of course with more rows and fields): category_id | client_id | date | step 1 1 2009-12-15 first_step 1 1 2010-02-03 last_step 1 2 2009-04-05 first_step 1 2 2009-08-07 last_step 2 ...

How to delete completely duplicate rows

Hello, Say i have duplicate rows in my table and well my database design is of 3rd class :- Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap'); Insert Into tblProduct (ProductId,ProductName,Description,Category) Values (1,'Cinthol','cosmetic soap','soap'); Insert Into tblProd...

Setting a password for a DB in SQL 2005

I am writing a program in VS 2005 and I wanna control the access to one of the DBs in SQL Server 2005 (Express) I mean, I do not like let other applications to connect to this server an access the data in this DB. What can I do? ...

What obstacles have you encountered when developing apps with your development tool?

I'm debating whether to re-write my INFORMIX-SQL app with I4GL (character-based) or another Windows/GUI-based tool. I want to know specific instances of limitations, bugs or drawbacks of using another development tool before I invest considerable amount of time evaling another product. Answers to this question could save me a lot of tim...

SQL Date Compare by only using Date not Time

This was originally going to be a question about how to implement this because I was stuck at a certain part but I am now curious as to why this was happening in the first place. I needed to compare only the dates not the time which wouldn't have been a problem if the times didn't differ. The code below shows the query I was originally t...

UUID returning as bytes_le from MS SQL

I'm querying a table from my Django app, but whenever I do a query on one specific table, I am getting the UUID column returned as a bytes_le style UUID instead of a workable string representation. Now, I know I convert it using uuid.UUID(bytes_le=value), but I'm using this query to populate a WTForms QuerySelectField, so I don't really...

Select Timeout With 2 Parameters

I've got the following View, called ViewGoods: SELECT G.Gid, SI.[$Id] AS FirstSiteInId, SI.Date AS FirstSiteInDate, SI.Comments AS FirstSiteInComments, S.[$Id] AS FirstSiteId, S.[$Refex] AS FirstSiteRefex, SI.Client AS ClientId, C.[$Refex] AS ClientRefex, CASE WHEN SI.Contract IS NULL THEN (SELECT Contract.[$Id] ...

MySQL: How do I SUM non duplicates values when doing multiples JOINS

I try to SUM values from columns, from a query which contains some JOINS. Example: SELECT p.id AS product_id, SUM(out_details.out_details_quantity) AS stock_bought_last_month, SUM(order_details.order_quantity) AS stock_already_commanded FROM product AS p INNER JOIN out_details ON out_details.product_id=p.id INNER JOIN or...

Using a TableName instead of Named Range in Excel datasource SQL Query

In Excel VBA, I am using Excel as datasource for some queries. I am using the query as SQL = "Select * from [NameRange1]" - This works But, I want to use a table name instead of Namerange(Excel 2007 Tables). How do I use that? I tried "select * from [Table1]" and I also tried creating a Namerange for this table1 and it didn't wor...

How to fetch entries starting with the given string from a SQL Server database?

Hi I have a database with a lot of words to be used in a tag system. I have created the necessary code for an autocomplete box, but I am not sure of how to fetch the matching entries from the database in the most efficient way. I know of the LIKE command, but it seems to me that it is more of an EQUAL command. I get only the words that...

Access 2007 to Oracle 10g linked table -- query with flawed results, but no errors thrown

Access 2007 databases querying linked oracle 10g tables are returning flawed result sets when using the WHERE clause to filter-out unwanted records. Oddly, some filtering is happening, but not reliably. I can reliably demonstrate/produce the problem like this: Create a *new* database with Access 2007. Create a second *new* database w...

Has anyone eval'd or dev'd database apps with RadVolution Designer?

I've been looking at this database app development tool which is integrated with Visual Studio and would like to hear from you if you have any comments about it, evaluated or developed database apps with this tool. The tools website is: www.developguidance.com ...

insert record if not exists in sql, duplicate column name

i wanted a solution to insert a record if it isn't there so i searched here and found a solution but i have another problem INSERT INTO closed_answers (question_id, subject_id) SELECT * FROM (SELECT 2, 2) AS tmp WHERE NOT EXISTS ( SELECT question_id FROM closed_answers WHERE question_id = 2 AND subject_id = 2 ) LIMIT 1 the output...

Datetime pattern for yyyy.mm.dd.hh.mm.ss pattern code?

What is the DATE FORMAT CODE for "yyyy.mm.dd.hh.mm.ss"? I know that 34 (date format code) is "yyyymmddhhmmss", but what about the code for "yyyy.mm.dd.hh.mm.ss"? This is on SQL 2005. ...

Excel pivot table Storing in SQL 2008 / Cross tabbed tables

Let's say I have a table with X and Y values like: x-1 x-2 x-3 y-1 V=1 v=4 v=6 y-2 V=1 v=4 v=67 y-3 V=2 v=0 v=9 y-4 V=4 v=5 v=62 where the value for x-1, y-1 is 1 and so on. I need to store all the x value field names, all the y value field names, and all values. ...

Select a nullable bit with a default value

I need to select a nullable bit column in a view, but use a default value of FALSE whenever the value is NULL. (For other reasons, I can't add the default value on the source table itself.) Here is what I am doing. CAST ( CASE WHEN bit_column IS NULL THEN 0 ELSE bit_column END AS BIT ) AS bit_column, ... I ha...

Stored procedure, pass table name as a parameter

I have about half a dozen generic, but fairly complex stored procedures and functions that I would like to use in a more generic fashion. Ideally I'd like to be able to pass the table name as a parameter to the procedure, as currently it is hard coded. The research I have done suggests I need to convert all existing SQL within my proce...