Understanding the Query Elements

[pranav_kandpal]

Understanding the Query Elements

Introduction

With the help of this document we will be able to correlate to the various tables in the backend where the objects of the Query are saved in the Query Designer.

To check the elements of a big complex query is really painful at times. We have to manually open each selection and look at the selections one by one.

However the easier solution is looking at the backend tables where all the entries of the tables are stored.
With the help of this document we will try to cover some of the important concepts of the BEX Query Designer, and how they can be cross referred and checked at the backend.

Important Tables Related To Query Elements

Table Name	Table Relevance
RSZCOMPDIR	Details of Reporting Query ,like the COMPUID(ID of the Reporting Query ) , owner name , last changed, creation date etc.
RSZELTXREF	Details of the various references of objects used in the Query like row details ,column details , filter details , default values, exceptions ,conditions etc. This is the main reference table for all the objects where the technical references are maintained.
RSZCALC	This table has all the Formula details of the Query level selections
RSZRANGE	This table has the details of all the selections in the Query
RSZELTPROP	This table holds the details of all the properties of the query level selections like the hierarchy used at the default restrictions, scaling factors , input readiness of cells ,etc.
RSZELTTXT	This table has the text details of all the selections in the reporting query.
RSZGLOBV	This table has the details of the variable (all variables).

Demystifying the queries:-

Now let’s concentrate on how to obtain various technical details of the Queries

In the sections to come we will cover the various technical objects of the queries like Default values, Filters, Row Structure , Column Structure ,Selections at row/column level ,Cell level properties, Cell restriction , Conditions and exceptions and so on.

Obtaining the COMPUID of the Table

In the Table RSZCOMPDIR enter the query technical name in the field COMPID to obtain the COMPUID of the query.

Table RSZELTREF

Now we have the COMPUID of the Query. Now we have to cross refer to various tables to find out the
query elements.

The table RSZELTXREF gives a list of cross references with the below values for the field LAYTP. Mentioned below is the segregation based on the LAYTP .

LAYTP	Reference to the query
VAR,QVS	Query Variable Sequence with the sequence mentioned in the field POS
NILL	Exceptions and Conditions
SHT	Cell restrictions , Row and Column Structure, Default values and Free   Char
SOB	Row and column and filter values.
AGG	Filter Details
ROW	Row Details
COL	Column Details
CELL	Cell References

With each corresponding SELTUID we have a corresponding TELUID as mentioned in the below screenshot.

Now let’s discuss how we can obtain various elements of the query

Query Variable Sequence Details

In the RSZELTXREF table enter the COMPUID of the query in the field SELTUID ,  select TELTUID for all the entries where LAYTP = ‘VAR’ or ‘SOB’.

Now lookup for all these values in the standard table RSZELTTXT where ELTUID = SELTUID to obtain the text of the Query variable
sequence

Also the position of the Query variable can be found in Table RSZELTXREF in the field POSN.

In the screenshot above SELTUID is the COMPUID of the query and LAYTP is the type of selection , net to it is the Position .

NOTE: This detail will have the Text of the Variable and not the technical name, for further details on the technical names please refer to the respective row / column structure details.

Filter Details

The filter details of the query are maintained in the LAYTP  ‘SOB’. When we cross refer these values in the RSZELTREF table, the TELTUIDs
with LAYTP = “AGG” is displayed which are the filter values.

Let’s take a look at the detailed approach using screenshots.

To obtain the filter details we will select the TELTUID from the Table RSZELTXREF where LAYTP = ‘SOB’.

Step 1:- Details of the TELUID’s in the RSZELTXREF Table:-

For each SELTUID there can be multiple references which are tracked in the TELUID . It is basically a reference of an element in the query .

For example , each query will have multiple elements in it . Each element is identified by maintaining a list of cross references , so each element is a cross reference . A row structure will have various rows , each row will be identified as a cross reference . Now with a row ,  a selection in the row which can further be broken down into specific Info Objects used for this selection . All this information is traced via the cross references of SELTUID and TELTUID's.

The first step is to find all the elements in the query . Start with the COMPUID of the query derived from the COMPDIR Table and get all the TELTUID's by setting SELTUID= COMPUID as mentioned in the below screenshot .

Step 2 : Select the TELUID and copy it to the SELTUID where LAYTP = 'SOB' in the selection for master data of the TABLE RSZELTXREF to get the new list of
TELUID:-

Now in the above mentioned screenshot the  value TELTUID  where LAYTP ='SOB' is copied into SELTUID Please note that in the above 2 screenshots the fields marked in red have the same values for TELTUID and SELTUID .

Now all the entries in the LAYTP as ‘AGG’ have all the details of the filters.

Step 3 : Select all the entries with LAYTP AS ‘AGG’ from table RSZELTXREF  and lookup on the Table RSZRANGE WHERE ELTUID = TELTUID, to obtain the technical details of the Filters as depicted in the below screenshot

Default Values Details

The filter details of the query are maintained in the LAYTP  ‘SHT’. When we cross refer these values in the RSZELTREF table, the TELTUIDs
with LAYTP = “AGG” is displayed which are the default values. Now this would be bit clearer from the below mentioned screenshots .

The below screenshot is the RSZELTXREF Details for the Query

