CODEWITHSUNDEEP
sqloracle-databaseoracle10g
provençal le breton
provençal le breton

Reputation: 1438

Reorder a table for/before a case/when clause

I have a query, with some case When clause.

I use then , the function lead(or lag) to check other lines.

The problem is, I check it, in the order of the table, without order. So it will not be always in the good order in my result.

 CASE
          WHEN Lead(s.id_location, 1, NULL) OVER (ORDER BY s.id_location) = s.id_location THEN  'at the same address '
          ELSE '\par \tab ' ||s.dir || '\par \tab '|| s.address || '\par \tab salle ' || s.lib_ || '\par \tab '|| s.etage  
        END AS adresscomplete,

I got a date associated that can order by. But the problem is that the wrong line can have the "at the same address" text, because I do not choose the order it made the lead(or lag). So, This can be as result :

My direction, my address my salle .... at the same address or at the same address .... My direction, my address my salle

The second is obvisouly wrong

Can I choose, before going into my case, to order the lines by date, so it will always the second, that will be with "at the same address" text.

This should be

My direction, my address my salle first date .... at the same address second date and not

at the same address date .... My direction, my address my salle second date

Thank you.

Upvotes: 0

Views: 70

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 95052

That looks okay. Only make sure that you use the same order, when displaying your result records:

SELECT ...
  CASE 
    WHEN LAG(s.id_location) OVER (ORDER BY s.id_location) = s.id_location 
    THEN  'at the same address '
  ...
FROM ...
WHERE ...
ORDER BY s.id_location; -- <== This is necessary to show your results ordered

EDIT: Why is it LEAD? You would want LAG to see wether the previous line had this address already in order to display 'at the same address'.

EDIT: To have it ordered by your date (and by location within date) use the following statement. I truncate the date in order to remove a time part in case there is any.

SELECT ...
  CASE 
    WHEN LAG(s.id_location) OVER (ORDER BY TRUNC(s.thedate), s.id_location) = s.id_location 
    THEN  'at the same address '
  ...
FROM ...
WHERE ...
ORDER BY TRUNC(s.thedate), s.id_location; -- <== This is necessary to show your results ordered

Upvotes: 1

Related Questions