ChrisM@Work

Forum Replies Created

Viewing 15 posts - 9,361 through 9,375 (of 10,143 total)

RE: removing duplicates

Try using UNION on its own rather than UNION ALL, which ensures that duplicates are retained in the output.
^{“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

November 25, 2008 at 8:31 am

#903191
RE: Insert and update in one stored proc (inserting record twice)

Patrick Ige (11/25/2008)
Chris why do you reckon this
select COUNT(*)
from companies where
code=@code and openedy=@openedyr and openedmonth=@openedmont
would return 2 rows?
If i send the paramters 01,2007,12 i do get 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

November 25, 2008 at 5:36 am

#903041
RE: Insert and update in one stored proc (inserting record twice)

Patrick
What do you expect the following statement to do:
select
@company
from companies where
code=@code and openedy=@openedyr and openedmonth=@openedmont
Without testing it, I reckon it will return one column with the pre-assigned value 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

November 25, 2008 at 4:33 am

#903022
RE: Bypassing the keyword DISTINCT

eddy (11/21/2008)
Have another little question considering these same 2 tables also.
I one would say,
for each BASISREL ROW where BASISREL.ART_ID = 5 (example)
...
^{“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

November 21, 2008 at 1:49 am

#901489
RE: Bypassing the keyword DISTINCT

How many rows in DEELREL and BASISREL?
^{“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

November 20, 2008 at 9:34 am

#901131
RE: To change the identity count

Identity columns uniquely identify individual rows in a table and exist for the benefit of the database, not the business. It doesn't matter to the database if there are gaps...
^{“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

November 20, 2008 at 8:51 am

#901098
RE: Bypassing the keyword DISTINCT

How many rows are returned by this...
[font="Courier New"]SELECT DISTINCT
                      b.BRL_ID, b.OFF_ID, b.MRL_ID, b.PAR_ID_FIRMA, b.PAR_ID_TYPE, b.PAR_ID_DISC, b.BRL_REGDATUM, b.BRL_BEGDATUM, b.BRL_ENDDATUM,
                      b.BRL_REMOVED, b.BRL_REMOVED_LISA, b.BRL_BASISNR, b.ADR_ID_KLANT, b.ARG_ID, b.ART_ID, b.ADR_ID_LAADNR, b.ADR_ID_LOSNR,
                      b.PAR_ID_EENHEID, b.PAR_ID_MUNT, b.PLS_ID_LADEN, b.PLS_ID_LOSSEN,...
^{“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

November 20, 2008 at 8:33 am

#901090
RE: Find Closest Match Data

Hi
It works like this. For two equal strings, say @mcode= '0011246';
Split c.TheValues into chunks (tokens), and test each one to see if it appears anywhere in @mcode. The tokens in...
^{“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

November 20, 2008 at 7:47 am

#901060
RE: reverse the table

Hello
Here's an excellent article which describes various solutions to your problem.
http://www.sqlservercentral.com/articles/T-SQL/63681/
Cheers
ChrisM
^{“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

November 20, 2008 at 4:10 am

#900962
RE: Help with Money and Decimal datatype

karthikeyan (11/20/2008)
Jeff,
Sorry ! a little late here..
Heh... "Inputs are welcome!"
🙂
Actually this query is scheduled as night job...what it will do ...it will run on every night...
^{“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

November 20, 2008 at 2:40 am

#900931
RE: conditional if statements

yisaaacs (11/20/2008)
yip. got it to compile.
but i found a better way, as my previous post.
thanks guys
[font="Courier New"]SET @user-id = (SELECT [UserID] FROM [AppUsers] WHERE [Username] = @OldUsername)
SET @OldMerchant = (SELECT Merchant 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

November 20, 2008 at 2:35 am

#900928
RE: conditional if statements

yisaaacs (11/19/2008)
hi Seth, you code does not compile.
Msg 137, Level 15, State 2, Line 28
Must declare the scalar variable "@NewUserName".
Msg 137, Level 15, State 2, Line 41
Must declare the scalar...
^{“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

November 20, 2008 at 2:24 am

#900924
RE: Using MAX in a SELECT Statement

Hi Michael
SELECT TOP (@RowCount) won't run (or let you create a function/sp etc) as it stands because TOP n is part of the statement. You would have to set 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

November 20, 2008 at 2:21 am

#900923
RE: Getting oldest/newest record by date

Like this, you mean?
[font="Courier New"]SELECT TOP 1 vom100.vst_int_id
            ,vom107.chi_evt_dtm
            ,vom107.order_code_ext_id
            ,vom200.order_code_desc1
FROM      VOM107_CHILD_ORDER vom107 (nolock)
            ,VOM100_ORDER_HEADER vom100 (nolock)
            ,VOM200_ORDER_CODE vom200 (nolock)
WHERE    vom107.ord_int_id = vom100.ord_int_id
            AND vom107.order_code_int_id = vom200.order_code_int_id
            --sample internal id
            AND vom100.vst_int_id = 936738
            --3=Lab, 4=microbiology, 18=lab dept use, 19=histology/path
            --40=lab pathology...
^{“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

November 19, 2008 at 10:01 am

#900622
RE: Help writing function to get decimal from string

Garadin (11/18/2008)
Rogue Dead Guy Ale is actually my favorite beer... I almost said as much in the last post, but got busy and just submitted it. Two Hearted Ale...
^{“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

November 19, 2008 at 9:43 am

#900613

Viewing 15 posts - 9,361 through 9,375 (of 10,143 total)