ORDER BY Not Working With Subquery Value
Dec 11, 2007
So.. I'm trying to add up the number of wins and losses from a schedule of games and list them with their corresponding team name from another table.
Then I want to be able to sort by each teams number of wins. This is what I've got now, and it runs through without error, but it does not ORDER the list by "Wins"
strSQL = "Select *, ((SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Win = true) + " & _
"(SELECT Count(Win) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Loss = true)) AS [Wins], " & _
"(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Loss = true) + " & _
"(SELECT Count(Loss) FROM Game_Schedule WHERE Game_Schedule.T2_ID = standings.ID AND Win = true) AS [Losses], " & _
"(SELECT Count(Tie) FROM Game_Schedule WHERE Game_Schedule.T1_ID = standings.ID AND Tie = true OR Game_Schedule.T2_ID = standings.ID AND Tie = true) AS [Ties] FROM standings ORDER BY T_Tier, T_Name, Wins DESC"
View 2 Replies
ADVERTISEMENT
Jul 20, 2006
Hello all. I have got some wonderful help getting me to this point, although now I have run into an issue which I really can't understand. I have the following query, which should give me the records for the employee in the query, but for some reason I am getting all records.
SELECT EmpName, EmpNumber, EmpLevel, JobTask, WorkYear, Sum(tblEmpTime.Hours) AS SumOfHours
FROM tblEmpTime
WHERE EXISTS (SELECT EmpName, EmpNumber, Hours, EmpLevel, JobTask, WorkYear
FROM tblEmpTime
WHERE EmpName = 'varEmpName' AND WorkYear = 'varWorkYear')
GROUP BY EmpName, JobTask, EmpNumber, EmpLevel, WorkYear
If I run just the subquery, the results are ok, but I need to sum the hours, so that is why I used the outer query. For some reason it seems that it is ignoring the results of the subquery.
Any suggestions?
Thanks,
Parallon
View 5 Replies
View Related
Nov 10, 2006
The following seems to work in SQL Server 2005, but I'm getting theerror 'Incorrect syntax near xml' when I run it in SQL Server 2000.select a.accountid , (select street, city, state, zip from account bwhere a.accountid =b.accountid for xml auto, elements) as xmldatafrom account aThe idea is to return 2 columns:accountidxmldata (address as xml)Assuming the fields are correct, any ideas on what the problem might be?
View 3 Replies
View Related
Jul 20, 2005
All,I've seen several posts regarding using UNION or UNION ALL to mashtogether two or more resultsets into a single result set, but can'tseem to find enough info here to help me answer my particularquestion.I have a stored procedure that gets the column names in a particularformat (i.e. "chassis_id"|"chassis_description"|"modify_date") as wellas actual data for a given table (in a quote-separated, pipe-delimitedmanner i.e. "1"|"description for the chassis"|"2004-09-08").I'd like to get both of these resultsets and mash them together. Thisworks, but when I need to order the second resultset (i.e. select *from chassis order by chassis_id), SQL Server returns an errorcomplaining about the chassis_id column name (invalid column name'chassis_id') in the Order By clause.From what I can tell, I'm using the UNION and Order By in correctly,but I'm not sure exactly what's wrong with it. If I take out the OrderBy, everything works great. Although I would like to be able to ordermy second resultset (in the same sproc) if possible.The actual queries I'm running are actually quite long, but here's onethat's a bit shorter to help illustrate:SELECT '"app_group_id"|"app_group_name"|"create_date"|"create_by"|"modify_date"|"modify_by"'UNION ALLSELECT + ISNULL(CONVERT(varchar,app_group_id), '') + '|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 1))), 1,0)+ '"' + ISNULL(CONVERT(varchar(1000), +REPLACE(CONVERT(nvarchar(4000),app_group_name), '"', '""')), '') +'"|'+ SUBSTRING(RTRIM(LTRIM(CONVERT(varchar, 2))), 1,0)FROM app_grouporder by app_group_idThank for any help on this./bc
View 3 Replies
View Related
Apr 14, 2008
I've recently just got started using sub queries. The sub query that I'm running is supposed to select distinct values out of a table. It doesn't seem to be working though.
SELECT id
FROM data
WHERE (issueID IN
(SELECT DISTINCT issueID
FROM data))
When I execute this query, it returns a list of id's, but they don't each have a unique issueID. In the data table, issueID can appear more than once (hence, I need to use distinct). However, id is the primary key, and is unique.
Any ideas?
View 7 Replies
View Related
Mar 1, 2006
Hi all,
I have the following UNION ALL statement that is my attempt to gather data for the past 5 weekdays (adding a "dummy" row for today's data).
I want the final output to end up in descending order, so for today, I would want today first, then Tuesday, then Monday, then Friday, then Thursday (provided there is data for each sequential day - if not, you get the idea, I want to select back to get the latest 5 days, most recent to oldest).
This select fails, because it doesn't like the ORDER BY in the subqueryselect
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight
UNION ALL
(select top 4
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc )
I know you can't use an ORDER BY in a subquery, UNLESS the subquery also uses a TOP n (which this one does)...but does anyone know why this isn't liking my code?
I got the select to work the way I want it to by doing the following (really UGLY) code...SELECT U.DOW, U.Freight FROM
((select
GETDATE() as [OrderDate],
CASE
WHEN DATENAME(dw, GETDATE()) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, GETDATE()) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, GETDATE()) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, GETDATE()) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, GETDATE()) = 'Friday' THEN
'FRI'
END AS Dow, 'N/A' AS Freight )
UNION ALL
(select h.OrderDate as [OrderDate], h.DOW, h.Freight FROM
(select top 4
[OrderDate] as [OrderDate],
CASE
WHEN DATENAME(dw, [OrderDate]) = 'Monday' THEN
'MON'
WHEN DATENAME(dw, [OrderDate]) = 'Tuesday' THEN
'TUES'
WHEN DATENAME(dw, [OrderDate]) = 'Wednesday' THEN
'WED'
WHEN DATENAME(dw, [OrderDate]) = 'Thursday' THEN
'THUR'
WHEN DATENAME(dw, [OrderDate]) = 'Friday' THEN
'FRI'
END as DOW,
CAST(CONVERT(int, (Freight * 100)) as VARCHAR(10)) as Freight
from Northwind.dbo.orders where employeeid = 9 order by [OrderDate] desc ) H)) U
order by OrderDate descbut am still confounded about why my original sub-select is rejected with such impunity.
My confusion seems likely related to understanding the set theory or basic concepts of the building of the select/Union rather than the way I am using the ORDER BY syntax, but I just can't seem to explain it to myself.
Thoughts?
Thanks!
View 14 Replies
View Related
Jul 20, 2005
Hi, I'm trying to run a stored proc:ALTER PROCEDURE dbo.UpdateXmlWF(@varWO varchar(50))ASDECLARE @varCust VARCHAR(50)SELECT @varCust=(SELECT Customer FROM tblWorkOrdersWHERE WorkOrder=@varWO)When I remove the SELECT @varCust= I get the correct return. With itin, it just appears to run but nothing comes up in the output window.PLEASE tell me where I'm going wrong. I'm using MSDE, although I don'tthink that should matter?Thanks, Kathy
View 2 Replies
View Related
Apr 23, 2008
I'm trying to create a view using the following code:
quote:SELECT TOP (100) PERCENT Program, COUNT(Program) AS Total,
(SELECT COUNT(Program) AS Count
FROM dbo.Active_Enrollments_by_Earn
WHERE (CDE_PROJ LIKE 'NC%') AND (Program = List.Program)
GROUP BY Program) AS CDC,
(SELECT COUNT(Program) AS Count
FROM dbo.Active_Enrollments_by_Earn AS Active_Enrollments_by_Earn_1
WHERE (CDE_PROJ LIKE 'WS%0') AND (Program = List.Program)
GROUP BY Program) AS WSC
FROM dbo.Active_Enrollments_by_Earn AS List
GROUP BY Program
ORDER BY Program
On my server, it sorts the resulting view in alphabetical order automatically (it didn't matter if I put "ORDER BY Program"). On my production server, however, it doesn't sort by Program at all and I can't seem to resolve it. HELP!
View 11 Replies
View Related
Aug 29, 2007
I am inserting rows into an Excel file and the ORDER BY is coming out wrong.
When I run the select I get priority 1,2,3,4, ...10, 11, 12, ... as I should.
But in the excel file the rows come out 1,10,11,12,13 ..., 2, 20, 21, ...
SET @sql = ' INSERT INTO OpenRowset(''Microsoft.Jet.OLEDB.4.0'',''Excel 5.0;Database='+@finalFile+';HDR=YES'',
''SELECT [ID],[Priority],[Comments] FROM [Sheet1$]'')
SELECT ID,priority,comments FROM OurTable WHERE orgId='+@orgId+' ORDER BY priority,ID'
EXECUTE (@sql)
Please help!! Thank you!
View 1 Replies
View Related
Jan 17, 2007
Hello, i have something like this, i want the annoucements (status = 0) to be on top, then topics with (status = 1) below, then the rest of the topics.
So i tried:
SELECT forum_topics.id, forum_topics.status, forum_topics.username AS starter, forum_topics.subject, forum_topics.closed, forum_topics.answerpostid, forum_topics.views, forum_topics.answers, forum_topics.lastanswer, forum_topics.lastanswerid, forum_topics.created AS started, forum_answer.username, forum_answer.answer, forum_answer.created FROM forum_topics LEFT OUTER JOIN forum_answer ON forum_answer.id = forum_topics.lastanswerid WHERE (boardid = @ID OR boardid = 0) ORDER BY (status) ASC, (created) ASC
Problem is that they are not sorted diffrently, when i change the (created) ASC to (created) DESC i get the same result and the rows are not sorted, they only get sorted by status so i have status=0 at the top, then status=1 then the rest. How do i get them to be sorted first by status ASC then by created ASC/DESC?
Patrick
View 1 Replies
View Related
Jan 24, 2006
I have the view below and if I use vwRouteReference as the rowsourcefor a combo box in an MS Access form or run "SELECT * FROMvwRouteReference" in SQL Query Analyzer, the rows don't come throughsorted by Numb.Everything I've read on the web suggests that including the TOPdirective should enable ORDERY BY in views. Does someone have an ideawhy the sorting is not working correctly for this particular view? thanks.CREATE VIEW vwRouteReferenceASSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblQuestion.DescrPrimary AS FullName,tblItem.Numb, tblQuestion.DescrPrimary AS TypeFROM tblItem INNER JOIN tblQuestionON (tblItem.ID = tblQuestion.Item_ID)WHERE (((tblItem.Category_ID)>0))UNIONSELECT TOP 100 PERCENT tblItem.ID,tblItem.Numb + ' - ' + tblItem.Type + ' : ' + tblItem.Class AS FullName,tblItem.Numb, tblItem.Type + ' : ' + tblItem.Class AS TypeFROM tblItemWHERE (((tblItem.Type) = 'Assignment' OR (tblItem.Type) = 'Check' OR(tblItem.Type) = 'Route'))ORDER BY tblItem.Numb
View 49 Replies
View Related
Sep 22, 2006
Hi there,
I have a view created using the following code. The view works perfectly but does not order by the name column as I've asked it to do. In the view designer if I click on execute then the order is applied but if I save the view and run it externally (i.e. in an ASP page or within the management terminal) it does not order correctly and seems to order by the Id column.
Any help would be much appreciated. Here's the code:
SELECT TOP (100) PERCENT dbo.Members.DivisionID, COUNT(*) AS Members, dbo.Country.Name
FROM dbo.Members INNER JOIN
dbo.Country ON dbo.Members.DivisionID = dbo.Country.CountryID AND dbo.Members.CountryID = dbo.Country.CountryID
GROUP BY dbo.Members.DivisionID, dbo.Country.Name
ORDER BY dbo.Country.Name
And another, more simpler view, that doesn't sort as it's supposed to.
SELECT TOP (100) PERCENT CountryID, Name, RegionID, IsActive, HasFlag, URL, Comments
FROM dbo.Country
ORDER BY Name
Many thanks,
Ady
View 5 Replies
View Related
Oct 3, 2006
hi. i'm trying to order my results ascending by date except i'm getting some really weird output. my ouput resembles something like this:
oct 2
oct 3
sep 13
sep 21
sep 22
sep 30
aug 3
aug 5
aug 16
the data is stored in a date field. i use getdate when inserting the date to the database. is there a reason why the dates are showing up weird and not ordering appropriately? thanks for your help.
also, can you not search here any more? i keep getting timeout errors.
View 7 Replies
View Related
Jul 20, 2005
Hello all... I'm using asp to get records from an access database, verysimilar to the way datagrid would work. The title of each column in my tableis a link that alternates the sort order between ascending and descending...my problem is that text WILL change its sort order just fine but nubers arenot always in order. ie: if sort order is ASC (ascending) I might see 2000,234, 789 (should be ordered but its not). I'm guessing that ASP is handingthe string as a text string (?) and getting confused, is there a way toforce ASP into treating the string as numerals if this is the case? anyother ideas? Thanks so much.here is one of my sql commands in case you want to see it. "sort" is avariable containing the recordset to sort by depending on which link isclicked. I hope I didn't confuse the whole issue because of a lack ofcaffiene over here :)strsql = "SELECT * FROM comments ORDER BY " & sort & " DESC"Thanks of the help, much appreciated.Eno
View 2 Replies
View Related
Aug 27, 2007
Hi,
I have the following code and output
select distinct week ,sum(itemvalue) as itemvalue from (Select ATP,
WEEK=
CASE
WHEN (datepart(Dd, ATP) < 7 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 8 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 7 AND datename(Month,ATP)='OCTOBER')
THEN '1'
WHEN (datepart(Dd, ATP) < 14 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 15 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 14 AND datename(Month,ATP)='OCTOBER')
THEN '2'
WHEN (datepart(Dd, ATP) < 21 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 22 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 21 AND datename(Month,ATP)='OCTOBER')
THEN '3'
WHEN (datepart(Dd, ATP) < 28 AND datename(Month,ATP)='JANUARY')
OR
( datepart(Dd, ATP) < 29 AND datename(Month,ATP)='JULY')
OR
( datepart(Dd, ATP) < 28 AND datename(Month,ATP)='OCTOBER')
THEN '4'
WHEN ((datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JANUARY')
OR
( (datepart(Dd, ATP) IN (29,30,31)) AND datename(Month,ATP)='JULY')
OR
( (datepart(Dd, ATP) IN (28,29,30,31)) AND datename(Month,ATP)='OCTOBER')
THEN '5'
WHEN (datepart(Dd, ATP) < 4 AND datename(Month,ATP)='FEBRUARY')
( datepart(Dd, ATP) < 5 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 4 AND datename(Month,ATP)='NOVEMBER')
THEN '5'
WHEN (datepart(Dd, ATP) <11 AND datename(Month,ATP)='FEBRUARY')
OR
( datepart(Dd, ATP) < 13 AND datename(Month,ATP)='MAY')
OR
( datepart(Dd, ATP) < 12 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 11 AND datename(Month,ATP)='NOVEMBER')
THEN '6'
WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='FEBRUARY')
OR
( datepart(Dd, ATP) < 19 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 18 AND datename(Month,ATP)='NOVEMBER')
THEN '7'
WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='FEBRUARY')
OR
( datepart(Dd, ATP) < 27 AND datename(Month,ATP)='MAY')
OR
( datepart(Dd, ATP) < 31 AND datename(Month,ATP)='AUGUST')
OR
( datepart(Dd, ATP) < 25 AND datename(Month,ATP)='NOVEMBER')
THEN '8'
WHEN ((datepart(Dd, ATP) IN (25,26,27,28)) AND datename(Month,ATP)='FEBRUARY')
OR
( (datepart(Dd, ATP) IN (27,28,29,30,31)) AND datename(Month,ATP)='MAY')
OR
( (datepart(Dd, ATP) IN (25,26,27,28,29,30)) AND datename(Month,ATP)='NOVEMBER')
OR
( (datepart(Dd, ATP) <2) AND datename(Month,ATP)='DECEMBER')
THEN '9'
WHEN (datepart(Dd, ATP) < 11 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 9 AND datename(Month,ATP)='DECEMBER')
THEN '10'
WHEN (datepart(Dd, ATP) < 18 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 16 AND datename(Month,ATP)='DECEMBER')
THEN '11'
WHEN (datepart(Dd, ATP) < 25 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 23 AND datename(Month,ATP)='DECEMBER')
THEN '12'
WHEN (datepart(Dd, ATP) > 24 AND datename(Month,ATP)='MARCH')
OR
( datepart(Dd, ATP) > 22 AND datename(Month,ATP)='SEPTEMBER')
OR
( datepart(Dd, ATP) < 30 AND datename(Month,ATP)='DECEMBER')
THEN '13'
ELSE 'BEYOND'
END , SUM(ITEMVALUE) as ITEMVALUE
FROM tOPENLINE_MODIFIED
LEFT OUTER JOIN
tZCHANNEL ON tOPENLINE_MODIFIED.ZCHANNEL = tZCHANNEL.ZCHANNEL
WHERE RequestQtr in ('Q4')
and tOPENLINE_MODIFIED.ATP >= '7/01/07'
and tOPENLINE_MODIFIED.ORDERTYPE in ('OR','ZBOS','ZECM','ZOR','ZOB','ZEXP')
and dbo.tZCHANNEL.ZCHANNEL in ('D','I','01', '02', '06', '07', '10')
and tOPENLINE_MODIFIED.ACCTASSIGNGRP in ('01','02')
AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%celestica%')
AND tOPENLINE_MODIFIED.SOLD2NAME NOT LIKE ('%giant%')
and tOPENLINE_MODIFIED.PLANT IN ('COF1', 'I405', 'I375', 'IOM4', 'IOM5', 'I316')
GROUP BY ATP)as A
GROUP BY week
output:
week itemvalue
------ ---------------------
1 1214003.60
10 9257193.45
11 12095432.11
12 11429629.08
13 7315751.08
2 1052337.53
3 951038.10
4 274769.21
5 465278.37
6 78003.67
7 607681.02
8 9042948.17
9 2255545.25
but i need the output as
week
1
2
3
4
5
6
7
8
9
10
11
12
13
iam not able to achieve this after trying so many times.Please help me on this.
Thanks,
SVGP
View 6 Replies
View Related
Apr 26, 2008
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 1 Replies
View Related
Jul 20, 2005
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 3 Replies
View Related
Mar 4, 2004
Hi everyone.
I know, I know, it should have been a datetime from the start...but here's the problem.
I'm trying to sort by my date field but because it looks like: "04/03/2004 12:14:21 PM" it's not ordering it properly using:
ORDER BY [Date]
Are there any work arounds for this? Is there some way of doing:
ORDER BY covert(datetime, [Date], 103) or something?
Cheers
Andrew
View 3 Replies
View Related
Apr 24, 2007
Hello,
I am trying insert the "Order by" clause into DMX but nothing is working.
INSERT INTO MINING STRUCTURE [ARS] (
[OrderID],
[Product_Table](SKIP, [Product])
)
SHAPE {
OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID"
From "dwMDA"."dbo"."vDetail" Order By "OrderID"')
} APPEND
( {
OPENQUERY ([dwMDA on PSD_TEST_TEST], 'Select Top 30000 "OrderID","Product"
From "dwMDA"."dbo"."vDetail" Order By "OrderID"')} RELATE [OrderID] TO [OrderID]
) AS T
This is the DMX for market basket analysis.
I am also unable to use "order by" when i pull data into excel.
here's an example of something that doesn't work:
Select Top 20000 "OrderID","ProductGroupDescription"
From "dwMDA"."dbo"."vDetail"
Order By "OrderID"
Can someone show me the exact working SQL commands they are using for the "Order By" clause when they import data from sql into an excel spreadsheet?
Even better would be if someone
1. Open excel, and go to the data mining tab
2. Click on the Cluster Button
3. Select Analysis Service Data Source
4. Input a command the includes "Order By"
5. Run the model
6. Show me the tracer.
Thanks
Davy
View 1 Replies
View Related
Nov 4, 2010
I have created a report that sharing two datasets for displaying the data. This datasets are using Stored procedure for execution of the report.One of the stored procedure using Order by clause and returning the data.But on running the report , the report viewer displays the unsorted data of the filed. But if we run the stored procedure directly from the sql server , it will return the sorted data.
View 8 Replies
View Related
Sep 30, 2015
Expected
[care session quarter]
Q3-14
Q4-14
Q1-15
Q2-15
Q3-15
Currently the output am getting shown below
care session quarter
Q1-15
Q2-15
Q3-14
Q3-15
Q4-14
I am using this [care session quarter] column in the group by clause to achieve but no success.IF I use date column in the select clause and Group by clause then it comes correctly but groups by all dates which is not required.
Ideally I want show only quarter aggregates. The [Date Dimension] table has the column [care session quarter] which stores all the quarters of years along with dates for each day. i..e I have all columns in [Date Dimension] table as shown below
Column_name
DATE_KEY
temp_date
DATE_NAME
DATE_WEEKDAY_NAME
DATE_IS_WORKDAY
DATE_NUMBER_IN_WEEK
[Code] ....
View 2 Replies
View Related
Jul 23, 2005
I have the following insert statement in place:Insert WPHPayments(constituentID, constituentName, campaignYear, fundID, fundDescription, dateAndTimeEntered, amount)Select gt.constituentID, gt.constituentName, gt.campaignYear, gt.fundID, gt.fundDescription, gt.dateAndTimeEntered, gt.amountFrom GTPROCENTERFUNDPAYMENTEXTRACT gt, WPHExtractWhere gt.constituentID = WPHExtract.wph_constIDI want to insert all of the values that are in the GTPROCENTERFUNDPAYMENTEXTRACT table that have the same constituentID that as the records in the WPHExtract table. Am I just missing something becasue the syntax is showing that everytihing is correct however there is nothing comming back in the result set. Thanks in advance everyone. Regards,RB
View 1 Replies
View Related
Mar 6, 2008
I am getting an error as
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
while running the following query.
SELECT DISTINCT EmployeeDetails.FirstName+' '+EmployeeDetails.LastName AS EmpName,
LUP_FIX_DeptDetails.DeptName AS CurrentDepartment,
LUP_FIX_DesigDetails.DesigName AS CurrentDesignation,
LUP_FIX_ProjectDetails.ProjectName AS CurrentProject,
ManagerName=(SELECT E.FirstName+' '+E.LastName
FROM EmployeeDetails E
INNER JOIN LUP_EmpProject
ON E.Empid=LUP_EmpProject.Empid
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid = LUP_FIX_ProjectDetails.Projectid
WHERE LUP_FIX_ProjectDetails.Managerid = E.Empid)
FROM EmployeeDetails
INNER JOIN LUP_EmpDepartment
ON EmployeeDetails.Empid=LUP_EmpDepartment.Empid
INNER JOIN LUP_FIX_DeptDetails
ON LUP_EmpDepartment.Deptid=LUP_FIX_DeptDetails.Deptid
AND LUP_EmpDepartment.Date=(SELECT TOP 1 LUP_EmpDepartment.Date
FROM LUP_EmpDepartment
WHERE EmployeeDetails.Empid=LUP_EmpDepartment.Empid
ORDER BY LUP_EmpDepartment.Date DESC)
INNER JOIN LUP_EmpDesignation
ON EmployeeDetails.Empid=LUP_EmpDesignation.Empid
INNER JOIN LUP_FIX_DesigDetails
ON LUP_EmpDesignation.Desigid=LUP_FIX_DesigDetails.Desigid
AND LUP_EmpDesignation.Date=(SELECT TOP 1 LUP_EmpDesignation.Date
FROM LUP_EmpDesignation
WHERE EmployeeDetails.Empid=LUP_EmpDesignation.Empid
ORDER BY LUP_EmpDesignation.Date DESC)
INNER JOIN LUP_EmpProject
ON EmployeeDetails.Empid=LUP_EmpProject.Empid
AND LUP_EmpProject.StartDate=(SELECT TOP 1 LUP_EmpProject.StartDate
FROM LUP_EmpProject
WHERE EmployeeDetails.Empid=LUP_EmpProject.Empid
ORDER BY LUP_EmpProject.StartDate DESC)
INNER JOIN LUP_FIX_ProjectDetails
ON LUP_EmpProject.Projectid=LUP_FIX_ProjectDetails.Projectid
WHERE EmployeeDetails.Empid=1
PLEASE HELP.................
View 1 Replies
View Related
May 14, 2008
Hi,
I've running the below query for months ans suddenly today started getting the following error :"Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression."
Any ideas as to why??
SELECT t0.DocNum, t0.Status, t0.ItemCode, t0.Warehouse, t0.OriginNum, t0.U_SOLineNo, ORDR.NumAtCard, ORDR.CardCode, OITM_1.U_Cultivar,
RDR1.U_Variety,
(SELECT OITM.U_Variety
FROM OWOR INNER JOIN
WOR1 ON OWOR.DocEntry = WOR1.DocEntry INNER JOIN
OITM INNER JOIN
OITB ON OITM.ItmsGrpCod = OITB.ItmsGrpCod ON WOR1.ItemCode = OITM.ItemCode
WHERE (OITB.ItmsGrpNam = 'Basic Fruit') AND (OWOR.DocNum = t0.DocNum)) AS Expr1, OITM_1.U_Organisation, OITM_1.U_Commodity,
OITM_1.U_Pack, OITM_1.U_Grade, RDR1.U_SizeCount, OITM_1.U_InvCode, OITM_1.U_Brand, OITM_1.U_PalleBase, OITM_1.U_Crt_Pallet,
OITM_1.U_LabelType, RDR1.U_DEPOT, OITM_1.U_PLU, RDR1.U_Trgt_Mrkt, RDR1.U_Wrap_Type, ORDR.U_SCCode
FROM OWOR AS t0 INNER JOIN
ORDR ON t0.OriginNum = ORDR.DocNum INNER JOIN
RDR1 ON ORDR.DocEntry = RDR1.DocEntry AND t0.U_SOLineNo - 1 = RDR1.LineNum INNER JOIN
OITM AS OITM_1 ON t0.ItemCode = OITM_1.ItemCode
WHERE (t0.Status <> 'L')
Thanks
Jacquues
View 4 Replies
View Related
Jul 19, 2007
Hi guys,
A have a problem that I need understand.... I have 2 server with the same configuration...
SERVER01:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
********************************
SERVER02:
-----------------------------------------------------
Microsoft SQL Server 2000 - 8.00.2191 (Intel IA-64)
Mar 27 2006 11:51:52
Copyright (c) 1988-2003 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
sp_dboption 'BB_XXXXX'
The following options are set:
-----------------------------------
trunc. log on chkpt.
auto create statistics
auto update statistics
OK, the problem is that if a run the below query in server01, i get error 512:
Msg 512, Level 16, State 1, Line 1
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
But, if run the same query in the server02, the query work fine -.
I know that I can use IN, EXISTS, TOP, etc ... but I need understand this behavior.
Any idea WHY?
SELECT dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao ds_tipo_transacao_movimento ,
dbo.opf_header_operacao.ds_tipo_transacao ds_tipo_transacao_header,
'SD' ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente,
0 vl_entrada_compra_ctro ,0 vl_entrada_compra_premio,
0 vl_entrada_venda_ctro , 0 vl_entrada_venda_premio,
0 vl_saida_compra_ctro, 0 vl_saida_compra_premio,
0 vl_saida_venda_ctro, 0 vl_saida_venda_premio,
0 vl_lucro , 0 vl_prejuizo, 0 vl_naoexec_contrato,
0 vl_naoexec_premio,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_ganho) vl_aprop_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_aprop_perda) vl_aprop_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_ganho) vl_rever_ganho,
sum(dbo.opf_saldo_ctb_opc_flx.vl_rever_perda) vl_rever_perda,
sum(dbo.opf_saldo_ctb_opc_flx.vl_irrf) vl_irrf
FROM dbo.opf_saldo_ctb_opc_flx,
dbo.opf_header_operacao ,
dbo.opf_movimento_operacao
WHERE dbo.opf_saldo_ctb_opc_flx.dt_saldo = '6-29-2007 0:0:0.000'
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_saldo_ctb_opc_flx.no_contrato )
and ( dbo.opf_header_operacao.no_contrato = dbo.opf_movimento_operacao.no_contrato )
and ( dbo.opf_movimento_operacao.dt_pregao = (select (o.dt_pregao) from dbo.opf_movimento_operacao o
where o.no_contrato = dbo.opf_movimento_operacao.no_contrato and o.dt_pregao <='6-28-2007 0:0:0.000' ) )
and (dbo.opf_saldo_ctb_opc_flx.ic_tipo_saldo = 'S')
group by dbo.opf_saldo_ctb_opc_flx.dt_saldo,
dbo.opf_saldo_ctb_opc_flx.cd_indice_opf,
dbo.opf_saldo_ctb_opc_flx.cd_classificacao,
dbo.opf_movimento_operacao.ds_tipo_transacao,
dbo.opf_header_operacao.ds_tipo_transacao ,
ds_status_operacao,
dbo.opf_header_operacao.ds_tipo_opcao ,
dbo.opf_header_operacao.id_empresa,
dbo.opf_saldo_ctb_opc_flx.ic_empresa_cliente
Thanks
Nilton Pinheiro
View 9 Replies
View Related
Aug 7, 2007
I am trying to set sorting up on a DataGrid in ASP.NET 2.0. I have it working so that when you click on the column header, it sorts by that column, what I would like to do is set it up so that when you click the column header again it sorts on that field again, but in the opposite direction. I have it working using the following code in the stored procedure: CASE WHEN @SortColumn = 'Field1' AND @SortOrder = 'DESC' THEN Convert(sql_variant, FileName) end DESC,
case when @SortColumn = 'Field1' AND @SortOrder = 'ASC' then Convert(sql_variant, FileName) end ASC,
case WHEN @SortColumn = 'Field2' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, Convert(varchar(8000), FileDesc)) end DESC,
case when @SortColumn = 'Field2' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), FileDesc)) end ASC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'DESC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end DESC,
case when @SortColumn = 'VersionNotes' and @SortOrder = 'ASC' then convert(sql_variant, convert(varchar(8000), VersionNotes)) end ASC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'DESC' THEN CONVERT(sql_variant, FileDataID) end DESC,
case WHEN @SortColumn = 'FileDataID' and @SortOrder = 'ASC' THEN CONVERT(sql_variant, FileDataID) end ASC And I gotta tell you, that is ugly code, in my opinion. What I am trying to do is something like this: case when @SortColumn = 'Field1' then FileName end,
case when @SortColumn = 'FileDataID' then FileDataID end,
case when @SortColumn = 'Field2' then FileDesc
when @SortColumn = 'VersionNotes' then VersionNotes
end
case when @SortOrder = 'DESC' then DESC
when @SortOrder = 'ASC' then ASC
end and it's not working at all, i get an error saying: Incorrect syntax near the keyword 'case' when i put a comma after the end on line 5 i get: Incorrect syntax near the keyword 'DESC' What am I missing here? Thanks in advance for any help -Madrak
View 1 Replies
View Related
Jul 6, 2014
I am trying to add the results of both of these queries together:
The purpose of the first query is to find the number of nulls in the TimeZone column.
Query 1:
SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename
The purpose of the second query is to find results in the AAST, AST, etc timezones.
Query 2:
SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')
Note: both queries produce a whole number with no decimals. Ran individually both queries produce accurate results. However, what I would like is one query which produced a single INT by adding both results together. For example, if Query 1 results to 5 and query 2 results to 10, I would like to see a single result of 15 as the output.
What I came up with (from research) is:
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST'))
I get a msq 102, level 15, state 1 error.
I also tried
SELECT ((SELECT COUNT(*) - COUNT (TimeZone)
FROM tablename) + (SELECT COUNT (TimeZone)
FROM tablename
WHERE TimeZone NOT IN ('EST', 'MST', 'PST', 'CST')) as IVR_HI_n_AK_results
but I still get an error. For the exact details see:
[URL]
NOTE: the table in query 1 and query 2 are the same table. I am using T-SQL in SQL Server Management Studio 2008.
View 6 Replies
View Related
Jan 7, 2007
Finding the "pieces of information" I need to successfully install the SQL Server Express edition is so complex. Uninstalls do "not" really uninstall completely, leading to failure of SQL install. Can you suggest a thorough, one-stop site for directions for the order of app uninstalls and then the order for app installs for the following...
SQL Server Express edition
Visual Studios 2005
Jet 4.0 newest upgrade
.Net Framework 2.0 (or should I use 3.0)
VS2005 Security upgrade
Anything else I need for just creating a database for my VS2005 Visual Basic project?
I was trying to use MS Access as my backend db but would like to try SQL Express
Thank you, Mark
View 7 Replies
View Related
Sep 24, 2012
In SQL sERVER 2008, I have two fields - Depatment and Employees. I need to sort the result set by employee number ascending order, with following exception
1)when department number = 50 - the preferred order is Employee # - 573 followed by 551-572 (employee # belong to Dept 50 = 551-573)
2)When Department number = 20 – the preferred sort order is Employee # 213-220, followed by Employee # 201-213 (employee # belong to Dept 20 = 201-220)
How shall I achieve this?
View 4 Replies
View Related
May 19, 2015
I never paid much attention to this before but I noticed this today in a new table I was creating.
For tables defined in the tabular model the table properties have something like SELECT Blah FROM TableName ORDER BY Blah Then in the tabular model the table's data is in the same order it was ordered by in the data source for the table.
I have a date table I setup and I noticed it is NOT respecting the sort order.
I have it sorted by DateID which sorts with the oldest date first and newest date as last row.However, the table that is imported and stored in the data model is not in that order.
I can of course manually sort the rows in BIDS/DataTools, but I find this discrepancy odd.
Would this have negative impacts on the EARLIER function for example if the data rows are not in the order specified?
View 8 Replies
View Related
Apr 10, 2014
I have a query that calculate the total amount of order details based on a particular order:
Select a.OrderID,SUM(UnitPrice*Quantity-Discount)
From [Order Details]
Inner Join Orders a
On a.OrderID=[Order Details].OrderID
Group by a.OrderID
My question is what if I wanted to create a formula to something like:
UnitPrice * Quantity - DiscountAmount Where DiscountAmount = UnitPrice Quantity * Discount
Do I need to create a function for that? Also is it possible to have m y query as a table variable?
View 7 Replies
View Related
Mar 27, 2008
Hi!
I recently run into a senario when a procedure quiered a table without a order by clause. Luckily it retrived data in the prefered order.
The table returns the data in the same order in SQL Manager "Open Table"
So I started to wonder what deterimins the sort order when there is no order by clause ?
I researched this for a bit but found no straight answers. My table has no PK, but an identiy column.
Peace.
/P
View 5 Replies
View Related
Jan 4, 2008
Hey guys, i need to find out how can i add order items under a Purchase Order number.
My table relationship is PurchaseOrder ->PurchaseOrderItem.
below is a Stored Procedure that i have wrote in creating a PO:
CREATE PROC spCreatePO (@SupplierID SmallInt, @date datetime, @POno SmallInt OUTPUT)
AS
BEGIN
INSERT INTO PurchaseOrder (PurchaseOrderDate, SupplierID) VALUES(@date, @SupplierID)
END
SET @POno = @@IDENTITY
RETURN
However, how do i make it that it will automatically adds item under the POno being gernerated? can i use a trigger so that whenever a Insert for PO is success, it automaticallys proceed to adding the items into the table PurcahseOrderItem?
CREATE TRIGGER trgInsertPOItem
ON PurchaseOrderItem
FOR INSERT
AS
BEGIN
'What do i entered???'
END
RETURN
help is needed asap! thanks!
View 14 Replies
View Related
