Home » SQL & PL/SQL » SQL & PL/SQL » Help with sql using analytical functions.
Help with sql using analytical functions. [message #677151] Thu, 22 August 2019 19:45 Go to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
Hi ,

I have a sql related question

I am writing a sql to check if a column has same value in multiple rows and if yes I have to use an filter condition if not do nothing and pass the rows. I am using analytical function rank and case statements to achieve the above.

Below is the code:
with t1 as 
(SELECT 
mnt,
case when rank() over (partition by ltrim(rtrim(mnt)) order by ltrim(rtrim(mnt)) asc) >1 then 'Y' else 'N'  end as flag,
rank() over (partition by mnt order by mnt) as rn,
dense_rank() over (partition by mnt order by mnt) as drn,
FROM tablename1),
t2 as (
SELECT
mnt, 
rel,
lif,
lts,
lokez
FROM tablename2
WHERE lts <> "" 
AND PARTITIONTIME = TIMESTAMP(CURRENT_DATE())
)
,t3 as 
(SELECT 
lif,
lifn, 
lts, 
par FROM tablename3)  

,t4 as (SELECT rcv FROM tablename4
WHERE mes = 'PRO')
select * from (
SELECT t1.mnt as mnt,
       t2.rel as zel,
       t2.lokez as ZLOEKZ,
       t4.rcv as Zrcv
       FROM t1
left join t2
on replace(t1.mnt, '00000000', '') = REPLACE(t2.mnt, '00000000', '') AND t1.lif = t2.lif and t2.lts <> "" 
and 
case when t1.flag = 'Y' and  t2.rel ='X' then 1
     when (t1.flag ='N' and t2.rel=t2.rel) or (t1.flag ='N' and t2.rel is null)  then 1
     when t1.flag = 'Y' and  t2.rel <>'X' then 2
     else 3
     end = 1
left join t3  ON t1.lif = t3.lif 
AND t2.lts = t3.lts
AND t3.par = 'BA'
left join t4 on t4.rcv = t3.lifn
and  t2.lokez is null 
) where ZLOEKZ is null 
order by mnt 


as you can see I am checking if there are more than one value in a group for mnt column i have to filter and pass only the record with the following condition rel='X' if not i.e if the mnt value is unique in the table then just pass the row no filter condition needs to be applied.

to achieve the above I have first used rank function to create a flag
case when rank() over (partition by ltrim(rtrim(mnt)) order by ltrim(rtrim(mnt)) asc) >1 then 'Y' else 'N'  end as flag

then in the filter condition I have applied filter based on the flag
case when t1.flag = 'Y' and  t2.rel ='X' then 1
     when (t1.flag ='N' and t2.rel=t2.rel) or (t1.flag ='N' and t2.rel is null)  then 1
     when t1.flag = 'Y' and  t2.rel <>'X' then 2
     else 3
     end = 1

but for some reason the filter is not getting applied so i checked using rank and dense rank if the functions are working as i intended and below is the sample output


mnt     flag     rn     drn     rel     lokez     rcv
100      N       1      1       X       abc       123
100      N       1      1      null     xyz       123
100      N       1      1      null     def       234

but since mnt column has value 100 three times only the row with rel = X should pass and remaining both rows should not be seen also the rank and dense rank function are 1 for all three rows I am not able to understand what I am doing wrong, even the flag is getting set wrong.

any advise appreciated.

note:I do not have access to data from home so tomorrow i will manually get few rows and paste the create and insert sql here in the mean time please glance through my code and point me if I am doing something wrong which is obvious.


Thanks for the time.

Regards,
RamKumar
Re: Help with sql using analytical functions. [message #677153 is a reply to message #677151] Fri, 23 August 2019 00:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66654
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

From your previous topic:

Michel Cadot wrote on Thu, 10 August 2017 10:25

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Also, we are still waiting for your feedback in your previous topics.


Re: Help with sql using analytical functions. [message #677163 is a reply to message #677153] Tue, 27 August 2019 19:32 Go to previous messageGo to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
Hi I used count() instead of rank and my goal got achieved Thanks all.
Re: Help with sql using analytical functions. [message #677164 is a reply to message #677153] Tue, 27 August 2019 19:34 Go to previous messageGo to next message
ramkumar10
Messages: 19
Registered: March 2017
Junior Member
Michel Cadot wrote on Fri, 23 August 2019 00:58

From your previous topic:

Michel Cadot wrote on Thu, 10 August 2017 10:25

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also, we are still waiting for your feedback in your previous topics.


Hi,

Which one are you talking about if it is about hash question I asked then there is no resoluttion I could find or that has worked for me.

Regarding sample data yes as I mentioned I did not have the data for me to post at that moment later on count() function worked for me so i have not posted any thing yet.

Also I have been away for a couple of days.

Thanks,
Ram.
Re: Help with sql using analytical functions. [message #677165 is a reply to message #677164] Wed, 28 August 2019 00:33 Go to previous message
Michel Cadot
Messages: 66654
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
Which one are you talking about

help with sql grouping

Also:

OraFAQ Forum Guide
#12. If you found an answer yourself, post it. That way we know the issue is resolved and we might learn from it.
Previous Topic: Compare two tables to find new/modified/removed records
Next Topic: help with sql grouping
Goto Forum:
  


Current Time: Wed Oct 23 21:54:50 CDT 2019