You are viewing this site as a guest. You can still ask questions and help others! Join our Dev2Dev IT Community to receive your own blog, share your knowledge, and much more.

Coalesce?

8 replies [Last post]
Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25

Hello again,

I have been trying to pull employee address data in a query. Employees will always have a Home address, but some will also have a Mailing address.

The problem is that I must pull only one address per employee, selecting the Mailing address first - if it exists. If it does not exist, then pull the Home address. I know that I can work all of that out once I send the data to Excel (just delete the HOME address for those who also have a MAIL address, but that is extra work that I hope can be eliminated.

What I have tried, and failed with, is the following on the Criteria Tab in Query 8.8:

LEFT SIDE is... ADDRESS_TYPE

CONDITION is... equal to

RIGHT SIDE is an Expression... COALESCE('MAIL','HOME')

When I run the query, I only get those employees with a MAIL address. Those with HOME addresses only are not returned.

Am I missing something here (like, you cannot do that in Query) or am I using the wrong function? The wrong syntax?

Help?

Please. :-)

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Coalesce?

Ed-D hello again,

Your query might sound easy to do but it is actually not. Not sure how you can do it in PS Query. Your Expression... COALESCE('MAIL','HOME') wont work. I don't think you can use COALESCE in a "Where" clause, you will have to use it up at the "Select" level (wont work for what you want anyway). Your results only have the mail address type cause this is what your coalesce is doing (if "MAIL" not null then MAIL) so it is always mail.

Anyway, here is what I've done to get the results you are looking for.

SELECT *
  FROM ps_addresses a
 WHERE a.address_type =
    DECODE ((SELECT MAX (DECODE (b.address_type, 'MAIL', 2, 1))
             FROM ps_addresses b
             WHERE a.emplid = b.emplid), 2, 'MAIL',  'HOME'  );

This might cause problems if your data has address type of anything other than MAIL or HOME. It will still return the correct number of rows and correct addresses but it will report for example an "OTHER" address type as "HOME" type.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Coalesce?

Hi CompShack!

Unfortunately, Query told me when I tried this that From clauses are not allowed in Expressions. Sigh...

I suppose I could just use two separate queries instead. But it would have been nice to have it all done at one time. "One stop shopping," so to speak.

I do thank you very much for trying though. I really do appreciate your efforts.

Best Regards,

Ed

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Coalesce?

Ya, unfortunately I can't think of a way to get this to work in a query based type of thing. Your development team can run this to you though (or even you) in SQL Developer, SQL Plus, or Toad to mention a few.

That being said though, I think we can find a better solution if you don't need all the fields from the table! Do you? if not, what are the ones that you are going after?

Take care and talk to you later!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Coalesce?

Hi CompShack!

I do not need all of the fields from the ADDRESSES Table, just these:

ADDRESS_TYPE
ADDRESS_1
ADDRESS_2
CITY
STATE
POSTAL

I do not necessarily need the ADDRESS_TYPE Field returned, but it would be nice.

If you can come up with a solution, I will be ecstatic!

Thanks!

Ed

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Coalesce?

Ok ED - I got it working using the Query Manager.

It is a little complex (you need to use subquery option and not expression). Here is what you need to do.

Start fresh with the addresses table and add a criteria of address_type = 'MAIL'. Now we need to add the subquery (this is the select ... from that your expression wont allow).
Go to the criteria tab and select add criteria then in the Expression 1 Type box check Expression, then in Expression 2 Type check Subquery.
On the Condition Type drop down select does not exist and then Edit/Define Subquery. When asked for a record, choose Addresses again. Now go to the criteria tab and add a new one where address_type = 'MAIL'. (make sure you take the field with alias B for example B.address_type). When all done, click on Subquery/Union Navigation then select Top Level of Query, then go back to the criteria tab and change the "AND" on the "do not exist" row to "OR".

That's it - run your query and you should get what you want. Here is the SQL after you do all the above (this was generated by query manager)!

SELECT a.emplid, a.address_type, a.address1, a.address2, a.city, a.state,
       a.postal
  FROM ps_addresses a
 WHERE     a.effdt =
              (SELECT MAX (a_ed.effdt)
                 FROM ps_addresses a_ed
                WHERE a.emplid = a_ed.emplid
                  AND a.address_type = a_ed.address_type
                  AND a_ed.effdt <= SYSDATE)
       AND a.address_type = 'MAIL'
    OR NOT EXISTS (
          SELECT 'X'
            FROM ps_addresses b
           WHERE b.effdt =
                    (SELECT MAX (b_ed.effdt)
                       FROM ps_addresses b_ed
                      WHERE b.emplid = b_ed.emplid
                        AND b.address_type = b_ed.address_type
                        AND b_ed.effdt <= SYSDATE)
             AND b.address_type = 'MAIL');

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Coalesce?

