Monday, December 29, 2008

Record Count : Interview Question

I thought its worth sharing this interview question which one of my friend faced during his siebel technical interview. Here you go :

"I have an "Opportunity" business component with a searchspec {[Opty Status] = "Won"}, whose underlying table is S_OPTY. This table has 100 records, out of which 50 records has Opty Status = "Won" and 50 records having Opty Status = "Lost". Now I have configure an applet on "Opportunity" business component and applet has the searchspec {[Opty Status] = "Lost"}. "

With the above configuration described, I compiled the srf. How many records I will see on the applet?

People have different views on this question. Some will say "50 records with status = Won" because Business Component's search spec will override the applet's searchspec. While some will say applet's search spec will take the precedence, so will see 50 records with Lost Status.

Here is the correct answer : "You will see Zero records".

let me take you back to the Siebel architecture where we have learned during the Siebel training class, which says Siebel has 3-tier architecture with 3 layers i.e.
a) User Interface Layer (UI Layer)
b) Business Object Layer (BO Layer)
c) Database Layer (DB Layer)

So, when data is being fetched on the UI, database layer will provide 100 records. By the time it reaches BO Layer, due to the searchspec on Opportunity business component, only 50 records with "Won" status will pass the condition. Now when these 50 records will pass onto the UI layer, no record will satisfy the searchspec available on applet (Opty Status = Lost), so no record will be available on the UI.

In technical terms this fact can be easily proved if you go and check the "SQL spool" generated by the siebel via dedicated client. You will see the both condition will be available in "Where" clause of the query and joined with the "AND" clause. So, none of the Opportunity can its Opty "Status = Won", and "Status = Lost", at the same time, consequently no record will appear on the UI.

1 comment:

  1. When we define a applet and buscomp searchspecfication on both then it works as an sql AND operation.
    So we must avoid in giving mutual exclusive search specification in applet and bus comp level.
    Thank you YOu approached with good example.