Modify tricky query - add selection

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Modify tricky query - add selection

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-09-30 : 13:01:46

Have following query that is working fine:

SET @SQLSTRING = 'SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO, SHDOCO ORDER BY SHUPMJ desc, SHTDAY desc) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL, 
''select     
           shkcoo,     
           shdoco,
           shsfxo, 
           shdcto,
           shmcu,
           shan8,
           shshan,
           shpa8,
           case when shtrdj > 0 then date(digits(decimal(shtrdj + 1900000,7,0))) else Null end as shtrdj,
           case when shpddj > 0 then date(digits(decimal(shpddj + 1900000,7,0))) else Null end as shpddj,
           shvr01,
           shptc,
           shtxa1,
           tataxa,
           shexr1,
           drdl01,
           shaft,
           jbname,
           jbflda,
           jbfldb,
           shurcd,
           abac02,
           shupmj,
           shtday,
           case when shupmj > 0 then date(digits(decimal(shupmj + 1900000,7,0))) else Null end as shumpj_datetime,
           case when substring(shtday,1,2) < 25 
             then concat(rtrim(char(substring(shtday,1,2))),CONCAT('''':'''',CONCAT(rtrim(char(substring(shtday,3,2))),Concat('''':'''',(rtrim(char(substring(shtday,5,2))))))))
           else
             concat(rtrim(char(substring(shtday,1,1))),CONCAT('''':'''',CONCAT(rtrim(char(substring(shtday,2,2))),Concat('''':'''',(rtrim(char(substring(shtday,4,2))))))))
           end as shtday_time
from       VGISOEDTA.F42019 as f1
left join  VGIPRDDTA.F0101 as f3 on f3.aban8 = f1.shan8
left join (select distinct wajob, wasite 
           from VGIWRQLIB.WRQASI) 
            as f2 on f2.wajob = f1.shvr01
left join  VGITFRLIB.F5984 as f4 on f4.jbsite = f2.wasite and f4.jbcode = f2.wajob
left join  VGIPRDDTA.F4008 as f5 on f5.tatxa1 = f1.shtxa1 and f5.taitm = 0 and f5.taefdj > ''''' + @JdeToDay + '''''
left join  vgiprdcom.f0005 as f6 on ltrim(f6.drky) = f1.shexr1 and f1.shexr1 <> '''' '''' and drsy = ''''00''''and drrt = ''''EX''''
where      concat(shupmj,shtday) >= ''''' + @JdeTimeStampFrom + ''''' and
           concat(shupmj,shtday) <= ''''' + @JdeTimeStampTo + ''''' 
order by   shkcoo,     
           shdoco ''))t WHERE Seq = 1'

Now I need to modify the query to include an additional selection. I am not sure how to do this or if it is possible.

In table F42019 there could be multiple records for same company and order number (shkcoo and shdoco). In the same table there is a line number field shsfxo that always starts with 001 and is incremented to 002, 003 etc. This is a varchar(3) field. I need to always select the line with highest value. Is this possible with my query? Thank you so much.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:09:27

but in above query you'll get only one record per shkcoo and shdoco combination. didnt understand how you will get multiples?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-09-30 : 13:13:36

Agreed.

Isn't your ROW_NUMBER() over (...) as Seq with WHERE Seq=1 already limiting your results to just one row per (SHKCOO, SHDOCO) based on this sequence: (ORDER BY SHUPMJ desc, SHTDAY desc). Do you want to modify that logic to (ORDER BY shsfxo) ?

Be One with the Optimizer
TG

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-09-30 : 13:26:02

Yes, I do just need one row, correct, and this is what I am getting. But I need the row with highest shsfxo value (now I am getting always rows with '001' when there are rows with '002' and '003' values)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:29:53

then it should be

ROW_NUMBER() OVER (PARTITION BY SHKCOO, SHDOCO ORDER BY shsfxo*1 desc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-09-30 : 13:45:00

Changed as follows:

SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO, SHDOCO ORDER BY shsfxo*1 desc) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL,

Result

Comp   Order    shsfxo   Ship Date Date  

00010	944	001	2011-09-10 00:00:00.000

In the table there are:

Comp       Order     Ship Date           shsfxo

000010       944     2011-9-10            001 
000010       944     2011-9-12            002

Not getting row with highest shsfxo value. Please note that field shsfxo is a char(3).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:54:27

show the where part of query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 13:55:06

try this too
ROW_NUMBER() OVER (PARTITION BY SHKCOO, SHDOCO ORDER BY CAST(shsfxo AS int) desc)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-09-30 : 14:04:18

Tried:

SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO, SHDOCO ORDER BY cast(shsfxo as int) desc) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL,

rendered same row as previously (with shsfxo value = '001')

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-30 : 14:30:27

you didnt show as where clause still

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-09-30 : 14:45:01

Complete query:

SET @SQLSTRING = 'SELECT *
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY SHKCOO, SHDOCO ORDER BY cast(shsfxo as int) desc) AS Seq FROM OPENQUERY( AS400SRV_MSDASQL, 
''select     
           shkcoo,     
           shdoco,
           shsfxo, 
           shdcto,
           shmcu,
           shan8,
           shshan,
           shpa8,
           case when shtrdj > 0 then date(digits(decimal(shtrdj + 1900000,7,0))) else Null end as shtrdj,
           case when shpddj > 0 then date(digits(decimal(shpddj + 1900000,7,0))) else Null end as shpddj,
           shvr01,
           shptc,
           shtxa1,
           tataxa,
           shexr1,
           drdl01,
           shaft,
           jbname,
           jbflda,
           jbfldb,
           shurcd,
           abac02,
           shupmj,
           shtday,
           case when shupmj > 0 then date(digits(decimal(shupmj + 1900000,7,0))) else Null end as shumpj_datetime,
           case when substring(shtday,1,2) < 25 
             then concat(rtrim(char(substring(shtday,1,2))),CONCAT('''':'''',CONCAT(rtrim(char(substring(shtday,3,2))),Concat('''':'''',(rtrim(char(substring(shtday,5,2))))))))
           else
             concat(rtrim(char(substring(shtday,1,1))),CONCAT('''':'''',CONCAT(rtrim(char(substring(shtday,2,2))),Concat('''':'''',(rtrim(char(substring(shtday,4,2))))))))
           end as shtday_time
from       VGISOEDTA.F42019 as f1
left join  VGIPRDDTA.F0101 as f3 on f3.aban8 = f1.shan8
left join (select distinct wajob, wasite 
           from VGIWRQLIB.WRQASI) 
            as f2 on f2.wajob = f1.shvr01
left join  VGITFRLIB.F5984 as f4 on f4.jbsite = f2.wasite and f4.jbcode = f2.wajob
left join  VGIPRDDTA.F4008 as f5 on f5.tatxa1 = f1.shtxa1 and f5.taitm = 0 and f5.taefdj > ''''' + @JdeToDay + '''''
left join  vgiprdcom.f0005 as f6 on ltrim(f6.drky) = f1.shexr1 and f1.shexr1 <> '''' '''' and drsy = ''''00''''and drrt = ''''EX''''
where      concat(shupmj,shtday) >= ''''' + @JdeTimeStampFrom + ''''' and
           concat(shupmj,shtday) <= ''''' + @JdeTimeStampTo + ''''' 
order by   shkcoo,     
           shdoco ''))t WHERE Seq = 1'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources