UPDATE ORDER BY Error

Jul 23, 2005

Hi I'm trying to update a db based on the select statement which has
ORDER BY in it.
And due to that I'm getting error which states that

Server: Msg 1033, Level 15, State 1, Line 13
The ORDER BY clause is invalid in views, inline functions, derived
tables, and subqueries, unless TOP is also specified

Here is my sql statement:

SELECT SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID, COUNT(*)
from PS_TEAM_CODE_TBL A, PS_TEAM_MEMBERS B, PS_MEMBER_PERSON C
WHERE A.SUPPORT_TEAM_CD = B.SUPPORT_TEAM_CD
AND A.EFF_STATUS = 'A'
AND A.EFFDT >= (SELECT MAX(AX.EFFDT) FROM PS_TEAM_CODE_TBL AX
WHERE A.SETID = AX.SETID
AND A.SUPPORT_TEAM_CD = AX.SUPPORT_TEAM_CD)
AND B.SUPPORT_TEAM_MBR = C.SUPPORT_TEAM_MBR
GROUP BY SUBSTRING(A.DESCR,1,10), B.SUPPORT_TEAM_MBR, EMPLID
HAVING COUNT(*) > 1
ORDER BY SUBSTRING(A.DESCR,1,10)

What should I do to avoid this problem.

Thanks in advance for your help.

View 10 Replies

Update Order

Jul 20, 2005

Hello,I'm using SS2000 on NT4.If I run the following query, is it garanteed that the field"OldField1Value" will hold the value Field1 had before the query was run?UPDATE MyTableSET Field1 = 42,OldField1Value = Field1.... and in this query?UPDATE MyTableSET OldField1Value = Field1,Field1 = 42

View 2 Replies View Related

T-SQL UPDATE .. FROM .. ORDER BY Problem

Jul 23, 2005

Hi,I was hoping someone could help me with what I'm sure is a very simpleproblem...I just can't seem to find the syntax!I'm wanting to update the rows in 'tbl_consolidate' from 'tbl_hold',but working through the records in 'tbl_hold' in the order of dates ina date field, rather than the order that the rows are necessarily in.I came up with the following code to do this:update tbl_consolidateset field1 = b.field1, field2 = b.field2, field3 = b.field3from(select * from tbl_hold order by datefield1) bwhere tbl_consolidate.ID1 = b.ID1(I originally tried to use an alias 'a' for tbl_consolidate but thisthrew an error)In tbl_consolidate, ID1 is unique, but in tbl_hold there can be manyrecords with the same value in ID1. Using my code, I'd expect theUPDATE to work its way through the records in tbl_hold in order of thedatefield1 column, but it doesn't seem to do it in this order. Cananyone help?

View 2 Replies View Related

UPDATE Query With ORDER BY Clause

May 7, 2012

I could write a query with a sub-query in order to perform an UPDATE on the most recent 60,000 records of a table based on a date field, but unfortunately I am receiving an error.

Code:
SELECT * FROM DMTM
SET transmit_date = '2012-05-07 00:00:00.000', transmit_status = '1223'
WHERE temp_pk in

[code]...

View 6 Replies View Related

Execution Order Insert && Update

Mar 18, 2008

Hello All,

I have a Conditional Split, where there are two outputs.

First output is a dataset which is to be inserted into the database.

Second output is a dataset for which the data already exists in the DB. I just need to update those data.

I have a doubt here. I want the insertion to be done first and then updation.
Is there any property to be set for insertion or updation, something that maintains the order of execution or priority of execution.

Please do ask me if you need any further clarification.

Thanks,
Kapadia Shalin P.

View 5 Replies View Related

SQL 2012 :: Using CASE In Order To Update Columns To Be Either A Or B

May 13, 2015

I am relatively new to complex queries and need creating a query using a CASE in order to update columns to be either A or B. A few things about this is that I am joining tables from linked servers as well. This is the last part. I execute the query and receive the error:

Incorrect syntax near the keyword 'from'.

