0

EDIT:: Using the new Query provided by Thorsten Kettner solved my original problem.

Pokemon Master1
  • 11
  • 7

2 Answers2

0

In your first query you have

max(DISTINCT AGENT_ID)

The DISTINCT is superfluous, because MAX(DISTINCT value) and MAX(ALL value) is the same of course, but most of all, this expression has no explicit name! (Oracle will create one on-the-fly; probably something like MAX_DISTINCT_AGENT_ID). Make this

max(AGENT_ID) as AGENT_ID

in order to access it later by that name.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Thank you, now my program isn't crashing anymore, but it won't find the teams in my program either? My original code is in my original post edited. – Pokemon Master1 Aug 15 '18 at 10:39
  • Are there `AGENT_INITIALS` with a length less than five characters and the applied allocation range in your table? What does the first query return? – Thorsten Kettner Aug 15 '18 at 10:42
  • The very first Query worked fine, but showed an outdated department for an employee, instead of his newest/active department. So i made the Query you see here, which started crashing my program, and now your code fix is making my program run but not showing employees. in my agent tree view. – Pokemon Master1 Aug 15 '18 at 10:52
0

This is not directly an answer to what you are asking, but maybe you want to ask something else :-)

If all you want is the latest entry per agent, then use ROW_NUMBER to rank your columns from newest to oldest per agent and only keep those rows ranked "newest":

SELECT *
FROM
(
  SELECT
    aa.*,
    ROW_NUMBER() OVER (PARTITION BY agent_id ORDER BY allocation_start DESC) AS rn
  FROM ks_drift.v_agent_allocation aa
)
WHERE rn = 1;
Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • That looks exactly as what i need, can you insert it into the Query 2 code snippet? Then i'll test if it Works. – Pokemon Master1 Aug 15 '18 at 11:10
  • I tried making it in C# but failed as I am not sure how to write it. – Pokemon Master1 Aug 15 '18 at 11:19
  • string sql = "SELECT * " + "FROM " + "(" + "SELECT aa.*, ROW_NUMBER()" + "OVER (PARTITION BY agent_id ORDER BY allocation_start DESC) AS rn FROM KS_DRIFT.V_AGENT_ALLOCATION aa)" + ")" + "WHERE rn = 1"; – Pokemon Master1 Aug 15 '18 at 11:19
  • By the way: I would always write and test queries with an appropriate tool (e.g. SQL Developer), before putting them in some program code. – Thorsten Kettner Aug 15 '18 at 11:26
  • Thank you for your help its now fixed. – Pokemon Master1 Aug 15 '18 at 11:56
  • I now get a NullReferenceException when trying to choose a single agent in my checkbox tree with the new code that you provided. Have any idea how to fix that @Thorsten Kettner – Pokemon Master1 Aug 27 '18 at 07:37
  • @Pokemon Master1: No, sorry, I have no idea what is causing the error. – Thorsten Kettner Aug 27 '18 at 07:42
  • The exact query i use is this: string sql = "select * from( SELECT age1.*, ROW_NUMBER() OVER (PARTITION BY age1.agent_id ORDER BY age1.allocation_start DESC) AS rn FROM KS_DRIFT.V_AGENT_ALLOCATION age1 INNER JOIN (" + agentIdSubQuery +") age2 ON age1.AGENT_ID = age2.AGENT_ID ORDER BY AGENT_INITIALS) where rn=1"; – Pokemon Master1 Aug 27 '18 at 07:43
  • The only thing that I see is that `ORDER BY AGENT_INITIALS` within the subquery makes no sense. It belongs at the end of the query (after `where rn=1`). On a sidenote: For readability I would never join when only checking for existence. I'd rather use an `IN` clause (or `EXISTS` for that matter): `WHERE age1.AGENT_ID in (select agent_id from ...)`. – Thorsten Kettner Aug 27 '18 at 07:49
  • I really don't know. – Thorsten Kettner Aug 27 '18 at 08:07
  • I found out the crash had nothing to do with your query, thank you alot for helping. – Pokemon Master1 Aug 27 '18 at 12:06