Jeff Moden

Forum Replies Created

Viewing 15 posts - 56,911 through 56,925 (of 59,067 total)

RE: unique column in 2 tables - interesting problem

My 2 cents... IDENTITY column on table "A" with trigger on table "A" that adds 1 to that column to store in table "B". Then, kill the person that designed...
--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)

February 14, 2007 at 11:26 am

#689784
RE: Simple query to select records older than 2 days

Maybe I misread and, if so, sorry...
If CMRGDT is an integer, I don't think that WHERE CONVERT(DATETIME,CMRGDT) > GETDATE() - 2 is going to work without an extra conversion like...
--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)

February 14, 2007 at 9:42 am

#689738
RE: DLL''''s function results into SQL server.

I'm not sure, Lowell, but have you tried converting the DLL to an "extended stored procedure"? Books Online tells how...
--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)

February 14, 2007 at 9:32 am

#689737
RE: Search function

Since I'm mostly a data troll, I don't know what language you are writing in, but I'll give it a shot even though this is Oracle... replace the "&" with...
--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)

February 14, 2007 at 9:30 am

#689736
RE: Refining my (simple) SQL queries

As a side bar... if your trans_date has time in it... don't use BETWEEN because it will ignore most of the end date. For that same reason, you shouldn't use...
--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)

February 14, 2007 at 9:22 am

#689733
RE: Refining my (simple) SQL queries

Just for grins... the following two items are functionally equivelent...
CASE
WHEN MONTH(ledger.trans_date) < 7 THEN MONTH(ledger.trans_date) + 6
WHEN MONTH(ledger.trans_date) > 6 THEN MONTH(ledger.trans_date) - 6
ELSE 13
END...
--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)

February 14, 2007 at 9:10 am

#689731
RE: Function to cast an integer to a varchar where 1 = A, 26 = Z, ...

A slightly different take on David's 2 character solution...
REPLACE(CHAR((Number/27)%27+64)+CHAR(Number%27+64),'@','0')
Just remember that both of those 2 character solutions "lose their mind" at a decimal value of 730... David's starts producing special characters...
--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)

February 14, 2007 at 7:37 am

#689703
RE: Function to cast an integer to a varchar where 1 = A, 26 = Z, ...

Heh... Thought I explained it just fine... Tim, the key for me was the value 1 being A, 26 being Z, and 52 being AZ... I should have said, "I don't think you want...
--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)

February 14, 2007 at 5:55 am

#689674
RE: Need script to UPDATE 1000 Rows then COMMIT, Iteratively

Ok... in the absence of a response about what the primary key might be...
First... no loops... think set based... loops just slow things down.
Second, I can only believe that you...
--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)

February 13, 2007 at 9:42 pm

#689627
RE: Function to cast an integer to a varchar where 1 = A, 26 = Z, ...

Hey there John, I think you'll like this...
First, your numbering is a bit off... if 1 is "A", and 26 is "Z", and 52 is "AZ", then 53 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)

February 13, 2007 at 9:04 pm

#689624
RE: Need script to UPDATE 1000 Rows then COMMIT, Iteratively

No matter which way you do it, we're going to need to know what the primary key for the table 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)

February 13, 2007 at 7:35 pm

#689616
RE: Deadlock assitance required

Guess I'll have to ask the obvious question... is either (or both) the SELECT and/or the INSERT wrapped in a BEGIN TRANSACTION/COMMIT pair?
--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)

February 13, 2007 at 7:24 pm

#689615
RE: Inserting into Remote Database

Is the ProfileID column an IDENTITY column or 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)

February 13, 2007 at 7:20 pm

#689614
RE: Unable to do Multiple select statements

Why not have Informix do an export to, say, a comma or tab delimited file and then DTS that bad boy in?
--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)

February 12, 2007 at 8:29 pm

#689359
RE: Simple query to select records older than 2 days

Anders is correct... the reason why you're getting the arithmetic overflow is because SQL Server looks at those types of numeric dates as the number of days since the 1st...
--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)

February 12, 2007 at 8:26 pm

#689358

Viewing 15 posts - 56,911 through 56,925 (of 59,067 total)