Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Clarification of association in CDS

Go to solution
roger_beach
Participant

‎08-16-2018 3:43 PM

0 Kudos

We are starting to develop using CDS and have a CDS view defined as follows:

@AbapCatalog.sqlViewName: 'ZHPT_MyEmployees'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS View to get Manager Employees'
define view ZHPT_CDS_MyEmployees     
    with parameters p_datum : datum,
                    p_user  : AENAM          
as select  from ZHPT_CDS_MSSEmployees ( p_datum: $parameters.p_datum , p_user: $parameters.p_user) as Manager_Positions 
inner join  hrp1001 as _hrp1001
on (Manager_Positions.sobid = _hrp1001.objid )
association to pa0001 as _pa0001
on _hrp1001.sobid = _pa0001.plans


{
    key Manager_Positions.pernr as Manager_Pernr , 
    Manager_Positions.sobid as Manager_position, 
    Manager_Positions.ManagerName  as Manager_Name,
    _hrp1001.objid as Supervisor_Position ,    
    _hrp1001.sobid as  Employee_Position,
    _pa0001.sname as Employee_Name,
    _pa0001.pernr as Employee_Pernr,
    _pa0001.mandt,
    _pa0001.plans as Employee_Plans,
    _pa0001.orgeh as Employee_Org, 
    _pa0001
}
where _hrp1001.begda  <= $parameters.p_datum
and   _hrp1001.endda  >= $parameters.p_datum 
and   _hrp1001.otype = 'S'
and   _hrp1001.plvar = '01'
and   _hrp1001.rsign = 'B'
and   _hrp1001.relat = '002'
and   _hrp1001.istat = '1'
and   _pa0001.begda  <= $parameters.p_datum
and   _pa0001.endda  >= $parameters.p_datum

Notice that _pa0001 is an association. We have fields from _pa0001 such as sname that we specifically expose with an alias of Employee_Name even though it is also available through the association _pa0001.

We have another CDS view that references the above CDS view. In the second CDS view, if we reference the sname defined with an alias it works fine.

If we reference it using the alias. The records begin to multiply. So that where we would have an individual record in the result from the alias Employee_Name, if we use the association, we end up with five duplicate records.

I've read through the documentation for association, I'm confused by the part where it indicates what type of join it will use:

When a CDS view is activated with path expressions, every association specified here is transformed to a join expression. The source data source represents the left side and the target data source represents the right side. The ON condition of the association is added to the ON condition of the join. By default, the category of the join is determined by where the path expression is used:

  • After FROM, it is an inner join (INNER JOIN)

  • In all other locations, it is a left outer join (LEFT OUTER JOIN)

We see pa0001 established as a left outer join. Was hoping someone could explain what is occurring when referenced as the specific alias vs. the projection path. The second CDS view is as follows for reference:

@AbapCatalog.sqlViewName: 'ZHPT_TimeReports'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'CDS View for Time Reports'
@UI.headerInfo: {  typeName: 'Time Report for my Employees' , typeNamePlural: 'Time Report for my Employees' }
@OData.publish: true
@Search.searchable : true      
define view ZHPT_CDS_TimeReports
    with parameters p_datum : datum,  
                    p_user  :AENAM,
                    p_BeginDate: datum,
                    p_EndDate : datum                  
as select from ZHPT_CDS_MyEmployees( p_datum: $parameters.p_datum, p_user: $parameters.p_user )  as EmployeeData
//association to catsdb as _catsdb
inner join catsdb as _catsdb
on EmployeeData.Employee_Pernr = _catsdb.pernr


//association to hrp1000 as _hrp1000
inner join  hrp1000 as _hrp1000
on _hrp1000.objid = EmployeeData.Employee_Org


