MS SQL Server :: Subquery With Multiple Rows Inside SELECT

LIKE Using Subquery Returning Multiple Rows

This is probably very elementary to someone more experienced, but I'm having a hard time coming up with a way to do the following:

SELECT * FROM Transactions
WHERE MyField LIKE (SELECT MyValues FROM SearchValues)

But I can't because the subquery will return multiple rows. That's all I'm really trying to do - search all the rows in Transactions.MyField for any of the search values returned from the subquery.

And I can't restructure the SearchValues table to combine them into a single-row comma-delimited field or anything like that.

Any help would be appreciated-

Kenneth

View Replies !

Select Query With Multiple Row Coalesce Subquery
Hey guys, I have a brain buster for you today:

I have a query where I need to select a bunch of rows from one table, hypothetically we'll call them ssn, first name, last name, and I need to select a subquery which coalesces a bunch of rows together (in no case will there be only one row returned from that subquery).

Anyone know how I could go about this? I'll give you an example of what I've tried, but it does not work currently.

delcare @path varchar(255)
select e.ssn,

e.firstname,
e.lastname,
( @path = select coalesce(@path + ', ', '')
from space s1 inner join space s2

on s1.lft BETWEEN s2.lft AND s2.rgt and s1.rgt BETWEEN s2.lft AND s2.rgt
where s1.spaceID = 133225
select @path)
from employees e
where e.id = 5

Using that spaceID is guaranteed to give me four rows, and I need them coalesced together, but I can't just use a function (too slow on the scale it would be used), any thoughts?

View Replies !

Select Data From Multiple Rows To One Row
Hi, I am new to to the usage of sql server.I have data in the below mentioned format in table called stock_transaction.

stocknumber transtype transsubtype balance

s01 in cust 100
s01 out cust 200
s01 in deal 300
s01 out cust 100

s02 in deal 200
s02 out cust 300
s02 in cust 100
s02 out cust 200
s02 in cust 300

I want to generate a report which has the sum of balances of each stock number that belong to a particular trans group like
(in,cust) (out,cust),(in,deal) as below where (in,cust) (out,cust),(in,deal) are temporary aliases only for displaying as shown below

stocknumber in,deal out,cust in,cust
s01 300 300 100
s02 200 500 400

I am using case statements, I am able to retrieve the data when selecting of single trans group like(in,cust) or (out,cust) by giving its condition but was unable to select all the details of a particular stock number as a single record .

View Replies !

Subquery Inside Case?
Hello,

I am trying to do something like this:

select (CASE
WHEN tableA.col = 'a' then 'A'
ELSE (select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id )END ) as TEST
from tableA

But the problem is that the part in bold returns more than one row..
i want it to be select table3.col1
from tableA, table 2, table3
where tableA.id = table2.id
and table2.id = table3.id
for every value of tableA.col.

How do I do this?

View Replies !

Select Distinct Returns Multiple Rows With Same Value
I have a Select Distinct myfield that returns multiple rows with same value for myfield when it should only one. Why is this happening?

View Replies !

How To Select Multiple Rows As Comma Separated List?
Hi,

I have a table of users, a table of categories, and a many-to-many table linking users to categories.

My problem is that I want to select all the users with an extra column containing a comma-separated list of the categories they belong to.

Here is a stripped-down version of the table fields:

tbl_User
UserId, Email

tbl_Category
CatId, CatName

tbl_User_Category
UserId, CatId

I have tried using the coalesce function to build a string, but can only get this to work for one row at a time:

DECLARE @list nvarchar(100)

SELECT @list = COALESCE(@list + ', ', '') + CAST(CatId AS varchar(4))
FROM tbl_User_Category
WHERE UserId = @UserId

SELECT @list as List

Any ideas on how to add to this to get it to do each row in tbl_Page? Or am I attacking this from the wrong angle?????

Any help would be fantastic!

thanks,
Rob

View Replies !

Newbie - How To Update Multiple Rows With Select Statment
Newbie to the SQL Server.

UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)

Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.

Immd help will be greatly appreciated.

Pam

View Replies !

Executing Stored Proc On Multiple Rows From Select
I have a couple of stored procs I'd like to execute sequentially building the parameter list from the result of a SELECT statement.

Each proc takes a large amount of parameters, and each proc takes several actions (a number of INSERTs and a number of UPDATEs)

