Order By Criteria

Jun 7, 2006

jiang writes "Apologies in advance for my inexperience.

I have a SQL table to hold my product information:
prods(prodnum(char(10), prodname(char20), quantity(int))

The values in prodname column are like:
ABCDEF
ADCDEF
BCDEFG
CDEFGH

For those products that sold out, I made a mark in the front of prodname, like *ABCDEF

Then in my query, I want to sort the product name in alphabetic order, in addition, I also want to put prodname start with * at the end of the result list, like:

ADCDEF
BCDEFG
CDEFGH
*ABCDEF

I tried to use:
select prodname from prods order by prodname

this query shows *ABCDEF is on the top of the result, then I tried:

select prodname from prods order by charindex('*', namecode)

this query does put *ABCDEF at the bottom, but other records are not in alphabetic order.

Could you please help me? Many many thanks!
Jiang"

View 3 Replies


ADVERTISEMENT

ORDER BY Specific Criteria

Aug 25, 2005

Hi,

I was wondering if it is possible to order a recordset by specific values.

That is, I want my recordset to be ordered alphabetically, but I want to set the order of the alphabet.

For example, I don't want my recordset to be odered by A, B, C, D ...I want it to be ordered by G, V, T, A ... or something.

Can you do something like
Code:

SELECT * FROM myTable ORDER BY myField (G, V, T, A, B, C, Q, X)

...etc

Thanks

e

View 2 Replies View Related

Transact SQL :: ORDER BY Clause Is Ignored When A Unique Index On Criteria Columns Exist

Sep 16, 2015

In SQL 2012.A query that joins 2 table, with order by clause doesn't get sorted and the result set is not ordered. This happens when some of the columns in the where criteria are in a unique index which is the index that is used for the join between the 2 tables, and all the columns in the unique index are in the where criteria.In the query plan there is no component for sort.The work around was to drop the unique index, or change it to a non-unique index. Once this was done, the execution plan was changed to add the sort component (even when the index was changed to non-unique and the join was still using this index).

View 4 Replies View Related

Best Practice Question: JOIN Criteria Vs. WHERE Criteria

May 24, 2004

For example, consider the following queries:


DECLARE @SomeParam INT
SET @SomeParam = 44

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID
WHERE B.SomeParamColumn = @SomeParam

SELECT *
FROM TableA A
JOIN TableB B ON A.PrimaryKeyID = B.ForeignKeyID AND B.SomeParamColumn = @SomeParam


Both of these queries return the same result set, but the first query filters the results in the WHERE clause whereas the the second query filters the results in the JOIN criteria. Once upon a time a DBA told me that I should always use the syntax of the first query (WHERE clause). Is there any truth to this, and if so, why?

Thanks.

View 3 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

Force The ORDER BY To Be In Ascending Order??

Jul 5, 2007

I noticed the StockDate is not sorted in proper order, like ascending order...


Code:

select top 1000 CONVERT(char, StockDate, 101) AS StockDate, timestamp from tblpurchaseraw where accountid = '119' order by stockdate desc



I noticed that StockDate is a datetime datatype so why does the month get ordered 1st, then day get ordered 2nd and year get ordered 3rd...

The sample data is MM/DD/YYYY...

So, how do I get it ordered propery by Year, Month then Day??

View 2 Replies View Related

Specify Order For Select Results, Order By: Help!

Nov 17, 2006

Lets say I have a table named [Leadership] and I want to select the field 'leadershipName' from the [Leadership] Table.

My query would look something like this:

Select leadershipName
From Leadership

Now, I would like to order the results of this query... but I don't want to simply order them by ASC or DESC. Instead, I need to order them as follows:

Executive Board Members, Delegates, Grievance Chairs, and Negotiators

My question: Can this be done through MS SQL or do I need to add a field to my [Leadership] table named 'leadershipImportance' or something as an integer to denote the level of importance of the position so that I can order on that value ASC or DESC?

Thanks,

Zoop

View 1 Replies View Related

In-Order/Level Order Etc. Traversal Using CTE

Apr 16, 2008

Hi,

I have some hierarchical data in a table. Say for example:

Parent Child
------------------------
NULL 1

1 2

1 3

2 4

2 5

3 6

3 7

5 8

5 9

7 10

7 11

11 12

11 13


Now I want to be able to use CTE's to be able to traverse this tree in
1) level by level order 1,2,3,4,5,6,7,8,9,10....
2) in order 1,2,4,5,8,9,3,6,7,10,11,12,13...

What would be the aueries for this. Using the following i get: 1,2,3,6,7,10,11,12,13,4,5,8,9 (interesting and potentially useful) but I would like to be able to experiment with the aforementioned orders as well.


with Tree (id)

as

