Jeff Moden

Forum Replies Created

Viewing 15 posts - 5,911 through 5,925 (of 59,101 total)

Reply To: Build hierarchy multi-column table on data in 1 column based on string length

ScottPletcher wrote:
Jeff Moden wrote:
FL0XN0X wrote:
I was too enthousiastic. What I did was I only took the 7 character long records (WHERE LEN(column1)=7) and then applied the LEFT functions as indicatde above....
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 11:03 pm

#3820490
Reply To: Help getting last 2 entries from split string with variable number of splits

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
This has less overhead...
Just curious, Scott... how are you measuring "overhead" for this?
Actually, for this one, I just looked at the query plans. The other code is...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 10:54 pm

#3820478
Reply To: Help Transforming TSQL to MSSQL
ScottPletcher wrote:
Jeff Moden wrote:
Jonathan AC Roberts wrote:
Are you terminating the preceding statement with a semicolon?
You can start statements that begin with "WITH" with a semicolon:
;WITH ret AS(
Now, there's an interview...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
December 14, 2020 at 10:45 pm

#3820475
Reply To: Create Unique combination as Key out of N columns

You have some values that have leading zeros. Must the leading zeros be preserved? If so, then not all of the columns are actually INTs as you said. Let me...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 10:31 pm

#3820467
Reply To: Store Decimal values without Trailing Zeros

IT researcher wrote:
I am using SQL Server 2008 R2 version. Is there any method to store decimal value in SQL without extra trailing zeros(like Float data type format). I have created...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 10:12 pm

#3820463
Reply To: Build hierarchy multi-column table on data in 1 column based on string length

FL0XN0X wrote:
I was too enthousiastic. What I did was I only took the 7 character long records (WHERE LEN(column1)=7) and then applied the LEFT functions as indicatde above. But, apparently,...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 10:09 pm

#3820462
Reply To: Build hierarchy multi-column table on data in 1 column based on string length

FL0XN0X wrote:
@ Jeff Moden,
It concerns the hierchy as described in https://en.wikipedia.org/wiki/Anatomical_Therapeutic_Chemical_Classification_System
Now, this said, what I want to achieve is to have a Dimension which contains all hierarchies, and a...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 10:07 pm

#3820461
Reply To: Are the posted questions getting worse?

I guess I'm an odd ol' duck. I don't need or even want F2F interaction with humans unless it's on a pool table in my favorite bar and that's not...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 9:53 pm

#3820459
Reply To: Help getting last 2 entries from split string with variable number of splits

ScottPletcher wrote:
This has less overhead...
Just curious, Scott... how are you measuring "overhead" for this?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 4:45 pm

#3820334
Reply To: Build hierarchy multi-column table on data in 1 column based on string length

FL0XN0X wrote:
SELECT * FROM [Staging].[3NF_ATCCOLLECTION] ATC WHERE ATC.[ATC_KEY] LIKE '[a-z]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z]' OR ATC.[ATC_KEY] LIKE '[a-z][0-9][0-9][a-z][a-z][0-9][0-9]' ORDER BY ATC.[ATC_KEY]
This results...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 14, 2020 at 3:56 am

#3820134
Reply To: nested while

mtz676 wrote:
abridged version of what I need
I'd recommend sharing what the unabridged version is because it will be in even more of a need of speed and a While loop...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 13, 2020 at 5:39 am

#3819902
Reply To: comma issues in sql statement

ScottPletcher wrote:
Jeff Moden wrote:
ScottPletcher wrote:
The standard pattern uses 0 as the base, not 1 or -1 or some other "cute" trick.
No sir... There are a couple of "standards". The...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 13, 2020 at 5:01 am

#3819893
Reply To: Build hierarchy multi-column table on data in 1 column based on string length

FL0XN0X wrote:
Hi, I need to build a hierachy multi-column table based on data in 1 column where the length of the string determines in which column the string must be...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 13, 2020 at 4:56 am

#3819890
Reply To: Create Unique combination as Key out of N columns

ktflash wrote:
Ahoi,
i have a set of 7 int columns in a table.
My goal is im trying to create a unique key/integer for each possible combination of these 7 by merging...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 13, 2020 at 4:51 am

#3819889
Reply To: Creating indexes on a very large table takes over 5 hours

ScottPletcher wrote:
ORDER BY is never needed, in the sense that it's irrelevant to whether SQL uses minimal logging or not. Therefore, the code doesn't need it for "safety". And it's...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

December 12, 2020 at 9:29 pm

#3819835

Viewing 15 posts - 5,911 through 5,925 (of 59,101 total)