Advanced SQL Query
Question
Application Type
Reactive

Will someone be kind enough to help me look at this

I am trying to find manager(hrro) or next level manager in a specific costcentre(kostl) where the employee report to have grade equal to M4

The result is very strange see below;

Thank you in advance

Hi Jerah,

Can you share your StmEmployee entity (or at least type of hrro and hrao) to better understand?

Regards,

Khuong

HI Jerah,

What is [hrao] column and can you try to remove the union and check with hrro condition and hrao condition separately .I think that can give you idea which query is giving you strange results.

Best Regards

Devendra

If i remove the union i will get only M4 which is correct but beside check for hrro i also need to check if next level manager exist with grade M4

Hi Jerah,

are [hrro] and [hrao] booleans?

if so, you can simplify that query:

Select {StmEmployee}.* 

From {StmEmployee} 

Where {StmEmployee}.[kost1]=@kost1 and 

             {StmEmployee}.[grade] = 'M4' and

             ({StmEmployee}.[hrro] = 1 or {StmEmployee}.[hrao] = 1)

My apology please see the data sample below ;

sapnokostlgradehrro (manager whom  sapno report to)hrao (next level manager whom sapno report to)
30000383145001           E63000012330000201
30000201145001           M33000040130000501
30000123145001          M43000020130000401
30000401145001           M53000050130000601
30000501145001           M63000060130000601
30000601145001            G3000070130000701

Hi Jerah,

According to your info, I think you need to replace {stmEmployee}.[hrro] and {stmEmployee}.[hrao] (the one after "and") by {stmEmployee}.[sapno]. 

So it's something like Select * from {stmEmployee} where {stmEmployee}.[kostl] = @kostl and  {stmEmployee}.[sapno] in (....).

Regards,

Khuong

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.