Complex 'order By' Clauses
Apr 11, 2007
I have a table, basically consisting of products and their prices. I want to select some products, then sort them by price in ascending order BUT putting prices of zero at the bottom. (e.g. 5.99, 8.99, 10.99, 0.00, 0.00)
I thought I'd be able to do something like:
ORDER BY (price != 0), price
thinking that it would sort rows according to whether the condition was true or not, and then by price, but MSSQL doesn't seem to allow this. should this work, or is there another way around this? One solution would be to load the values into a table object and sort them using that, but I'd rather do all of this in SQL if possible, for speed.
any suggestions?
thanks!
View 1 Replies
ADVERTISEMENT
Feb 17, 2008
Hi, I hope some one can help me. I have a stored procedure (Microsoft SQL 2005 Express Edition) that I want users to be able to dynamically set the, group by, order by (@orderby) and where clause (@where). I have managed to get the group by to work but can't seem to get the where and order by to work. Here's my stored procedure. Any idea how this can be done? ALTER PROCEDURE [dbo].[sp_aggregate] -- Add the parameters for the stored procedure here @finfileid int, @phaseid int, @supplierid int, @measurementid int, @roleid int, @groupby int, @orderby int, @where int AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsMonthId) AS ProjFinFileMonthItemsMonthId, SUM(ProjectFinFileMonthItems.ProjFinFileMonthItemsValue * ProjectFinFileMonthItems.ProjFinFileMonthItemsRate * ProjectFinFileMonthItems.ProjFinFileMonthItemsAvail / 100) AS total, MAX(ProjectFinFileItems.ProjPhaseId) AS phaseid, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS deliveid, MAX(ProjectFinFileMonthItems.SupplierId) AS supplierid, MAX(ProjectFinFileMonthItems.ProjFinFileItemsId) AS ProjFinFileItemsId, MAX(ProjectFinFileMonthItems.ProjFinFileMonthItemsId) AS ProjFinFileMonthItemsId, MAX(ProjectFinFileMonthItems.ProjDeliveId) AS ProjDeliveId, MAX(ProjectPhases.ProjectPhaseName) AS ProjectPhaseName, MAX(Suppliers.SupplierName) AS SupplierName, MAX(ProjectFinFileMonthItems.RoleId) AS RoleId, MAX(Measurements.MeasurementName) AS MeasurementName, MAX(ProjectFinFileMonthItems.MeasurementId) AS MeasurementId, MAX(ProjectFinFileMonthItems.FinDataTypeId) AS FinDataTypeId, MAX(FinDataTypes.FinDataTypeName) AS FinDataTypeName, max(ProjectFinFileItems.FinFileId) as finfileid FROM ProjectFinFileItems INNER JOIN ProjectFinFileMonthItems ON ProjectFinFileItems.ProjFinFileItemsId = ProjectFinFileMonthItems.ProjFinFileItemsId LEFT OUTER JOIN FinDataTypes ON ProjectFinFileMonthItems.FinDataTypeId = FinDataTypes.FinDataTypeId LEFT OUTER JOIN Measurements ON ProjectFinFileMonthItems.MeasurementId = Measurements.MeasurementId LEFT OUTER JOIN Roles ON ProjectFinFileMonthItems.RoleId = Roles.RoleId LEFT OUTER JOIN ProjectPhases ON ProjectFinFileItems.ProjPhaseId = ProjectPhases.ProjectPhaseId LEFT OUTER JOIN Suppliers ON ProjectFinFileMonthItems.SupplierId = Suppliers.SupplierId /*dynamic where clause needs to go here */ /*dynamic group by clause */ GROUP BY CASE when @groupby=1 then ProjectFinFileItems.projphaseid --phaseid when @groupby=2 then ProjectFinFileMonthItems.supplierid -- supplierid when @groupby=3 then ProjectFinFileMonthItems.measurementid -- measurment when @groupby=4 then ProjectFinFileMonthItems.roleid --role else ProjectFinFileMonthItems.ProjFinFileMonthItemsId END /*dynamic order clause needs to go here */ END cheers Mark :)
View 2 Replies
View Related
Jun 4, 2007
Hello All,
I am trying to do a complex ordering of my query results. Hopefully I can explain what I am trying to do. To simplify the explanation I have a table with two fields in it.
Table name: SPACE
Column names: SPACE_TYPE_ID, OWNER_ID
What I would like to to is order the results in this exact way:
SPACE_TYPE_ID = 2
SPACE_TYPE_ID = 5
SPACE_TYPE_ID = 6
OWNER_ID Alphabetically (if SPACE_TYPE_ID = 3,4,9)
SPACE_TYPE_ID = 1
SPACE_TYPE_ID = 7
SPACE_TYPE_ID = 8
Basically it will be ordered by the id unless the id is 3,4 or 9. I thought I could possibly use a UNION with three select statements but I believe I can't have three order by statements with a UNION. I also was told about temp tables in the database but have no idea how to use them. Is this a possible solution.
Any help on how to achieve this customized order would be greatly appreciated.
Thanks
jlmoshier
View 5 Replies
View Related
Nov 12, 2003
I have web forms with about 10-15 optional search parameters (fields) for a give table. Each item (textbox) in the form is treated as an AND condition.
Right now I build complex WHERE clauses based on wheather data is present in a textbox and AND each one in the clause. Also, if a particular field is "match any word", i get a ANDed set of OR's. As you can imagine, the WHERE clause gets quite large.
I build clauses like this (i.e., 4 fields shown):
SELECT * from tableName WHERE (aaa like '%data') AND (bbb = 'data') AND (ccc like 'data%') AND ( (xxx like '%data') OR (yyy like '%data%') )
My question is, are stored procedures better for building such dynamic SQL clauses? I may have one field or all fifteen. I've written generic code for building the clauses, but I don't know much about stored procedures and am wondering if I'm making this more difficult on myself.
View 7 Replies
View Related
Feb 23, 2006
I'm importing a text field that is called advertiserTYPE. It will be one of four options in the parenthesis below:
advertiserTYPE (FSBO,BROKER,DEVELOPER,REALTOR) VarChar(50)
I need to be able to construct a statement that says something like:
If advertiserTYPE = FSBO
Then INSERT INTO [COLUMN1]
If advertiserTYPE = BROKER
Then INSERT INTO [COLUMN2
If advertiserTYPE = DEVELOPER
Then INSERT INTO [COLUMN3]
Am I on the right track??
View 8 Replies
View Related
Feb 21, 2007
Hello helpful people :)
I have this sproc.
ALTER PROCEDURE dbo.cis_UpdateCourseUserWithGrade
@Grade nvarchar,
@UaaStudentId nchar,
@CourseId int
AS
UPDATE cis_CourseUser
SET Grade =@Grade
WHERE UaaStudentId = @UaaStudentId
AND WHERE CourseID = @CourseId
RETURN
the ANE WHERE isn't much liked. How can I update the field with the grade where both of the conditions are true? I'm getting an invalid syntax near @UaaStudentId
Thanks.
View 4 Replies
View Related
Apr 29, 2008
I've seen lots of entries recommending the use of ISNULL in SQL WHERE clauses, e.g. in a search sproc where users can enter some or all parameters to search a table. Previously I would have used something like:SELECT * FROM MyTableWHERE (FName = @fname OR @fname IS NULL) AND(MName = @mname OR @mname IS NULL) AND(LName = @lname OR @lname IS NULL)So using the neat ISNULL syntax it could be updated to:SELECT * FROM MyTableWHERE (FName = ISNULL(@fname, FName)) AND(MName = ISNULL(@mname, MName)) AND(LName = ISNULL(@lname, LName))Having played around with this I stumbled upon a problem. If one of the fields, e.g. MName, is NULL then that clause will return false since MName = NULL isn't true and you have to use MName IS NULL. Did I miss all the caveats with using ISNULL in this way on fields that can contain NULL or have I missed something else?
View 4 Replies
View Related
May 19, 2008
So how do you do dynamic WHERE clauses with asp.net?
I have a QueryString parameter with a value that will look like this. |1| or |1||2| or |1||2||3| or etc. For each number in the value I want to add an EXISTS function to the WHERE clause. I have accomplished this in T-SQL. But I can't figure out how to bind asp.net control to it. It gives me 'Invalid syntax near the keyword 'ORDER'. I know this has something to do with .net not being able to exaluate the schema properly.
So I tried Linq to Sql. Well, so happens that my WHERE clause is pointing to the existance of records in another table which the default install of Linq to Sql doesn't seam to support. I came across this post (http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2979081&SiteID=1) that explains how to dynamically query across multiple tables. I tried this and got errors when trying to plug in the additional code. And this still does not let me dynamically add where clauses.
I tried the who Predicate thing and ran into the same problem where I can't reference the existance of values in foreign tables.
Here is my sproc. Can anyone tell me how to get this into a asp.net environment. I also do NOT want to auto-generate fields in my GridView.ALTER PROCEDURE dbo.Item_SelectByFilters @Item_Category_Id int = NULL, @ItemSpecificValueIdList varchar(100) = NULL, @debug bit = 0 AS DECLARE @sql nvarchar(4000), @paramlist nvarchar(4000) SELECT @sql = 'SELECT Item_Id, Number, Primary_Item_Category_Id, Secondary_Item_Category_Id, Engineering_Document_Id, CategoryIdList FROM Item WHERE (1 = 1)' IF @Item_Category_Id IS NOT NULL SELECT @sql = @sql + ' AND Item.CategoryIdList Like ''|'' + CONVERT(varchar, (@xItem_Category_Id) + ''|''' IF @ItemSpecificValueIdList IS NOT NULL BEGIN WHILE @ItemSpecificValueIdList <> '' BEGIN DECLARE @StartLocation int SELECT @StartLocation = CHARINDEX('-', @ItemSpecificValueIdList, 1) IF @StartLocation <> 0 BEGIN DECLARE @EndLocation int SELECT @EndLocation = CHARINDEX('|', @ItemSpecificValueIdList, @StartLocation + 1) IF @EndLocation <> 0 BEGIN DECLARE @ValueLength int SELECT @ValueLength = @EndLocation - @StartLocation - 1 DECLARE @ValueBetween varchar(10) SELECT @ValueBetween = SUBSTRING(@ItemSpecificValueIdList, @StartLocation + 1, @ValueLength) IF @ValueBetween IS NOT NULL SELECT @sql = @sql + ' AND EXISTS(SELECT Item_Specific_Value_Id FROM Item_Specific_Value WHERE Item_Id = Item.Item_Id AND Item_Specific_Value.Item_Specific_Value_Id = ' + @ValueBetween + ')' END END SET @ItemSpecificValueIdList = SUBSTRING(@ItemSpecificValueIdList, @EndLocation + 1, len(@ItemSpecificValueIdList) - @EndLocation) END END SELECT @sql = @sql + ' ORDER BY Item.Number' IF @debug = 1 PRINT @sql SELECT @paramlist = '@xItem_Category_Id int'
EXEC sp_executesql @sql, @paramlist, @Item_Category_Id
View 20 Replies
View Related
May 27, 2008
Here is another project that seams to be the same as my previous post but a little simpler. http://forums.asp.net/t/1263330.aspx
My Item_Category table is self referencing. I have a list of category Ids in a particular order representing the tree of categories from the current category to the top. For example: 1 - Fasteners, 4 - Screws, 12 - Sheet Metal Screws or the reverse.
I need to translate this so I can bind a cookie crumb type control to it. ie. Fasteners > Screws > Sheet Metal ScrewsI originally accomplished this like so;
Dim q = From ic In itemdc.Item_Category _Where valueId.Contains(ic.Item_Category_Id) _ Select Id = ic.Item_Category_Id, ic.Name But found that this does not order them properly. Remember that the list of Ids is in a particular order. I need a query to result to the below. I was using a horizontal DataList control as the cookie crumb control. Again, I am trying to do this with Linq to Sql without using Stored Procedures even though I am the DBA as well. Or is there some other better way to do this?
Id
Name
Sort
1
Fastener
1
4
Screws
2
12
Sheet Metal Screws
3
Finally, as I mentioned in my previous post, I accomplished this concept with a stored procedure but then the asp.net controls could not recognize the schema, hence I could not bind the controls to the data source control.
View 2 Replies
View Related
Oct 28, 2014
I have an existing SPROC which works the way it should do and was difficult to construct. But now I need to add 1 more condition and hopefully that should be it however I am struggling how to do this. Here is a snip of the existing SPROC.
quote:
IF @columnName = 'Rating - Fire' OR @columnName = 'Rating - PPE' OR @columnName = 'Rating - Reactivity' OR @columnName = 'Rating - Health'
BEGIN
SELECT @totalRecords = (SELECT COUNT(p.[SID]) FROM S_Summary p INNER JOIN S_Detail detail ON detail.SID = p.SID
WHERE
CASE @columnName
[code]...
so this works fine but now I need to add 1 more thing..if a new parameter is supplied (lets call it @stringBranch), then I want to join another table and also match the param value to a field in that table along with any existing WHERE conditions being applied to this:
quote:
WHERE (@columnName IS NULL AND @columnValue IS NULL)
OR
CASE @columnName
WHEN 'Rating - Fire' THEN detail.F
WHEN 'Rating - PPE' THEN detail.P
[code]...
View 9 Replies
View Related
Feb 23, 2006
I'm trying to use multiple where clauses but its not working. I want it to look something like this:
INSERT INTO [USCondex_Development].[dbo].[miamiheraldExceptions]([InvalidEmails], [InvalidAdPrintID], [InvalidPropertyStreetAddress], [InvalidPropertyPrice])
SELECT [AdvertiserEmail],[AdPrintId],[AdvertiserAddress], [PropertyPrice]
FROM [Development].[dbo].[table2]
WHERE advertiseremail is NULL
and WHERE adPrintID is NULL
and WHERE firstinsertdate is NOT NULL
and WHERE propertystreetaddress is NOT NULL
and WHERE propertyprice < 100
But it's not working.
View 7 Replies
View Related
Nov 28, 2007
I am trying to wrap my WHERE clause with an IF or a CASE but cannot seem to get it to work. This is what I am trying:
WHERE
CASE
WHEN EVENT_TYPE='d' THEN
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000
ELSE
(link_inc.incident_id = 10000005) AND (B.incident_id <> 10000005) AND link_rsn.link_rsn_sc = 'CHANGE' AND B.incident_id > 10000000 AND act_type.act_type_sc <> 'CLOSURE'
END
ORDER BY B.incident_id DESC, act_reg.act_reg_id DESC
Basically I want to run a different WHERE clause based on a value (EVENT_TYPE). The error message I am getting is:
Incorrect syntax near '='.
View 5 Replies
View Related
Dec 4, 2007
If I have a table called "content_hits_tbl" and want to pull information, can't i write something like this:
SELECT COUNT(visitor_id) AS HITS, COUNT(DISTINCT visitor_id) AS VISITORS, COUNT(DISTINCT visitor_id) WHERE visit_type = 0 AS NEW, COUNT(DISTINCT visitor_id) WHERE visit_type = 1 AS RETURNING
FROM content_hits_tbl
Can't you have multiple WHERE clauses in the SELECT statement?
Any suggestions would be great. I have been wrestling with it and SQL queries arent my strong area ...
I'm using VS05 connecting to a SQL database.
Sincerely,
Tommy
View 3 Replies
View Related
Sep 18, 2014
Below is a script that count the amount of Void properties we had in a specific month.
SELECT COUNT(C.[Place Referance]) AS [April Total Voids]
FROM
(
SELECT DISTINCTTOP 100 PERCENT
HIST.[PLACE-REF] AS 'Place Referance'
[Code] ....
It tells me I have 53 total voids.
What I also want is a column next to this to say how many of those voids back in April are STILL Void.
So basically the WHERE clause would still be the same -
WHERE [Void Start Date YEAR] = '2014'
AND [Void Start Date MONTH] = '4'
but with the added -
AND HIST.[END-DATE] IS NULL
So ideally I'm after two columns with figures in them and then going forward I can then calculate other months as well.
View 5 Replies
View Related
Aug 1, 2013
I have a Master table with a OrderNbr which is also contained in the Detail table.
It's a 1 to Many relationship, respectively.
I want to update the MASTER.FinalizedDate using a "select top 1 FinalizedDate order by FinalizedDate DESC" from the Detail table but the clause is ALL the Status have to be "F". So OrderNbr 12345 should not get updated because it contains a 'O'. OrderNbr 67899 should get updated in the Master table to 2/26/2013 because all have a 'F' and the last date to post is the official finalized date.
Here is what I came up with.......so far, but not sure how to work in the Status piece on 1 to M.
The rub here is that even if one row has the 'O' status I want to ignore the update. If all have the 'F' then I want the udpate to happen.
Update MASTER
Set FinalizedDate = (select top 1 d.FinazliedDate from Detail d
where m.OrderNbr = d.OrderNbr
and d.Status not in ('O')
Order by FinalizedDate DESC)
From MASTER m
How do I not include all 3 rows for OrderNbr 12345 because one row has the Status "O" in the DETAIL table?
Here are the table looks........
MASTER
OrderNbr Ytotals Ztotals Xtotals Finalized Date
12345$1,500$1,500$1,200
67899$1,200$1,100$900
DETAIL
OrderNbrItemNbr PriceStatusFinalized Date
1234563453453 $1,400F1/2/2013
12345554444 $1,500F1/2/2013
12345545444 $2,200O NULL
67899333334 $899F2/24/2013
678993434344 $659F2/24/2013
67899434676 $499F2/26/2013
6789978888 $599F2/24/2013
View 3 Replies
View Related
May 2, 2008
hai,
i'm using a table in sql.the name of register. the table datas are
FirstName LastName UserName Pwd dob
krishna murthy ckm0006 asdfg 1985-04-01 00:00:00.000
krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja fdd ddd ddd 1985-01-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000
i want the result as:[group by or distinct by FirstName && order by dob]
FirstName LastName UserName Pwd dob
krishna dfgd ckm0006 cxbcv 1985-05-01 00:00:00.000
raja hgff fgrgf fgf 1985-02-01 00:00:00.000
i tried many queries i'm getting error Like this
Column 'register.LastName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
can anyone resolve my problem...
View 5 Replies
View Related
Jan 14, 2008
Howdy folks, first time poster.
In a UDF, how is the best way to extend a query with additional clauses based on expressions? The user input here is used to refine the basic query by introducing additional clauses. Is there something like the following?
-- The basic query
SELECT column
FROM table
WHERE clause
-- Additional clause, only appended to query
-- if expression evaluates to true
IF @parameter <> default_value
BEGIN
AND additional_clause
END
Right now I'm using CASE like the following, but it necessarily makes the query longer. Is there a more efficient way?
-- The basic query
SELECT column
FROM table
WHERE clause
-- Additional clause, should only effect result
-- set when the parameter is not default_value
AND table.column =
CASE @parameter
WHEN default_value THEN
-- identity, table.column=table.column,
-- should have no effect other than just a long query
table.column
ELSE
@parameter
END
View 8 Replies
View Related
Jan 12, 2008
Hi
I'm not sure if I have stated my subject line correctly for what I want to achieve, but I will attempt to explain it below.
In addition to what I have in my script below, I also need to include the following clauses:
1. where TransPerPaySequence.FinancialYTDCode like '2007', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72, it needs to be 35; and
2. where TransPerPaySequence.FinancialYTDCode like '2008', and e.EmployeeStatusCode like 'CASUAL' and p.PositionGroupCode like 'AC', then instead of using the divisor of 72 or 35, it needs to be 31.
I would really appreciate any assistance that can be provided.
Thanks
SELECT DISTINCT
pc.PositionClassificationCode, pc.Description AS positionclass, pg.PositionGroupCode, pg.Description AS positiongroup, p.Description AS position,
e.PreferredName + ' ' + e.LastName AS employeename, SUM(ha.Quantity)
/ ((CASE p2.PositionGroupCode WHEN 'AC' THEN 72 WHEN 'AL' THEN 75 WHEN 'EX' THEN 80 WHEN 'MG' THEN 80 WHEN 'SM' THEN 80 END) *
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%'))) AS FTE,
(SELECT COUNT(DISTINCT PaySequence) AS Expr1
FROM TransPerPaySequence AS TransPerPaySequence_1
WHERE (PayPeriodCode LIKE 'EIT') AND (Closed = '1') AND (Description LIKE 'St%')) AS payseq
FROM HistoricalAllowance AS ha LEFT OUTER JOIN
Position AS p ON ha.PositionCode = p.PositionCode LEFT OUTER JOIN
PositionGroup AS pg ON p.PositionGroupCode = pg.PositionGroupCode LEFT OUTER JOIN
PositionClassification AS pc ON p.PositionClassificationCode = pc.PositionClassificationCode LEFT OUTER JOIN
WAP ON ha.WAPCode = WAP.WAPCode LEFT OUTER JOIN
Employee AS e ON ha.EmployeeCode = e.EmployeeCode LEFT OUTER JOIN
Position AS p2 ON e.PositionCode = p2.PositionCode LEFT OUTER JOIN
TransPerPaySequence AS tpps ON ha.PaySequence = tpps.PaySequence
WHERE (e.EmployeeCode IN ('83', '739')) AND (ha.AllowanceCode IN ('005', '201', '203', '101')) AND (tpps.FinancialYTDCode LIKE '2007%')
GROUP BY pc.PositionClassificationCode, pg.PositionGroupCode, pc.Description, pg.Description, p.Description, e.PreferredName, e.LastName,
p2.PositionGroupCode
View 8 Replies
View Related
May 8, 2008
hi, i'm using Access 2007 and i'm trying to join two selects and create two new columns[complete and not complete] where 'x' denotes a hit was made. i will use this later for grouping. here is my code so far. thanks.
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.ID
FROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)=100))
SELECT tblOutlookTask.TaskSubject, tblOutlookTask.PercentComplete, tblOutlookTask.IDFROM tblOutlookTask
WHERE (((tblOutlookTask.PercentComplete)<>100))
View 3 Replies
View Related
Jun 20, 2001
I have a web page which passes back parameters to a stored procedure.
From the web page the user selects different clauses for the 'where' criteria.
Based upon the number of clause items in the parameters sent back, a select statement is built and executed.
In the stored procedure I have many if statements to chose the correct sql statement.
As the no of clauses in the where statement can vary, it can become messy script.
Has anyone dealt with this scenario. What is the best strategy ?
A simple illustation of this is as follows
A statement with two clauses :-
Select * from Sales where
user = 'John' and country = 'England'
A statement with three clauses :-
Select * from Sales where
user = 'John' and country = 'England' and County = 'Staffordshire'
The stored procedure would except three parameters and would build a string based on the number of actual where clases sent back
View 2 Replies
View Related
Feb 26, 2004
Hi,
I have a complex SQL query against multiple tables that ideally would be best used as a stored procedure.
I know of and have read about creating named parameters and setting the parameters' values in ASP just before issuing an execute command via the Command object. What I'd like to do is assign the text of the filter and sort clauses to the parameters. Not the values, but the entire string, such as
"WHERE lastname LIKE 'A%' "
"ORDER BY lastname "
I need to assign the entire clause because the query may or may not use a particular clause.
I have experimented. However SQL treats the parameter as a literal string rather than part of the SQL query itself. Is there another technique that I may use to accomplish my goal?
Thanks in advance for your opinion, suggestion, criticism, etc.
--Tom.
View 2 Replies
View Related
Jun 18, 2014
I came across this structure today and haven't seen it before:
SELECT blablabla
FROM T1
FULL OUTER JOIN T2 ON
T1.Col1 = T2.Col1
AND T1.Col2 = T2.Col2 ON
T3.Col1 = T1.Col1
AND T3.Col2 = T1.Col2 ON
T4.Col1 = T1.Col1
AND T4.Col2 = T1.Col2
I can follow along until the second "ON".
View 9 Replies
View Related
Nov 28, 2007
Hello,
i've written the following query:
SELECT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE (SELECT MONTH(s.datum_zeit)) = ((SELECT MONTH(GETDATE()))-2)and
(SELECT year(s.datum_zeit)) = (SELECT year(GETDATE()))
order by s.stunde_id
when copying that query to the view editor and executing it, it trys to fix it somehow to:
SELECT TOP (100) PERCENT dbo.KALENDER.KALENDER_ID, dbo.KALENDER.JAHR_BEZ, dbo.KALENDER.JAHR_WERT, dbo.KALENDER.HALBJAHR_WERT,
dbo.KALENDER.HALBJAHR_BEZ1, dbo.KALENDER.HALBJAHR_BEZ2, dbo.KALENDER.QUARTAL_WERT, dbo.KALENDER.QUARTAL_BEZ1,
dbo.KALENDER.QUARTAL_BEZ2, dbo.KALENDER.MONAT_BEZ, dbo.KALENDER.MONAT_WERT, dbo.KALENDER.TAGE_IM_MONAT,
dbo.TAG.KALENDERWOCHE, dbo.TAG.WOCHENTAG, dbo.TAG.TAG, s.STUNDE_ID, s.DATUM_ZEIT
FROM dbo.KALENDER INNER JOIN
dbo.TAG ON dbo.KALENDER.KALENDER_ID = dbo.TAG.KALENDER_ID INNER JOIN
dbo.STUNDE AS s ON dbo.TAG.TAG_ID = s.TAG_ID
WHERE ((SELECT MONTH(s.datum_zeit) AS Expr1
FROM ) =
(SELECT MONTH(GETDATE()) AS Expr1) - 2) AND
((SELECT YEAR(s.datum_zeit) AS Expr1
FROM ) =
(SELECT YEAR(GETDATE()) AS Expr1))
ORDER BY s.STUNDE_ID
... but this causes syntax-errors. I don't understand why this query works fine in the query editor but then gets automatically "destroyed" by the view editor. Do i have to use more statements to get the working query to run inside a view?
Thanks alot for reading.
View 1 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
May 8, 2007
hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL
SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @Date_ordered OR @Date_ordered IS NULL) AND ([Date_ordered] <= @Date_ordered2 OR @Date_ordered2 IS NULL OR (Order_ID=ISNULL(@OrderID_ID,Order_ID) OR @Order_ID IS NULL))">
but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!
Thanks
Jez
View 4 Replies
View Related
Apr 14, 2008
Hi,
We got a problem.
supposing we have a table like this:
CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go
insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)
select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde
select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View 14 Replies
View Related
Jul 20, 2005
Hi,guys!I have a table below:CREATE TABLE rsccategory(categoryid NUMERIC(2) IDENTITY(1,1),categoryname VARCHAR(20) NOT NULL,PRIMARY KEY(categoryid))Then I do:INSERT rsccategory(categoryname) VALUES('url')INSERT rsccategory(categoryname) VALUES('document')INSERT rsccategory(categoryname) VALUES('book')INSERT rsccategory(categoryname) VALUES('software')INSERT rsccategory(categoryname) VALUES('casus')INSERT rsccategory(categoryname) VALUES('project')INSERT rsccategory(categoryname) VALUES('disert')Then SELECT * FROM rsccategory in ,I can get a recordeset with the'categoryid' in order(1,2,3,4,5,6,7)But If I change the table definition this way:categoryname VARCHAR(20) NOT NULL UNIQUE,The select result is in this order (3,5,7,2,6,4,1),and 'categoryname 'in alphabetic.Q:why the recordset's order is not the same as the first time since'categoryid' is clustered indexed.If I change the table definition again:categoryname VARCHAR(20) NOT NULL UNIQUE CLUSTEREDthe result is the same as the first time.Q:'categoryname' is clustered indexed this time,why isn't in alphabeticorder?I am a newbie in ms-sqlserver,or actually in database,and I do havesought for the answer for some time,but more confused,Thanks for yourkind help in advance!
View 2 Replies
View Related
Apr 14, 2008
Hi,
We got a problem.
supposing we have a table like this:
CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go
insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)
select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde
select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9 Bank of abcde
8 Bank of abcde
7 Bank of abcde
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde
According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?
View 5 Replies
View Related
