How can I modify this request

Hello,

When I remove the clause on companies (TST_SIS_W_FAN050P1. RSTE < 50000 now TST_SIS_W_FAN050P1. RSTE > 50999), the request can return several lines with codes companies(TST_SIS_W_FAN050P1. RSTE) different.
 The principle is then the following one, for all the fields, if there are several lines, it is necessary to get back the value of the line respecting the filter:
 TST_SIS_W_FAN050P1. RSTE < 50000 now TST_SIS_W_FAN050P1. RSTE > 50999
 If there is not, we take the value max of the remaining lines (and thus of whom(which) TST_SIS_W_FAN050P1. RSTE costs 50xxx)


SELECT DISTINCT
  cast((case when ((TST_SIS_W_FAN050P1.TCENTR IS NULL ) OR (TST_SIS_W_FAN050P1.TCENTR = ''))
    then '999999'
    else TST_SIS_W_FAN050P1.TCENTR
    end)
    as varchar(6)) RTY_CD_TIERS_ANA,
  case  when (SAS_W_AG_EXPLOIT_REGION.RON_CD_REGION = '11') 
    then '' 
    else 
      case 	when ((TST_SIS_W_FAN052P1.TREP IS  NULL ) OR (TST_SIS_W_FAN052P1.TREP = ''))
      then 'N/A'
      else TST_SIS_W_FAN052P1.TREP
    end end RTE_CD_COM, 
  digits(TST_SIS_W_FAN050P1.TSIREN) || digits( TST_SIS_W_FAN050P1.TSIRET) RTE_NO_SIRET,
  digits(TST_SIS_W_FAN050P1.TSIREN) RTE_NO_SIREN,
  TST_SIS_W_FACC04F1.AT4SA  AT4SA, 
  TST_SIS_W_FACC04F1.AT4TIE AT4TIE,
  coalesce(TST_SIS_W_FACC04F1.AT4TYT, ' ') AT4TYT
FROM 
  ODS_FACC04F1 TST_SIS_W_FACC04F1,
  ag_exploit_region SAS_W_AG_EXPLOIT_REGION,
  ODS_FAN050P1 TST_SIS_W_FAN050P1 
  LEFT OUTER JOIN ODS_FAN052P1 TST_SIS_W_FAN052P1
    ON (TST_SIS_W_FAN052P1.RSTE = TST_SIS_W_FAN050P1.RSTE
    AND TST_SIS_W_FAN052P1.RAUX = TST_SIS_W_FAN050P1.RAUX
    AND TST_SIS_W_FAN052P1.RSEQ = TST_SIS_W_FAN050P1.RSEQ
    AND TST_SIS_W_FAN052P1.REGION = TST_SIS_W_FAN050P1.REGION
    AND TST_SIS_W_FAN052P1.TKLEPS = 'C3')
where TST_SIS_W_FAN050P1.RSEQ = TST_SIS_W_FACC04F1.AT4SEQ
  and TST_SIS_W_FAN050P1.RAUX = TST_SIS_W_FACC04F1.AT4AUX
  and	TST_SIS_W_FAN050P1.REGION = TST_SIS_W_FACC04F1.REGION
  and TST_SIS_W_FAN050P1.RAUX IS NOT NULL
  and TST_SIS_W_FAN050P1.RSEQ <> 'PE'
  and TST_SIS_W_FAN050P1.TNOM <> '' 
  and (TST_SIS_W_FAN050P1.RSTE < 50000 or TST_SIS_W_FAN050P1.RSTE > 50999 )
  and digits(TST_SIS_W_FACC04F1.AT4SA) = SAS_W_AG_EXPLOIT_REGION.ROA_CD_AG_EXPLOIT
  and  digits(TST_SIS_W_FAN050P1.RSTE)  = lpad(SAS_W_AG_EXPLOIT_REGION.ROS_CD_SOC_CPTABLE,5,'0') 
  AND TST_SIS_W_FAN050P1.FLG_CHARGE_ODS = 'O'
  AND TST_SIS_W_FAN050P1.FLG_CHARGE_DWH = 'N'
  AND TST_SIS_W_FACC04F1.FLG_CHARGE_ODS = 'O'
  AND TST_SIS_W_FACC04F1.FLG_CHARGE_DWH = 'N'
  
 

Open in new window


Thanks

bibi
bibi92Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

momi_sabagCommented:
your question is not clear
can you specify what is the problem and what you are looking to get?
0
bibi92Author Commented:
I want to modify the request for having these conditions :

I have to remove the clause TST_SIS_W_FAN050P1. RSTE < 50000 now TST_SIS_W_FAN050P1. RSTE > 50999)
But when I do it  the request can return several lines with codes companies(TST_SIS_W_FAN050P1. RSTE) different.
if there are several lines, it is necessary to get back the value of the line respecting the condition:
 TST_SIS_W_FAN050P1. RSTE < 50000 now TST_SIS_W_FAN050P1. RSTE > 50999
 If there is not, we take the value max of the remaining lines : (TST_SIS_W_FAN050P1. RSTE costs 50xxx)

Thanks

bibi
0
tliottaCommented:
Let me see if I have this straight.

