asked on

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

momi_sabag

your question is not clear
can you specify what is the problem and what you are looking to get?

bibi92

ASKER

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

Member_2_276102

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

bibi92

ASKER

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

ASKER CERTIFIED SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bibi92

ASKER

Hello, I will test it tomorrow. Thanks bibi

bibi92

ASKER

Thanks bibi