SQL coalesce on multiple joins

[brulard]

hi,

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,

[ballardw]

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

should work.

[brulard]

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?

[brulard]

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

[ballardw]

---

@brulard wrote:
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).