ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,421 through 9,435 (of 10,143 total)

RE: Help writing function to get decimal from string

Jeff Moden (11/14/2008)
Try the following...
SELECT COUNT(*) FROM Master.dbo.SysColumns
If it doesn't work, there are one of two problems... either you don't have the privs to read from the master database (very...
^{“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

November 17, 2008 at 3:09 am

#899215
RE: Problem with SQL SP Where - using IF or Case

You don't really need dynamic SQL for this. Try the following as your WHERE clause:
WHERE M.DateTransactionDue >= '01/01/1900' AND M.DateTransactionDue <= @AllocationEndDate) AND M.TransactionTypeID = 1
AND M.SalesOrderID = CASE WHEN...
^{“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

November 14, 2008 at 9:08 am

#898689
RE: Case in a join statement

Or ISNULL:
CREATE TABLE #Orders (orderid INT, shipto VARCHAR(3), locationid VARCHAR(3))
INSERT INTO #Orders (orderid, shipto, locationid)
SELECT 1, 'acf', 'fcd' UNION ALL
SELECT 2, NULL, 'acf' UNION ALL
SELECT 3, 'dre', 'acf'
CREATE TABLE...
^{“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

November 14, 2008 at 9:01 am

#898684
RE: Help writing function to get decimal from string

So what happens if there are several pairs of brackets, some with numbers in?
This picks the string between the first pair of brackets and grabs the numbers from it, but...
^{“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

November 14, 2008 at 8:37 am

#898656
RE: Help writing function to get decimal from string

Dooza (11/14/2008)
I prefer Chris's solution, it returns the decimal just like I needed 😀
Seth's is almost identical identical apart from the datatype of the return value. He did what you...
^{“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

November 14, 2008 at 8:01 am

#898617
RE: Help writing function to get decimal from string

Garadin, that's uncanny! I was working on this earlier and came up with the following:
CREATE FUNCTION [dbo].[uftGetNumbersInBrackets]
(
@String VARCHAR(50)
)
RETURNS DECIMAL (10,2)
AS
BEGIN
DECLARE @NewString VARCHAR(50)
SET...
^{“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

November 14, 2008 at 7:44 am

#898593
RE: Left Outer Join (+ Group By) Mystery

asaacks (11/13/2008)
Sorry about that. I have added the aliases now in the first post. I don't want to place any restrictions in the ON clause, because I want to count...
^{“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

November 13, 2008 at 4:14 am

#897970
RE: Need help optimizing long running query

A LEFT JOIN is often faster than a NOT IN...
[font="Courier New"]SELECT p.PartNum,
                p.PartDescription,
                b.BinNum,
                b.OnHandQty,
                c.Cost,
                b.OnHandQty * c.Cost AS ExtCost
FROM @Part p
INNER JOIN @Cost c ON p.PartNum=c.PartNum
INNER JOIN @Bin b ON...
^{“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

November 12, 2008 at 9:48 am

#897623
RE: CTE's and IF Statements

meichner (11/10/2008)
I am trying to learn how to use CTE's. Unless I am mistaken it seems as if the statement following the CTE is an If statement, the CTE...
^{“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

November 10, 2008 at 7:25 am

#896298
RE: stored procedure is not working

Johann Montfort (11/10/2008)
working excellently now
but in the where clause why just
WHERE p.Deleted = 0
If you have branches.deleted = 0 in the WHERE clause, then your output will have exactly...
^{“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

November 10, 2008 at 4:57 am

#896207
RE: stored procedure is not working

Hi Johann
Your problem was almost certainly caused by including the deleted filters in your where clause. Put them into the join conditions instead:
[font="Courier New"]SELECT TOP (100) PERCENT
ISNULL(p.productID, 0)...
^{“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

November 10, 2008 at 4:38 am

#896196
RE: Problem washing a list with another list

Hello
Try using proper join syntax, it will help to understand what's going on and will also give you a better insight into your data:
-- this will return all rows from...
^{“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

November 10, 2008 at 3:40 am

#896175
RE: using varchar variable to query a table

smunson (11/6/2008)
Chris,
You might want to make mention that your function relies on a "tally table" called "Numbers", as the OP here may or may not be familiar with that or...
^{“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

November 6, 2008 at 9:39 am

#895024
RE: using varchar variable to query a table

Use a function to return the list as a table variable:
[font="Courier New"]CREATE FUNCTION [dbo].[uftSplitString]
(
@String VARCHAR(8000),
@Delimiter VARCHAR(255)
)
RETURNS
@Results TABLE
(
SeqNo INT IDENTITY(1, 1),
Item VARCHAR(8000)...
^{“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

November 6, 2008 at 9:17 am

#894998
RE: using varchar variable to query a table

Try this...
EXEC (@cSQL)
^{“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

November 5, 2008 at 8:50 am

#894310

Viewing 15 posts - 9,421 through 9,435 (of 10,143 total)