(

select id from WithTest

where parent is null

union all

select a.id

from Tree b join WithTest a

on b.id = a.parent



)

select * from Tree


Any ideas? Thanks.

View 3 Replies View Related

Order ID For Latest Order For Every Customer

Jan 8, 2008

Hi!
For the Orders table (let's assume for the Northwind database), I'm trying
to get the order id of the latest order for every customer.
That means that the result should be one record per customer and that would
display CustomerID and OrderID.

Any ideas?

Thanks,
Assaf

View 1 Replies View Related

WHERE Criteria

Nov 28, 2007

SELECT Wins, Losses, Wins/Games AS WinningPct
FROM standings
Where WinningPct > 0.5

SQL will not allow me to put a column that I created(WinningPct) as criteria for WHERE (I know this is cause Select is evaluated last)

How can I list my results according to criteria I am creating in my query?

View 9 Replies View Related

Best Searching Criteria

Sep 30, 2007

I have a table
 GO
 CREATE TABLE [dbo].[Speech] (  [SpeechId] [int] IDENTITY(1,1) NOT NULL CONSTRAINT PkSpeech_SpeechId PRIMARY KEY,  [UniqueName] [varchar](52) NOT NULL,  [NativeName] [nvarchar](52) NOT NULL,  [Place] [nvarchar](52) NOT NULL,  [Type] [smallint] NOT NULL,  [LanguageId] [char](2) NOT NULL CONSTRAINT FkSpeech_LanguageId FOREIGN KEY (LanguageId) REFERENCES Language(LanguageId) ON UPDATE CASCADE ON DELETE CASCADE,  [SpeakerId] [int] NOT NULL CONSTRAINT FkSpeech_SpeakerId FOREIGN KEY (SpeakerId) REFERENCES Speaker(SpeakerId) ON DELETE CASCADE,  [IsFavorite] [bit] NOT NULL,  [IsVisible] [bit] NOT NULL,  [CreatedDate] [datetime] NOT NULL DEFAULT GETDATE(),  [ModifiedDate] [datetime] NULL )
Now I want to search the Table Speech
Sometimes by : SpeechIdSometimes by : SpeakerIdSometimes by : LanguageIdSometimes by : SpeechId And LanguageIdSometimes by : SpeakerId And LanguageId
All can have conditions with IsVisible, IsFavorite and Type columns.
for example
I need all Speeches withany particular SpeakerId and LanguageIdwith IsVisible equals to trueand IsFvaorite No Matterand Type equals to Audio
For these type of queries I think the solution is
GO
 CREATE PROCEDURE [dbo].[sprocGetSpeech]
  @speechId int = NULL,  @uniqueName varchar(52) = NULL,  @nativeName nvarchar(52) = NULL,  @place nvarchar(52) = NULL,  @type smallint = NULL,  @languageId char(2) = NULL,  @speakerId int = NULL,  @isFavorite bit = NULL,  @isVisible bit = NULL
 AS
  SELECT   SpeechId,   UniqueName,   NativeName,   Place,   Type,   LanguageId,   SpeakerId,   IsFavorite,   IsVisible,   CreatedDate,   ModifiedDate  FROM   Speech  WHERE   SpeechId = @speechId   AND UniqueName = CASE WHEN @uniqueName IS NULL THEN [UniqueName] ELSE @uniqueName END   AND NativeName = CASE WHEN @nativeName IS NULL THEN [NativeName] ELSE @NativeName END   AND Place = CASE WHEN @place IS NULL THEN [Place] ELSE @place END   AND Type = CASE WHEN @type IS NULL THEN [Type] ELSE @type END   AND LanguageId = CASE WHEN @languageId IS NULL THEN [LanguageId] ELSE @languageId END   AND SpeakerId = CASE WHEN @speakerId IS NULL THEN [SpeakerId] ELSE @speakerId END   AND IsFavorite = CASE WHEN @isFavorite IS NULL THEN [IsFavorite] ELSE @isFavorite END   AND IsVisible = CASE WHEN @isVisible IS NULL THEN [IsVisible] ELSE @isVisible END
Can anyone tell me?
Is it right way to do?Do you have any better solution?If my solution is better then Is there any performance loss with that query?

View 1 Replies View Related

BCP With Differing Criteria

Jul 12, 2002

I am familiar and happy with using BCP to export from SQL Server to a flat file

.. 1) Is there any way to pass a parameter to the sql script file each time so that i can vary the selection critria the script file uses each time?

.. 2) Can i batch the BCP calls together so they all use this parameter with some kind of 'super' BCP cammand?

Thanks in anticipation

View 3 Replies View Related

Group By Different Criteria

Feb 2, 2015

I have a table in the following format

reporting_date interest_payment balance
200401 10 10
200402 20 15
200403 30 20
200404 40 30
200405 50 40
200406 60 50
200407 70 60

i wanted to generate an OUTPUT in the following format :

The output of the query should look like this :

reporting_date interest_payment balance
Q1 -2004 60 10
Q2 -2004 170 30
Q3 -2004 70 60
Q4 -2004 0 0

i.e i wanted to represent data by quarter and year and group by quarter and year for interest_payment column but for balance i need to pick up the value from the first reporting date in that quarter ,so as you can see q1-2004 has 10,15 and 20 but only 10 is accounted as that was the first reporting date in that quarter

I have my query working for interest payment but i am not sure how do i pickup the first reporting value for balance in a quarter

SELECT report_year as "@date",'Q'+CAST(report_quarter+1 as varchar(1)) as "@quarter", SUM(a.balance) as "@balance", SUM(a.interest_payment) as "@interest_payment"
FROM (SELECT *,
(reporting_date%100 - 1)/3 as report_quarter,
reporting_date/100 as report_year
FROM employee) a
GROUP by report_year, report_quarter
order by report_year, report_quarter

View 1 Replies View Related

Get Multiple MAX With Where Criteria

Mar 17, 2015

use of Row_Number() over ( partition... but I dont understand how.

Imagine I have a table like
CustomerID, PartNum, QtyinOrder, shipped
1 6 3 0
1 6 2 0
2 6 1 0
2 5 1 0
2 5 2 0
2 5 3 0
2 5 4 1
1 6 4 1
2 6 2 1

But I wanted to return

CustomerID, PartNum, MaxQtyOrderedNotShipped

That would be just the rows
1 6 3 0
2 6 1 0
2 5 3 0

If I use this:

Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1

there is no restriction, so I get the shipped...If I alter the where clause to work only on not shipped, I get no records...as below

Select CustomerId,PartNum, shipped, QtyInOrder AS MaxOrderedNotShipped
from
(SELECT [CustomerID]
,[PartNum]
,[QtyInOrder]
,shipped
, row_number() over (partition by [CustomerID], PartNum order by QtyInOrder desc) as recid from [SILK].[dbo].[MaxofGroup]) as f where recid =1 and shipped=0

View 2 Replies View Related

FROM Vs WHERE Selection Criteria

Jan 27, 2006

Hi,

While playing with SQL Server 2000 I found you can specify the selection criteria in either the FROM clause or the WHERE clause:
e.g.
select *
from Table1 a inner join Table2 b ON a.key = b.key and a.field = 1

Is logically the same as:
select *
from Table1 a inner join Table2 b ON a.key = b.key
where a.a = 1

Any comments on which is best, and why?

Thanks,

Chris

View 3 Replies View Related

Searching Criteria

Feb 5, 2007

hi all... how do i write my where clause if i wanna search BETWEEN something to something, but at the same time, find ALL if user send nothing (''), NOT searching for '' column... and also find date if they send a date, and if they dont send date, do not consider date at all(find all at any dates).. is this possible to in one where clause without any IF statement... thanks..


WHERE d.Ownership LIKE '%' + @ClientID +'%' AND
d.WhsID LIKE '%' + @WhsFrom + '%' AND
d.CustomLotNo LIKE '%' + @CustomlotnoFrom+ '%' AND
d.LocID BETWEEN @LocFrom AND @LocTo AND
d.ItemID LIKE '%'+ @ItemFrom + '%' AND
substring(d.LocID,1,1) LIKE '%' + @ZoneFrom AND
d.RecvDate <= @Date

~~~Focus on problem, not solution~~~

View 20 Replies View Related

Not Filtering Criteria

Mar 22, 2007

I am trying to filter data from columns and this is just not working. If I select all the criteria below and try to run it - I do not get any records returned.

WHERE (DropDt >= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 13, 0)) AND (DropDt <= DATEADD(month, DATEDIFF(month, 0, GETDATE()) - 1, 0))
and Type IN ('Employee', 'Refinance')

and Chan IN ('XM', 'BN', 'RS', 'MM')

and Seg IN ('Hoc','LeftOver', 'COnly')

View 4 Replies View Related

Using Dates In Criteria

Aug 31, 2007

I am just learning SQL server 2005 and I am having trouble with the sql statement of my sqlcommand. I am just trying to query for any ticket that was open yesterday. I need this to run daily
If I run the following it works
SELECT Assigned_Group,
Assigned_Technician,
Date_Created
From "Support Center Ticket" where "Date_Created" > '08/30/2007 00:00:00'
and division = 'Northern'

however when I change it
SELECT Assigned_Group,
Assigned_Technician,
Date_Created
From "Support Center Ticket" where "Date_Created" > convert(varchar, getdate()-1, 101) + ' 00:00:00'
and division = 'Northern'



