CASE on 1 field to query multiple fields

[mc1392]

I have 2 tables:
 

Quote

 

tableA

id | address1 | address2

 

Quote

 

tableB

id | address1 | address2

 

 

My goal:
 

Quote

 

if tableA.address1 is NULL then I want to select :

tableB.address1, tableB.address2

 

 

Quote

 

if tableB.address1 is NULL then I want to select:

tableA.address1, tableA.address2

 

 

I am having trouble getting to this result.

Any ideas?

[Barand]

Use the COALESCE() function

[mc1392]

42 minutes ago, Barand said:

Use the COALESCE() function

I've used COALESCE before, but I don't see how it applies to my situation?

Could provide a sample?

Should I COALESCE then use a CASE?

[Barand]

TABLE addya                                TABLE addyb
+----+-----------+-----------+             +----+-----------+-----------+
| id | address1  | address2  |             | id | address1  | address2  |
+----+-----------+-----------+             +----+-----------+-----------+
|  1 | add A 1 1 | add A 1 2 |             |  1 | add B 1 1 | add B 1 2 |
|  2 | add A 2 1 | add A 2 2 |             |  2 | NULL      | NULL      |
|  3 | NULL      | NULL      |             |  3 | add B 3 1 | add B 3 2 |
+----+-----------+-----------+             +----+-----------+-----------+

SELECT id
     , COALESCE(a.address1, b.address1) as add1
     , COALESCE(a.address2, b.address2) as add2
FROM addya a 
        JOIN
     addyb b USING(id);   

+----+-----------+-----------+
| id | add1      | add2      |
+----+-----------+-----------+
|  1 | add A 1 1 | add A 1 2 |
|  2 | add A 2 1 | add A 2 2 |
|  3 | add B 3 1 | add B 3 2 |
+----+-----------+-----------+

 

[benanamen]

Why do you have duplicate table structures? This points to a design flaw.

[Barand]

1 hour ago, benanamen said:

Why do you have duplicate table structures? This points to a design flaw.

Not necessarily

TABLE customer               TABLE customer_order
+--------------+             +------------------+
| customer_id  |             | order_no         |
| cust_name    |             | order_date       |
| address      |             | customer_id      |
+--------------+             | deliver_to       |
                             +------------------+ 

SELECT cust_name
     , order_no
     , order_date
     , COALESCE(deliver_to, address) as deliver_to
FROM customer_order o 
        JOIN
     customer c USING (customer_id)   

 

[benanamen]

3 hours ago, Barand said:

Not necessarily

When it comes to the forums I have never seen it not be.

OP, tell us about what you actually have going on rather than asking about your attempted solution to it.

[mc1392]

18 hours ago, benanamen said:

When it comes to the forums I have never seen it not be.

OP, tell us about what you actually have going on rather than asking about your attempted solution to it.

We have Accounts. Those accounts may or may not have an attached address record.

Currently, the report is using the attached address, but there is a request for me to use the address that is attached to the actual contribution/donation instead of the account.

FYI: speaking to the requester, we agreed on a totally separate report using the contribution address only.

 

I was finding it difficult to accommodate the request within the body of a very large Stored Procedure. If not for the Stored Procedure, This would be pretty easy.

Here is the stored procedure:
 

DROP Temporary TABLE IF EXISTS temp_fc;
create temporary table temp_fc as
  select odt.Id as OpportunityDonationTargetId, dt.Source__c as DonationTargetSource, dt.Name as DonationTarget, odt.Amount__c as Amount, op.CloseDate, op.Submit_Date__c as SubmitDate
      , acc.ATS_Account_ID__c as ATSID, case when acc.LastName is null then acc.Name else '' end as BusinessName
      , acc.Salutation, acc.FirstName, acc.Middle_Name__pc as MiddleName, acc.LastName, acc.Suffix__pc as Suffix
      , acc.Degree_Designation__pc as Designation, acc.PersonTitle, acc.PersonEmail, acc.Phone, acc.PersonHomePhone as HomePhone, acc.Fax
      , asm.Name as ToAssembly, odt.Message__c as Message
      , op.id as opportunityId, acc.PersonContactId, acc.Preferred_Mailing_Address__c as mailAddressId, opay.id as opportunityPaymentId
      , b.Batch_Code__c as OpportunityBatchCode, if(op.Is_Matching_Gift__c, 'Yes', 'No') as IsMatchingGift
      , facilitatorAcc.Name as FacilitatorName, op.Appeal_Code__c as AppealCode
    from sf.opportunity op
      join sf.opportunity_donation_target__c odt on odt.Opportunity__c = op.id
      join sf.donation_target__c dt on odt.Donation_Target__c = dt.id
      join sf.account acc on op.AccountId = acc.id
      join sf.opportunity_payment__c opay on opay.Opportunity__c = op.id
      left join sf.assembly__c asm on odt.Assembly__c = asm.id
      left join sf.batch__c b on b.id = opay.Batch__c
      left join sf.account facilitatorAcc on op.Facilitator__c = facilitatorAcc.Id
  where op.StageName = 'Posted'
      and op.CloseDate between startDate and endDate;