When you take out that WHERE clause, you expect two possibilities.

1. if there are b,... -- You expect a single one of those lines to satisfy the WHERE clause that you removed and you then want your query to return just that line.

2. But If there is not -- You want the MAX() value of all lines that do not satisfy the WHERE clause that you took out.

Or something like that.

For the If there is not part, what are you expecting not to be? Are you expecting there will not be several lines at all? Or are you expecting that there will not be several lines with codes companies(TST_SIS_W_FAN050P1. RSTE) different? Or are you expecting that there will not be just a single line that matches the WHERE clause that you took out? Or are you expecting something completely different...?

Tom
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

bibi92Author Commented:
For the if there is not part , I have to take the value max of the remaining lines : (TST_SIS_W_FAN050P1. RSTE costs 50xxx)

Thanks

bibi
0
LowfatspreadCommented:
like this ?

you may need to adjust the partition by clause to include more of your columns....

which version of db2/udb do you have?

select  RTY_CD_TIERS_ANA,
   RTE_CD_COM, 
  RTE_NO_SIRET,
  RTE_NO_SIREN,
   AT4SA, 
   AT4TIE,
   AT4TYT
from (
SELECT 
  cast((case when ((TST_SIS_W_FAN050P1.TCENTR IS NULL ) OR (TST_SIS_W_FAN050P1.TCENTR = ''))
    then '999999'
    else TST_SIS_W_FAN050P1.TCENTR
    end)
    as varchar(6)) RTY_CD_TIERS_ANA,
  case  when (SAS_W_AG_EXPLOIT_REGION.RON_CD_REGION = '11') 
    then '' 
    else 
      case 	when ((TST_SIS_W_FAN052P1.TREP IS  NULL ) OR (TST_SIS_W_FAN052P1.TREP = ''))
      then 'N/A'
      else TST_SIS_W_FAN052P1.TREP
    end end RTE_CD_COM, 
  digits(TST_SIS_W_FAN050P1.TSIREN) || digits( TST_SIS_W_FAN050P1.TSIRET) RTE_NO_SIRET,
  digits(TST_SIS_W_FAN050P1.TSIREN) RTE_NO_SIREN,
  TST_SIS_W_FACC04F1.AT4SA  AT4SA, 
  TST_SIS_W_FACC04F1.AT4TIE AT4TIE,
  coalesce(TST_SIS_W_FACC04F1.AT4TYT, ' ') AT4TYT
,row_number() over (partition by TST_SIS_W_FAN050P1.TCENTR 
    order by case when (TST_SIS_W_FAN050P1.RSTE < 50000 or TST_SIS_W_FAN050P1.RSTE > 50999 ) then 1 else 2 end
            ,TST_SIS_W_FAN050P1.RSTE desc) as rn
FROM 
  ODS_FACC04F1 TST_SIS_W_FACC04F1,
  ag_exploit_region SAS_W_AG_EXPLOIT_REGION,
  ODS_FAN050P1 TST_SIS_W_FAN050P1 
  LEFT OUTER JOIN ODS_FAN052P1 TST_SIS_W_FAN052P1
    ON (TST_SIS_W_FAN052P1.RSTE = TST_SIS_W_FAN050P1.RSTE
    AND TST_SIS_W_FAN052P1.RAUX = TST_SIS_W_FAN050P1.RAUX
    AND TST_SIS_W_FAN052P1.RSEQ = TST_SIS_W_FAN050P1.RSEQ
    AND TST_SIS_W_FAN052P1.REGION = TST_SIS_W_FAN050P1.REGION
    AND TST_SIS_W_FAN052P1.TKLEPS = 'C3')
where TST_SIS_W_FAN050P1.RSEQ = TST_SIS_W_FACC04F1.AT4SEQ
  and TST_SIS_W_FAN050P1.RAUX = TST_SIS_W_FACC04F1.AT4AUX
  and	TST_SIS_W_FAN050P1.REGION = TST_SIS_W_FACC04F1.REGION
  and TST_SIS_W_FAN050P1.RAUX IS NOT NULL
  and TST_SIS_W_FAN050P1.RSEQ <> 'PE'
  and TST_SIS_W_FAN050P1.TNOM <> '' 
  and digits(TST_SIS_W_FACC04F1.AT4SA) = SAS_W_AG_EXPLOIT_REGION.ROA_CD_AG_EXPLOIT
  and  digits(TST_SIS_W_FAN050P1.RSTE)  = lpad(SAS_W_AG_EXPLOIT_REGION.ROS_CD_SOC_CPTABLE,5,'0') 
  AND TST_SIS_W_FAN050P1.FLG_CHARGE_ODS = 'O'
  AND TST_SIS_W_FAN050P1.FLG_CHARGE_DWH = 'N'
  AND TST_SIS_W_FACC04F1.FLG_CHARGE_ODS = 'O'
  AND TST_SIS_W_FACC04F1.FLG_CHARGE_DWH = 'N'
) as x
where rn=1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bibi92Author Commented:
Hello, I will test it tomorrow. Thanks bibi
0
bibi92Author Commented:
Thanks bibi
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.