Is anyone aware of a set command where I could control where the SQL translator writes a where statement? I want the translator to push the where statement into the FROM statement instead of the normal where statement.

For example in the following example:

JOIN
 LEFT_OUTER STUD_CRSE_SECTION.ISTUD_CRSE_SECTION.PROFILE_KEY IN 
STUD_CRSE_SECTION TO MULTIPLE
 STUD_ADVISEMENT.STUD_ADVISEMENT.PROFILE_KEY IN STUD_ADVISEMENT
 TAG J1 AS J1
 END
JOIN
 LEFT_OUTER J1.STUD_ADVISEMENT.PRF_ADVISOR_KEY IN STUD_CRSE_SECTION
 TO MULTIPLE PROFILE.PROFILE.PROFILE_KEY IN PROFILE TAG J2 AS J2
 END
TABLE FILE STUD_CRSE_SECTION
PRINT 
     STUD_CRSE_SECTION.STUD_CRSE_SECTION.STUD_CRSE_SECTION_KEY
     STUD_CRSE_SECTION.STUD_CRSE_SECTION.REG_SEQ_NBR
     J2.PROFILE.GENDER_CD
     J2.PROFILE.RELT_PRIORITY_NBR
     STUD_CRSE_SECTION.STUD_CRSE_SECTION.CRSE_COMMENT_TXT
     J1.STUD_ADVISEMENT.PRF_ADVISOR_KEY
     J1.STUD_ADVISEMENT.PRIMARY_IND
WHERE J1.STUD_ADVISEMENT.PRF_ADVISOR_KEY EQ 48
END  

FOCUS would translate it as such:

  SELECT
 T1.”STUD_CRSE_SECTION_KEY”,
 T1.”REG_SEQ_NBR”,
 T1.”CRSE_COMMENT_TXT”,
 T1.”PROFILE_KEY”,
 T2.”PRIMARY_IND”,
 T2.”PRF_ADVISOR_KEY”,
 T3.”PROFILE_KEY”,
 T3.”GENDER_CD”,
 T3.”RELT_PRIORITY_NBR”
  FROM
 ( ( IDR.STUD_CRSE_SECTION T1
  LEFT OUTER JOIN IDR.STUD_ADVISEMENT T2
  ON T2.”PROFILE_KEY” = T1.”PROFILE_KEY” )
  LEFT OUTER JOIN IDR.PROFILE T3
  ON T3.”PROFILE_KEY” = T2.”PRF_ADVISOR_KEY” )
  WHERE					
   (T2.”PRF_ADVISOR_KEY” = 48);

The way I would like it to read is:

  SELECT
 T1.”STUD_CRSE_SECTION_KEY”,
 T1.”REG_SEQ_NBR”,
 T1.”CRSE_COMMENT_TXT”,
 T1.”PROFILE_KEY”,
 T2.”PRIMARY_IND”,
 T2.”PRF_ADVISOR_KEY”,
 T3.”PROFILE_KEY”,
 T3.”GENDER_CD”,
 T3.”RELT_PRIORITY_NBR”
  FROM
 ( ( IDR.STUD_CRSE_SECTION T1
  LEFT OUTER JOIN IDR.STUD_ADVISEMENT T2
  ON T2.”PROFILE_KEY” = T1.”PROFILE_KEY” 
 and T2.”PRF_ADVISOR_KEY” = 48)
  LEFT OUTER JOIN IDR.PROFILE T3
  ON T3.”PROFILE_KEY” = T2.”PRF_ADVISOR_KEY” )

I haven't seen anything yet that would do this. Does anyone have any thoughts?

Eric

This message has been edited. Last edited by: eric.woerle,

You might try a conditional JOIN to see how the query is translated.

The conditional JOIN should do it!

Well that was to easy for you two. Let me see if I can make it more difficult. What if my join is in the MFD and I am not calling a join in the report directly. I wouldn't have the option to do a conditional join then. Ultimately I will be creating Reporting Objects and therefore need to do it through a setting rather then in each report.

thoughts?
Eric

Have you looked into FILTER FILE?
I have never messed with Reporting Objects.
Aren't they basically views for the users to build reports with that have friendly columns names.

Max,

quote:

Aren't they basically views for the users to build reports with that have friendly columns names.

Yes and no.

No, they are the metadata layer that defines the information contained in a table.

Yes, they are views for the users to build reports off of.