The procs themselves are pretty bulky and probably somewhat error prone. For that reason, I'd like to avoid modifying them, potentially having to spend alot of time debugging there.

I'd like to basically have it work like this, but this doesn't work out syntactically:

SELECT
exec sp1 f1,f2,f3,f4...
exec sp2 f2,f4,f5,f6...
FROM
SomeTable

I first thought the easiest parallel would be to move the sps to UDFs and use the UDFs, but when I tried this it failed due to my trying to INSERT/UPDATE from a UDF.

I've read several posts like mine, and the suggestions are usually to convert the proc to a UDF (like I tried, but I need INSERT/UPDATE), or to rework the proc involved. Here I need 2 procs to exec on the SELECT data, and it would probably be much more of a hassle to rework these procs than the performance penalty the cursor use will involve.

That said, my thought is to use a cursor. I understand how to use the cursor, and I'm pretty sure this will work just fine. Before implementing that though, I wanted to see if anyone had any alternate suggestions since cursors seem to be the "unnatural" way of doing most things.

View Replies !

Select Multiple Rows Of Data Not From Any Pre-existing Table
I know that this is legal sql: "SELECT 1 AS Blah"
I want to do something like this except for I need to select multiple rows each with a different value for Blah. The query needs to be legal to be passed to the SqlCommand.ExecuteReader Method. Is this possible?

View Replies !

Select Rows Based On Multiple Date Conditions
Hi,
I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today.
I'm not sure how to do this.

SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything.
I like to end up with a column containing the count of rows <= today
and a column with rows where date > today.

Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?

Thanks,
Marc

View Replies !

EXEC Inside CASE Inside SELECT
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.

@val1 and @val2 are passed in

CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)

INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2

SELECT COUNT(*) FROM #TEMP WHERE mybool = 1

If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.

Any suggestions?

View Replies !

Select DISTINCT On Multiple Columns Is Not Returning Distinct Rows?
Hi, I have the following script segment which is failing:

CREATE TABLE #LatLong (Latitude DECIMAL, Longitude DECIMAL, PRIMARY KEY (Latitude, Longitude))

INSERT INTO #LatLong SELECT DISTINCT Latitude, Longitude FROM RGCcache

When I run it I get the following error: "Violation of PRIMARY KEY constraint 'PK__#LatLong__________7CE3D9D4'. Cannot insert duplicate key in object 'dbo.#LatLong'."

Im not sure how this is failing as when I try creating another table with 2 decimal columns and repeated values, select distinct only returns distinct pairs of values.

The failure may be related to the fact that RGCcache has about 10 million rows, but I can't see why.

Any ideas?

View Replies !

Merge Multiple Rows Into A One Or More Rows With Multiple Columns
Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

ID           Pat_ID

1             A

          2             A
          3             A
          4             A
          5             A
          6             B

          7             B
          8             B
          9             C

          10           D

          11           D

I want output for the above as:

Pat_ID         ID1          ID2           ID3
A                 1            2              3
A                 4            5             null
B                 6            7              8
C                 9            null          null
D                 10          11            null

Please help me. Thanks!

View Replies !

Subquery More Than 1 Rows Problem
Hi all,

I've 3 tables COMPANY, COMPANY_SOFTWARES, SOFWARES. COMPANY_SOFTWARES table holds the data of the softwares used in the companies like;

COMPANY TABLE
COMPANY_ID COMPANY_NAME
------------- ----------------
10 My Company

COMPANY SOFTWARES TABLE
COMPANY_ID SOFTWARE_ID
------------- --------------
10 1
10 2
10 3

SOFWARES TABLE
SOFTWARE_ID SOFTWARE_NAME
-------------- -----------------
1 X
2 Y
3 Z

I need a result like;
COMPANY_ID COMAPNY_NAME SOFTWARES
------------- ---------------- ------------
10 My Company X, Y, Z

I've used a query like;

SELECT
COMPANY_ID
,COMPANY_NAME
,(
SELECT
SOFTWARE_NAME
FROM
COMPANY_SOFTWARES COS,
SOFTWARES S
WHERE
S.SOFTWARE_ID=CS.SOFTWARE_ID AND
COS.COMPANY_ID = C.COMPANY_ID
) AS SOFWARE_NAMES
FROM
COMPANY C
WHERE
AND C.COMPANY_ID = 10

