0

We have a program that picks up csv files and then puts them into our ERP system. The CSV has "header" rows and "item" rows. The order within this CSV is crucial as all the "item" rows under a "header" row pertain to that "header", thus I am required to ORDER the result before export. If it don't order, then all the "header" rows are at the top and all the "Item" rows at the bottom associate to the last "header row"

what i want:

H, order number1, customer1, address1
I, item1, 6, $3.00 
I, item2, 2, $6.00 
H, order number2, customer2, address2
I, item1, 5, $2.00 
I, item2, 3, $8.00

what i get without ORDER BY:

H, order number1, customer1, address1
H, order number2, customer2, address2
I, item1, 6, $3.00 
I, item2, 2, $6.00 
I, item1, 5, $2.00 
I, item2, 3, $8.00

Unfortunately when i use order by, i get a "The ORDER BY clause is invalid in views"

SET @location = 'c:\exportfile.csv'
SET @query = 'SELECT [TYPE], [ORDER_ITEM], [CUSTOMER_QUANTITY], [ADDRESS_PRICE], [SORTER] FROM NewOrders S ORDER BY [SORTER], [Type]'
SELECT @sqlcmd = CONCAT('SQLCMD -S SERVER -E -Q "SET NOCOUNT ON; ', @query, '" -h -1 -s "," -W -b -o ', @location)
EXEC  master..xp_cmdshell @sqlcmd, no_output

is there any way around this?

Question The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries - Where the top should be? does not answer my question as I'm not counting anything, the query is just a Select * but the order of the rows is crucial.

Community
  • 1
  • 1
BelgoCanadian
  • 893
  • 1
  • 11
  • 31
  • Possible duplicate of [The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries - Where the top should be?](http://stackoverflow.com/questions/25221670/the-order-by-clause-is-invalid-in-views-inline-functions-derived-tables-subqu) – Shachaf.Gortler Apr 25 '16 at 21:38
  • that doesn't work for me, i'm just selecting all rows from a temp table that has the required results, but a simple select query is ordering according to the order of the columns messing with the order i have set out for it – BelgoCanadian Apr 25 '16 at 22:33
  • 1
    You need to post all of your information. The SQL you've posted is invalid. It has a closing bracket and no opening bracket, and there is no `order by` inside a view in any of the SQL you've posted. It's possible you just need to remove your closing bracket (and opening one) to solve your issue. – Nick.Mc Apr 25 '16 at 23:10
  • the order by clause is in the @sqlcmd, that's where i get the error from. note that i edited the query before i posted it here, the bracket is inconsequential to the question. – BelgoCanadian Apr 26 '16 at 14:20
  • See my answer on **[SQL Server Query Error -ORDER BY clause is invalid in views](https://stackoverflow.com/questions/36697511/sql-server-query-error-order-by-clause-is-invalid-in-views/61702593#61702593)**. – Murat Yıldız May 09 '20 at 19:35

0 Answers0