• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 245
  • Last Modified:

selecting data from table

CREATE PROCEDURE [spAS400JTPLOADMixLotSDB]
@CABBV char(6),
@REQDATTODAY datetime,
@REQDATPLUSSEVEN datetime,
@VIN char(17)
AS
Select DISTINCT PARTNO, CABBV, REQDAT, QTYRQ, ENGREV, RANNO, CPART, VIN#, DABBV, OEML, TRNTYP, ARVTM, REQTIM, PONO, RLSDTE, PKGREQ
From AS400.dbo.JTPLoad
Where REQDAT >= @REQDATTODAY AND REQDAT <= @REQDATPLUSSEVEN AND SUBSTRING(VIN#,5,2) = @VIN AND CPART LIKE '78500SDB%'
Order By RANNO DESC
GO

I have the above stored procedure. Here are part of the results I get from this stored procedure.

PARTNO                     RANNO            VIN#
78500SDB-C510           V11060502901      216148
78500SDB-C310             V11060502902      216148
78500SDB-C410            V11060502903      216148
78500SDB-C610           V21060502901      116148
78500SDB-C510             V21060502902      116148
78500SDB-C410            V21060502903      116148

The operator enterers 48 and based on that I pull the data. The problem I have is the result is not correct because they have to go be in two sets. The first set should be

78500SDB-C510           V11060502901      216148
78500SDB-C310             V11060502902      216148
78500SDB-C410            V11060502903      216148

and the second set should be..

78500SDB-C610           V21060502901      116148
78500SDB-C510             V21060502902      116148
78500SDB-C410            V21060502903      116148

How do I change the query to fix the problem? Probably in my VB.net program I should give the operator to select one. How do I do that?


0
shamif
Asked:
shamif
  • 2
1 Solution
 
HuyBDCommented:
If you have PK in table, try to select top statement

CREATE PROCEDURE [spAS400JTPLOADMixLotSDB]
@CABBV char(6),
@REQDATTODAY datetime,
@REQDATPLUSSEVEN datetime,
@VIN char(17),
@num int
AS
Select DISTINCT CABBV, REQDAT, QTYRQ, ENGREV, RANNO, CPART, VIN#, DABBV, OEML, TRNTYP, ARVTM, REQTIM, PONO, RLSDTE, PKGREQ,
(select Top 1 PARTNO from AS400.dbo.JTPLoad as t where t.pk=AS400.dbo.JTPLoad.pk order by PARTNO) as PARTNO
into tmp
From AS400.dbo.JTPLoad
Where REQDAT >= @REQDATTODAY AND REQDAT <= @REQDATPLUSSEVEN AND SUBSTRING(VIN#,5,2) = @VIN AND CPART LIKE '78500SDB%' AND PARTNO is not null
Order By RANNO DESC
if @num=1
begin
      select * from tmp
end
else
begin
      Select DISTINCT CABBV, REQDAT, QTYRQ, ENGREV, RANNO, CPART, VIN#, DABBV, OEML, TRNTYP, ARVTM, REQTIM, PONO, RLSDTE, PKGREQ,
      (select Top 1 PARTNO from AS400.dbo.JTPLoad as t where t.pk=AS400.dbo.JTPLoad.pk and t.PARTNO not in(select PARTNO from tmp) order by PARTNO) as PARTNO
      into tmp
      From AS400.dbo.JTPLoad
      Where REQDAT >= @REQDATTODAY AND REQDAT <= @REQDATPLUSSEVEN AND SUBSTRING(VIN#,5,2) = @VIN AND CPART LIKE '78500SDB%' AND PARTNO is not null
      Order By RANNO DESC            
end
GO

the @num specity value at 1 and other
1 to  get first resultset and other to get second
0
 
shamifAuthor Commented:
HuyBD,
The table doesn't have a primary key
0
 
HuyBDCommented:
If table have no PK, try to change codition in subquery to select records have same PARTNO, RANNO and you can add more codition if you want:

CREATE PROCEDURE [spAS400JTPLOADMixLotSDB]
@CABBV char(6),
@REQDATTODAY datetime,
@REQDATPLUSSEVEN datetime,
@VIN char(17),
@num int
AS
Select DISTINCT CABBV, REQDAT, QTYRQ, ENGREV, RANNO, CPART, VIN#, DABBV, OEML, TRNTYP, ARVTM, REQTIM, PONO, RLSDTE, PKGREQ,
(select Top 1 PARTNO from AS400.dbo.JTPLoad as t where t.PARTNO=AS400.dbo.JTPLoad.PARTNO and t.RANNO=AS400.dbo.JTPLoad.RANNO order by PARTNO) as PARTNO
into tmp
From AS400.dbo.JTPLoad
Where REQDAT >= @REQDATTODAY AND REQDAT <= @REQDATPLUSSEVEN AND SUBSTRING(VIN#,5,2) = @VIN AND CPART LIKE '78500SDB%' AND PARTNO is not null
Order By RANNO DESC
if @num=1
begin
     select * from tmp
end
else
begin
     Select DISTINCT CABBV, REQDAT, QTYRQ, ENGREV, RANNO, CPART, VIN#, DABBV, OEML, TRNTYP, ARVTM, REQTIM, PONO, RLSDTE, PKGREQ,
     (select Top 1 PARTNO from AS400.dbo.JTPLoad as t where t.PARTNO=AS400.dbo.JTPLoad.PARTNO and t.RANNO=AS400.dbo.JTPLoad.RANNO and t.PARTNO not in(select PARTNO from tmp) order by PARTNO) as PARTNO
     into tmp
     From AS400.dbo.JTPLoad
     Where REQDAT >= @REQDATTODAY AND REQDAT <= @REQDATPLUSSEVEN AND SUBSTRING(VIN#,5,2) = @VIN AND CPART LIKE '78500SDB%' AND PARTNO is not null
     Order By RANNO DESC          
end
GO
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now