ChrisM@Work

Forum Replies Created

Viewing 15 posts - 8,371 through 8,385 (of 10,143 total)

RE: If we do not know exactly which column to join to, can we use an or clause?

Of course, it's fine!
WHERE (Row1 IS NOT NULL OR Row2 IS NOT NULL) .. or whatever.
But don't you want to know what the problem is, with an OR in...
^{“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

December 9, 2009 at 9:06 am

#1089948
RE: If we do not know exactly which column to join to, can we use an or clause?

namrata.dhanawade-1143388 (12/9/2009)
Well, the OR does work. So I am going to use this -
select RowID
from dbo.Table1 a
inner join dbo.Table2 b on (a.RowID = b.RowID1 and b.TableID1 = 30) 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

December 9, 2009 at 8:57 am

#1089938
RE: If we do not know exactly which column to join to, can we use an or clause?

namrata.dhanawade-1143388 (12/9/2009)
And I dont want to check for NULLS in the where clause
Neither do I, it's so untidy.
^{“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

December 9, 2009 at 8:43 am

#1089921
RE: Ages and Age Ranges

Ryan Keast (12/9/2009)
[/code]
Now getting the following message -
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Age'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid 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

December 9, 2009 at 8:30 am

#1089898
RE: If we do not know exactly which column to join to, can we use an or clause?

Other than those pesky NULLS in the last row of your output where table B has no matches to table A, does the query do what you want it to...
^{“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

December 9, 2009 at 7:58 am

#1089861
RE: How to remove strings from Integer valued Column

<<while importing data>>
You could avoid a huge headache by importing your data into a staging table first, then pouring it into your real table. The staging table can have any...
^{“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

December 9, 2009 at 7:08 am

#1089817
RE: Ages and Age Ranges

The new column [Age range] has the new column [Age] as an input. It can't. You can either replace references to Age with the entire expression, or use an "onion"...
^{“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

December 9, 2009 at 6:58 am

#1089806
RE: update query error

charu.verma (12/9/2009)
I am writing the following update query which is giving me an error saying more than 1 records returned by the subquery
update
p_audit set j_num = (select c.new_j_num from tbl1...
^{“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

December 9, 2009 at 5:24 am

#1089752
RE: If we do not know exactly which column to join to, can we use an or clause?

Left join twice to the same table:
SELECT a.*,
ISNULL(b1.TableID1, b2.TableID2) AS TableID,
ISNULL(b1.RowID1, b2.RowID2) AS RowID
FROM Table1 a
LEFT JOIN Table2 b1 ON b1.TableID1 = a.TableID AND b1.RowID1 = a.RowID
LEFT...
^{“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

December 9, 2009 at 4:37 am

#1089743
RE: Comparing and extracting data in one table

SELECT ClientNo, ContractNo, InstallmentNo, Amount, EBTDate, [Status]
FROM MyTable m
INNER JOIN (
SELECT clientno, Contractno, ebtdate
FROM MyTable
GROUP BY clientno, Contractno, ebtdate
HAVING COUNT(*) > 1) d
ON d.clientno = m.clientno
AND d.ContractNo = m.ContractNo...
^{“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

December 9, 2009 at 3:32 am

#1089718
RE: To cursor, or not?

Top work, Dom.
A CTE is like a derived table defined outside the main body of the query, with the advantage that you can refer to it multiple times in the...
^{“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

December 9, 2009 at 2:11 am

#1089684
RE: To cursor, or not?

DECLARE @FileNM VARCHAR(70)
SET @FileNM = 'Audio Visual Enterprise Centre (Sidney Str) CYP0076 Floor Plan'
;WITH Numbers AS (SELECT (N10 + N1) + 1 AS n
...
^{“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

December 8, 2009 at 9:25 am

#1089328
RE: To cursor, or not?

Here you go...
-- This is a crude numbers table and is not part of the solution
;WITH Numbers AS (SELECT (N10 + N1) + 1 AS n
FROM (SELECT CAST(0 AS...
^{“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

December 8, 2009 at 8:45 am

#1089291
RE: To cursor, or not?

Hi
Is that...
space - [word] - space - "floor" - space - "plan"
where [word] = 4 numerics '####', 5 numerics '#####' or 'CYP####'
Or to put it another way, 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

December 8, 2009 at 8:14 am

#1089263
RE: looking for help to get rid of my cursor

davidandrews13 (12/8/2009)
Chris Morris-439714 (12/8/2009)
Hi David
This implies that you can only update one column, of one row, per statement - is this correct or am I missing the point somewhere?
broadly...
^{“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

December 8, 2009 at 7:31 am

#1089226

Viewing 15 posts - 8,371 through 8,385 (of 10,143 total)