{


        @UI.lineItem: { label: 'Pernr', position: 3, importance: #HIGH }
        @UI.selectionField: {position:3, qualifier: 'EmployeeData' }
//        @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 2 }]
        @Search: { defaultSearchElement: true, fuzzinessThreshold: 0.7 }
        //@UI.identification: { label: 'Pernr', position: 10, importance: #HIGH } to get to detail page         
        key EmployeeData.Employee_Pernr  as EmployeePernr,
        
        @UI.lineItem: { label: 'Time Entry Date', position: 2, importance: #HIGH }
        @UI.selectionField: {position:2,qualifier:'TimeData' }
        @UI.fieldGroup: [{qualifier: 'TimeData', position: 1 }]
        @Search: { defaultSearchElement: true, fuzzinessThreshold: 0.7 }                        
        _catsdb.workdate as EntryDate,
                
        
        @UI.lineItem: { label: 'Employee Name', position: 1, importance: #HIGH }
        @UI.selectionField: {position:1 }
        @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 1 }]
        @Search: { defaultSearchElement: true, fuzzinessThreshold: 0.7 }        
        //@UI.identification: { label: 'Pernr', position: 10, importance: #HIGH } to get to detail page         
         EmployeeData._pa0001.sname as EmployeeName,        
        
        @UI.lineItem: { label: 'Status', position: 8, importance: #HIGH }
        @UI.fieldGroup: [{qualifier: 'TimeData', position: 4 }]                               
        _catsdb.status as Status,        
        
        // For Correction Indicator - TODO 
        
        
        @UI.lineItem: { label: 'Payroll Area', position: 16, importance: #HIGH }
        @UI.selectionField: {position:50 }  
        @UI.fieldGroup: [{qualifier: 'PayData', position: 6 }]
        _catsdb.zz_payrollarea as CatsPayArea,  
        
        
        @UI.lineItem: { label: 'Pay Rate', position: 20, importance: #HIGH }  
        @UI.fieldGroup: [{qualifier: 'PayData', position: 10 }]
        _catsdb.zz_payrate as CatsPayrate, 
        
        @UI.lineItem: { label: 'Hours', position: 24, importance: #HIGH }
        @UI.selectionField: {position:90 } 
        @UI.fieldGroup: [{qualifier: 'TimeData', position: 12 }]      
        @DefaultAggregation: #SUM
        _catsdb.catsquantity as CatsHours,
            
         @UI.lineItem: { label: 'Att./Absence type', position: 28, importance: #HIGH   }
    
        @UI.selectionField: {position:70 } 
       @UI.fieldGroup: [{qualifier: 'TimeData', position: 16 }]


            _catsdb.awart as TimeType,   
            
       @UI.lineItem: { label: 'Att./Absence type Text', position: 32, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'TimeData', position: 20 }]
            _catsdb.zz_attabstext as TimeTypetext,
            
            
       @UI.lineItem: { label: 'Valuation Basis ', position: 36, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'TimeData', position: 24 }]
            _catsdb.bwgrl as ValuationBasis,            
            
       @UI.lineItem: { label: 'Wage Type', position: 40, importance: #HIGH }
       @UI.selectionField: {position:80 } 
       @UI.fieldGroup: [{qualifier: 'PayData', position: 14 }]
            _catsdb.lgart as WageType,  
            
       @UI.lineItem: { label: 'Wage Type Text', position: 44, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'PayData', position: 18 }]
            _catsdb.zz_wagetext as WageTypeText,               
            
   @Semantics.currencyCode: true           
    _catsdb.waers as CurrencyKey,              
            
         @UI.lineItem: { label: 'Amount', position: 48, importance: #HIGH }
        @Semantics.amount.currencyCode: 'CurrencyKey'                       
        @UI.selectionField: {position:100 }
       @UI.fieldGroup: [{qualifier: 'PayData', position: 22 }]        
            _catsdb.catsamount as catsAmount,              
            
        @UI.lineItem: { label: 'Cost Center/Sub-Object Code', position: 52, importance: #HIGH }         
        @UI.selectionField: {position:140 }
       @UI.fieldGroup: [{qualifier: 'PayData', position: 26 }]          
            _catsdb.rkostl as CostCenter,   
            
            
        @UI.lineItem: { label: 'Order/Project Code', position: 56, importance: #HIGH }         
        @UI.selectionField: {position:150 }
        @UI.fieldGroup: [{qualifier: 'PayData', position: 30 }]   
            _catsdb.raufnr as ProjCode,                       
            
           @UI.lineItem: { label: 'WBS Element/Sub-Account', position: 60, importance: #HIGH }         
        @UI.selectionField: {position:130 }
                @UI.fieldGroup: [{qualifier: 'PayData', position: 34 }]  
            _catsdb.rproj as WBSSubAccn,              
            
           @UI.lineItem: { label: 'Fund/Account', position: 64, importance: #HIGH }         
        @UI.selectionField: {position:120  }
                @UI.fieldGroup: [{qualifier: 'PayData', position: 38 }]          
            _catsdb.fund as Fund,           
            
            
            
                      @UI.lineItem: { label: 'Functional Area/Org Ref ID', position: 68, importance: #HIGH }         
        @UI.selectionField: {position:160 }
                @UI.fieldGroup: [{qualifier: 'PayData', position: 42 }]          
            _catsdb.func_area as FuncArea ,
            
            
            
       @UI.lineItem: { label: 'Created On', position: 72, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 1 }]
            _catsdb.ersda as CreatedOn,  
            
        @UI.lineItem: { label: 'Created by', position: 76, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 2 }]
            _catsdb.ernam as CreatedBy, 
            
            
       @UI.lineItem: { label: 'Time of Entry', position: 80, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 3 }]
            _catsdb.erstm as CreatedTime, 
            
            
        @UI.lineItem: { label: 'Date of Last Change', position: 84, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 4 }]
            _catsdb.laeda as CreatedDate, 
            
        @UI.lineItem: { label: 'Name of person who changed Object', position: 88, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 5 }]
            _catsdb.aenam as Chgbyname, 
            
        @UI.lineItem: { label: 'Time of Last Change', position: 92, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 6 }]
            _catsdb.laetm as ChangedTime,
            
        @UI.lineItem: { label: 'Approver Name', position: 96, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 7 }]
            _catsdb.apnam as ApproverName, 
            
        @UI.lineItem: { label: 'Date of approval', position: 100, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 8 }]
            _catsdb.apdat as ApprovalDate, 
            
                                @UI.lineItem: { label: 'Rejection Reason', position: 104, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 9 }]
            _catsdb.reason as Reason,   
            
            
            @UI.lineItem: { label: 'Organizational Unit', position: 108, importance: #HIGH }
        @UI.selectionField: {position:60 }
        @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 10 }]        
            EmployeeData.Employee_Org as Employeeorg,
                        
//Get Org Unit TExt    TO DO 


           @UI.lineItem: { label: 'Org. Unit Text', position: 112, importance: #HIGH }
           @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 14 }]        
                           _hrp1000.stext as EmployeeorgText,
            
         @UI.lineItem: { label: 'Supervisor Name', position: 116, importance: #HIGH }
         @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 16 }]        
            EmployeeData.Manager_Name as MangerName,
            
         @UI.lineItem: { label: 'Work Order', position: 120, importance: #HIGH }
         @UI.fieldGroup: [{qualifier: 'PayData', position: 46 }]        
            _catsdb.zz_work_ord as WorkOrder,   
            
                  @UI.lineItem: { label: 'Task Code', position: 124, importance: #HIGH }
         @UI.fieldGroup: [{qualifier: 'PayData', position: 50 }]        
            _catsdb.zz_task_cd as TaskCode,  
            
          @UI.lineItem: { label: 'Overtime Flag', position: 128, importance: #HIGH }
         @UI.fieldGroup: [{qualifier: 'TimeData', position: 28 }]        
            _catsdb.zz_overtime as OvertimeFlag,  
            


                               /* @UI.lineItem: { label: 'SupervisorName', position: 104, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 9 }]
            _catsdb.reason as Reason,  


                                @UI.lineItem: { label: 'Rejection Reason', position: 104, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 9 }]
            _catsdb.reason as Reason,  
            
                                @UI.lineItem: { label: 'Rejection Reason', position: 104, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 9 }]
            _catsdb.reason as Reason,  
            
                                                        @UI.lineItem: { label: 'Rejection Reason', position: 104, importance: #HIGH }
       @UI.fieldGroup: [{qualifier: 'Audit Data', position: 9 }]
            _catsdb.reason as Reason, */ 
            






         
                        
        @UI.hidden: true         
  //      @UI.lineItem: { label: 'Personnel Sub Area', position: 20, importance: #HIGH }
        @UI.selectionField: {position:20 } 
                        @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 14 }]  
            EmployeeData._pa0001.btrtl as EmployeeSubArea,
            
        //@UI.lineItem: { label: 'Employee Group', position: 30, importance: #HIGH }
       // @UI.hidden: true
        @UI.selectionField: {position:30  }   
                                     @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 18 }]  
            EmployeeData._pa0001.persg as EmployeeGroup,   
            
