SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Where clause in Join Transformation

Accepted Solution Solved
Reply
Highlighted
Super Contributor
Posts: 625
Accepted Solution

Where clause in Join Transformation

Appreciate if someone of you help me understand to put the following in where clause in Join transformation?

 

WHERE (substr(t1.POLICY_ID,1,1)) = '9' AND t1.DELETED_IND = 0 AND t1.POLICY_VERSION = 1 AND 
           (datepart(t1.CREATED_DTTM)) >= today()-17
           and
           (datepart(t1.CREATED_DTTM)) <= today()-4;

Untitled.png


Accepted Solutions
Solution
‎03-05-2018 12:32 AM
SAS Super FREQ
Posts: 820

Re: Where clause in Join Transformation

you can choose whether the Join transformation should use ON or just WHERE

 

Select the Join in the outline and go down to join properties, this is where you can set implicit = no, so that you use the ON for the join and WHERE for everything else

Capture.PNG

 

 

View solution in original post


All Replies
Respected Advisor
Posts: 4,736

Re: Where clause in Join Transformation

What's the problem? Each line will be one condition so just start adding these conditions line by line. First line will be:
substr(t1.POLICY_ID,1,1)) = '9'

 

You should end up with 5 lines as below:

          substr(t1.POLICY_ID,1,1) = '9' 
          AND t1.DELETED_IND = 0 
          AND t1.POLICY_VERSION = 1 
          AND datepart(t1.CREATED_DTTM) >= today()-17
          and datepart(t1.CREATED_DTTM) <= today()-4
Super Contributor
Posts: 625

Re: Where clause in Join Transformation

Whether I have to add the conditions under 'operator' per the screenshot I
placed before? Where and how can I add the functions like substr and
datepart which are part of my conditions?
Respected Advisor
Posts: 4,736

Re: Where clause in Join Transformation

[ Edited ]

Just click into column Operand, select in the drop down Choose column(s) and select your column. This will populate the field with

t1.POLICY_ID

Then click again into the field and edit the entry by wrapping your substr() function around the column.

Capture.JPG

 

 

Super Contributor
Posts: 625

Re: Where clause in Join Transformation

Let me give a try. My join ON condition is different and by default the
same condition is applied to the where clause. Can I delete the default
condition in where clause and add the filters which I mentioned above?
Respected Advisor
Posts: 4,736

Re: Where clause in Join Transformation

From a DIS tool usage perspective: Yes, the panel works the same for the Where and the On clause.

From a coding perspective: I can't tell you if your logic needs to go into the Where or the On clause without knowing the details of what have and what you want.

Super Contributor
Posts: 625

Re: Where clause in Join Transformation

With the following code, could you please clarify whether I can delete the default condition in where clause and add the condition which I mentioned before?

 

CREATE TABLE WORK.QUERY_FOR_QISDM_COVERAGES_C_000B AS 
   SELECT DISTINCT t1.CREATED_DTTM, 
          t11.POLICY_ID, 
          t11.POLICY_START_DT, 
          t11.POLICY_SUSPENSION_START_DT, 
          t11.POLICY_END_DT, 
          t11.POLICY_STATUS_CD
      FROM OEXTCOM.COVERAGES t1
           INNER JOIN OEXTCOM.DIM_POLICY t11 ON (t1.POLICY_SK = t11.POLICY_SK)
      WHERE (substr(t1.POLICY_ID,1,1)) = '9' AND t1.DELETED_IND = 0 AND t1.POLICY_VERSION = 1 AND 
           (datepart(t1.CREATED_DTTM)) >= today()-17
           and
           
Solution
‎03-05-2018 12:32 AM
SAS Super FREQ
Posts: 820

Re: Where clause in Join Transformation

you can choose whether the Join transformation should use ON or just WHERE

 

Select the Join in the outline and go down to join properties, this is where you can set implicit = no, so that you use the ON for the join and WHERE for everything else

Capture.PNG

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 200 views
  • 4 likes
  • 3 in conversation