ChrisM@Work

Forum Replies Created

Viewing 15 posts - 3,886 through 3,900 (of 10,143 total)

RE: Nested CASE WHEN in SELECT

Sean Lange (12/16/2013)
Jeff Moden (12/16/2013)
ChrisM@Work (12/16/2013)
Jeff Moden (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing 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

December 17, 2013 at 1:42 am

#1673970
RE: select house_no order

;WITH MyAddresses (HouseNo) AS ( -- meagre sample data set
SELECT '3-4-53 . HYDERABAD' UNION ALL
SELECT '3-4-199/1.ASVV' UNION ALL
SELECT '3-4-vizag' UNION ALL
SELECT '3-4-22,nagar' UNION ALL
SELECT '3-4-45.old colony' UNION ALL
SELECT '3-4-66/99' UNION...
^{“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 16, 2013 at 9:42 am

#1673770
RE: Convert Columns to Row in SQL

jeganbenitto.francis (12/16/2013)
ChrisM@Work ,
Thanks for your reply. I am getting below Error
Ambiguous column name colEEEE .
Without a table name or column names to work with, I made up the names...
^{“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 16, 2013 at 7:42 am

#1673692
RE: Convert Columns to Row in SQL

SELECT ColAAAA, ColBBBB, ColCCCC, ColDDDD, ColEEEE
FROM Sometables
CROSS APPLY (
SELECT colEEEE UNION ALL
SELECT colFFFF UNION ALL
SELECT colGGGG UNION ALL
SELECT colHHHH
) x
^{“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 16, 2013 at 7:17 am

#1673674
RE: I have multiple identifier Could not be bound when I use ROW_NUMBER()

bkshn (12/15/2013)
Hello ChrisM@home
let me explain my question again.
I want to do paging with Row_Number on several Join Table.
My temp table fill by executing UserMidlist Procedure
I use code below
DECLARE @RowsPerPage...
^{“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 16, 2013 at 6:13 am

#1673649
RE: Select is Slow

yuvipoy (12/15/2013)
here i meant scroll is where condition..... for every scroll i will be giving 1000 records...
For the 1st time i will return 1000 records and next time 1001 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 16, 2013 at 2:09 am

#1673600
RE: Nested CASE WHEN in SELECT

Jeff Moden (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned,...
^{“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 16, 2013 at 2:06 am

#1673597
RE: Union Join between 3 SQL Queries

Greg Edwards-268690 (12/13/2013)
So this gets around the limitation that normally turns the outer join into an inner join.
LEFT OUTER JOIN dbo.Email m
ON m.EKey = e.EKey AND m.Next_of_Kin = 'true'
Mostly...
^{“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 13, 2013 at 9:59 am

#1673331
RE: Union Join between 3 SQL Queries

Here you are Greg, I'll leave you to draw your own conclusions.
-- Original query using views reads employee table 5 times, child tables twice
SELECT a.EKey, b.Emp_First, b.Emp_Last, c.Address, d.Phone, e.Email
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

December 13, 2013 at 9:07 am

#1673302
RE: Nested CASE WHEN in SELECT

Sean Lange (12/13/2013)
ChrisM@Work (12/13/2013)
Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values...
^{“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 13, 2013 at 8:19 am

#1673277
RE: Union Join between 3 SQL Queries

Greg Edwards-268690 (12/13/2013)
Chris -
I agree with a lot of your Why's. See this all the time.
If you could look at this, run the view for next of kin info, and...
^{“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 13, 2013 at 7:25 am

#1673252
RE: query Help

pietlinden (12/12/2013)
Your table is really structured like that? What happens if you get more software packages? Does the table structure change? IF y
I think storing information using this...
^{“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 13, 2013 at 6:27 am

#1673228
RE: what's the name for this type of table used in the following way?

If it's an ETL job then you'd probably call it a data load; incremental, if you're not truncating the target table beforehand.
^{“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 13, 2013 at 6:26 am

#1673226
RE: Nested CASE WHEN in SELECT

Sean Lange (12/12/2013)
Yes David is on the right path here. A case expression has not bearing on the number of rows returned, it merely affects the values for a single...
^{“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 13, 2013 at 6:06 am

#1673217
RE: Select is Slow

yuvipoy (12/12/2013)
Having a table with 100 columns and 1 million records.
Select Count(*) from Mytable;
COUNT(*)
----------
1001920
Time taken to return is 15 sec
select * from Mytable;
Time...
^{“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 13, 2013 at 2:17 am

#1673185

Viewing 15 posts - 3,886 through 3,900 (of 10,143 total)