sql

Is there a way to get the sql created by a linq querty?

I've been developing a ASP.NET page and have been using LINQ to handle talking to MS SQL server. I'm ok at basic SQL, however I'm a lot better with designing queries using LINQ. I know they are similar but I find it easer to design complex queries in LINQ. My question is this: Is there a way to design a query in LINQ and then get the SQL...

Oracle Syntax to Postgres Help

I am trying to convert this code from Oracle: CREATE VIEW PLANNED AS SELECT ASP.ASP_SPACE_NM, SUM(MO.MO_TKR_TOTAL_OFF_SCHEDULED) "TOTAL_PLANNED" FROM OBJECTIVE MO, SPACE ASP WHERE ASP.ASP_SPACE_NM = MO.ASP_SPACE_NM (+) AND MO.MO_MSN_CLASS_NM = 'EPIC' GROUP BY ASP.ASP_SPACE_NM To Postgres. Two lines in this are confusing...

Conditional operator in Transact-sql

Is there a way to do this shorter, for instance using some sort of conditional operator in Transact-sql? IF @ParentBinaryAssetStructureId = -1 BEGIN SET @ParentBinaryAssetStructureId = NULL END UPDATE BinaryAssets.BinaryAssetStructures SET ParentBinaryAssetStructureId = @ParentBinaryAssetStructureId WHERE BinaryAssetStructu...

I need to generate a file from binary information in sql........

I am writing a windows desktop application in visual basic and I need it to connect to a SQL database, take a rows unique id, and using that number generate a file from the binary data that is stored in the SQL database and save it to a file directory. The part that is hanging me up is the creating the file from the binary SQL data. ...

SQL Server 2008: Count Number of Keys In Multiple Date Ranges Across Multiple Tables

Thanks to the people who answered my question this morning: http://stackoverflow.com/questions/2087664/sql-server-2008-count-number-of-keys-in-multiple-date-ranges This is an extension of it and I'm not clear how to generalize the previous solutions to this case. The database I'm working with has medications, lab values, and diagnoses...

Parsing SQL file in Objective-C

Does anyone know of an Objective-C library that will easily allow developers to parse a SQL file? I am building an iPhone application that contains a SQLite database and I am attempting to come with a system to upgrade a user's writable database. After reading a lot of the questions and answers on this site it appears that the best way t...

selecting COUNT(*) sometimes returns 0, and sometimes returns nothing

I am trying to find the number of different things in my database, using SELECT COUNT(*). The problem is when there are zero -- For example, SELECT COUNT(*) FROM `images` WHERE `approved` = '1' If there are no results, I will still get a 0 back and can check $result['COUNT(*)']. But with this query, SELECT COUNT(*) , `first_name` ,...

Oracle Select Syntax into Postgres

I am converting Oracle Syntax into Postgres SELECT MSN_INT_ID, MO_INT_ID, 'Y' "AIRMOVE" FROM MISSION_OBJECTIVE WHERE MO_MSN_CLASS_CD = 'AMV' GROUP BY MSN_INT_ID, MO_INT_ID This part is confusing me: SELECT MSN_INT_ID, MO_INT_ID, 'Y' "AIRMOVE" What is the 'Y' "AIRMOVE" doing? ...

How to do a full outer join without having full outer join available.

Last week I was surprised to find out that sybase 12 doesn't support full outer joins. But it occurred to me that a full outer join should be the same as a left outer join unioned with a right outer join of the same sql. Can anybody think of a reason this would not hold true? ...

having limit in SQL queries on primary keys

Hello, I have table like; CREATE TABLE IF NOT EXISTS `user_t` ( `user_id` int(11) NOT NULL auto_increment, `name` varchar(20) NOT NULL, PRIMARY KEY (`user_id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ; And lets say there is 1000 records in it. Now I want to make a query where the user_id = 500. Is it a good p...

Why this code fails in PostgreSQL and how to fix it (work-around)? Is it Postgres SQL engine flaw?

I've been working on text parsing task when I found strange Postgres behavior. My original code exposing strange error was written in Java with JDBC connectivity for PostgreSQL (v8.3.3 and v8.4.2 tested), here is my original post: Is it an error of PostgreSQL SQL engine and how to avoid (workaround) it?. I've just ported my Java code giv...

What is the preferred way to store custom fields in a SQL database?

My friend is building a product to be used by different independent medical units. The database stores a vast collection of measurements taken at different times, like the temperature, blood pressure, etc... Let us assume these are held in a table called exams with columns temperature, pressure, etc... (as well as id, patient_id and ti...

MySQL incorrect key file for tmp table when making multiple joins

I don't come here for help often but I am pretty frustrated by this and I am hoping someone has encountered it before. Whenever I try to fetch records from a table using more than one join I get this error: #126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it So this query will produce the error: SELECT * FROM...

Is it possible to auto-indent a query in Sql Server 2008 Management Studio Express?

I mean, something equivalent to Ctrl + K + D from Visual Studio that automatically indents code. Is there something in this sense? ...

What's the best way to update n items in relation to one item?

I apologize that the title may not make sense, so let me describe my problem. I have a table for Questions, and a table for Answers. One Question has many Answers. When I create a Question, I simply INSERT as many Answers as were provided. The problem comes into play when I have to update the Answers (either adding new ones, editing exis...

MySQL @variable

I'm trying to set up a MySQL trigger, but I can't figure out how exactly to get what I want done. I think I need to set up a MySQL @variable but what I've tried hasn't worked and I have not been able to find a good resource figure it out. I change the delimiter in phpMyAdmin, and the comment in the below is not used in the actual query. ...

Top Values in a Query by Group

I know how to get the top values but am having trouble with something very simple. I have a student table. It has: name numberoflaps grade I want the get a query or report that shows the top two kids with the most laps per grade. ...

MySQL: Replace substring if string ends in jpg, gif or png

Hi gang, I'm doing a favor for a friend, getting him off of Blogger and onto a hosted WordPress blog. The big problem is, with over 1,800 posts, there are a lot of image links to deal with. WordPress has no mechanism to import these automatically, so I'm doing it manually. I've used wget to download every single image that has ever be...

SQL Server query : SELECT 1 WHERE EXISTS versus SELECT TOP 1 1

I need to present a flag - 0 if a condition is not meet, 1 if it is - and I can do it in two different ways : Get Employee ID, name, 1 if has others in suborder - 0 if not : SELECT e.ID , e.Name , ISNULL ( ( SELECT TOP 1 1 FROM Employee se WHERE se.ManagerID = e.ID ) , 0 ) AS HasSubordinates FROM Employee e or SELECT ...

In Access97 SQL how do I return a range of rows?

Hi, I have an Access97 database (unfortunately) and I am querying it over ODBC. I want to construct a query that will return me J ordered rows starting at offset K. Right now for a related operation I am using a sub-query and the TOP keyword, this works fine but I don't think I can use it for this new operation I am trying to perform. ...