ChrisM@Work

Forum Replies Created

Viewing 15 posts - 5,626 through 5,640 (of 10,143 total)

RE: Query plan - largest cost

rgtft (9/17/2012)
ChrisM@Work (9/17/2012)
Grant Fritchey (9/17/2012)
ISNULL and COALESCE on columns in the where clause are going to hurt performance....
This should be close to a SARGable version of the query:
Chris M:
Your code...
^{“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

September 18, 2012 at 2:12 am

#1538941
RE: Unpivot Help

roryp 96873 (9/17/2012)
ChrisM@Work (9/17/2012)
Lynn Pettis (9/17/2012)
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx
When you've read through that, read this excellent alternative by Dwain Camps[/url].
But if you are really using SQL 2005, you're going to have...
^{“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

September 17, 2012 at 9:57 am

#1538692
RE: Unpivot Help

Lynn Pettis (9/17/2012)
Start here: http://msdn.microsoft.com/en-us/library/ms177410(v=sql.90).aspx
When you've read through that, read this excellent alternative by Dwain Camps[/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

September 17, 2012 at 9:42 am

#1538681
RE: Find records which are within the depth range of other records

oliver.morris (9/17/2012)
I changed the code to this to sort that change. Is there any problem with this?
-- identify rows with/without overlaps
;WITH SplitData AS (
SELECT a.*,
Parent = CASE WHEN EXISTS...
^{“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

September 17, 2012 at 9:31 am

#1538676
RE: Find records which are within the depth range of other records

This yields slightly different results to Mark's solution - I'm assuming that you want the comments from the middlemen in a group:
-- identify rows with/without overlaps
;WITH SplitData AS (
SELECT a.*,...
^{“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

September 17, 2012 at 8:44 am

#1538649
RE: Query plan - largest cost

Grant Fritchey (9/17/2012)
ISNULL and COALESCE on columns in the where clause are going to hurt performance....
This should be close to a SARGable version of the query:
SELECT DISTINCT COALESCE(f.FranchiseNumber, di.FranchiseNumber) 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

September 17, 2012 at 6:41 am

#1538564
RE: 'Arithmetic overflow error converting numeric to data type numeric'

ananda.murugesan (9/17/2012)
No, there is sql trace running for capture error results. I thought application side might be issue? pl. check error page here.
I'm unable to reproduce the error with 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

September 17, 2012 at 5:56 am

#1538537
RE: 'Arithmetic overflow error converting numeric to data type numeric'

ananda.murugesan (9/17/2012)
This data and execute statement working fine in SSMS.
whenever call application side working fine at first time, calling next time error throwing like "'Arithmetic overflow error converting numeric... "
Insert...
^{“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

September 17, 2012 at 4:15 am

#1538487
RE: 'Arithmetic overflow error converting numeric to data type numeric'

Jason-299789 (9/17/2012)
ChrisM@Work (9/17/2012)
ananda.murugesan (9/17/2012)
DDL statements...
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1003|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1004|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1005|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|1|4DA-TE-304A/B/C'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|2|30 Aug 2012-14645'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|3|JGDTA'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|4|FCC/PRU'
exec...
^{“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

September 17, 2012 at 3:56 am

#1538477
RE: 'Arithmetic overflow error converting numeric to data type numeric'

ananda.murugesan (9/17/2012)
DDL statements...
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1003|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1004|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_H','Tran_Code|Ref_No|Modify_By|Modify_Dt','1005|2012083014645|37100666|17 Sep 2012 12:25:45'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|1|4DA-TE-304A/B/C'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|2|30 Aug 2012-14645'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|3|JGDTA'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|4|FCC/PRU'
exec ChangeTable 'SIL_TRAN_D','Tran_Code|Ref_No|Rec_no|column_id|column_value','1003|2012083014645|1|5|553'
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

September 17, 2012 at 3:46 am

#1538471
RE: 'Arithmetic overflow error converting numeric to data type numeric'

ananda.murugesan (9/17/2012)
please find the attached sample data for header and detail table
...
I don't want to open an attachment, which isn't the best way to provide the information I asked for:...
^{“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

September 17, 2012 at 3:20 am

#1538457
RE: Need Help In Query .

abhas (9/17/2012)
Still not tried anything as busy on other task.
If u have any idea in the mind then pls share.
It's a simple assignment. What have you tried so far?
^{“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

September 17, 2012 at 2:47 am

#1538444
RE: 'Arithmetic overflow error converting numeric to data type numeric'

Please post the following:
1. Code for function dbo.GetDataType
2. Values for;
@Table varchar(50),
@FldNames varchar(8000),
@FldValues varchar(8000)
which cause the stored procedure to fail.
3. DDL...
^{“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

September 17, 2012 at 2:34 am

#1538442
RE: T-SQL Help Needed

ScottPletcher (9/14/2012)
ChrisM@Work (9/14/2012)
It's quite common for a product to "evolve" over time without its' stock code changing - it's rampant in the fashion world. Clients, agents, reps all know an...
^{“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

September 14, 2012 at 9:47 am

#1537984
RE: how to select active inventory

Andy Hyslop (9/14/2012)
Ninety minutes to beer o'clock and focus is slipping
Judging by my last post I think mine is out of the door and at the pub already!! 😉
Heh right...
^{“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

September 14, 2012 at 9:06 am

#1537925

Viewing 15 posts - 5,626 through 5,640 (of 10,143 total)