Hi again,

Thanks for the details on this. I think I may have missed something though. At first the query just timed out when I ran it. Then I grouped the two criteria which use the Logical 'OR' and data was returned. Unfortunately, it was only employees with 'MAIL' type of address.

Don't know if it is me, or just the PSoft setup that I have to use. If you would not mind having another look, here are the query details:

Table A = the EMPLOYEES Table (actually an alternate Employees table with sensitive data removed)
Table B = ADDRESSES
Table C = ADDRESSES

CRITERIA
A.EFFDT <= Current Date
AND (A.ADDRESS_TYPE equal to MAIL
OR does not exist SUBQUERY)
AND A.EMPLID equal to C.EMPLID
AND C.EFFDT <= Current Date(EffSeq = Last)
AND C.LOCATION equal to 00006
AND C.EMPL_STATUS in list ('A','L','P')

Subquery Criteria
B.EFFDT <= Current Date
AND B.ADDRESS_TYPE equal to MAIL

*** Tried using Using ADDRESS_TYPE, and then EMPLID as the output field in the subquery and got same results - 'MAIL' only.

Query SQL

SELECT A.EMPLID, A.ADDRESS_TYPE, A.ADDRESS1, A.ADDRESS2, A.CITY, A.STATE, A.POSTAL, C.NAME
  FROM PS_ADDRESSES A, PS_AMX_EMPLOYEES_V C, PS_FAST_PERSGL_VW1 C1
  WHERE C.EMPLID = C1.EMPLID
    AND C1.ROWSECCLASS = 'USA&LOB'
    AND ( A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_ADDRESSES A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.ADDRESS_TYPE = A_ED.ADDRESS_TYPE
          AND A_ED.EFFDT <= SYSDATE)
     AND ( A.ADDRESS_TYPE = 'MAIL'
     OR NOT EXISTS (SELECT B.EMPLID
  FROM PS_ADDRESSES B
  WHERE B.EFFDT =
        (SELECT MAX(B_ED.EFFDT) FROM PS_ADDRESSES B_ED
        WHERE B.EMPLID = B_ED.EMPLID
          AND B.ADDRESS_TYPE = B_ED.ADDRESS_TYPE
          AND B_ED.EFFDT <= SYSDATE)
     AND B.ADDRESS_TYPE = 'MAIL'))
     AND A.EMPLID = C.EMPLID
     AND C.EFFDT =
        (SELECT MAX(C_ED.EFFDT) FROM PS_AMX_EMPLOYEES_V C_ED
        WHERE C.EMPLID = C_ED.EMPLID
          AND C.EMPL_RCD = C_ED.EMPL_RCD
          AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ =
        (SELECT MAX(C_ES.EFFSEQ) FROM PS_AMX_EMPLOYEES_V C_ES
        WHERE C.EMPLID = C_ES.EMPLID
          AND C.EMPL_RCD = C_ES.EMPL_RCD
          AND C.EFFDT = C_ES.EFFDT)
     AND C.LOCATION = '00006'
     AND C.EMPL_STATUS IN ('A','L','P') )
  ORDER BY 8

CompShack's picture
User offline. Last seen 5 years 27 weeks ago. Offline
Joined: 12/09/2007
Posts: 167
Re: Coalesce?

Ed, I reformatted your SQL so we can view it better and found the problem!
Right after the line were it says
AND B.ADDRESS_TYPE = 'MAIL' you need to add AND a.emplid = b.emplid. To do that, you need to access the "subquery" and add the criteria AND a.emplid = b.emplid.

So this:

AND B_ED.EFFDT <= SYSDATE)
     AND B.ADDRESS_TYPE = 'MAIL'))

should be:
AND B_ED.EFFDT <= SYSDATE)
     AND B.ADDRESS_TYPE = 'MAIL'
     AND a.emplid = b.emplid))

This should solve your problem. Cheers!

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Ed-D's picture
User offline. Last seen 8 years 42 weeks ago. Offline
Joined: 05/14/2008
Posts: 25
Re: Coalesce?

CompShack,

WOW! This works perfectly!

You have just helped make my life a lot easier. Thank you SO MUCH for all of your time and assistance.

You are AWESOME!!!!!!!!!!

I am taking a week off from work for some R&R, and I was able to get out a report using this code before I left today.

Thank you again!

Best Regards,

Ed