Hello all, im currently having an

[talenx]

Hello all,
I’m currently having an issue with retrieving a unique record from a table that contains "almost duplicates" I mean the table contains valid data but each record is assigned a [Key] which is unique. Here is the table layout (I apologize for the length of the post.)

HSKey --unique key
HSCPMN, --company
HSDIVN, --division
HSVNDN, --vendor
HSPONB, --PO number
HSRVRN, --ref number
HSITMN, --item nbr
HSLNHS, --line nbr
The above fields combined make a unique record without the HSKey)
...
As well as some metric values (not important)

Ok here the thing I need to pull the FIRST UNIQUE record from the table without removing the HSKey field (I need it to refer to another table)

The data result look like this:
HSKey|HSCPMN|HSDIVN|HSVNDN|HSPONB|HSRVRN|HSITMN|HSLNHS
1--------1---------2---------90901----23468----87653----345323----1
2--------1---------4---------33456----99999----12345----324688----1
3--------1---------4---------33456----99999----12345----987688----2
4--------1---------4---------33456----99999----12345----987688----3
5--------1---------3---------23459----65432----34556----757644----1

You can have a record (PO) from a vendor for multiple items; the only thing that makes it unique is the "HSLNHS" the Line number the item was received. Unfortunately I can't use the "line number" field as a WHERE option because the record that contains the needed data it doesn't always have a line number of 1 applied to it ... though it is the FIRST record of the unique record set. Example

HSKey|HSCPMN|HSDIVN|HSVNDN|HSPONB|HSRVRN|HSITMN|HSLNHS
2--------1---------4---------33456----99999----12345----324688----1
3--------1---------4---------33456----99999----12345----987688----2
4--------1---------4---------33456----99999----12345----987688----3

If anybody has any suggestions please let me know...
Thanks
talenx

 

[Bobalooey]

Ok talenx, ready for this?

The only way I've ever been able to get this to work is to create a cursor from the unique values (minus the key and line number in your case), then sequentially fetch those cursor rows into memory variables and then select the "top 1 *" from my main table where the unique values match the memory variables. I tested this, it works like a charm. My stored procedure example assumes that your table is called PO. I also assumed that all fields were integer as you gave no field defintiions. Comment out the "set identity_insert" line if your PO table doesn't use identity as your primary unique key. Enjoy.

Create Procedure GetTopLineItems
AS
set nocount on
-- Create an empty scratch table matching PO structure 
select PO.* into #temp from PO where 1 = 2
set identity_insert #temp on

-- Create memory variables to house sequential data

DECLARE @hscpmn int,@hsdivn int,@hsvndn int,@hsponb int,@hsrvrn int,@hsitmn int

-- Create sequential cursor

DECLARE distinct_cursor CURSOR FOR
select distinct 
hscpmn,
hsdivn,
hsvndn,
hsponb,
hsrvrn,
hsitmn
from PO (nolock)

OPEN distinct_cursor


-- Perform the first fetch.
FETCH NEXT FROM distinct_cursor
INTO @hscpmn,@hsdivn,@hsvndn,@hsponb,@hsrvrn,@hsitmn

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert top 1 PO row into #temp table
insert into #temp
(
hskey,
hscpmn,
hsdivn,
hsvndn,
hsponb,
hsrvrn,
hsitmn,
hslnhs
)	
select 
top 1 * 
from PO (nolock)
where 
hscpmn = @hscpmn 
and 
hsdivn = @hsdivn 
and 
hsvndn = @hsvndn 
and 
hsponb = @hsponb 
and 
hsrvrn = @hsrvrn 
and 
hsitmn = @hsitmn

-- This is executed as long as the previous fetch succeeds.
   FETCH NEXT FROM distinct_cursor
   INTO @hscpmn,@hsdivn,@hsvndn,@hsponb,@hsrvrn,@hsitmn
END

CLOSE distinct_cursor
DEALLOCATE distinct_cursor

--now select final results

select * from #temp

--and delete the temporary scratch table

drop table #temp

 

[talenx]

Bobalooey, thanks for your quick response, I tried your to run with your example, unfortunately I received an error stating:

Server: Msg 213, Level 16, State 1, Line 37
Insert Error: Column name or number of supplied values does not match table definition.

I have made the following changes to the SQL

Create Procedure GetTopLineItems
AS
set nocount on
-- Create an empty scratch table matching PO structure
select Staging_tPODetail.* into POTemp from Staging_tPODetail where 1 = 2
set identity_insert POTemp on

-- Create memory variables to house sequential data

DECLARE @hscmpn char,@hsdivn char,@hsvndn char,@hsponb char,@hsrvnr char,@hsitmn char

-- Create sequential cursor

DECLARE distinct_cursor CURSOR FOR
select distinct
hscmpn,
hsdivn,
hsvndn,
hsponb,
hsrvnr,
hsitmn
from Staging_tPODetail (nolock)

OPEN distinct_cursor


-- Perform the first fetch.
FETCH NEXT FROM distinct_cursor
INTO @hscmpn,@hsdivn,@hsvndn,@hsponb,@hsrvnr,@hsitmn

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
--Insert top 1 PO row into POTemp table
insert into POTemp
(
hs_key,
hscmpn,
hsdivn,
hsvndn,
hsponb,
hsrvnr,
hsitmn,
hslnhs
)
select
top 1 *
from Staging_tPODetail (nolock)
where
hscmpn = @hscmpn
and
hsdivn = @hsdivn
and
hsvndn = @hsvndn
and
hsponb = @hsponb
and
hsrvnr = @hsrvnr
and
hsitmn = @hsitmn

-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM distinct_cursor
INTO @hscmpn,@hsdivn,@hsvndn,@hsponb,@hsrvnr,@hsitmn
END

CLOSE distinct_cursor
DEALLOCATE distinct_cursor

--now select final results

select * from POTemp

--and delete the temporary scratch table

--drop table POTemp
any idea on what maybe causing that error . i have found that if you don't set the identity feild up i will error out but i have confrim it is set.
again it looks like it would work, though it may need a little tweaking.
thanks agian,
talenx

 

[Rhys666]

You're problem is probably because select '*' selects all columns, not just the ones in the WHERE clause, and if I understood your previous posts, your source table has more columns that your TEMP table. I think that segment should read...
--Insert top 1 PO row into POTemp table
insert into POTemp
(
hs_key,
hscmpn,
hsdivn,
hsvndn,
hsponb,
hsrvnr,
hsitmn,
hslnhs
)
select
top 1
hs_key,
hscmpn,
hsdivn,
hsvndn,
hsponb,
hsrvnr,
hsitmn,
hslnhs
from Staging_tPODetail (nolock)
where
hscmpn = @hscmpn
and
hsdivn = @hsdivn
and
hsvndn = @hsvndn
and
hsponb = @hsponb
and
hsrvnr = @hsrvnr
and
hsitmn = @hsitmn