Here we are looking to extend the latter. For example I have created a .MAS that has 600 segments in it (which breaks the internal matrix because of total field usage length limits, but that's another story ). In this .MAS I have no fields, just relationships between tables. I have set it up this way, so that if I have to make a change to a field, I only need to do it once, and not 20 times or more. These relationships contain a series of LEFT OUTER and INNER joins. To help retain the LEFT OUTER Join I am looking for a way to push the WHERE statement into the FROM statement. Ultimately at the end of the day I need to make this process not only easier for myself, but also developers and ultimately the end users as these master files will be utilized to create the business views for adhoc reporting. This is why I need a set command to do this. Even if I could do a conditional join in the MFD, I wouldn't be able to account for all of the possibilities, and then parameterize them on top of that. Oh the nightmare that would cause!

As for FILTER I did a quick sql trace and it still does not do the filtering within the FROM statement. It is still on the WHERE clause.

Is there a performace difference between the two sets of SQL ?

Looks like I may have copied in the wrong code... let me fix those inner joins and make them outers like they are supposed to be. But yes, it makes a difference when dealing with left outer joins.

Now that those are outer joins, you would have a record difference between the two statements. One would return lets say 30 records and the other 20.

There was a massive post from someone a year or two ago about this. It covered the different ways the join and what the effective join was.

You wouldn't happen to have any suggestions on what to search for would you? I haven't been able to find anything as of yet...

I think this is it

Thanks Waz!

Thats going to take some time to weed through....

Hi

You can use conditional joins within a master file, you can use DMC synonym editor to define it,
example of a master file with conditional joins:

FILENAME=sql_joins, $
SEGMENT=AA_FACTINTERNETSALES, CRFILE=BASEAPP/AA_FACTINTERNETSALES, CRINCLUDE=ALL, $
SEGMENT=INBAL_DIMPRODUCT, SEGTYPE=KU, PARENT=AA_FACTINTERNETSALES, CRFILE=BASEAPP/INBAL_DIMPRODUCT, CRINCLUDE=ALL, CRJOINTYPE=LEFT_OUTER,
JOIN_WHERE=AA_FACTINTERNETSALES.PRODUCTKEY EQ INBAL_DIMPRODUCT.PRODUCTKEY AND ( COLOR EQ 'Blue' );, $


The Table request:

SET SHORTPATH=SQL

TABLE FILE SQL_JOINS
SUM SALESAMOUNT
BY MODELNAME
END

The generated SQL:

SELECT
T2."ModelName",
SUM(T1."SalesAmount")
FROM
( AdventureWorksDW.dbo.FactInternetSales T1
LEFT OUTER JOIN AdventureWorksDW.dbo.DimProduct T2
ON T2."ProductKey" = T1."ProductKey" AND
(T2."Color" = 'Blue') )
GROUP BY
T2."ModelName"
ORDER BY
T2."ModelName";

Im using 7703HF6.
In addition in WF8002 you will be able to define the conditional join with a prompt so InfoAssist users could be prompted at run time and choose a value

Thanks for the post WF_IL,

unfortunately that still doesn't help my situation because I can't just go creating master files for each report I write and I can't create all of the possible relationships/needs that a developer or end user would have with conditional joins in the master file. I still need to find another solution.

Eric,

Getting the WHERE where you need it might not be possible using WF owing to the parser.

However, as Mighty suggested, FILTERs might suit your needs. Speak to AB who I believe is on the London end for you? He's pretty sharp on using them.

T

Hey Tony,

Yes AB just started up with our London branch a few weeks ago. I haven't had an opportunity to chat with him to much as of yet.

I did do some testing with FILTERs and ran some SQL traces. FILTERS parsed the same way a where statement would. Next time I connect with AB I can definitely get his thoughts on it though.

I was reading the post that Waz had suggested and found the below statement in there.

quote:

In release 7.7 the suggested syntax to report against a JOIN’ed structure is as follows:-

SET ALL = SQL
JOIN
LEFT_OUTER | INNER COLA IN A TO COLB IN B AS J1
END
TABLE FILE A
PRINT …
WHERE COLB EQ ‘value’
[WHERE COLB IS MISSING]
[WHERE COLB EQ ‘value’ OR COLB IS MISSING]
END

Testing with this and using the idea of [WHERE COLB EQ ‘value’ OR COLB IS MISSING] I was able to get the results I wanted in some cases (Note: Had to use SET ALL = PASS not SQL). Its not ideal and it will be difficult to explain the idea of NULL to business users, but this may be what I have to do. I'd still like to have a setting where I could control this in the parser (I know that's possible in other tools, just don't think it is here), but if this is the closest that I can get then at least I can get it to work. Maybe its time for an NFR.