ChrisM@Work

Forum Replies Created

Viewing 15 posts - 4,666 through 4,680 (of 10,143 total)

RE: conversion of the varchar value overflowed an int column

I'm sure there's an easier (and safer) way of doing this.
ALTER PROCEDURE [dbo].[BS_GetSTNValue]
--DECLARE
@pFromStore int = 1001,
@pToStore int = 1004,
@pSTN VARCHAR(20) = 0 output
AS
--BEGIN
DECLARE
@count INT = 0,
@AWBNo VARCHAR(10)...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 7, 2013 at 3:30 am

#1612897
RE: conversion of the varchar value overflowed an int column

TOP (n) without ORDER BY can return different results between executions of the same query. TOP 1 effectively means "give me a row from the set matching the search criteria,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 7, 2013 at 1:46 am

#1612878
RE: conversion of the varchar value overflowed an int column

Four queries in your stored procedure use TOP (n) without ORDER BY. The results may be unpredictable.
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 7, 2013 at 1:20 am

#1612873
RE: How to sum in SQL removing varchar

niladri.primalink (5/3/2013)
Thank u all for your awesome output.
It works fine for me. But the same query when I am pasting in SP it shows me the error
SELECT file_id...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 5:12 am

#1612145
RE: How to sum in SQL removing varchar

ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
<<snip>>
Queries having WHERE clause connected by AND operators are evaluated from left to right in the order they are written.
What is the source for this statement? It's...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 4:03 am

#1612129
RE: How to sum in SQL removing varchar

IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
Queries having WHERE...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 3:59 am

#1612127
RE: How to sum in SQL removing varchar

IgorMi (5/3/2013)
ChrisM@Work (5/3/2013)
IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
Queries having WHERE clause connected...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 3:52 am

#1612123
RE: How to sum in SQL removing varchar

IgorMi (5/3/2013)
Hi
SELECT sum(CONVERT(float, ft.amount))
FROM filetransaction ft
WHERE ft.fileid <>332 and ISNUMERIC(ft.amount)=1
Check this also http://www.sqlservercentral.com/articles/IsNumeric/71512/
Regards
IgorMi
Which of those two filters will SQL Server apply first?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 3:20 am

#1612113
RE: Out of the two given queries, which will perform faster!!!

T.Ashish (5/2/2013)
Yes, I also feel second query should be slower. But both are working exactly same.
How long do they take to run?
What method are you using to measure the execution...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 3:09 am

#1612108
RE: How to sum in SQL removing varchar

SELECT
SUM(x.FloatAmount)
FROM filetransaction ft
CROSS APPLY (
SELECT FloatAmount = CASE
WHEN ISNUMERIC(ft.amount) = 1 THEN CAST(ft.amount AS FLOAT)
ELSE 0 END
) x
WHERE 1 = 1
NOTE: ISNUMERIC has limitations. Here's[/url]...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 3:07 am

#1612107
RE: Query for non-existent entries

a_ud (5/3/2013)
Thanks, I'm still getting my head around it, I'm going to try it right now.
I've never used a CROSS APPLY, but I guess this could be translated (done) also...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 2:57 am

#1612105
RE: Comparing 2 Tables, Joins with Specific Data

You have a column [ID] in the two skills tables. Is this [EmployeeID]? The table definition of users_hierarchy clashes with the later INSERT; which has the correct column name?
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 2:54 am

#1612104
RE: SUM CASE, COLUMN MINUS COLUMN

wolfkillj (5/2/2013)
Sean Lange (5/1/2013)
ChrisM@Work (5/1/2013)
Sean Lange (5/1/2013)
So what exactly is the issue here?
I would suggest you use the newer join constructs. With this query you don't even need a where...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 2:40 am

#1612099
RE: Are the posted questions getting worse?

Lynn Pettis (5/2/2013)
Okay, I have to ask without pointing to anything specific, do any of you think I have been harassing any of the OP's recently rather than helping them?
Absolutely...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 1:25 am

#1612092
RE: Are the posted questions getting worse?

L' Eomot Inversé (5/1/2013)
ChrisM@Work suggested that I should revisit the complex many-to-many relationship thread, so I've done so. I found it quite difficult to avoid calling the OP an idiot,...
^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

May 3, 2013 at 1:20 am

#1612090

Viewing 15 posts - 4,666 through 4,680 (of 10,143 total)