But I get an error message;
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I've read that in MySQL there is CONCAT function for the result I want but I couldn't find any solution in MSSQL server, any solutions, suggestions?

Thanks.

View Replies !

SubQuery - Rank Of Rows
Hi all:

In the Sql below, sample from William Pearson, the amount Spend is in descending order and the Rank number is in ascending order. Like this:

Spend Rank
24 1
12 2
10 3
9 4

What I wish to accomplish is:

Spend Rank (descending)
24 4
12 3
10 2
9 1

Please let me know what I need to accomplish it.

Thanks for the help

Victor

SELECT
CompanyName, Spend,
(SELECT COUNT(*)
FROM
ACC0704 AS CoSpendTotal
WHERE
ACC0704.Spend <= CoSpendTotal.Spend)
AS Rank
FROM
ACC0704
ORDER BY
Spend DESC

View Replies !

Top Percent In Subquery Return Various Rows
Hi,

We are running sql server 2005 standard edition. In the query below, it has top percent clause in the subquery and returns various rows, 2, or 3, or 4 rows each times when running manually one after another. Does anyone have an explaination?

SELECT
dbo.WOB_STEP.STEP_UID
FROM
dbo.WOB_STEP
INNER JOIN dbo.CURRENT_DATES CD1 ON (dbo.WOB_STEP.CD_UID=CD1.CD_UID)
WHERE
( dbo.WOB_STEP.STEP_UID IN (SELECT TOP (50) PERCENT WITH TIES STEP_UID FROM dbo.vWOB_STEP_RANDOM WHERE OPERATOR_NAME = 'Jennelyn Llobrera' AND VENDOR_RETURN_FLAG = 1
ORDER BY RandomID) )

Thanks very much in advance !!!

View Replies !

Return Subquery Rows As One Delimited Column
I don't know if this is possible, but I haven't been able to find anyinformation.I have two tables, for example:Table 1 (two columns, id and foo)id foo--- -----1 foo_a2 foo_b3 foo_cTable 2 (two columns, t1_id, and bar)t1_id bar------ ----1 bar_a1 bar_b1 bar_c2 bar_d3 bar_e3 bar_fWhat I'm shooting for is returning the result of a subquery as atext-delimited column. In this example, using a comma as thedelimiter:Recordset Returned:foo bars----- -----foo_a bar_a,bar_b,bar_cfoo_b bar_dfoo_c bar_e,bar_fI know that it's usually pretty trivial within the code that isquerying the database, but I'm wondering if the database itself can dothis.Is this possible, and if so, can someone please point me to how it canbe done?

View Replies !

For Deleting Duplicate Rows Subquery Or Cueser Is Better In Performance????????????
For deleting duplicate rows, i can use cursor and subquery.

cursor code

Declare dup_cursor cursor for
select acctnumber from LinkUrnABSADMBAR
group by acctnumber
having count(*) > 1

Open dup_cursor

Fetch Next from dup_cursor INTO @acctnumber

While (@@Fetch_Status = 0)
Begin

Delete from LinkUrnABSADMBAR
where acctnumber = @acctnumber

Fetch Next from dup_cursor INTO @acctnumber
End

Close dup_cursor
Deallocate dup_cursor

Subquery code

delete from galupload2000..test where id in (select id from galupload2000..test group by id having count(*) >1)

My question is which one is Better in performance????????????

Thanks

Sandipan

View Replies !

Multiple Column SubQuery
I need to query out multuple rows of data from the following table and retrieve it as a single row.   Ideally, I'd like to do some sort of subquery that supports multiple columns, but as far as I know, that only exists on Oracle, not MSSQL.
Here's an example of the data:UserDefinedFieldId     UserDefinedRowID     FieldValue1                              1                              date (stored as text)2                              1                              text3                              1                              text1                              2                              date (stored as text)2                              2                              text3                              2                              text...27 UserDefinedFieldIds for each row in the actual table
I wrote a working query, but I need something more efficient for this particular application. (The query to do this must fit in a 2000char data field--with all 27columns brought in with separate subqueries, I'm pushing 5000.) Already tried shorter table names, which only saved me about a 1000 characters.
SELECT     (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable           WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId          AND SecondaryTable.UserDefinedFieldId = 1) AS Column1,     ,,,     (SELECT SecondaryTable.FieldValue FROM UserDefinedData AS SecondaryTable           WHERE SecondaryTable.UserDefinedRowID = PrimaryTable.UserDefinedRowId          AND SecondaryTable.UserDefinedFieldId = 27) AS Column27,FROM UserDefinedData AS PrimaryTableWHERE PrimaryTable.UserDefinedFieldId = 5 AND     Cast(Cast(PrimaryTable.FieldValue AS varchar(64)) AS datetime) > GetDate() ORDER BY PrimaryTable.UserDefinedRowID, PrimaryTable.UserDefinedFieldId
Any suggestions would be appreciated.
Thanks,
Brad

View Replies !

How Do I Imbed A Select Inside A Select
I need a select that gets a value and than appends another value if the criteria is met otherwise nothing is appended.

The statement has a select with an imbedded select and when I execute it I get the error:
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Thia is a crude sample of the statement

SELECT ID + ( select * from tableB where TableB = 0 ) as result1
FROM TableB

Why am I getting this error and how do I fix the statement?
thanks

View Replies !

Arranging Data On Multiple Rows Into A Sigle Row (converting Rows Into Columns)
Hello,
I have a survey (30 questions) application in a SQL server db. The application uses several relational tables. The results are arranged so that each answer is on a seperate row:
user1 answer1user1 answer2user1 answer3user2 answer1user2 answer2user2 answer3
For statistical analysis I need to transfer the results to an Excel spreadsheet (for later use in SPSS). In the spreadsheet I need the results to appear so that each user will be on a single row with all of that user's answers on that single row (A column for each answer):
user1 answer1 answer2 answer3user2 answer1 answer2 answer3
How can this be done? How can all answers of a user appear on a single row
Thanx,Danny.

View Replies !

Running The Same Subquery Multiple Times
Hi,

I was wondering if this can be done...

I have a complex query which has to do a few calculations. I'm using subqueries to do the calcs, but most of the calcs have to use a value gotten from the first subquery. I don't want to have to type the subquery out each time, so is there a way of assigning it to a variable or putting it in a UDF or SP?

E.g.
I have a table with 2 cols - amount, date.

SELECT total_amount, closing_amount,
FROM table1
GROUP BY month(date)

Total amount is the SUM(amount) for the month.
Closing amount is the Total Amount plus the amounts for the current month with a few extra calcs.

As I have to use SUM(amount) in the second subquery, is there a way I can do it without having to type hte subquery out again?

This is only a basic example, what I'm trying to do will invovle a lot more calcultions.

Hope someone can help,
Thanks,
Stuart

View Replies !

Combine Multiple Results Of Subquery
Table users:
userid, name, added...

Table groups
groupid, groupname...

Table groupadmins:
userid, groupid

The users to groups relationship is many-to-many, which is why I created the intermediate table. I would like to return a recordset like:
userid, name, groupids
12344, 'Bob', '123,234,345'

If I try to just select groupid from groupadmins:
select userid, name, (select groupid from groupadmins where groupadmins.userid = users.userid) as groupids from users

then I'll get an error that a subquery is returning multiple results. Some users are not group admins and those that are may have a single or multiple groups. The only thing I can think of is to select the groupids seperately and use a cursor to loop through the results and build a string with the groupids. Then I would select the string with the rest of the fields that I want for return. Is there a better way to do this?

View Replies !

[RESOLVED] Multiple Records Subquery..
Hi everybody,

   I like to display the records for AccountNo = 221 from table records shown below, how would I do this? I am display this results in a crystal report. What is sql statement to do this? Thanks.

Sql Statement: (this statement is not allowed)
Select AccountNo, RecordID, (Select Description From Table1 Where RecordID In (Select RecordID From Table2 Where Date < '04/05/2006')) As Description, Amount From Table2 Where Date < '04/05/2006'

Desired Result:

AccountNo   RecordID        Description                       Amount
   221                 20          Whew                                       290.00
   221                 21           Hi There Good Morning          728.00



Table 1

RecordID Description
20               Whew
21                Hi
21                There
21                Good Morning

Table 2
Date               AccountNo RecordID Amount
04/02/2006   220              19               80.0
04/03/2006   221              20               290.0
04/04/2006   221              21               728.0
04/06/2006   223              23               200.3
04/07/2006   225              25               2893.20

den2005

View Replies !

Select From Subquery
Hi.I'm new in SqlServer programing.Is it possible to do something like this ( It is common construction in oracle )Select X from(select a+1 as X from tab1)without creating view containig subquery ?thx. MD

View Replies !

Select & Subquery
I am trying to do select from several tables:
select
(case receivedrowId when 0 then '' else
(select distinct documenttype+document from documentdetails dd
where dd.rowId=receivedRowId)end)as Invoice,

(case receivedRowID when 0 then
(case (select count(*) from Customers c where C.[Name]=[Name])
when 0 then ''
when 1 then (select C.Name from Customers c where C.[Name]=[Name] )
else 'Multiple matches' end)
else
(select distinct d.customer from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID)end)as Account#,

(case receivedRowID when 0 then [Name] else
(select distinct d.[Name] from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID)end) as CustomerName,

(case receivedrowid when 0
then (case (select count(*) from Customers c where C.[Name]=[Name])
when 0 then ''
when 1 then (select C.Phone from Customers c where C.[Name]=[Name] )
else 'Multiple matches' end)

else (select distinct d.busphone from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID) end) as Phone,

StoredTires.Tag,
StoredTires.ReceivedRowID,
StoredTires.ReturnedRowID,
StoredTires.Memo,
StoredTires.Location, StoredTires.Type,

StoredTires.TreadRemaining, StoredTires.NewTread, (CASE StoredTires.Wheel
WHEN 1 THEN 'Yes'
ELSE ''
END) as Wheel,

(case receivedrowId when 0 then StoredTires.[Date] else
(select distinct d.InvoiceDate from documents d join documentdetails dd on d.documenttype=dd.documenttype and d.document=dd.document
where dd.rowId=receivedRowID)end)as InvoiceDate

from storedtires

I am saying 'multiple matches' because it seems that I get not only the particular Phone for this record but all other phones for whole table.
I have difficulties retrieving phone & account number because of that.
Can anyone help?
thank you.

View Replies !

Select Without Subquery
Hi,

I have two tables. Customers and Orders.
Customers table contains customer id, and customer name columns.
Orders table contain order id,product id,customer id. So orders table contains products bought for each order by a customer.

I want to write a query to retrieve all order details (products for each order and customer id), where product with id 5 is bought.
Can I write this sql without using a subquery.

Thanks,
Chamal.

View Replies !

Using IF Inside SELECT ?
Is there possibility to use IF conditions inside SELECT statements?For example, can i write something like this:CREATE PROCEDURE [search](@OPTION int,@KEYWORD nvarchar(40))ASBEGINSELECT id FROM projects WHERE title LIKE @KEYWORD IF (@OPTION = 1)THEN (OR description LIKE @KEYWORD)ENDor am i limited to this:....BEGINIF @OPTION = 1SELECT id FROM projects WHERE title LIKE @KEYWORD OR description LIKE@KEYWORDELSESELECT id FROM projects WHERE title LIKE @KEYWORDEND

View Replies !

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.
hello friends.. I am newbie for sql server...I having a problem when executing this procedure .... ALTER PROCEDURE [dbo].[spgetvalues] @Uid intASBEGIN SET NOCOUNT ON; select DATEPART(year, c.fy)as fy, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1% JRF' ) as survivorship, (select contribeamount from wh_contribute where and contribename like 'Gross Earnings' and ) as ytdgross, (select contribeamount from wh_contribute where and contribename like 'Retire-Plan B-1.5% JRP') as totalcontrib, from wh_contribute c where c.uid=@Uid Order by fy Asc .....what is the wrong here?? " Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."please reply asap...

View Replies !

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.
Hi,Urgent Help appreciated....I am getting resultset with first condition and when try to get theresutlset from second condition i am getting the above error inSQL200.I know that i am getting more then one value in the second conditionbut even i am getting more then one value in my first statement........i tried taking out '=' replacing 'in' it is not working pls needuregent help......CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View Replies !

Subquery Returned More Than 1 Value. This Is Not Permitted When The Subquery Follows =, !=, &<, &<= , &>, &>= Or When The Subquery Is Used As An Expression.
I am getting 2 resultsets depending on conditon, In the secondconditon i am getting the above error could anyone help me..........CREATE proc sp_count_AllNewsPapers@CustomerId intasdeclare @NewsId intset @NewsId = (select NewsDelId from NewsDelivery whereCustomerId=@CustomerId )if not exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count( NewsPapersId) from NewsPapersendif exists(select CustomerId from NewsDelivery whereNewsPapersId=@NewsId)beginselect count(NewsDelId) from NewsDelivery whereCustomerid=@CustomeridendGO

View Replies !

SQL Problem With Subquery In Select
1) Getting this error:
Msg 156, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near the keyword 'in'.
Msg 102, Level 15, State 1, Procedure SP_ImportHotels_Step4, Line 10
Incorrect syntax near ','.
Msg 512, Level 16, State 1, Procedure SP_ImportHotels_Step4, Line 7
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
The statement has been terminated.
2) I also coild not use subquery as myname... It did not allow to specify "as" for some reason...
Here is my procedure.

alter procedure SP_ImportHotels_Step4
As
BEGIN
INSERT INTO classifieds_Photos
( AdId, IsMainPreview, DateCreated, createdOn, createdBy, modifiedOn, modifiedBy, URL, ThumbnailURL )
SELECT classifieds_Ads.Id,
[i.ID] in (Select Min(Images.ID) from Images group by HotelID),
GetDate() AS Expr2, GetDate() AS Expr3, 'admin' AS Expr4, GetDate() AS Expr5, GetDate() AS Expr6, i.URL, i.ThumbnailURLFROM (classifieds_Ads
INNER JOIN classifieds_Address ON classifieds_Ads.LocationId = classifieds_Address.addressID)
INNER JOIN Images as i ON classifieds_Address.tempIONHotelID = i.HotelID;
END
go
execute SP_ImportHotels_Step4

View Replies !

Select Subquery Returned More Than 1 Value
select t1.a, (select t2.b from t2 where t1.c = t2.c) b from t1
I need to write that kind of sql to return me single value a and multiple values b on each of lines, like
a b
----------------------------
tom small,big,hugh
But if I execute that sql, I would get error like 'select Subquery returned more than 1 value'. Please help me find a solution, thanks!

View Replies !

How Do I Select Tablename In Subquery
hi all how are you today i am not good because i can't set this variable in this query please read this thanks

create table aaa1 (id_no int, name varchar(20))
go
create table aaa2 (id_no int, name varchar(20))
go

insert into aaa1 values (1,'rahmi')
insert into aaa1 values (2,'atilganer')
insert into aaa1 values (3,'hasan')

insert into aaa2 values (4,'rahmi')
insert into aaa2 values (5,'atilganer')
insert into aaa2 values (6,'hasan')

/* declaring any numeric variable*/
declare @id_no_var int
/* and set variable to max table name's (aaa2 table in this example)
id_ no column

note :insqlhelp and insqlhelp2 is an alias for tablename query (recommended from sql help
*/

set @id_no_var =
(select max(id_no) from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2 )

this query return:

Server: Msg 207, Level 16, State 3, Line 3
Invalid column name 'id_no'.

this error returned because max(id_no) column is absent

if you are run
select * from
(select max(name) insqlhelp from
sysobjects where name like 'aa%') insqlhelp2

this query return that

insqlhelp ---------------
aaa2

(1 row(s) affected)

this mean that my table name query returned table name but i cant use this name in any other query (i think because of daclaring alias "insqlhelp, insqlhelp2")

other way is

set to any other text variable to table name,
and concetanate to query,
and execute with exec

but in this way you cant set to my int variable

please help me thanks for all

hra

View Replies !

SELECT DISTINCT Subquery
I the following table:

table1
member_name legacy_id team_name
-----------------------------------------
Bill 1234 nationals
Bill 1234 nationals
Tom 3456 nationals
Tom 3456 orioles

I wish I could restructure the data or normalize it but this is unfortunately what I have to deal with.

I need a query that returns the team name and the number of times it appears in the table excluding duplicates for each person. I have duplicates all over the place in this tables. Bill could have nationals listed a couple hundred times.

My query should return

team_name count
-----------------
nationals 2 - because it occurs for bill, and tom
orioles 1 - because it occurs for tom

If I do something like:

select
distinct(team_name),
count(team_name)
from table1
group by team_name

I get back:

team_name count
-------------------
nationals 3 - because it occurs for bill twice, and tom once
orioles 1 - because it occurs for tom once

I've tried something like:

