I'm stumped on this.
My developers proc left to run all night consumes tons of cpu but does no
updates at all and I have to kill the connection in the morning.
The select in the cursor declaration returns 357 rows so not an outrageous
result set. When I run the select in Qry Analyser it runs in under 6 seconds
.
When I run the stored proc in QA it takes forever. Debug print statements
appear up until the initial fetch and then nothing.
Can a cursor loop indefinitely?
CREATE PROCEDURE dbo.stc_Insert_Instrument AS
set nocount on
declare
@.rc int -- returncode
, @.errmsg varchar(250)
, @.msg varchar(50)
select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
-- create temp tables
select
<Snip>
into #moodystaging
from moodystaging
select
<Snip>
into #spstaging
from spstaging
declare ins_cursor cursor read_only for
select
iss.IssuerId,
st.SECURITY_DES,
case isdate(st.maturity)
when 1 then st.maturity
else null
end as maturity,
case isdate(st.issue_dt)
when 1 then st.issue_dt
else null
end as issue_dt,
case isnumeric(st.cpn)
when 1 then st.cpn
else null
end as cpn,
cp.CouponTypeId,
st.CRNCY,
case isnumeric(st.AMT_ISSUED)
when 1 then st.AMT_ISSUED
else null
end as amt_issued,
case isnumeric(st.AMT_OUTSTANDING)
when 1 then st.AMT_OUTSTANDING
else null
end as amt_outstanding,
r.RatingId,
sprt.RatingId as spRatingId,
st.id_bb_unique,
st.id_isin,
st.id_cusip
from
staging st
left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
left join external e on st.id_bb_unique = e.externalid and
e.externaltype='BB'
left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
left join rating r on mt.rtg = r.rating and r.type = 'Moody'
left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
where
e.externalid is null
and iss.sourceid is not null
open ins_cursor
declare
@.issuerid int,
@.instrument_name varchar(50),
@.maturitydate datetime,
@.issuedate datetime,
@.coupon float(8),
@.coupontypeid int,
@.currency varchar(3),
@.amountissued float(8),
@.amountoutstanding float(8),
@.moodyratingid int,
@.spratingid int,
@.bb_id varchar(50),
@.isin varchar(50),
@.cusip varchar(50),
@.instrumentid int
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
print 'after first fetch'
while @.@.fetch_status = 0
begin
print 'processing'
insert into instrument (
issuerid,
instrumentname,
maturitydate,
issuedate,
coupon,
coupontypeid,
currency,
amountissued,
amountoutstanding,
moodyratingid,
spratingid)
values (
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into Instrument failed.'
goto errhandler
end
set @.instrumentid = @.@.identity
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'BB', @.bb_id)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for BB type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'ISIN', @.isin)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for ISIN type.'
goto errhandler
end
insert into external (instrumentid, externaltype, externalid) values
(@.instrumentid, 'Cusip', @.cusip)
-- check for errors
select @.rc = @.@.error
if @.rc <> 0
begin
select @.msg = 'Insert into External failed for Cusip type.'
goto errhandler
end
fetch next from ins_cursor into
@.issuerid,
@.instrument_name,
@.maturitydate,
@.issuedate,
@.coupon,
@.coupontypeid,
@.currency,
@.amountissued,
@.amountoutstanding,
@.moodyratingid,
@.spratingid,
@.bb_id,
@.isin,
@.cusip
end
close ins_cursor
deallocate ins_cursor
drop table #moodystaging
drop table #spstaging
return 0 -- success
errhandler:
raiserror ('%s %s',16,1,@.errmsg,@.msg)
if @.@.trancount > 0
rollback transaction
drop table #moodystaging
drop table #spstaging
return @.rc
GOMaybe one of the tables that you are updating is locked and the stored
procedure is waiting for the lock to free up?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6
seconds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>|||One thing is clear - this can be done without a cursor. Or have I missed
something?
What else is going on in there during the night?
ML
http://milambda.blogspot.com/|||The reason for the cursor was to extract the identity value from the
instrument table to enter it into the external table. (Sorry, I didn't
include table definitions)
I'd be very happy if there isanother way to achieve this and get rid of the
curse, I mean cursor!
There is nothing else going on overnight apart from backups.
"ML" wrote:
> One thing is clear - this can be done without a cursor. Or have I missed
> something?
> What else is going on in there during the night?
>
> ML
> --
> http://milambda.blogspot.com/|||Thanks Jim,
I don't think this is the case but I'll double check.
Progress seems to halt before then, right after the initial fetch.
Simon
"Jim Underwood" wrote:
> Maybe one of the tables that you are updating is locked and the stored
> procedure is waiting for the lock to free up?
> "Si" <Si@.discussions.microsoft.com> wrote in message
> news:4A962893-F4D1-4BDA-814E-9DB0E891EA6F@.microsoft.com...
> seconds.
>
>|||So the 'after first fetch'
is never reached?
"Si" <Si@.discussions.microsoft.com> wrote in message
news:E15851B6-2879-453D-8F01-C96375BF7A1C@.microsoft.com...
> Thanks Jim,
> I don't think this is the case but I'll double check.
> Progress seems to halt before then, right after the initial fetch.
> Simon
>
> "Jim Underwood" wrote:
>
no
outrageous
statements|||Si wrote:
> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of th
e
> curse, I mean cursor!
>
At least three possible solutions that don't need a cursor.
1. Create an insert trigger on the Instrument table to populate
External.
2. Use a table variable or temp table (SQL Server 2000):
DECLARE @.instrument TABLE ...;
INSERT INTO @.instrument (...)
SELECT ...
FROM staging ...;
INSERT INTO instrument (...)
SELECT ...
FROM @.instrument;
INSERT INTO external
(instrumentid, ...)
SELECT I.instrumentid, ...
FROM instrument AS T
LEFT JOIN @.instrument AS I
ON ... etc
3. Use a table variable and INSERT ... OUTPUT (SQL Server 2005):
DECLARE @.instrument (instrumentid INTEGER);
INSERT INTO instrument (...)
OUTPUT Inserted.instrumentid INTO @.instrument
SELECT ...
FROM staging ...;
INSERT INTO external
(instrumentid, ...)
SELECT instrumentid, ...
FROM @.instrument;
Hope this helps.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||curse (n) - an unnecessary cursor
I like that. :) Maybe it should be added:
http://www.webster.com/dictionary/curse
ML
http://milambda.blogspot.com/|||You don't need a cursor for a select.. insert..
insert into TableA( a, b, c )
select a, b, c from TableB
Meanwhile, the next time it seems to freeze, use the procedure sp_who2 to
determine if the process is blocked.
"Si" <Si@.discussions.microsoft.com> wrote in message
news:717A26A2-8862-49D0-A78D-44879CD2FA3B@.microsoft.com...
> The reason for the cursor was to extract the identity value from the
> instrument table to enter it into the external table. (Sorry, I didn't
> include table definitions)
> I'd be very happy if there isanother way to achieve this and get rid of
> the
> curse, I mean cursor!
>
> There is nothing else going on overnight apart from backups.
> "ML" wrote:
>|||Some re-thinking on the design and cursor no longer needed.
Thanks very much everyone for your time and replies.
Simon.
"Si" wrote:
> I'm stumped on this.
> My developers proc left to run all night consumes tons of cpu but does no
> updates at all and I have to kill the connection in the morning.
> The select in the cursor declaration returns 357 rows so not an outrageous
> result set. When I run the select in Qry Analyser it runs in under 6 secon
ds.
> When I run the stored proc in QA it takes forever. Debug print statements
> appear up until the initial fetch and then nothing.
> Can a cursor loop indefinitely?
>
>
> CREATE PROCEDURE dbo.stc_Insert_Instrument AS
> set nocount on
> declare
> @.rc int -- returncode
> , @.errmsg varchar(250)
> , @.msg varchar(50)
>
> select @.errmsg = 'Error in proc ' + object_name(@.@.procid) + ': '
> -- create temp tables
> select
> <Snip>
> into #moodystaging
> from moodystaging
>
> select
> <Snip>
> into #spstaging
> from spstaging
>
> declare ins_cursor cursor read_only for
> select
> iss.IssuerId,
> st.SECURITY_DES,
> case isdate(st.maturity)
> when 1 then st.maturity
> else null
> end as maturity,
> case isdate(st.issue_dt)
> when 1 then st.issue_dt
> else null
> end as issue_dt,
> case isnumeric(st.cpn)
> when 1 then st.cpn
> else null
> end as cpn,
> cp.CouponTypeId,
> st.CRNCY,
> case isnumeric(st.AMT_ISSUED)
> when 1 then st.AMT_ISSUED
> else null
> end as amt_issued,
> case isnumeric(st.AMT_OUTSTANDING)
> when 1 then st.AMT_OUTSTANDING
> else null
> end as amt_outstanding,
> r.RatingId,
> sprt.RatingId as spRatingId,
> st.id_bb_unique,
> st.id_isin,
> st.id_cusip
> from
> staging st
> left JOIN Issuer iss on st.ID_BB_COMPANY = iss.SourceId
> INNER JOIN CouponType cp on st.CPN_TYP = cp.CouponTypeName
> left join external e on st.id_bb_unique = e.externalid and
> e.externaltype='BB'
> left join #moodystaging mt on st.ID_BB_UNIQUE = mt.ID_BB_UNIQUE
> left join rating r on mt.rtg = r.rating and r.type = 'Moody'
> left join #spstaging spt on st.ID_BB_UNIQUE = spt.ID_BB_UNIQUE
> left join rating sprt on spt.rtg = sprt.rating and sprt.type = 'Sp'
> where
> e.externalid is null
> and iss.sourceid is not null
>
> open ins_cursor
> declare
> @.issuerid int,
> @.instrument_name varchar(50),
> @.maturitydate datetime,
> @.issuedate datetime,
> @.coupon float(8),
> @.coupontypeid int,
> @.currency varchar(3),
> @.amountissued float(8),
> @.amountoutstanding float(8),
> @.moodyratingid int,
> @.spratingid int,
> @.bb_id varchar(50),
> @.isin varchar(50),
> @.cusip varchar(50),
> @.instrumentid int
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> print 'after first fetch'
> while @.@.fetch_status = 0
> begin
> print 'processing'
> insert into instrument (
> issuerid,
> instrumentname,
> maturitydate,
> issuedate,
> coupon,
> coupontypeid,
> currency,
> amountissued,
> amountoutstanding,
> moodyratingid,
> spratingid)
> values (
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into Instrument failed.'
> goto errhandler
> end
> set @.instrumentid = @.@.identity
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'BB', @.bb_id)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for BB type.'
> goto errhandler
> end
>
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'ISIN', @.isin)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for ISIN type.'
> goto errhandler
> end
> insert into external (instrumentid, externaltype, externalid) values
> (@.instrumentid, 'Cusip', @.cusip)
> -- check for errors
> select @.rc = @.@.error
> if @.rc <> 0
> begin
> select @.msg = 'Insert into External failed for Cusip type.'
> goto errhandler
> end
> fetch next from ins_cursor into
> @.issuerid,
> @.instrument_name,
> @.maturitydate,
> @.issuedate,
> @.coupon,
> @.coupontypeid,
> @.currency,
> @.amountissued,
> @.amountoutstanding,
> @.moodyratingid,
> @.spratingid,
> @.bb_id,
> @.isin,
> @.cusip
> end
> close ins_cursor
> deallocate ins_cursor
> drop table #moodystaging
> drop table #spstaging
> return 0 -- success
> errhandler:
> raiserror ('%s %s',16,1,@.errmsg,@.msg)
> if @.@.trancount > 0
> rollback transaction
> drop table #moodystaging
> drop table #spstaging
> return @.rc
> GO
>
Showing posts with label stumped. Show all posts
Showing posts with label stumped. Show all posts
Wednesday, March 7, 2012
Really need help with this tough query
Hello all. What I want to do has left me stumped. I will try to articulate everything, but please reply if I have omitted some details.
My goal is to effectively choose the next sequencial contract number as follows: there are two tables, each with a contract_number field, whose format is as follows:
ANNNA-NNNN, where A represents a single alphabetic character, and each N represents an integer.
What I need done is to chose the largest value occupying the last four digits of a contract number.
An example would clarify:
table1
C600D-1234
C500E-1231
table2
A400F-1235
G300I-1222
My query should return: A400F-1235 (or just 1235) since the last four digits are greatest in this instance.
I have a feeling that I will use the functions CAST, MAX, and SUBSTRING, but this is just too complicate for me. To make matters worse, there will be some obsolete ill-formatted numbers that should be ignored in case they do not conform to the format.
Even the smallest bit of advice could prove priceless.
Thanks!To make this problem a whole lot more manageable, forget the issue about there being two table; I can take care of that with my server-side code. So basically, I just need the maximum integer value retrieved from a column containing both numbers and letters, as described in the previous post. Again, however, I need a way to cope with (ignore) poorly-formatted contract numbers.
Thanks!
Originally posted by ujohnc00
Hello all. What I want to do has left me stumped. I will try to articulate everything, but please reply if I have omitted some details.
My goal is to effectively choose the next sequencial contract number as follows: there are two tables, each with a contract_number field, whose format is as follows:
ANNNA-NNNN, where A represents a single alphabetic character, and each N represents an integer.
What I need done is to chose the largest value occupying the last four digits of a contract number.
An example would clarify:
table1
C600D-1234
C500E-1231
table2
A400F-1235
G300I-1222
My query should return: A400F-1235 (or just 1235) since the last four digits are greatest in this instance.
I have a feeling that I will use the functions CAST, MAX, and SUBSTRING, but this is just too complicate for me. To make matters worse, there will be some obsolete ill-formatted numbers that should be ignored in case they do not conform to the format.
Even the smallest bit of advice could prove priceless.
Thanks!|||Try this
create table foo (test varchar(30))
insert into foo select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'
select test from foo where right(test,4) in (select max (right(test,4)) from foo)
HTH|||I think you would get faster performance using the = operator rather than the "in" operator:
where right(test,4) = (select max (right(test,4)) from foo)
...but unless the query is very large the difference is probably negligable.
Another minor point; you have the option of running your select clause from the union query directly, without using a temporary table if you want.
blindman|||Oops, absolutely right, you want to use the '=' not 'in'
Thanks|||Originally posted by rhigdon
insert into foo select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'
HTH
hi, i am new to all these db stuff
but wat does the above statement do
i don't get the
select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'
part
thanx|||Just a way to insert rows instead of saying
insert into table select 'blah'
insert into table select 'blah'
insert into table select 'blah'
Using the union all statement allows you to write one insert that accomodates multiple inserts.
HTH|||Originally posted by rhigdon
Just a way to insert rows instead of saying
insert into table select 'blah'
insert into table select 'blah'
insert into table select 'blah'
Using the union all statement allows you to write one insert that accomodates multiple inserts.
HTH
arhh... thanx,
is there any good resources that you gurus out would recommend me to read, either books or online resources
cheers
j|||BOL - http://microsoft.com/sql/techinfo/productdoc/2000/books.asp
Inside SQL Server - Kalen Delaney
Data and databases - Joe Celko
Guru's guide to transact SQL - Ken Henderson
HTH|||thank you all for your replies.
i think your suggestions will work for my situation, with one exception: some of the sequence numbers are poorly-formatted, and in that case, the "max (right(test,4))" may cause an error, or produce undesriable results. basically, if "right(test,4)" is not an integer, i don't want to deal with that record. any ideas of how to accomplish this?
also, blindman wrote:
"Another minor point; you have the option of running your select clause from the union query directly, without using a temporary table if you want."
Can you show the sql code for this (I can't picture what you mean)?
Thank you infinitely!|||You can use the isnumeric function.
Not tested but something like this:
if
isnumeric(right(test,4)) = 1
then|||This illustrates how to use UNION to combine both tables into a subquery, for both your outer select and for determining the largest current value. Whether you use this, a temporary table, or a table variable is a matter of choice and the details of your project.
This also shows how to use the ISNUMERIC function to screen out non-numeric bad data.
select test
from (select * from table1 Union select * from table2) foo
where right(test,4) =
(select max(right(test, 4))
from (select * from table1 Union select * from table2) foo2
where ISNUMERIC(right(test, 4))=1)
blindman|||blindman and rhigdon...
thanks infinitely for helping me out. i believe i now have exactly what i want thanks to you.
one curiosity: when we write SELECT MAX(RIGHT(...)), why doesn't some sort of cast to an integer need to take place (or does sql server do this implicitly)?
take care.|||You can use the Max and Min aggregate functions on non-numeric data, which is then evaluated according the sort-order of the character set. For numeric data stored as text, there is no difference in the result as long as all values have the same number of digits.
Of course, you can't use statistical functions like Avg with non-numeric data!
blindman|||I bet you thought this thread was dead... never!
anyways, thanks for the continual help.
as a slight modfication to my original question, imagine now that the formatting is in one of the following formats (where L's represent letters, and N's represent integers):
L600-NNN-LLLL
LL600-NNN-LLLL
LLL600-NNN-LLLL
LLLL600-NNN-LLLL
the question remains the same: how can I find the largest NNN in a column whose contents usually follow the above formatting? how can i ignore those numbers that don't conform to the above formatting? the reason that this formatting poses a new problem for me is that i can't use the RIGHT or LEFT functions, and therefore don't know how to parse out the NNN values.
thank you in advance for your time.
cheers|||Consider using the LIKE operator, along with wildcards, to filter out bad data. For example:
Where [DataString] like '%-[0-9][0-9][0-9]-[a-z][a-z][a-z][a-z]'
(This is untested, but you get the idea...)
You are going to need to familiarize yourself with the character functions available in TSQL: RIGHT, LEFT, TRIM, SUBSTRING, CHARINDEX, for starters.
To get the substring for your example, we'll nest two functions. The inner-most peels off the leftmost 8 characters, and the outer function grabs the left three from the result:
left(right([DataString], 8), 3)
blindman|||Is this a test?
We are really delving into the Sea of Volitility here...
If you ALWAYS have the hyphens, try this on for size:
SELECT MAX(LEFT(RIGHT(test,LEN(test)-CHARINDEX('-',test)),CHARINDEX(RIGHT(test,LEN(test)-CHARINDEX('-',test))-1)) FROM (SELECT test FROM table1) UNION ALL (SELECT test FROM table2)
I think a better solution would be to scold the person who created the contract numbering scheme and take it away from them.|||blindman and bpdWork,
thanks again for all of your guidance. you are the reason that the internet is such a vast resource.
i ended up following your suggestions, blindman, and things are working great. i will surely look into those functions you mentioned. bpdWork, I may have used your SQL but I couldn't parse it mentally, and that would be a bad thing if I needed to modify it later on.
cheers!|||bpdWork, rest assured that I have scolded those who create the contract numbering scheme, but unfortunately I don't have the power to take it away from them.
I do have yet another question for this thread (that has been so helpful thus far. The question is the same as the first one I posted on this thread, with the only difference being that I can't use the RIGHT function, because now the RIGHT four characters might not be the sequence number. This is because an options "-TO##" suffix may be appended to the contract number, where ## is a two digit integer between 00 and 99.
I expect to use CHARINDEX or maybe the CASE conditional, but I am not sure.
If I am bugging you with seemingly simple questions that aren't very different from eachother, it is only because I don't yet have full comprehension of the tools needed to write these types of queries (but you are helping a lot).
Thanks again!
Originally posted by bpdWork
Is this a test?
We are really delving into the Sea of Volitility here...
If you ALWAYS have the hyphens, try this on for size:
SELECT MAX(LEFT(RIGHT(test,LEN(test)-CHARINDEX('-',test)),CHARINDEX(RIGHT(test,LEN(test)-CHARINDEX('-',test))-1)) FROM (SELECT test FROM table1) UNION ALL (SELECT test FROM table2)
I think a better solution would be to scold the person who created the contract numbering scheme and take it away from them.|||I think you should use user-defined functions for this. I would write two: one to call that returns an integer, and one to parse the contract number into parts (it will be called from the first).
The parsing function is useful in many cases if written well. Here's one that'll accept a huge VARCHAR and a delimiter such as a hyphen (defaults to comma), and returns a numbered table of elements:
CREATE FUNCTION [fn_parse_string] (@.data_string VARCHAR(8000), @.delimiter VARCHAR(3)=',')
RETURNS @.element_table TABLE (element_id INT, element VARCHAR(512)) AS
BEGIN
DECLARE @.temp_table TABLE (element_id INT NOT NULL IDENTITY(1,1), element VARCHAR(512))
DECLARE @.iPos INT, @.element VARCHAR(512)
WHILE (LEN(@.data_string)>0) AND (@.iPos<>0)
BEGIN
SET @.iPos = CHARINDEX(@.delimiter, @.data_string)
IF @.iPos > 0
SET @.element = LEFT(@.data_string, @.iPos - 1)
ELSE
SET @.element = @.data_string
SET @.data_string = SUBSTRING(@.data_string, @.iPos + 1, LEN(@.data_string) - @.iPos)
INSERT @.temp_table (element)
VALUES (SELECT LTRIM(RTRIM(@.element)))
END
INSERT @.element_table
SELECT element_id, element
FROM @.temp_table
RETURN
END
Now you will want the function that will return the integer from a supplied contract code.
CREATE FUNCTION [fn_get_contract_number] (@.contract_code VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.contract_number AS INT
SELECT @.contract_number = CAST(element AS INT)
FROM dbo.fn_parse_string(@.contract_code)
WHERE element_id=2 -- if this is the part that holds the number
RETURN @.contract_number
END
Ok, it could use some error handling, as it will puke if element number two is not in integer, but you can play with that bit. Anyway, you could then use this function to do something like:
SELECT MAX(dbo.fn_get_contract_number(contracts.contract_ code)) FROM contracts
to get the Max number.
-b|||BTW: it will be slow, but you will have more managable code, and you will be able to exercise some more control without dizzying Select statements. Just get bigger processors!|||bpdWork, what a response!
I really appreciate your help. Your solution certainly seems flexible!
I am trying to figure out if this solution can handle these examples correctly:
V600P-1324 (will work)
V600P-1653-TO12 (will work)
V600P(C)-SAS-1653 (won't work as I need the 1653, not the SAS)
V600P(C)-SAS-1651-TO21 (won't work as I need the 1651)
I sincerely apologize for the continual complications on this question. The most simple way to put it is that if the RIGHT four characters or ints, then that is the number I want. If the RIGHT four character are TO##, then the four numbers that come before the TO## are what I want. I am sure that I could modify your code somehow to get this to work.
Thanks.|||Sure, simply modify the fn_get_contract_number to iterate through and return the first element that isnumeric().
If ISNUMERIC(element)...|||hmmm... not sure if I understand.
in all of the above contract number examples, I will need to extract the four digit number out of the whole string. wouldn't "If ISNUMBERIC" incorrectly skip some of the records?
thanks|||CREATE FUNCTION [fn_get_contract_number] (@.contract_code VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.contract_number AS INT
SELECT @.contract_number = CAST(element AS INT)
FROM dbo.fn_parse_string(@.contract_code)
WHERE ISNUMERIC(element)=1 -- only returns numeric elements
RETURN @.contract_number
END|||wow; i actually took 15 minutes to fully read through all of your code, and i fully understand what's going on. I have never used functions in SQL Server, but I can see how they can be useful. Any tips on when to use Sql Server functions?
i have really learned a lot from you and this thread.
thanks!
My goal is to effectively choose the next sequencial contract number as follows: there are two tables, each with a contract_number field, whose format is as follows:
ANNNA-NNNN, where A represents a single alphabetic character, and each N represents an integer.
What I need done is to chose the largest value occupying the last four digits of a contract number.
An example would clarify:
table1
C600D-1234
C500E-1231
table2
A400F-1235
G300I-1222
My query should return: A400F-1235 (or just 1235) since the last four digits are greatest in this instance.
I have a feeling that I will use the functions CAST, MAX, and SUBSTRING, but this is just too complicate for me. To make matters worse, there will be some obsolete ill-formatted numbers that should be ignored in case they do not conform to the format.
Even the smallest bit of advice could prove priceless.
Thanks!To make this problem a whole lot more manageable, forget the issue about there being two table; I can take care of that with my server-side code. So basically, I just need the maximum integer value retrieved from a column containing both numbers and letters, as described in the previous post. Again, however, I need a way to cope with (ignore) poorly-formatted contract numbers.
Thanks!
Originally posted by ujohnc00
Hello all. What I want to do has left me stumped. I will try to articulate everything, but please reply if I have omitted some details.
My goal is to effectively choose the next sequencial contract number as follows: there are two tables, each with a contract_number field, whose format is as follows:
ANNNA-NNNN, where A represents a single alphabetic character, and each N represents an integer.
What I need done is to chose the largest value occupying the last four digits of a contract number.
An example would clarify:
table1
C600D-1234
C500E-1231
table2
A400F-1235
G300I-1222
My query should return: A400F-1235 (or just 1235) since the last four digits are greatest in this instance.
I have a feeling that I will use the functions CAST, MAX, and SUBSTRING, but this is just too complicate for me. To make matters worse, there will be some obsolete ill-formatted numbers that should be ignored in case they do not conform to the format.
Even the smallest bit of advice could prove priceless.
Thanks!|||Try this
create table foo (test varchar(30))
insert into foo select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'
select test from foo where right(test,4) in (select max (right(test,4)) from foo)
HTH|||I think you would get faster performance using the = operator rather than the "in" operator:
where right(test,4) = (select max (right(test,4)) from foo)
...but unless the query is very large the difference is probably negligable.
Another minor point; you have the option of running your select clause from the union query directly, without using a temporary table if you want.
blindman|||Oops, absolutely right, you want to use the '=' not 'in'
Thanks|||Originally posted by rhigdon
insert into foo select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'
HTH
hi, i am new to all these db stuff
but wat does the above statement do
i don't get the
select 'abcd-1234' union all select 'dfrg-1235' union all select 'adgf-9999'
part
thanx|||Just a way to insert rows instead of saying
insert into table select 'blah'
insert into table select 'blah'
insert into table select 'blah'
Using the union all statement allows you to write one insert that accomodates multiple inserts.
HTH|||Originally posted by rhigdon
Just a way to insert rows instead of saying
insert into table select 'blah'
insert into table select 'blah'
insert into table select 'blah'
Using the union all statement allows you to write one insert that accomodates multiple inserts.
HTH
arhh... thanx,
is there any good resources that you gurus out would recommend me to read, either books or online resources
cheers
j|||BOL - http://microsoft.com/sql/techinfo/productdoc/2000/books.asp
Inside SQL Server - Kalen Delaney
Data and databases - Joe Celko
Guru's guide to transact SQL - Ken Henderson
HTH|||thank you all for your replies.
i think your suggestions will work for my situation, with one exception: some of the sequence numbers are poorly-formatted, and in that case, the "max (right(test,4))" may cause an error, or produce undesriable results. basically, if "right(test,4)" is not an integer, i don't want to deal with that record. any ideas of how to accomplish this?
also, blindman wrote:
"Another minor point; you have the option of running your select clause from the union query directly, without using a temporary table if you want."
Can you show the sql code for this (I can't picture what you mean)?
Thank you infinitely!|||You can use the isnumeric function.
Not tested but something like this:
if
isnumeric(right(test,4)) = 1
then|||This illustrates how to use UNION to combine both tables into a subquery, for both your outer select and for determining the largest current value. Whether you use this, a temporary table, or a table variable is a matter of choice and the details of your project.
This also shows how to use the ISNUMERIC function to screen out non-numeric bad data.
select test
from (select * from table1 Union select * from table2) foo
where right(test,4) =
(select max(right(test, 4))
from (select * from table1 Union select * from table2) foo2
where ISNUMERIC(right(test, 4))=1)
blindman|||blindman and rhigdon...
thanks infinitely for helping me out. i believe i now have exactly what i want thanks to you.
one curiosity: when we write SELECT MAX(RIGHT(...)), why doesn't some sort of cast to an integer need to take place (or does sql server do this implicitly)?
take care.|||You can use the Max and Min aggregate functions on non-numeric data, which is then evaluated according the sort-order of the character set. For numeric data stored as text, there is no difference in the result as long as all values have the same number of digits.
Of course, you can't use statistical functions like Avg with non-numeric data!
blindman|||I bet you thought this thread was dead... never!
anyways, thanks for the continual help.
as a slight modfication to my original question, imagine now that the formatting is in one of the following formats (where L's represent letters, and N's represent integers):
L600-NNN-LLLL
LL600-NNN-LLLL
LLL600-NNN-LLLL
LLLL600-NNN-LLLL
the question remains the same: how can I find the largest NNN in a column whose contents usually follow the above formatting? how can i ignore those numbers that don't conform to the above formatting? the reason that this formatting poses a new problem for me is that i can't use the RIGHT or LEFT functions, and therefore don't know how to parse out the NNN values.
thank you in advance for your time.
cheers|||Consider using the LIKE operator, along with wildcards, to filter out bad data. For example:
Where [DataString] like '%-[0-9][0-9][0-9]-[a-z][a-z][a-z][a-z]'
(This is untested, but you get the idea...)
You are going to need to familiarize yourself with the character functions available in TSQL: RIGHT, LEFT, TRIM, SUBSTRING, CHARINDEX, for starters.
To get the substring for your example, we'll nest two functions. The inner-most peels off the leftmost 8 characters, and the outer function grabs the left three from the result:
left(right([DataString], 8), 3)
blindman|||Is this a test?
We are really delving into the Sea of Volitility here...
If you ALWAYS have the hyphens, try this on for size:
SELECT MAX(LEFT(RIGHT(test,LEN(test)-CHARINDEX('-',test)),CHARINDEX(RIGHT(test,LEN(test)-CHARINDEX('-',test))-1)) FROM (SELECT test FROM table1) UNION ALL (SELECT test FROM table2)
I think a better solution would be to scold the person who created the contract numbering scheme and take it away from them.|||blindman and bpdWork,
thanks again for all of your guidance. you are the reason that the internet is such a vast resource.
i ended up following your suggestions, blindman, and things are working great. i will surely look into those functions you mentioned. bpdWork, I may have used your SQL but I couldn't parse it mentally, and that would be a bad thing if I needed to modify it later on.
cheers!|||bpdWork, rest assured that I have scolded those who create the contract numbering scheme, but unfortunately I don't have the power to take it away from them.
I do have yet another question for this thread (that has been so helpful thus far. The question is the same as the first one I posted on this thread, with the only difference being that I can't use the RIGHT function, because now the RIGHT four characters might not be the sequence number. This is because an options "-TO##" suffix may be appended to the contract number, where ## is a two digit integer between 00 and 99.
I expect to use CHARINDEX or maybe the CASE conditional, but I am not sure.
If I am bugging you with seemingly simple questions that aren't very different from eachother, it is only because I don't yet have full comprehension of the tools needed to write these types of queries (but you are helping a lot).
Thanks again!
Originally posted by bpdWork
Is this a test?
We are really delving into the Sea of Volitility here...
If you ALWAYS have the hyphens, try this on for size:
SELECT MAX(LEFT(RIGHT(test,LEN(test)-CHARINDEX('-',test)),CHARINDEX(RIGHT(test,LEN(test)-CHARINDEX('-',test))-1)) FROM (SELECT test FROM table1) UNION ALL (SELECT test FROM table2)
I think a better solution would be to scold the person who created the contract numbering scheme and take it away from them.|||I think you should use user-defined functions for this. I would write two: one to call that returns an integer, and one to parse the contract number into parts (it will be called from the first).
The parsing function is useful in many cases if written well. Here's one that'll accept a huge VARCHAR and a delimiter such as a hyphen (defaults to comma), and returns a numbered table of elements:
CREATE FUNCTION [fn_parse_string] (@.data_string VARCHAR(8000), @.delimiter VARCHAR(3)=',')
RETURNS @.element_table TABLE (element_id INT, element VARCHAR(512)) AS
BEGIN
DECLARE @.temp_table TABLE (element_id INT NOT NULL IDENTITY(1,1), element VARCHAR(512))
DECLARE @.iPos INT, @.element VARCHAR(512)
WHILE (LEN(@.data_string)>0) AND (@.iPos<>0)
BEGIN
SET @.iPos = CHARINDEX(@.delimiter, @.data_string)
IF @.iPos > 0
SET @.element = LEFT(@.data_string, @.iPos - 1)
ELSE
SET @.element = @.data_string
SET @.data_string = SUBSTRING(@.data_string, @.iPos + 1, LEN(@.data_string) - @.iPos)
INSERT @.temp_table (element)
VALUES (SELECT LTRIM(RTRIM(@.element)))
END
INSERT @.element_table
SELECT element_id, element
FROM @.temp_table
RETURN
END
Now you will want the function that will return the integer from a supplied contract code.
CREATE FUNCTION [fn_get_contract_number] (@.contract_code VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.contract_number AS INT
SELECT @.contract_number = CAST(element AS INT)
FROM dbo.fn_parse_string(@.contract_code)
WHERE element_id=2 -- if this is the part that holds the number
RETURN @.contract_number
END
Ok, it could use some error handling, as it will puke if element number two is not in integer, but you can play with that bit. Anyway, you could then use this function to do something like:
SELECT MAX(dbo.fn_get_contract_number(contracts.contract_ code)) FROM contracts
to get the Max number.
-b|||BTW: it will be slow, but you will have more managable code, and you will be able to exercise some more control without dizzying Select statements. Just get bigger processors!|||bpdWork, what a response!
I really appreciate your help. Your solution certainly seems flexible!
I am trying to figure out if this solution can handle these examples correctly:
V600P-1324 (will work)
V600P-1653-TO12 (will work)
V600P(C)-SAS-1653 (won't work as I need the 1653, not the SAS)
V600P(C)-SAS-1651-TO21 (won't work as I need the 1651)
I sincerely apologize for the continual complications on this question. The most simple way to put it is that if the RIGHT four characters or ints, then that is the number I want. If the RIGHT four character are TO##, then the four numbers that come before the TO## are what I want. I am sure that I could modify your code somehow to get this to work.
Thanks.|||Sure, simply modify the fn_get_contract_number to iterate through and return the first element that isnumeric().
If ISNUMERIC(element)...|||hmmm... not sure if I understand.
in all of the above contract number examples, I will need to extract the four digit number out of the whole string. wouldn't "If ISNUMBERIC" incorrectly skip some of the records?
thanks|||CREATE FUNCTION [fn_get_contract_number] (@.contract_code VARCHAR(50))
RETURNS INT AS
BEGIN
DECLARE @.contract_number AS INT
SELECT @.contract_number = CAST(element AS INT)
FROM dbo.fn_parse_string(@.contract_code)
WHERE ISNUMERIC(element)=1 -- only returns numeric elements
RETURN @.contract_number
END|||wow; i actually took 15 minutes to fully read through all of your code, and i fully understand what's going on. I have never used functions in SQL Server, but I can see how they can be useful. Any tips on when to use Sql Server functions?
i have really learned a lot from you and this thread.
thanks!
Subscribe to:
Posts (Atom)