Technology Blog Posts by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
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: 
Read only

ABAP CDS Views: Is it possible to Select Single?

ABAPMarty
Participant
‎2024 Jul 18 10:58 PM
5,481

Can you achieve "select single" in a CDS view join? (Part 1)

There is no “select single” command in the SQL used within ABAP CDS Views. And even though the developer can use a select single OpenSQL command in an ABAP program, this is often not where the requirement for one record is encountered. It is often encountered or required in a table join and this is the focus of this article.

If you know a better way to implement this, I would certainly like to hear from you. Please add your thoughts in the comments.

Requirement

Here is a real-world example requirement that I encountered some time ago.

User: “I want the program to fetch the main barcode, by material number and unit of measure, and display it in the report.”

Me (after some analysis): “Some of the barcodes don’t have the “main barcode” flag set, and there are often multiple barcodes. Do you want all of them?”

User: “No. if the main barcode flag is not set then just get the first barcode.”

Me: “Does it have to be the first one?”

User: “No, as long as it is for that material and unit of measure, any barcode is fine.”

Note that the developer would go from the material master table and join to the barcode table. So, there is no option to “select single” here unless you move back to the application server and select the material data and then in a loop access the barcode table. In other words, lose the benefits of code pushdown.

Solution – with a “Counter”

In order to emulate the scenario above I have created some custom tables which include a barcode “header table” table with material and unit of measure (this would be the DB equivalent of the material master table) and then an “item” table which contains the barcode information which is the same as the SAP “MEAN” table.

You can try this one your own ABAP system if you create these as local objects.

The “Z” Tables

Table: ZMC_MEAN_HDR (Material Master)

ABAPMarty_0-1721191719127.png

FIelds:

MANDT type MANDT
MATNR type MATNR
MEINH type MEINH

Table: ZMC_MEAN (Barcode table)

ABAPMarty_0-1721191828890.png

Fields:

MANDT type MANDT
MATNR type MATNR
MEINH type MEINH
LFNUM type LFNUM
EAN11 type EAN11
HPEAN type HPEAN

This table has a counter (LFNUM) that is populated. This makes the finding of the first record much easier. This technique can then be used with lots of SAP tables like sales order items, purchase items, delivery item, etc.

In a later section I cover another technique that doesn’t need a counter.

Dataset

I populated these tables with this dataset to demonstrate clearly what we are trying to achieve.

Table: ZMC_MEAN_HDR

ABAPMarty_1-1721191904812.png

Table: ZMC_MEAN

ABAPMarty_2-1721191938622.png

Note the first material has a main barcode while the second material does not.

 

  lt_mean_hdr = VALUE #( ( matnr = '150000000000009555' meinh = 'EA'  )
                         ( matnr = '150000000000009888' meinh = 'EA'  )
                         ( matnr = '150000000000007711' meinh = 'PAL' ) ) .

  lt_mean = VALUE #( ( matnr = '150000000000009555' meinh = 'EA'  lfnum = '00010' ean11 = '4444-5555-6666-776' hpean = '' )
                     ( matnr = '150000000000009555' meinh = 'EA'  lfnum = '00020' ean11 = '4444-5555-6666-777' hpean = '' )
                     ( matnr = '150000000000009555' meinh = 'EA'  lfnum = '00030' ean11 = '4444-5555-6666-778' hpean = '' )
                     ( matnr = '150000000000009555' meinh = 'EA'  lfnum = '00040' ean11 = '4444-5555-6666-779' hpean = '' )
                     ( matnr = '150000000000007711' meinh = 'PAL' lfnum = '00010' ean11 = '4444-1111-6666-771' hpean = '' )
                     ( matnr = '150000000000007711' meinh = 'PAL' lfnum = '00020' ean11 = '4444-1111-6666-772' hpean = 'X' )
                     ( matnr = '150000000000007711' meinh = 'PAL' lfnum = '00030' ean11 = '4444-1111-6666-773' hpean = '' ) ) .

 

The CDS Views

CDS View - ZMC_SELS_MEAN_CNT

The first CDS View is simply a straight select from the barcode table.

 

@AbapCatalog.sqlViewName: 'ZMCSELSCNT'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode selection (with counter)'
define view ZMC_SELS_MEAN_CNT as select from zmc_mean
{
    matnr,
    meinh,
    lfnum,
    ean11,
    hpean
}

 

Data preview:

ABAPMarty_0-1721339311388.png

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL' lfnum = '00010' ean11 = '4444-1111-6666-771' hpean = '' )
( matnr = '150000000000007711' meinh = 'PAL' lfnum = '00020' ean11 = '4444-1111-6666-772' hpean = 'X' )
( matnr = '150000000000007711' meinh = 'PAL' lfnum = '00030' ean11 = '4444-1111-6666-773' hpean = '' )
( matnr = '150000000000009555' meinh = 'EA' lfnum = '00010' ean11 = '4444-5555-6666-776' hpean = '' )
( matnr = '150000000000009555' meinh = 'EA' lfnum = '00020' ean11 = '4444-5555-6666-777' hpean = '' )
( matnr = '150000000000009555' meinh = 'EA' lfnum = '00030' ean11 = '4444-5555-6666-778' hpean = '' )
( matnr = '150000000000009555' meinh = 'EA' lfnum = '00040' ean11 = '4444-5555-6666-779' hpean = '' )
)

CDS View - ZMC_SELS_MEAN_CNT_MIN

Note that this CDS View uses the MIN() function. Which for a given material and UoM will give the record with the lowest value of LFNUM, in this case that would be the first record.

 

@AbapCatalog.sqlViewName: 'ZMCSELSCNTMIN'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode selection (with counter) MIN'
define view ZMC_SELS_MEAN_CNT_MIN as select from zmc_mean
{
    matnr,
    meinh,
    min(lfnum) as lfnum
} group by matnr, meinh

 

Data preview:

ABAPMarty_1-1721339348283.png

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL' lfnum = '00010' )
( matnr = '150000000000009555' meinh = 'EA' lfnum = '00010' )
)

Here you can see the CDS view returned only the first records for each material/UoM combination and not the main barcode for the first material number.

CDS View - ZMC_SELECT_BARCODES_COUNT

This CDS view returns the first barcodes for all material/UoM.

 

@AbapCatalog.sqlViewName: 'ZMCSELSCNTCOUNT'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode selection count'
define view ZMC_SELECT_BARCODES_COUNT as select from ZMC_SELS_MEAN_CNT_MIN as Minimum

    inner join ZMC_SELS_MEAN_CNT as BarcdodeWithCount
      on Minimum.matnr = BarcdodeWithCount.matnr
      and Minimum.meinh = BarcdodeWithCount.meinh
      and Minimum.lfnum = BarcdodeWithCount.lfnum
{
    Minimum.matnr,
    Minimum.meinh,
    BarcdodeWithCount.ean11
}

 

Data preview:

ABAPMarty_0-1721339164286.png

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL' ean11 = '4444-1111-6666-771' )
( matnr = '150000000000009555' meinh = 'EA' ean11 = '4444-5555-6666-776' )
)

CDS View - ZMC_SELECT_BARCODES

Now we can put all this together to fetch either the main barcode or the first barcode if no main barcode is flagged.

 

@AbapCatalog.sqlViewName: 'ZMCSELSCNTALL'
@AbapCatalog.compiler.CompareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Barcode selection'
define view ZMC_SELECT_BARCODES as select from zmc_mean_hdr as Header

    left outer join ZMC_SELS_MEAN_CNT as MainBarcode
      on Header.matnr = MainBarcode.matnr
      and Header.meinh = MainBarcode.meinh
      and MainBarcode.hpean = 'X'
      
    left outer join ZMC_SELECT_BARCODES_COUNT as BarcodeMinimum
      on Header.matnr = BarcodeMinimum.matnr
      and Header.meinh = BarcodeMinimum.meinh 
{
    Header.matnr,
    Header.meinh,
    
    case when MainBarcode.ean11 is not null then MainBarcode.ean11
         when BarcodeMinimum.ean11 is not null then BarcodeMinimum.ean11
         else ''
         end as Barcode 
}

 

Data preview:

As you can see below:

  • For the first material 150000000000007711, the CDS View returns the second record in the table, the one flagged as “main barcode” (where HPEAN = ‘X’)
  • For the second material 150000000000009555, the CDS View returns the first barcode value and as can be seen (above) that material does not have a barcode flagged as “main barcode”.

ABAPMarty_3-1721339458718.png

VALUE #(
( matnr = '150000000000007711' meinh = 'PAL' Barcode = '4444-1111-6666-772' )
( matnr = '150000000000009555' meinh = 'EA' Barcode = '4444-5555-6666-776' )
)

Part 2 will show how to do the same when you don't have a counter field in the table.

1 Comment