//                             @UI.lineItem: { label: 'Employee SubGroup', position: 40, importance: #HIGH }
 //@UI.hidden: true
                       @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 20 }]                               
        @UI.selectionField: {position:40  }        
            EmployeeData._pa0001.persk as EmployeeSubGroup,  
            
                             //@UI.lineItem: { label: 'Payroll Area', position: 50, importance: #HIGH }
 //@UI.hidden: true                             
                        @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 24 }]                               
        @UI.selectionField: {position:50}        
            EmployeeData._pa0001.abkrs as EmployeePayArea


                                
}
 where
  _catsdb.workdate >= $parameters.p_BeginDate
  and   _catsdb.workdate <= $parameters.p_EndDate 
    and  _hrp1000.begda  <= $parameters.p_datum
and   _hrp1000.endda  >= $parameters.p_datum 
and   _hrp1000.otype = 'O'
and   _hrp1000.plvar = '01'
and   _hrp1000.istat = '1'

The portion of the second CDS we are toying with is:

        @UI.lineItem: { label: 'Employee Name', position: 1, importance: #HIGH }
        @UI.selectionField: {position:1 }
        @UI.fieldGroup: [{qualifier: 'EmployeeData', position: 1 }]
        @Search: { defaultSearchElement: true, fuzzinessThreshold: 0.7 }        
        //@UI.identification: { label: 'Pernr', position: 10, importance: #HIGH } to get to detail page         
         EmployeeData._pa0001.sname as EmployeeName,

if we reference:
EmployeeData._pa0001.sname as EmployeeName
we get all the duplicate records.

But if we reference
EmployeeData.EmployeeName
then there are no duplicate records.

Isn't it using the same joins and such given EmployeeData.EmployeeName is defined in the first CDS view as:

 _pa0001.sname as Employee_Name,

Any guidance is appreciated.

1 ACCEPTED SOLUTION
7 REPLIES 7

Go to solution
roger_beach
Participant
In response to horst_keller

‎08-16-2018 6:55 PM

0 Kudos

Horst,

I had read through your blog yesterday and the documentation you linked. I attempted in the second CDS to reference like so:
EmployeeData._pa0001[inner].sname as EmployeeName,

But the result is the same, duplicate records.

I even considered that perhaps due to referencing the association via the path expression that perhaps it loses the where portion from the first CDS. I added the where conditions for _pa0001 to the 2nd CDS but it had no effect.

Go to solution
horst_keller
Product and Topic Expert
Product and Topic Expert
In response to horst_keller

‎08-16-2018 10:57 PM

0 Kudos

Use SQL trace in the debugger or in ST05 in order to see the joins resulting from the associations.

Go to solution
roger_beach
Participant
In response to horst_keller

‎08-17-2018 1:16 PM

0 Kudos

Horst,

I don't see that detail in ST05, the call to the CDS View is there.

However, I can see in Eclipse itself by opening with the dependency analyzer that for ZHPT_CDS_MYEMPLOYEES it is treating the association to PA0001 as a Left Outer Join.

However, all the examples I am seeing are where the association is defined and the selection takes place within the same CDS view. Here we are defining the association in a parent CDS and doing the selection in the child CDS. I thought this nesting may be part of the issue. I redefined the second CDS to contain that association directly and included the same date limitations in the where clause. I then referenced that association in the second CDS view and the data looks correct.

I then added just _pa0001.begda to see what it returns to the second CDS and it is returning records for all pa0001 records, not just the ones with the correct date range specified in the where clause. Do you lose the specifications of the where clause if you are referencing an association from a parent CDS?

Preview file
16 KB
Preview file
34 KB

Go to solution
horst_keller
Product and Topic Expert
Product and Topic Expert
In response to horst_keller

‎08-17-2018 11:12 PM

0 Kudos

An association defines a relation between the CDS view where it is defined and a target data source. It can be used in path expressions in CDS and Open SQL and at this position is then instantiated into join expressions as documented, the left side always being the CDS view where it is defined. If you use a path expression in another CDS view, you will see the join in its database object. If you use a path expression in Open SQL, you will see the join in ST05.

I think, you are expecting a redirection of the source when using an association of a parent view in a child view. This is not supported yet. If it is supported, it will require an explicit syntax.

Go to solution
roger_beach
Participant
In response to horst_keller

‎08-20-2018 2:43 PM

0 Kudos

Horst, thanks so much for your reply. I see, at this point, we are taking things too far 🙂

Your confirmation is appreciated though, we can adjust for that

Go to solution
dmitry_kuznetsov1
Active Participant

‎04-08-2019 1:52 PM

0 Kudos

I think it comes due to the fact that

EmployeeData._pa0001.sname as EmployeeName

you get the duplicates due to the fact that this construct does not restrict the InfoType 1 data to just one time-interval, thus multiplies the rows as many times as there are time-dependent records.