SSIS package "Package_test.dtsx" starting.

Information: 0x4004300A at Data Flow Task, DTS.Pipeline: Validation phase is beginning.

Information: 0x40043006 at Data Flow Task, DTS.Pipeline: Prepare for Execute phase is beginning.

Information: 0x40043007 at Data Flow Task, DTS.Pipeline: Pre-Execute phase is beginning.

Error: 0xC0047062 at Data Flow Task, DataReader Source [46]: System.Data.Odbc.OdbcException: ERROR [420] Driver]Unexpected extra token: (

at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()

at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper90 wrapper)

Error: 0xC004701A at Data Flow Task, DTS.Pipeline: component "DataReader Source" (46) failed the pre-execute phase and returned error code 0x80131937.

Information: 0x40043009 at Data Flow Task, DTS.Pipeline: Cleanup phase is beginning.

Information: 0x4004300B at Data Flow Task, DTS.Pipeline: "component "OLE DB Destination" (6856)" wrote 0 rows.

Task failed: Data Flow Task

Warning: 0x80019002 at Package_test: The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.

SSIS package "Package_test.dtsx" finished: Failure.

View 2 Replies View Related

Does It Matter If The Criteria Is In The FROM Or The WHERE?

May 6, 2008

I have a query that searches through a 4 million record table. The data is fed from the UNIX flat file systems so the data is not in optimal search format. So I created some views that massaged the data and then index them. I select and join the original table with the view, with NOEXPAND hint on the view. My question is this theory right: If I put the criteria in the FROM join part then it will make the join easier than if I put it in the where clause?

Example (any difference)
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON m.RecNumber LIKE '112%' AND u.ID = m.ID
versus
SELECT stuff1, stuff2 FROM UglyData u INNER JOIN MassageTable m ON u.ID = m.ID WHERE m.RecNumber LIKE '112%'


THANKS!!!

View 3 Replies View Related

FilterExpressions With Multiple Criteria

Sep 10, 2007

I am creating a .aspx page that links with Miscrosoft SQL Server 2005 Express. It includes a GridView control that displays all the table data on the page. You can then select a record from the control (currently by clicking an image button to the left of each record- is there any way of selecting the record by clicking anywhere on the row? How would that be done?) and it displays the data in a detailsview control below where the data can be changed etc.
 The data is like a phonebook (Name, Telephone number, and some other misc fields) and the user should be able to search by either name or number to filter out the records shown in the gridview control. I have two textboxes for this, and I started with the name text box and it works fine. i.e. with one filterparameter and one filterexpression. So that if you just enter 'Da' it filters out the records displaying only those whose name starts with 'Da'.
 I have experimented but have found no way of including filter expressions to use the number as a search. I added the second filter parameter (under sqldatasource control so that:
 <FilterParameters>
<asp:ControlParameter Name="DestinationName" ControlID="txtName" /><asp:ControlParameter Name="DestinationNumber" ControlID="txtNumber" />
</FilterParameters>
But I don't know what to do for the FilterExpressions. currently I just have:
FilterExpression="DestinationName LIKE '{0}%'"
i have tried using "DestinationName LIKE '{0}%' OR DestinationNumber LIKE '{0}%'" but it requires that both text boxes have data entered.
 
What I want is something that allows the user to enter either a name or number or both (all or part of so don't need to enter in full name/number) and it filters out the records accordingly. I.e. if you enterd 'Dav' and '079' it would bring back all the records who had a name starting with Dav and a number starting with 079. However if you enterd just 079 then it should just bring back all records with numbers starting 079 whatever their associated name.
 
Thanks

View 9 Replies View Related

Query Criteria Format

Feb 9, 2004

I have a text box that is used to submit stock symbols that are to be saved in a sql table. The symbols are to be separated by a space or a comma (I don't know which, yet). I want to retrieve the symbols later to be used in a query, but I don't know how to get the symbols in the proper string format for the query, eg

The symbols are stored in the tables as: A B C D
The query string criteria would look like: IN('A', 'B', 'C', 'D')

The IN('A', 'B', 'C', 'D') citeria would be the values in the @Symbol variable in this SPROC

SELECT a_Name_Symbol.Symbol, a_Financials.Revenue
FROM a_Financials INNER JOIN
a_Name_Symbol ON a_Financials.Symbol = a_Name_Symbol.Symbol
WHERE (a_Name_Symbol.Symbol @Symbol)
ORDER BY a_Name_Symbol.Symbol

Is there a slick (ie easy) way to change the contents entered in the text box (A B C D) into IN('A', 'B', 'C', 'D') ?

Thanks,

Paul

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved