ChrisM@Work

Forum Replies Created

Viewing 15 posts - 2,881 through 2,895 (of 10,143 total)

RE: Error: "On full left cross inner right using exception"

Koen Verbeeck (10/6/2014)
ChrisM@Work (10/6/2014)
Looks to me like an error in the query:
...
Good catch.
And that's why we use the SQL formatting people 😀
+1
Our little team's working with DB2. If you think...
^{“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

October 6, 2014 at 4:06 am

#1750252
RE: Error: "On full left cross inner right using exception"

Looks to me like an error in the query:
SELECT
A."CCUST" AS "CUSTOMER_NO",
A."CNME" AS "CUSTOMER_NM",
B."CTDESC" AS "CUSTOMER_GROUP_NM",
C."SNAME" AS "CUSTOMER_CAT15_DESC",
D."TMDESC" AS "TERMS DESCRIPTION",
E.CCCODE AS ZCC_CODE
FROM
ERPLXF."RCM" A
JOIN ERPLXF."RCT" B on B."CTCOMP"=A."CCOMP" 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

October 6, 2014 at 3:58 am

#1750249
RE: Select case statement

ZZartin (9/30/2014)
The basic idea would be something like,
CREATE TABLE #test_table (COL_ONE varchar(30), COL_TWO varchar(30), COL_THREE int)
...
-- Cursor version
DECLARE @sql varchar(max),
@col_three int
DECLARE temp_con CURSOR FOR SELECT DISTINCT COL_THREE FROM #test_table ORDER...
^{“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

October 3, 2014 at 9:12 am

#1750007
RE: Reverse recursion for some specific statistical calculation

;WITH SampleData AS (
SELECT * FROM (VALUES (1 ,0.11, 0),(2 ,0.45, 0.5),(3 ,0.85, 0.75)) d (id, A, B)
)
SELECT *, F = r3.A * ( 1 - r3.B * (...
^{“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

October 2, 2014 at 9:51 am

#1749725
RE: Trying to set variable to last day of current month, but seeing NULL instead of 28, 30 or 31?

Igor Micev (10/1/2014)
You can use this function
CREATE FUNCTION dbo.fnGetMonthDays(@myDate DATE) RETURNS INT
AS
BEGIN
DECLARE @isLeap INT = 0
IF (YEAR(@myDate) % 400 = 0 OR (YEAR(@myDate) % 4 = 0 AND YEAR(@myDate) %...
^{“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

October 1, 2014 at 4:19 am

#1749342
RE: Trying to set variable to last day of current month, but seeing NULL instead of 28, 30 or 31?

Mohit Dhiman (9/30/2014)
SELECT DATEADD(m,DATEDIFF(m,'19001231',GETDATE()),'19001231')
This will give you the month end date for the current month (which can be changed by replacing GETDATE() with whichever other month-date you want)..
And 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

October 1, 2014 at 4:12 am

#1749341
RE: An Alternative (Better?) Method to UNPIVOT (SQL Spackle)

taylor_benjamin (9/29/2014)
I have been demonstrating how to create high performance Pivot transformations of data using SQL or Linq. Those queries both use a Pivot Table to enhance the performance of...
^{“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 30, 2014 at 2:31 am

#1749071
RE: Row Size Limitation

The "data length" of a row is more complex than simply counting the data characters. Columns have a minimum length, and many of the columns in your table have 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 29, 2014 at 9:45 am

#1748863
RE: Row Size Limitation

arrjay (9/29/2014)
Thanks but I can't change what is already there. I can update the field I have referenced but a structural change would take too long to get deployed...
^{“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 29, 2014 at 5:40 am

#1748770
RE: Row Size Limitation

Koen Verbeeck (9/29/2014)
NVARCHAR(MAX) values are not stored inside the table but in LOB pages. However, a 24-byte pointer is still stored in the regular pages.
What's the Point of Using VARCHAR(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

September 29, 2014 at 5:17 am

#1748763
RE: query running 15hrs for 1lakh records

Here is your DB2 query reformatted somewhat for clarity. The two subqueries in the WHERE clause are most likely to hit performance due to the non-SARGable predicates. If DB2 has...
^{“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 29, 2014 at 3:59 am

#1748751
RE: Linked Server to AS400 Error Null Value

We're using OPENQUERY & linked server to run ad-hoc queries against a DB2 database. We discovered quite early on that DB2 (or possibly the driver) will balk at almost anything...
^{“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 29, 2014 at 2:09 am

#1748727
RE: Trying to set variable to last day of current month, but seeing NULL instead of 28, 30 or 31?

If you're looking for a simple and reliable means of obtaining the last date of the current month, try this:
SELECT DATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,GETDATE()),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

September 29, 2014 at 2:03 am

#1748726
RE: temporary table in a stored procedure

Like this?
IF OBJECT_ID('tempdb..#iscr','U') IS NOT NULL DROP TABLE ##iscr
SELECT * INTO #iscr FROM _Iscritto WHERE 0 = 1
IF @cod_fondo = '2100'
BEGIN
INSERT INTO #iscr (<<column list>>)
SELECT <<column list>> FROM _Iscritto...
^{“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 26, 2014 at 9:49 am

#1748553
RE: Order by numeric doesn't work

Shouldn't you be setting FldNumeric23764 to different values here:
UPDATE C14 SET
[FldNumeric23764]= case
when tID = 1 and Q.n <= 100 then 2
when tID = 2 and Q.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

September 26, 2014 at 9:38 am

#1748547

Viewing 15 posts - 2,881 through 2,895 (of 10,143 total)