select
team_name,
count(team_name)
from
table1
where legacy_id in (
select distinct legacy_id from table1
)

I get a syntax error. Regardless, I'm not sure this will give me what I need.

I've tried over a dozen variations of select distinct, joins, etc but with no luck.

Any of you sql gurus know how to solve this problem? I've been banging my head against it for a couple days and boy does my head hurt.

View Replies !

Subquery In SELECT Statement Before FROM
Hello!

I can use querys like these in Access:

SELECT Field1,
(SELECT Field2 FROM Table2 WHERE Key=1) AS Field2 FROM Table1
SELECT Field1,
(SELECT Count(Field2) FROM Table2 ) AS Field2 FROM Table1

But when I
try execute it with SQL Server Everywhere it says "Token in error
=
SELECT".

Is there some kind of limitations to do this with SQL Everywhere? SQL Everywhere seems to be nice compared with Access and JET but for my project it's useless if I can't use subquerys.

-Teemu

View Replies !

How To Merge Multiple Rows One Column Data Into A Single Row With Multiple Columns

Please can anyone help me for the following?

I want to merge multiple rows (eg. 3rows) into a single row with multip columns.

for eg:
data

Date           Shift Reading
01-MAR-08     1     879.880
01-MAR-08     2     854.858
01-MAR-08     3     833.836
02-MAR-08     1     809.810
02-MAR-08     2    785.784
02-MAR-08     3     761.760

i want output for the above as:

Date              Shift1         Shift2         Shift3
01-MAR-08     879.880       854.858       833.836
02-MAR-08     809.810       785.784       761.760
Please help me.

View Replies !

Handling Multiple Columns Returned By A Subquery With An IN Clause
Hi,

I would like to know how i can handle multiple columns returned by a subquery via IN clause in case of sql server 2005. I can do that in oracle by using the following statement:

DELETE FROM TEST1
WHERE (ID, ID1) NOT IN (SELECT ID,ID1 FROM TEST2);

Thanks and Regards
Salil

View Replies !

Using A While Inside A Select Statement

Hi All,
Can we use the while loop inside a select statement? Meaning, something like this:

Code Block
SELECT DATE,
WHILE (SELECT TOP 1 DATEPART(HH,DATE) FROM SC_DATEDIMENSION_TABLE) <= 23
(SELECT DATEADD(HH,6,SC_DATEDIMENSION_TABLE.DATE) )
FROM SC_DATEDIMENSION_TABLE

What I want to do here is I have a table which has all the dates but with time only representing 00 hrs. I want to display this column and along side, I want to have another column, which displays the date split at 6 hours. So, one left column, there will 4 columns on the right.

Hope the question is clear.

Thanks a lot.

Mannu.

View Replies !

SET Inside Of SELECT Statement
How can I do this

Select id
set @temp = id
from tblexample

I want to do something, on each row output, with @temp.

View Replies !

Select Statement Inside UDf
iam trying to rerieve a certain value from one table
and i want to use that vaue inside a UDF
iam usinf a table valued function as i have to retireve no of values
Can i do something like this to retrieve the value

SET @Value=Select Value from Table WHERE xyz='some no.'
as this value is being calculated by some other fucntion and now this funcation has to use this at runtime.

View Replies !

Obtaining Data To Be Displayed In Multiple Columns From Multiple Rows

Hello All,

I am rather new to reporting on SQL Server 2005 so please be patient with me.

I need to create a report that will generate system information for a server, the issue im having is that the table I am having to gather the information from seems to only allow me to pull off data from only one row.

For example,. Each row contains a different system part (I.e. RAM) this would be represented by an identifier (1), but I to list each system part as a column in a report

The table (System Info) looks like:-

ID | System part |
1 | RAM
2 | Disk Drive
10| CPU
11| CD ROM |

Which

So basically I need it to look like this.

Name | IP | RAM | Disk Drive|
----------------------------------------------
A | 127.0.0.1 | 512MB | Floppy

So Far my SQL code looks like this for 1 item
SELECT SYSTEM PART
FROM System Info
WHERE System.ID = 1

How would I go about displaying the other system parts as columns with info

Any help is much appreciated!

View Replies !

Insert Query With A Select Subquery
Hi.I have an insert query which inserts record that are returned from a select subquery:
INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...
col1 and col2 in tbl1 combined ,are a unique index.
So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.
The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).How can I solve this ?

