sql

SQL error: Invalid identifier

I am an SQL rookie and I would very much appreciate some assistance on this rather basic issue. select comp_table.* from (select column_1,avg(column_2) as "avg" from table_1, group by column_1) comp_table returns correct records with 2 columns named column_1 and avg But if I change to: select comp_table.avg from (select colum...

MySQL returning value from a SELECT statement

I keep getting this error: Result consisted of more than one row I have this function: DROP FUNCTION IF EXISTS db.GetUserIDByCourseID; CREATE FUNCTION db.`GetUserIDByCourseID`(CourseID int) RETURNS int(11) BEGIN SELECT (c.user_id + COALESCE(pi.user_id, 0) + COALESCE(p.user_id, 0)) INTO @user_id FROM courses c ...

strip 0 from all rows in single column MySQL

I want to TRIM(LEADING 0 FROM month) FROM table1 where table1 has column month with data formatted like so: 01, 02, 03 I would like to update the data so it is formatted as: 1, 2, 3, ... Thanks! ...

Problem with MySQL query

Hi, i have a table that am trying to filter a particular ID (rid) that falls between particular dates....but! what am getting is a query that doesn't consider the ID (rid) as condition. This is the query: SELECT * FROM booking WHERE '2010-10-01' BETWEEN datefrom AND dateto - INTERVAL 1 DAY OR '2010-10-09' BETWEEN datefrom + I...

How to create a schema-bound function that counts nodes in an xml and then be able to persist this result in a column

I have an xml column and I want to persist a node count in it in an adjacent column. For example, in an xml given below, I have 3 nodes a and so I want to output it. The problem that I have, however, is that I can't manage to create a generic schema-bound function that would take @xml and @nodeName so as to return the count of a speci...

SQL fundamental question '!=' vs '<>' vs 'Not'

Possible Duplicate: Testing for inequality in T-SQL Hi, does there any different to use the '!=' vs '<>' vs 'Not'? which one will have the worst performance or it exist just because of the backward compatibility syntax? ...

Best Practice: One Stored Proc that always returns all fields or different stored procedure for each field set needed?

If I have a table with Field 1, Field 2, Field 3, Field 4 and for one instance need just Field 1 and Field 2, but another I need Field 3 and Field 4 and yet another need all of them... Is it better to have a SP for each combination I need or one SP that always returns them all? ...

Facing error of "The default schema does not exist." when executing runtime query inside sp using exec()

Hi, i have made a runtime query inside a sp and am exceuting the query within the sp using exec(), but when creating the sp i am getting the error The default schema does not exist. The SP is: CREATE PROCEDURE MySP @tableName varchar(100) AS BEGIN SET NOCOUNT ON; declare @selectQuery varchar(MAX) set @selectQuery = 'se...

How to get COUNT from stored procedure return?

I have a stored procedure in SQL which I can't change. It needs few input parameters and returns a table with 100+ rows and several columns. exec dbo.Select_Data 0, 0, 18, 50 I need something to get count of returned rows: select count(*) from (exec dbo.Select_Data 0, 0, 18, 50) and a way to get values from e.g. Name column:...

Calculate Age of Debt in SQL Server

I have three tables: Charges Payments Adjustments Each has a value, called Amount. There are no allocations done on this data, we are assuming the oldest Payments are paying the oldest Charges or Adjustments. Each Amount could be +ve or -ve. I need to produce a report which shows the age of the debt, based on the current balance bein...

Select elements of child tables with a given property

I am really not good at sql, The problem is the following: I have a metadata table like this META: id(pk) prop1 prop2 1 'pr1' 'pr2' 2 'pr1' 'pr2' At the same time, I have many child tables which look something like this: TABLE1: metaID(fk-pk) indx(pk) active ... 1 1 1 ... ...

Copy data from table of one db into table of another db using query (both tables have same structure)

I have two database in the same schema. My db is in Postgres. I want to copy data of any table (i.e product) of my 1st db into the same table of the 2nd db. Is it possible to do so using query? ...

sql IN operator

Hi. I have the sql below but can not get it to work select product_category, ( select top 1 sub_category from sub_categories where product_category IN (keywords) ) as s from products; product category is Baby Gift Baskets and the keywords field is Baby Gift Baskets,Baby Gifts basically ...

Installing SQL Server 2005 Reporting Services after SQL Server 2005 is installed.

I need to install SSRS. Have alreay installed SQL Server. Dosn't seem possible in Add/Remove programs and when I run the installation wizard again it seems to think its a fresh install. Whats the best appraoch for installation of SSRS after you have SQL Server installed ...

is it possible to explicity insert values in timestamp column of DB2

Hi, is it possible to explicity insert values in timestamp column of DB2? For example i have a date time value '2\11\2005 4:59:36 PM'. How to convert it to timestamp value in DB2? Thanks in advance ...

Adding javascript loops into SQL database

What i am trying to achieve is adding the javascript loops and the named variables into an sql database, some of them are already added to an external script so work fine however some which i have named in the SQL script at the bottom still need adding, however as the database won't accept a colon ":" they won't enter it and is returning...

How to use ALTER TABLE to add a new column and make it unique?

How do I use ALTER TABLE to add a new column and make it unique? ...

Why cant I execute this alter procedure firebird query?

the query is: SET TERM ^ ; ALTER PROCEDURE SALVARROTA ( datahora timestamp, distancia double precision, custo double precision, capacidadelivre double precision, capacidadetotal double precision, nome varchar(50), depositox double precision, depositoy double precision, chegadax double precision, ...

What's the difference: Windows Authentication, Passport Authentication and Form Authentication?

Just going to start making a web application and was wondering which was better, or at least what are the main differences between them (as it probably matters what I am using them for)? Windows Authentication Passport Authentication Form Authentication ...

Is there a better way to calculate the median (not average)

Suppose I have the following table definition : CREATE TABLE x (i serial primary key, value integer not null); I want to calculate the MEDIAN of "value" (not the AVG). The median is a value that divides the set in two subsets containing the same number of elements. If the number of elements is even, the median is the average of the b...