CREATE INDEX pid_indx ON temp_fc(PersonContactId);  #create index
CREATE INDEX oid_indx ON temp_fc(opportunityId);  #create index
CREATE INDEX mid_indx ON temp_fc(mailAddressId);  #create index
CREATE INDEX opid_indx ON temp_fc(opportunityPaymentId);  #create index

select fc.*
    , mailAddr.Institution__c as MailingInstitution, mailAddr.Department__c as MailingDepartment
    , mailAddr.Address_Line_1__c as MailingAddress1, mailAddr.Address_Line_2__c as MailingAddress2, mailAddr.Address_Line_3__c as MailingAddress3
    , mailAddr.City__c as MailingCity, mailAddr.State_Province__c as MailingState, mailAddr.ZIP_Postal_Code__c as MailingZip, c.Name as MailingCountryName
    , oc.Gift_Type__c as GiftType, oc.Personal_Message__c as PersonalMessage
    , oc.Tribute_Salutation__c as TributeSalutation, oc.Tribute_First_Name__c as TributeFirstName, oc.Tribute_Last_Name__c as TributeLastName, oc.Send_Acknowledgement__c as SendAcknowledgement
    , oc.Recipient__c as Recipient, oc.Recipient_Address_Line_1__c as RecipientAddress1, oc.Recipient_Address_Line_2__c as RecipientAddress2
    , oc.Recipient_City__c as RecipientCity, oc.Recipient_State_Province__c as RecipientState, oc.Recipient_ZIP_Postal_Code__c as RecipientZip, recipientC.Name as RecipientCountryName
    , group_concat(distinct asm.Name order by asm.Name) as PrimaryAssembly
  from temp_fc fc
    left join sf.online_contribution__c oc on oc.Opportunity_Payment__c = fc.opportunityPaymentId
    left join sf.country__c recipientC on recipientC.id = oc.Recipient_Country__c
    left join sf.address__c mailAddr on mailAddr.Id = fc.mailAddressId
    left join sf.country__c c on c.id = mailAddr.Country__c
    left join sf.contact_assembly__c ca on PersonContactId = ca.Contact__c and ca.Is_Primary__c = true
    left join sf.assembly__c asm on asm.id = ca.Assembly__c
group by fc.opportunityId, OpportunityDonationTargetId
order by DonationTargetSource, DonationTarget, FirstName;

DROP Temporary TABLE IF EXISTS temp_fc;
 
END

The part of the Stored Procedure to in question is:

select fc.*
    , mailAddr.Institution__c as MailingInstitution, mailAddr.Department__c as MailingDepartment
    , mailAddr.Address_Line_1__c as MailingAddress1, mailAddr.Address_Line_2__c as MailingAddress2, mailAddr.Address_Line_3__c as MailingAddress3
    , mailAddr.City__c as MailingCity, mailAddr.State_Province__c as MailingState, mailAddr.ZIP_Postal_Code__c as MailingZip, c.Name as MailingCountryName
    , oc.Gift_Type__c as GiftType, oc.Personal_Message__c as PersonalMessage
    , oc.Tribute_Salutation__c as TributeSalutation, oc.Tribute_First_Name__c as TributeFirstName, oc.Tribute_Last_Name__c as TributeLastName, oc.Send_Acknowledgement__c as SendAcknowledgement
    , oc.Recipient__c as Recipient, oc.Recipient_Address_Line_1__c as RecipientAddress1, oc.Recipient_Address_Line_2__c as RecipientAddress2
    , oc.Recipient_City__c as RecipientCity, oc.Recipient_State_Province__c as RecipientState, oc.Recipient_ZIP_Postal_Code__c as RecipientZip, recipientC.Name as RecipientCountryName
    , group_concat(distinct asm.Name order by asm.Name) as PrimaryAssembly
  from temp_fc fc
    left join sf.online_contribution__c oc on oc.Opportunity_Payment__c = fc.opportunityPaymentId
    left join sf.country__c recipientC on recipientC.id = oc.Recipient_Country__c
    left join sf.address__c mailAddr on mailAddr.Id = fc.mailAddressId
    left join sf.country__c c on c.id = mailAddr.Country__c
    left join sf.contact_assembly__c ca on PersonContactId = ca.Contact__c and ca.Is_Primary__c = true
    left join sf.assembly__c asm on asm.id = ca.Assembly__c
group by fc.opportunityId, OpportunityDonationTargetId
order by DonationTargetSource, DonationTarget, FirstName;

I wanted to alternate between using the address from mailAddr or online_contribution__c oc. I find it very difficult to accommodate that condition in the middle of the Stored Procedure.

[Barand]

, COALESCE(oc.recipient_address_line_1, mailAddr.Address_Line_1) as RecipientAddress1

etc

instead of the two address_Line_1 columns.

Do same for each address column.