sql

So close to getting a big stored proc to work, just this last problem with CROSS APPLY. Can you help?

The below cross apply sql statement is the only part of a stored proc i cannot get to work. The error says that there is incorrect syntax near the keyword 'ON' Can anyone see the problem? The select statement works fine if that helps at all. CROSS APPLY ( SELECT tbrm_Votes.ArticleID, AVG(tbrm_Votes.Truth), AVG(tbrm_Votes.Relevanc...

SQL JOIN and WHERE statement

I have a problem getting this sql statemen to return what I want: I want it to return a list of properties both the employee or Job_Profile. If one of them do not have the property it should return NULL in that row/column Now the sql looks like: SELECT Parameter.Patameter_Description ParamName, Job_Profile.Title, Job_Property.Ma...

sql query distinct on multiple columns

i have this data and i am trying to find cases where there are different ids but duplicate data in Field 1,2,3,4 id field1 field2 field3 field4 ==== ====== ====== ===== ======= 1 A B C D 2 A B C D 3 A A C B 4 A A C B so, in whatever ...

Oracle JDBC select with WHERE return 0

Hi, Similar question to: http://stackoverflow.com/questions/903488/strange-problem-with-jdbc-select-returns-null but people didn't ask for this. My code: public int myMethod(String day) throws SQLException{ String sql = "Select count(*) from MyTable WHERE someColumn = " + day; Connection connection = ConnFactory.get(); PreparedS...

Hierarchical query in SQL Server 2005

I have a data structure inside a table in SQL Server 2005 representing a chain of related objects. Each object can have replacements in many steps. I want to perform a query that returns all objects and each object's leaf in the replacement chain. The data: id replacement 1 null 2 3 3 null 4 5 5 6 6 null The result shoul...

T-SQL - Aliasing using "=" versus "as"

Is there any particular reason (performance or otherwise) to use AS ahead of = when aliasing a column? My personal preference (for readability) is to use this: select alias1 = somecolumn alias2 = anothercolumn from tables etc... instead of this: select somecolumn as alias1 anothercolumn as alias2 from tables etc... Am I mi...

SQL statement to select all rows from previous day

I am looking for a good SQL Statement to select all rows from the previous day from one table. The table holds one datetime column. I am using SQL Server 2005. ...

dynamic column formatting in sql - and a backend to store the formatting

Hi all, I'm trying to create a system in Python in which one can select a number of rows from a set of tables, which are to be formatted in a user-defined way. Let's say the table a has a set of columns, some of which include a date or timestamp value. The user-defined format for each column should be stored in another table, and querie...

Multiple queries using one SqlCommand possible?

Is it possible to add multiple queries to the SqlCommand object in .net? And if one of the queries fails and the rest succeeds, does the command report this as a failure or succes? last question: can the queries share the same parameters? So, is this possible: UPDATE mytable SET column1 = @param1 WHERE recid = @recid; UPDA...

mssql_fetch_object text character limit?

Hi there, I'm fetching some data from an MSSQL table using the mssql_fetch_object, but the text appears to cut off on the page. The data is all there in the table, but it seems to cut off in the view page. Has anyone else encountered this problem and perhaps knows of a workaround? Here is my code; <?php include('includes/session.php'...

How to count occurrences of a column value efficiently in SQL?

I have a table of students: id | age -------- 0 | 25 1 | 25 2 | 23 I want to query for all students, and an additional column that counts how many students are of the same age: id | age | count ---------------- 0 | 25 | 2 1 | 25 | 2 2 | 23 | 1 What's the most efficient way of doing this? I fear that a sub-query will be slo...

Consolidating Queries

I'm trying to understand how to combine queries when one of them returns more than one record. This is an invoicing report where I want to pull in the Serial Numbers of products invoiced. I'll abbreviate the script as much as possible to clarify. Here is my script before adding the serials: SELECT ARM.fcustno AS [Cust No] , ...

Slow Postgres Query

I'm new to Postgres and SQL. I created the following script that draws a line from a point to a projected point on the nearest line. It works fine on a small data set 5 to 10 points with the same number of lines; however, doing it on 60 points with 2,000 lines, the query takes about 12 hours. it is based on a nearest neighbour function p...

Output SQL messages to a Log File

Hi, I have an SQL script than runs a couple of DBBC commands. Is there a way to send the messages generated from executing these commands to a simple log file e.g "C:\log.txt"? The messages I mean are the ones that appear in the messages box at the bottom of SQL Server 2005 Management Studio after a query has executed. The reason for ...

Help with Query design in MS-Access

CredTypeID is a number the CredType is the type of Credential I need the query to display the Credential in a drop down list so I can change the credential by selecting a new one. Currently I have to know the CredTypeID number to change the Credential. I just want to select it from a drop down list. Currently to change Betty Smith...

T-SQL code to get a DateTime contain only Month and Year from any DateTime

Given a table Event containing a field called EventTime of type DateTime and that the value will contain both date and time elements, I need create a summary query which counts the number of events in each month. The resulting type of the Group By field must also be Date Time with a 0 time element and set to 1st day of the month. This ...

MySQL: How to store/retrieve artist information?

It's very confusing; it seems like I'll need to have at least one many-to-many relationship. A track might be sung by 2+ artist - how can I store that in the database? While showing that track, I want link to each artist so that when users click the track it opens that artist's profile page. Can anyone provide me with some assistanc...

sql turn dates of adding and removing into date ranges

I am using a timetabling application called CELCAT and trying to pull out some data about when students should have been marked for reporting... This seems to be extremely difficult because of the way the adding and removing of students on registers is structured see below: studentid  eventid     fromdatetime                 additio...

Combining multiple sources of data into a unified table

My company is working with 3 partners and each partner can have multiple brands. Each week, I get a dump of each brand's user list which I store in a MySQL database with a table for each brand. Each brand contains a list of users and some basic information (birthyear, zip code, gender). Some users can be signed up with different brands...

SQL design approach for searching a table with an unlimited number of bit fields

Consider searching a table that contains Apartment Rental Information: A client using the interface selects a number of criteria that are represented as bit fields in the DB, for instance: AllowsPets HasParking HasDeck ModernKitchen etc.. We are facing a situation where each new client of our software has additional fields they w...