I want to find out which synonym a report is using. I want to do this with SQL, not with 'Impact Analyses'.

Is this possible? If yes - how?

You'd have to understand the structures of the tables in the QWQREPOS library to get at this stuff with SQL. It's pretty cryptic. If it's just a single report, you could just right click on the report and view the source (don't change the source if it was created with InfoAssist), or just go ahead and open it in InfoAssist and look for the TABLE FILE spec(s).

Here is a start (modified from old WebFocus forum post for use with webquery):

One correction: I was using SYS naming and had QWQREPOS in my list. So as is it might not work because the QWQBTOU is in QWQREPOS and in my statement it is not qualified. Just change QWQBTOU to QWQREPOS.QWQBTOU

I reread your question and realized you wanted it the other way around: given a report, you wanted a list of synonyms it uses. I tried regex to parse that out of the fex, so it might not be 100% accurate, but should be a good start:

Hi peter, really great, works perfect.

I modified it so it works perfect for me:

WITH COUNTER (N) AS (

 SELECT CAST(N AS INT) FROM (VALUES (1)) TMP(N)

 UNION ALL

 SELECT N+1 FROM COUNTER WHERE N < 100),

FEX AS(

 SELECT QWQREPOS.QWQBTOU(BCONTENT) AS CODE, T2.OBJNAME, T2.PRT_PATH, REPLACE(T2.PRT_PATH || '/' || T2.OBJNAME, '/WFC/Repository/', '') AS PATH

 FROM QWQREPOS.WF_CONTENT_REVS T1

  INNER JOIN QWQREPOS.WF_REPOSOBJ T2 ON T1.OBJ_HANDLE = T2.HANDLE)

SELECT 

SUBSTR(REPLACE(TRIM(PATH), TRIM(OBJNAME), ''), 1, LENGTH(REPLACE(TRIM(PATH), TRIM(OBJNAME), '')) - 1) AS REPORT_PATH,

OBJNAME AS REPORT_NAME, 

CASE WHEN POSITION('/', SYNONYM) > 0

   THEN SUBSTR(SYNONYM, 1, POSITION('/', SYNONYM) - 1) 

   ELSE ''

END AS SYN_PATH,

TRIM(

CASE WHEN POSITION('/', SYNONYM) > 0

   THEN SUBSTR(SYNONYM, POSITION('/', SYNONYM) + 1, LENGTH(TRIM(SYNONYM)))

   ELSE SYNONYM

END) || '.mas' AS SYNONYM_NAME

FROM (

SELECT DISTINCT 

N, REGEXP_COUNT(CODE,'(TABLE FILE\s+|TO.* IN\s+)(\S+)') AS N1, 

TRIM(CAST(REGEXP_SUBSTR(CODE,'(TABLE FILE\s+|TO.* IN\s+)(\S+)',1,n, 'i', 2) AS VARCHAR(150))) AS SYNONYM, 

TRIM(OBJNAME) AS OBJNAME, 

TRIM(PATH) AS PATH

FROM FEX CROSS JOIN COUNTER

WHERE N = 1 AND IFNULL(REGEXP_COUNT(CODE,'(TABLE FILE\s+|TO.* IN\s+)(\S+)'), 0) <> 0)

Only two lines I don't understand, maybe you can explain me?

The lines with REGEXP_COUNT and REGEXP_SUBSTR

regards mike

REGEXP_COUNT gives you a number of occurrences for regular expression match. REGEXP_SUBSTR returns those matches. Since a synonym can be included in as the main synonym on which the report is built (TABLE FILE synonym) or can be included as a part of a join (JOIN field IN primary_synonym TO field IN joined_sysnonym) and the fact that you won't know how many synonyms there might be included in one fex, the only viable solution in my mind was to use regular expressions.

In (TABLE FILE\s+|TO.* IN\s+)(\S+) the first capture group (TABLE FILE\s+|TO.* IN\s+) matches any string that starts with TABLE FILE followed by as much white space as necessary (\s+), or (|) starts with TO, followed by any character (.*) followed by IN and as much white space as necessary (\s+). We just use that capture group to find the correct start, but what we really care about is the second capture group (\S+) which matches all non white space characters immediately following the first capture group.

All that to say that it will go through the entire fex and find all TABLE FILE and all JOIN occurrences. That is why I included the counter CTE - to list them all. The n <= REGEXP_COUNT just limits the number of records to number actual matches.

But it seems you limited the n to 1, which will really give you just the very first synonym it finds. This is most likely going to be the joined synonym and not actually the primary synonym. Now, you might not use join in your reports and it works for you, but you don't need the counter then and you can simplify the REGEXP_SUBSTR to REGEXP_SUBSTR(CODE,'(TABLE FILE\s+)(\S+)',1,1, 'i', 2). Notice, I changed the first capture group as well as 1,n,'i',2 to 1,1,'i',2. Basically getting only the first occurence of the match. The 2 tells it that you want the second capture group and 'i' tells it to perform case insensitive search.

Peter thank you for the good description,

Yes you are right, we don't use JOINs in Webquery. We always makke one View for Webquery - all the JOINs and others are in this View.

What I learned from your solution is that I've less knowledge about REGEXP... .

regards mike

Hi Mike,

Glad to see the collaboration and that you've found a solution! Just a cautionary note that these repository files are internal structures and subject to change without notice.

Best,

Ann