select (select FirstName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorFirstName,
(select LastName from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as supervisorLastName,
(select PersonID from [ZZZXXX].HCM.dbo.tPerson where PersonGUID = tPersonJobHistAlias.SupervisorPersonGUID) as SupervisorEmployeeID,

[Code] .....

View 4 Replies View Related

Analysis :: Deploying In Order To Update The Cube?

Nov 2, 2015

Scenario: [**tableA**] plus [**tableDim1**] plus [**tableDim2**]. I create a DSW, a cube I deploy the [@@CUBE@@]..so connect the data using an excel file that show the data as [¬¬dashboard¬¬].

It works.

My question is: after three days the [**tableA**] is populated with new rows. In order to allow my colleagues to see the new rows I deploy the [@@CUBE@@] again. My colleagues can see the new rows in the [¬¬dashboard¬¬].

It works, ok. But do I really need to deploy the [@@CUBE@@] every time or it should be update automatically when you, for example, refresh the data in Excel. Do I miss something?

View 3 Replies View Related

Stored Procedure To Update A Display Order Field.

Dec 20, 2006

I am creating an app that allows the user to change the order of the list by changing a value in a displayOrder field. I'd love a button for move up /move down move bottom/move top and then pass that parameter to a stored procedure and it would renumber all the items in the list.
Example
ItemID description DisplayOrder Action0 item 1 0 Moveup/move down1 item 2 1 Moveup/move down2 item 3 2 Moveup/move down
So clicking on move up on item 2 would pass and itemID, Action and perhaps a list id to a stored proc and it would renumber the list. I'm assuming it would be done with a loop but I've never tried that.. suggestions?
Thanks - Mark

View 1 Replies View Related

T-SQL (SS2K8) :: Adding N Prefix In Update Statement In Order To Store The Text Correctly?

Sep 4, 2014

The FirstName and LastName values being passed in are in cyrillic text.

How do I add the N prefix in the update statement in order to store the text correctly.

I've tried FirstName = N@Firstname

or FirstName = '''N''' + @FirstName + '''

[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),

[code].....

View 8 Replies View Related

Error In Sp When Using Order By

Sep 2, 2004

CREATE PROCEDURE getC

AS
(
SELECT top 3 c FROM table1
order by c Desc

)
GO

and it gives me error "Incorrect syntax near keyword order", and secondly how will i get the result in a var like...

CREATE PROCEDURE getC

@d char(6)
AS
(
SELECT @d=top 1 c FROM table1
order by c Desc

)
GO

View 5 Replies View Related

ORDER BY Error

Dec 26, 2005

Hello,

in a complex stored procedure, i get an error when i want to apply an ORDER BY statement :

Here is a part of this Stored Procedure :

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut,

103) + ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

If I want to apply an ORDER BY statement, i have

SELECT TaOperations.Id_Operation, TaOperations.HeureDebut, TaOperations.Signe, TaOperations.Facturer, TaOperations.IdUserVerrou,

TaOperations.Id_Client, TaTypeOperation.Facturable, CONVERT(CHAR(10), TaOperations.HeureDebut, 103)

+ ' - ' + TaTypeOperation.TypeOperation AS Code

FROM TaOperations INNER JOIN

TaTypeOperation ON TaOperations.Id_TypeOperation = TaTypeOperation.Id_TypeOperation

WHERE (TaOperations.Id_Client = CONVERT(int, @Id_Client)) AND (TaTypeOperation.Facturable = 1) AND (TaOperations.IdUserVerrou IS NULL)

ORDER BY TaOperations.HeureDebut

I get this error :

Msg 156, Level 15, State 1, Procedure SIE_ListeOperationsFacturablesParClient, Line 46

Incorrect syntax near the keyword 'ORDER'.

Does anyone cab help me ?

View 5 Replies View Related

Error On Order By With Union All

Jun 1, 2006

Hello,

I want to order by a field that is a code, like 1,2,3,11,12,13, and i get the following error

"Only text pointers are allowed in work tables, never text, ntext, or image columns sql server"

Why do i get this, what can i do to overcome this?

Thank you

View 7 Replies View Related

Error In Use Of Order By In Over Clause

Sep 13, 2007

Hi,
I am getting a wierd error while using order by in the over clause. Consider the following query:

select count (*) over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

The error reported is :

Msg 156, Level 15, State 1, Line 2

Incorrect syntax near the keyword 'order'.

But at the same time this particular query seems to be working fine:

select rank () over (order by STD_CLL_CNTR_KEY) as cnt

from FCT_CLL_CS_DTLS

Am I missing something fundamental or is there a bigger issue.

Thanks in advance,

Regards,
Emil

View 3 Replies View Related

Passing ORDER BY Parameter Causes Error

Feb 7, 2005

Hi everyone,

I want to be able to pass an "ORDER BY" parameter into my stored procedure but I am receiving errors when I do. For example:

CREATE PROCEDURE GetFromTable
(
@SortOn varchar(20)
)
AS
SELECT *
FROM Table
ORDER BY @SortOn
GO

This is the error I get: Variables are only allowed when ordering by an expression referencing a column name. Any suggestions on what I can do to make this work?

Thanks in advance,

Piet

View 9 Replies View Related

Sql Error-order By Clause Not Working

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

Order By Case Cast Convert Error

Jul 23, 2005

I have created a SQL Stored Procedure that uses a Case statement todetermine the Order By. For one of the Case statements I am trying toturn a Char field into Datetime in for the Order By, however I can notget it to work. Can someone please take a look and my code below andtell me what I am doing wrong. Thank you.ORDER BYCASE WHEN @SortBy = 'dttm_stamp' THEN dttm_stamp End,CASE WHEN @SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),Event_Date1,101) as datetime) End,CASE WHEN @SortBy = 'FullName' THEN Emp_lastname + ', ' +Emp_firstname End,CASE WHEN @SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +Emp_firstname End DESC,CASE WHEN @SortBy = 'Emp_SSN' THEN Emp_SSN End

View 11 Replies View Related

Order By Error Running DBCC DBREINDEX

Jul 20, 2005

I'm running a simple DBCC DBREINDEX ('myTable') and I receive thefollowing error:"Server: Msg 169, Level 15, State 2, Line 2A column has been specified more than once in the order by list.Columns in the order by list must be unique. DBCC executioncompleted. If DBCC printed error messages, contact your systemadministrator."I can successfully reindex other tables in this database. I thoughtthat perhaps I had objects in the database that ended up with the samename, but I've pretty much ruled that out.Any suggestions?ThanksJohn D. Morrismailto://jmorris_42@hotmail.com

View 2 Replies View Related

Error Using Case Expression In Order By Statement

Feb 22, 2008

I have been trying to get the following Selects to work using a case expression in the order by section.

I know I can easily separate out the two statements but I want to do a select using the case statement below ;
however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union.

If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned.

Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid.

Thanks
J
declare @date
set @date = '2001'

select internalID from section_data_v3

union

select internalID from section_data_v4

order by
case when (@date = '2001') then internalID end desc

View 5 Replies View Related

Floating Point Error - Order By Mystery

Oct 27, 2006

I'm having a problem that I think is due to corrupt data. Depending on
the column I use in my order by clause two problems are occuring.

1. No results are returned and I get this error:
A floating point exception occured in the user process.

2. Results are returned but there are a different number of rows depending on which columns I use in my Order By clause.

Examples
SELECT * FROM SymbolStats
ORDER BY calc_date, symbol

Returns - 12207 rows but only includes one of the 25 dates in the table.

----------

SELECT * from SymbolStats
ORDER BY current_hv

Returns - 0 rows.

----------

SELECT * from SymbolStats
ORDER BY average_hv

Returns - floating point error

With more conditions in the WHERE clause the number of results returned varies greatly.

The
fact that different numbers of rows can be returned from the same query
only differing in how they are ordered seems like a bug.

Does this sound like corrupt data? If so, what are the best methods for fixing it?

Thanks,
patrick

View 1 Replies View Related

ERROR: The Nested Query May Be Missing An ORDER BY Clause.

Aug 19, 2006

Hi,

on executing the below query i am getting the following error

ERROR: Errors in the back-end database access module. Nested table keys in a SHAPE query must be sorted in the same order as the parent table. The nested query may be missing an ORDER BY clause.

even though the order by clause is presenet in the nested query

SELECT t.[ProductId], Predict ([Association].[Product Basket],3)
From
[Association]
PREDICTION JOIN
SHAPE {
OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT DISTINCT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
APPEND
({OPENQUERY([Adventure Works Cycle MSCRM],
'SELECT [ProductId] FROM (SELECT ProductId FROM ProductBase) as [Product] ORDER BY [ProductId]')}
RELATE [ProductId] To [ProductId]
)
AS
[Product] AS t
ON
[Association].[Product Id] = t.[ProductId] AND
[Association].[Product Basket].[Product Id] = t.[Product].[ProductId]

View 5 Replies View Related

Express Will Not Load. Insurmountable Difficulties With Order Of Uninstalls/order Of Installs/ Suggestions Plz

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

Conditional Order By - Sort Result Set By Employee Number Ascending Order

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

Analysis :: Order Of Rows In Tabular Table Not In Same Order Data Was Retrieved?

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

Calculate Total Amount Of Order Details Based On Particular Order

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

Default Sort Order - Open Table - Select Without Order By

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

How To Add Order Item Into A Purchase Order Using A Stored Procedure/Trigger?

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

Find Order By Date Range Or Order Id

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

Default Sort Order When Order By Column Value Are All The Same

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

Recordset's Order And Database's Physical Order?

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

Default Sort Order When The Order By Column Value Are All The Same

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

Order By Clause In View Doesn't Order.

May 18, 2006

I have created view by jaoining two table and have order by clause.

The sql generated is as follows

SELECT TOP (100) PERCENT dbo.UWYearDetail.*, dbo.UWYearGroup.*
FROM dbo.UWYearDetail INNER JOIN
dbo.UWYearGroup ON dbo.UWYearDetail.UWYearGroupId = dbo.UWYearGroup.UWYearGroupId
ORDER BY dbo.UWYearDetail.PlanVersionId, dbo.UWYearGroup.UWFinancialPlanSegmentId, dbo.UWYearGroup.UWYear, dbo.UWYearGroup.MandDFlag,
dbo.UWYearGroup.EarningsMethod, dbo.UWYearGroup.EffectiveMonth

If I run sql the results are displayed in proper order but the view only order by first item in order by clause.

Has somebody experience same thing? How to fix this issue?

Thanks,

View 16 Replies View Related

Inconsistent Sort Order Using ORDER BY Clause

Mar 19, 2007

I am getting the resultset sorted differently if I use a column number in the ORDER BY clause instead of a column name.

Product: Microsoft SQL Server Express Edition
Version: 9.00.1399.06
Server Collation: SQL_Latin1_General_CP1_CI_AS

for example,

create table test_sort
( description varchar(75) );

insert into test_sort values('Non-A');
insert into test_sort values('Non-O');
insert into test_sort values('Noni');
insert into test_sort values('Nons');

then execute the following selects:
select
*
from
test_sort
order by
cast( 1 as nvarchar(75));

select
*
from
test_sort
order by
cast( description as nvarchar(75));

Resultset1
----------
Non-A
Non-O
Noni
Nons

Resultset2
----------
Non-A
Noni
Non-O
Nons

Any ideas?

View 4 Replies View Related

SQL To Order Results In Predefined Order

Mar 27, 2008

I have a DB with items which can have lengths from 0 to 400 meter.In my resultset I want to show the items with length 1-400 meter and then the results with length 0 meterHow to build my SQL?

View 4 Replies View Related