View Replies !

Select Subquery To Return COUNT
I have 2 tables, Jobs and Categories.Each job belongs to a category. At present, I am returning all categories as follows:SELECT categoryID, categoryName FROM TCCI_CategoriesWhat I'm trying to do, is also return the number of jobs assigned to each category, so in my web page display, it would show something like this:Engineering(5)Mechanical(10) etc.My db currently has 5 categories, with only one job assigned to a category. I tried the following sub-query, but instead of returning all the categories with their job counts, it just returns the category that has a job assigned to it:SELECT c.categoryID, c.categoryName, COUNT(j.jobID)FROM TCCI_Categories c, (SELECT jobID, categoryID FROM TCCI_Jobs) jWHERE j.categoryID = c.categoryIDGROUP BY c.categoryID, c.categoryName, j.jobIDThis is the output when I run the query:categoryID categoryName Column1 ---------------- ---------------------- ------------------------------32 Engineering 1 How would I fix this?

View Replies !

SubQuery In Both SELECT And WHERE Part. How To Optimise?
Ok I have the following SQL, I have a subquery in the SELECT part but also the same subquery in the WHERE part as well.What I'm trying to do is get all parents that have children OR get all parents with no children OR just get all parents regardless (@HasResponses is a BIT that can be 1, 0 or null). At the same time I want to count the total number of children in the select list, but I'm having to copy the same subquery in the SELECT and WHERE parts which doesn't seem terribly optimal to me (maybe it is, that's why I'm asking). I've tried referencing the column alias in the select list (AS [Responses]) for the where part (@HasResponses = 0 AND [Responses] = 0), but it doesn't seem to work.Is this the most optimal way to do it? Is there a better way? I'm working with SQL Server 2005.SELECTf.FeedbackText AS [Feedback Comment],u.Name AS [Feedback Author],f.CreatedDate AS [Created On],(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) AS [Responses]FROM Feedback fINNER JOIN [User] u ON f.StaffID = u.StaffIDWHERE f.CreatedDate >= @DateFromAND f.CreatedDate <= @DateToAND(@HasResponses IS NULLOR(@HasResponses = 1 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) > 0)OR(@HasResponses = 0 AND(SELECT COUNT(*)FROM FeedbackResponse frWHERE fr.FeedbackID = f.ID) = 0))

View Replies !

Subquery Issues In A Select Statement
Hi there,

I am pulling back records from the DB in this case to get Wheel information. I am pulling back based on user input, but also need to query a second table that contains the Price and model number from another table based on a field being pulled back in the original select.

I am not sure if this makes sense, here is a working copy of the SQL I have , but it's not pretty. There must be another way of stating this statement that i am missing, can anyone give me some suggestiosn?

SELECT tblMacPak2.*,
(SELECT ListPrice
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS ListPrice,
(SELECT PartNumber
FROM tblMacPakPrices
WHERE WheelId = OEMWheel) AS PartNumber
FROM tblMacPak2
WHERE (Make = N'honda') AND (Model = N'civic') AND (SubModel = N'standard') AND (YearRange = N'2006') AND (Factory_Wheel_Diameter = N'15')

3 selects in one statement...that can't be right.

Thanks,

View Replies !

Ordering A UNION Select With Subquery
I have a select such as this:

select COLUMNS from TABLE where WHERE_CLAUSE1
union
select COLUMNS from TABLE where WHERE_CLAUSE2

Now, I want to order the result set by COLUMNS. When I try the following query, the SQL fails.

select * from
(
select COLUMNS from TABLE where WHERE_CLAUSE1

union

select COLUMNS from TABLE where WHERE_CLAUSE2

) as T
order by COLUMNS

Any idea how this can be done?

Thanks

View Replies !

Insert Query With A Select Subquery
Hi.
I have an insert query which inserts record that are rturned from a select subquery:

INSERT tbl1 (col1,col2,col3) SELECT (col1,col2,col3) FROM tbl2 WHERE...

col1 and col2 in tbl1 combined ,are a unique index.

So, as I understand it sql server first returns all the records from tbl2 and then starts to insert them one by one into tbl1.

The problem is, that if one of the records returned from tbl2 violates the unique keys constraint in tbl1, sql server will not insert all of the records (even those which maintain the key constraint).
How can I solve this ?

View Replies !