Select the TELTUID Of the record which has LAYTP = ‘SHT’. Use the TELTUID as SELTUID to get the list of all the cross references .

The screenshot shows all the records with LAYTP = “AGG”.

Please note the fields marked in RED show the logic of SELTUID = TELTUID.

FOR Default Value Selection

We select the TELTUID from the RSZELTXREF and lookup in the RSZRANGE Table :-

Thus we get all the restrictions of the default values.
However we still need to find default values which have no restrictions or have hierarchy restrictions.

Technical Info object Names of all the Default values

Next step is to get the text of the Default values and we make lookup on the table RSZELTDIR with ELTUID = TELTUID (from RSZELTXREF table) .

For Selecting the Info-object Name , the Default Hint field gives the name of the Info Object used for Selection

For Selecting the Hierarchy .

We also make a Lookup on the table RSZELTPROP, ELTUID =  TELTUID (from RSZELTXREF table) to obtain the list of all the records with Hierarchies included as mentioned in the below ,
screenshot

Please note the fields marked in Red follow the logic of ELTUID = TELTUID .

Row /Column Selection Details

This is a bit more complex work. Let’s take a look at the screenshots to demystify the Rows/Columns.

Please note that the approach for ROW/Column level details is same. In this example , I have taken Column as base.The similar approach can
be used to derive ROWS details  as well

The below screenshot is the RSZELTXREF Details for the Query

Now Select the all TELTUID Of the record which has LAYTP = ‘SHT’ and cross lookup on the RSZELXREF table with SELTUID = TELTUID.

Now we have details of all the LAYTD =’SHT’ and new TELTUIDS.Now Select values with TELUID for the LAYTP as ‘ROW’ /’Column’ as mentioned in the below example.

Now again lookup on the table RSZELTXREF to obtain the list of all the related items of LAYTP = ‘Row/Column’. Please remember to set SELTUID = TELTUID.

The below screenshot has all the related references of the LAYTP =’COL’ .If you note the TELTUID of the above screenshot is similar to the
SLETUID of the below screenshot

I have trimmed down this screenshot as this usually would be huge as it contains all the cross references of your row's/column's.

Now we have a list of all the references and now we need to find the details of selections, filters, text and simple info objects with no selections. Please note in the below examples we look up with the tables with the condition that ELTUID (in the new tables) = TELTUID (all values obtained from the table  RSZELTXREF).

To Obtain the Details of Selections

Select all the entries and lookup in the table RSZRANGE To obtain the restrictions :-

To Obtain Details of Formulae

Lookup in the table RSZCALC

To obtain the text refer to the table RSZELTXT

Infoobject details at row/column level

In case if just an Info object is used in the row/column level this info object details can be looked up in the RSZELTDIR Table.

Exceptions and Conditions

By now we are clear of obtaining the basic details in the RSZELTXREF table, so I have skipped the first step of obtaining the list of all the related objects in the query .

Select the LYTP ‘NIL’ from the table RSZELTXREF to select the details of the Conditions and Exceptions.

Now cross refer to the table RSZRANGE to select the details of Conditions and exceptions as mentioned below in the screenshot.

Now follow the usual process of  select ELTUID (RSZRANGE) = TELUID (RSZELTXREF)

Please note that in case of Conditions, in the field IOBJN in RSZRANGE  the value is 1CONDITION and in case of Exception the value in the field IOBJNM the value is 1EXCEPTION marked in red in the above screenshot.

Cell References Details

Select the LYTP ‘SHT’ from the table RSZELTXREF to select the details of Cell References. Now cross refer to the Table RSZELTXREF where SELTUID =
TELTUID where LYTP = ‘SHT’. Now Select the TELTUID of records with LAYTP = ‘CELL’.

For cell references of type formulae we lookup on the table RSZCALC with the selection ELTUID = TELTUID.

For cell references of type selection we look up on the table RSZRANGE with the selection ELTUID = TELTUID.

And for just cell level references we look up on the table RSZELTPROP

Understanding Table RSZRANGE

The RSZRANGE table has all the details related to the selection in a Query. Now let’s look at the structure of the RSZRANGE table.

Now let’s discuss some of the important fields

Field Name	Relevance
ELTUID	Unique ID Of the selection
IOBJNM	The respective Info Object on which the restrictions have been made
ENUM	Order of restriction in a selection
SELTYP	Has various values to describe the selection type as mentioned in the   below screenshot
SIGN	Describes the type of selection sign for ex Equal to or not equal to   i.e Include or exclude a selection
OPT	Has the sign of operator i.e, eq ,lt ,gt etc.
LOW	Has the low value
HIGH	In case of range values ,the high value is populated.
LOWFLAG	Describes the types of value used in a low field. Mentioned below are   the various allowed types
SHIFTLOW	In case of an offset usage , the flag is used to set the offset   values i.e(+,- the values)
HIGHFLAG	Describes the types of value used in a high field
SHIFTHIGH	n case of an offset usage , the flag is used to set the offset values   i.e(+,- the values)
HIENM	Name of hierarchy if any used
HIENMFLAG	Flag which holds the value of type of selection for hierarchy name
VERSION	Version name
VERSIONFLAG	Flag which holds the value of type of selection for Version.
DATETO	The TO date of the Hierarchy.
DATETOFLAG	Flag which holds the value of type of selection for To Date.