08-28-2017 04:59 PM
When using the coalesce function, i'd like to know how to refer back to variable that I am coalescing, in terms of linking the coalesced variable, to the target right table (a.id=c.id in a typical join, but if a.id is now the coalesce(a.id,b.id), how to name this variable in the join criteria?
Similar question on how to name the 'order by' variable when this variable is the one coalesced.
Thanks in advance,
08-28-2017 05:37 PM
Since Join criteria involves comparing variables in two different tables (or at least aliases) the coalesce you are proposing can't exist before the join. Just because you have select a.id it does not mean that you are are replacing the ID value in table a. It would be going to the table on the Create table if you have one, if not it goes to the result view.
If you use a variable just created in a query then the predicate CALCULATED is used to indicate that you are referencing the just created value. So if named the coalesced value on the Select statement as C_id
order by calculated C_id
08-29-2017 09:02 AM - edited 08-29-2017 09:04 AM
Yeah, I had tried using Calculated but got error : "the following were not found as CALCULATED references in the immediate query".
Proc sql; create table b AS SELECT DISTINCT COALESCE(A.ID,B.ID) AS ACCT_ID, COALESCE(A.ACCT_NUM, B.ACCT_NUM) AS ACCT_NUM, A.PRD_CD AS PRODUCT, A.OPRTR_ID AS PTS, A.EVENT_SCRN_ID AS SCREEN, A.EVENT_DT AS DATE, A.TIME, A.EVENT_TP_CD AS CODE, A.FROM_EMPL_NAME, A.TO_EMPL_NAME, A.FROM_EMPL_ADDRESS, A.TO_EMPL_ADDRESS, B.FROM_JOB_TITLE, B.TO_JOB_TITLE, b.EVENT_TP_CD AS CODE_JOB_TITLE, B.time AS TIME_JOB_TITLE, b.oprtr_id AS PTS_JOB_TITLE, B.EVENT_SCRN_ID AS SCREEN_JOB_TITLE, C.crnt_bal_amt FROM B0 AS A full JOIN B1 AS b ON A.ACCT_NUM=B.ACCT_NUM AND A.EVENT_DT=B.EVENT_DT AND A.EVENT_TP_CD='055' AND B.EVENT_TP_CD='054' INNER JOIN ice.snapshot C ON calculated acct_id=C.ID AND C.CUST_DATA_43=''; quit;
I've highlighted the two lines of concern in green (the second line, and the one prior to the very last line). Should I instead make the join condition: on a.id or b.id=c.id?
08-29-2017 02:43 PM
08-29-2017 05:28 PM
Ok, so I split the query into two. In the first segment, was able to use coalesce and end with "order by calculated ...". Still unsure why i am getting error when using calculated in the join criteria, but ultimately am getting the result i need after splitting my multiple joins.Thanks again
As I understand it any variable mentioned on the join must exist in one of the source tables or subqueries. You can apply functions to individual variables such as substr(a.var,2,4) but your Calculated value does not exist until after the join is completed in effect.
So you get an error message that the calculated variable doesn't exist (yet).