SQL view subquery modification

Hi,

I have the following created

CREATE VIEW Finance.TransactionLog AS
SELECT
  'BANK' AS TransactionTargetTypeCode,
  'PAYMENT_RECEIVED' AS TransactionTypeCode,
  PY.PaymentID AS TransactionIdentificationID,
  PY.DepositDate AS TransactionDate,
  PY.Description AS TransactionTargetName,
  S.ExternalIdentifier AS TransactionTargetIdentificationID,
  PY.Amount AS TransactionAmount
FROM
  DATABASE.Payment PY
  INNER JOIN DATABASE.Sponsor S ON
    PY.SponsorID = S.SponsorID
    AND PY.SubType = 2 -- Sponsor
    AND PY.DeletedFlag = 0
    AND UPPER(PY.Description) IN ('BOA','CITIBANK') -- Be sure to modify list below as well
 
UNION ALL
 
SELECT
  'EMPLOYER' AS TransactionTargetTypeCode,
  'INVOICE' AS TransactionTypeCode,
  ST.StatementID AS TransactionIdentificationID,
  ST.InvoiceDate AS TransactionDate,
  S.Name AS TransactionTargetName,
  S.ExternalIdentifier AS TransactionTargetIdentificationID,
  SS.OutstandingBalance AS TransactionAmount
FROM
  DATABASE.Statements ST
  LEFT OUTER JOIN DATABASE.StatementSummary SS ON
    SS.StatementID = ST.StatementID  
    AND SS.DeletedFlag = 0
    AND ST.StatementTypeID = 2 -- Sponsor Invoice
  INNER JOIN DATABASE.Sponsor S ON
    ST.SponsorID = S.SponsorID
 
UNION ALL
 
SELECT
  'EMPLOYER' AS TransactionTargetTypeCode,
  'PAYMENT_RECEIVED' AS TransactionTypeCode,
  PY.PaymentID AS TransactionIdentificationID,
  PY.DepositDate AS TransactionDate,
  S.Name AS TransactionTargetName,
  S.ExternalIdentifier AS TransactionTargetIdentificationID,
  PY.Amount AS TransactionAmount
FROM
  DATABASE.Payment PY
  INNER JOIN DATABASE.Sponsor S ON
    PY.SponsorID = S.SponsorID
    AND PY.SubType = 2 -- Sponsor
    AND PY.DeletedFlag = 0
    AND UPPER(PY.Description) NOT IN ('BOA','CITIBANK') -- Be sure to modify list above as well
 
UNION ALL
 
SELECT
  'EMPLOYER' AS TransactionTargetTypeCode,
  'PAYMENT_POSTED' AS TransactionTypeCode,
  PY.PaymentID AS TransactionIdentificationID,
  PY.DepositDate AS TransactionDate,
  S.Name AS TransactionTargetName,
  S.ExternalIdentifier AS TransactionTargetIdentificationID,
  PAA.Amount AS TransactionAmount
FROM
  DATABASE.Payment PY
  INNER JOIN DATABASE.Sponsor S ON
    PY.SponsorID = S.SponsorID
    AND PY.SubType = 2 -- Sponsor
    AND PY.DeletedFlag = 0
  INNER JOIN DATABASE.PaymentApplyAllocation PAA ON
    PAA.PaymentID = PY.PaymentID
    AND PAA.DeletedFlag = 0
 
UNION ALL
 
SELECT
  'EMPLOYER' AS TransactionTargetTypeCode,
  'ADJUSTMENT' AS TransactionTypeCode,
  ADJ.Adjustment_ID AS TransactionIdentificationID,
  DATE(ADJ.Insert_TS) AS TransactionDate,
  S.Name AS TransactionTargetName,
  S.ExternalIdentifier AS TransactionTargetIdentificationID,
  ADJ.Adjustment_Amount AS TransactionAmount
FROM
  DATABASE.Adjustment ADJ  
  INNER JOIN DATABASE.Sponsor S ON
    ADJ.Entity_Identifier = S.ExternalIdentifier
    --AND ADJ.DeletedFlag = 0
    AND ADJ.Adjustment_Status_Ref_ID = 3
 
UNION ALL
 
SELECT
  'CARRIER' AS TransactionTargetTypeCode,
  'DISBURSEMENT' AS TransactionTypeCode,
  D.ID AS TransactionIdentificationID,
  D.Processing_Date AS TransactionDate,
  S.Name AS TransactionTargetName,
  S.ExternalIdentifier AS TransactionTargetIdentificationID,
  D.Amount AS TransactionAmount
FROM
  DATABASE.Disbursements D
  INNER JOIN DATABASE.Sponsor S ON
    D.Person_External_ID = S.ExternalIdentifier
    --AND D.DeletedFlag = 0
 
UNION ALL
 
SELECT
  'INDIVIDUAL' AS TransactionTargetTypeCode,
  'INVOICE' AS TransactionTypeCode,
  ST.StatementID AS TransactionIdentificationID,
  ST.InvoiceDate AS TransactionDate,
  CONCAT(P.FirstName,CONCAT(' ',P.LastName)) AS TransactionTargetName,
  P.ExternalIdentifier AS TransactionTargetIdentificationID,
  SS.OutstandingBalance AS TransactionAmount
FROM
  DATABASE.Statements ST
  INNER JOIN DATABASE.StatementSummary SS ON
    SS.StatementID = ST.StatementID  
    AND SS.DeletedFlag = 0
    AND ST.StatementTypeID = 1 -- Invididual Invoice
  INNER JOIN DATABASE.Person P ON
    ST.PersonID = P.PersonID
 
UNION ALL
 
SELECT
  'INDIVIDUAL' AS TransactionTargetTypeCode,
  'PAYMENT_RECEIVED' AS TransactionTypeCode,
  PY.PaymentID AS TransactionIdentificationID,
  PY.DepositDate AS TransactionDate,
  CONCAT(P.FirstName,CONCAT(' ',P.LastName)) AS TransactionTargetName,
  P.ExternalIdentifier AS TransactionTargetIdentificationID,
  PY.Amount AS TransactionAmount
FROM
  DATABASE.Payment PY
  INNER JOIN DATABASE.Person P ON
    PY.PersonID = P.PersonID
    AND PY.SubType = 6 -- Person
    AND PY.DeletedFlag = 0
 
UNION ALL
 
SELECT
  'INDIVIDUAL' AS TransactionTargetTypeCode,
  'PAYMENT_POSTED' AS TransactionTypeCode,
  PY.PaymentID AS TransactionIdentificationID,
  PY.DepositDate AS TransactionDate,
  CONCAT(P.FirstName,CONCAT(' ',P.LastName)) AS TransactionTargetName,
  P.ExternalIdentifier AS TransactionTargetIdentificationID,
  PAA.Amount AS TransactionAmount
FROM
  DATABASE.Payment PY
  INNER JOIN DATABASE.Person P ON
    PY.PersonID = P.PersonID
    AND PY.SubType = 6 -- Person
    AND PY.DeletedFlag = 0
  INNER JOIN DATABASE.PaymentApplyAllocation PAA ON
    PAA.PaymentID = PY.PaymentID
    AND PAA.DeletedFlag = 0
 
UNION ALL
 
SELECT
  'INDIVIDUAL' AS TransactionTargetTypeCode,
  'APTC' AS TransactionTypeCode,
  BLC.BLChargeID AS TransactionIdentificationID,
  DATE(BLSG.UpdateTS) AS TransactionDate,
  CONCAT(P.FirstName,CONCAT(' ',P.LastName)) AS TransactionTargetName,
  P.ExternalIdentifier AS TransactionTargetIdentificationID,
  BLC.Amount AS TransactionAmount
FROM DATABASE.BLCharge BLC
  INNER JOIN DATABASE.RateCharge RC ON
    BLC.RateChargeID = RC.RateChargeID
    AND RC.CalcCD = 17
    AND RC.DeletedFlag = 0
    AND BLC.DeletedFlag = 0
    AND BLC.Amount <> 0
  INNER JOIN DATABASE.BLStatementGroup BLSG ON
    BLC.BLStatementGroupID = BLSG.BLStatementGroupID
  INNER JOIN DATABASE.BLOperation BLO ON
    BLSG.BLOperationID = BLO.BLOperationID  
  INNER JOIN DATABASE.BLTransaction BLT ON
    BLO.BLTransactionID = BLT.BLTransactionID  
  INNER JOIN DATABASE.Person P ON
    BLT.PersonID = P.PersonID;  
 
 
CREATE VIEW Reports.FMSRPT03 AS
SELECT
  T.TransactionIdentificationID,
  T.TransactionTargetTypeCode,
  T.TransactionTypeCode,
  T.TransactionDate,
  T.TransactionTargetIdentificationID,
  T.TransactionTargetName,
  T.TransactionAmount,
  (CASE T.TransactionTypeCode WHEN 'INVOICE' THEN T.TransactionDate ELSE NULL END) AS InvoiceDate,
  (CASE T.TransactionTypeCode WHEN 'INVOICE' THEN T.TransactionIdentificationID ELSE NULL END) AS InvoiceReferenceID,
  (CASE T.TransactionTypeCode WHEN 'INVOICE' THEN T.TransactionAmount ELSE NULL END) AS InvoiceAmount,
  (CASE T.TransactionTypeCode WHEN 'APTC' THEN T.TransactionAmount ELSE NULL END) AS APTCAmount,
  (CASE T.TransactionTypeCode WHEN 'ADJUSTMENT' THEN T.TransactionAmount ELSE NULL END) AS AdjustmentAmount,
  (CASE T.TransactionTypeCode WHEN 'ADJUSTMENT' THEN T.TransactionDate ELSE NULL END) AS AdjustmentDate,
  (CASE T.TransactionTypeCode WHEN 'PAYMENT_RECEIVED' THEN T.TransactionAmount ELSE NULL END) AS PaymentReceivedAmount,
  (CASE T.TransactionTypeCode WHEN 'PAYMENT_RECEIVED' THEN T.TransactionDate ELSE NULL END) AS PaymentReceivedDate,
  (CASE T.TransactionTypeCode WHEN 'PAYMENT_POSTED' THEN T.TransactionAmount ELSE NULL END) AS PaymentPostedAmount,
  (CASE T.TransactionTypeCode WHEN 'PAYMENT_POSTED' THEN T.TransactionDate ELSE NULL END) AS PaymentPostedDate,
  (CASE T.TransactionTypeCode WHEN 'DISBURSEMENT' THEN T.TransactionAmount ELSE NULL END) AS DisbursementAmount,
  (CASE T.TransactionTypeCode WHEN 'DISBURSEMENT' THEN T.TransactionDate ELSE NULL END) AS DisbursementDate
FROM Finance.TransactionLog T;

 
My question is for APTC, i do not want to create a separate select, but no other tables in my union have a similiar field, so i have to create a separate view.but i want to include it in Individuals
kanneswaraAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SharathData EngineerCommented:
I do not understand the question completely. You have already included APTC in Finance.TransactionLog. Can you explain what exactly you are looking for.
By the way query 1 and 3 are same except the filter on UPPER(PY.Description). I have clubbed both of them in one SELECT clause with a CASE condition to display BANK or EMPLOYER.
CREATE VIEW Finance.TransactionLog 
AS 
  SELECT CASE 
           WHEN UPPER(PY.Description) IN ( 'BOA', 'CITIBANK' ) THEN 'BANK' 
           ELSE 'EMPLOYER' 
         END                  AS TransactionTargetTypeCode, 
         'PAYMENT_RECEIVED'   AS TransactionTypeCode, 
         PY.PaymentID         AS TransactionIdentificationID, 
         PY.DepositDate       AS TransactionDate, 
         PY.Description       AS TransactionTargetName, 
         S.ExternalIdentifier AS TransactionTargetIdentificationID, 
         PY.Amount            AS TransactionAmount 
    FROM database.Payment PY 
         INNER JOIN database.Sponsor S 
                 ON PY.SponsorID = S.SponsorID 
                    AND PY.SubType = 2 -- Sponsor  
                    AND PY.DeletedFlag = 0 
                    AND PY.Description IS NOT NULL 
  UNION ALL 
  SELECT 'EMPLOYER'            AS TransactionTargetTypeCode, 
         'INVOICE'             AS TransactionTypeCode, 
         ST.StatementID        AS TransactionIdentificationID, 
         ST.InvoiceDate        AS TransactionDate, 
         S.Name                AS TransactionTargetName, 
         S.ExternalIdentifier  AS TransactionTargetIdentificationID, 
         SS.OutstandingBalance AS TransactionAmount 
    FROM database.Statements ST 
         LEFT OUTER JOIN database.StatementSummary SS 
                      ON SS.StatementID = ST.StatementID 
                         AND SS.DeletedFlag = 0 
                         AND ST.StatementTypeID = 2 -- Sponsor Invoice  
         INNER JOIN database.Sponsor S 
                 ON ST.SponsorID = S.SponsorID 
  UNION ALL 
  SELECT 'EMPLOYER'           AS TransactionTargetTypeCode, 
         'PAYMENT_POSTED'     AS TransactionTypeCode, 
         PY.PaymentID         AS TransactionIdentificationID, 
         PY.DepositDate       AS TransactionDate, 
         S.Name               AS TransactionTargetName, 
         S.ExternalIdentifier AS TransactionTargetIdentificationID, 
         PAA.Amount           AS TransactionAmount 
    FROM database.Payment PY 
         INNER JOIN database.Sponsor S 
                 ON PY.SponsorID = S.SponsorID 
                    AND PY.SubType = 2 -- Sponsor  
                    AND PY.DeletedFlag = 0 
         INNER JOIN database.PaymentApplyAllocation PAA 
                 ON PAA.PaymentID = PY.PaymentID 
                    AND PAA.DeletedFlag = 0 
  UNION ALL 
  SELECT 'EMPLOYER'            AS TransactionTargetTypeCode, 
         'ADJUSTMENT'          AS TransactionTypeCode, 
         ADJ.Adjustment_ID     AS TransactionIdentificationID, 
         DATE(ADJ.Insert_TS)   AS TransactionDate, 
         S.Name                AS TransactionTargetName, 
         S.ExternalIdentifier  AS TransactionTargetIdentificationID, 
         ADJ.Adjustment_Amount AS TransactionAmount 
    FROM database.Adjustment ADJ 
         INNER JOIN database.Sponsor S 
                 ON ADJ.Entity_Identifier = S.ExternalIdentifier 
                    --AND ADJ.DeletedFlag = 0  
                    AND ADJ.Adjustment_Status_Ref_ID = 3 
  UNION ALL 
  SELECT 'CARRIER'            AS TransactionTargetTypeCode, 
         'DISBURSEMENT'       AS TransactionTypeCode, 
         D.ID                 AS TransactionIdentificationID, 
         D.Processing_Date    AS TransactionDate, 
         S.Name               AS TransactionTargetName, 
         S.ExternalIdentifier AS TransactionTargetIdentificationID, 
         D.Amount             AS TransactionAmount 
    FROM database.Disbursements D 
         INNER JOIN database.Sponsor S 
                 ON D.Person_External_ID = S.ExternalIdentifier 
  --AND D.DeletedFlag = 0  
  UNION ALL 
  SELECT 'INDIVIDUAL'                                 AS TransactionTargetTypeCode, 
         'INVOICE'                                    AS TransactionTypeCode, 
         ST.StatementID                               AS TransactionIdentificationID, 
         ST.InvoiceDate                               AS TransactionDate, 
         CONCAT(P.FirstName, CONCAT(' ', P.LastName)) AS TransactionTargetName, 
         P.ExternalIdentifier                         AS TransactionTargetIdentificationID, 
         SS.OutstandingBalance                        AS TransactionAmount 
    FROM database.Statements ST 
         INNER JOIN database.StatementSummary SS 
                 ON SS.StatementID = ST.StatementID 
                    AND SS.DeletedFlag = 0 
                    AND ST.StatementTypeID = 1 -- Invididual Invoice  
         INNER JOIN database.Person P 
                 ON ST.PersonID = P.PersonID 
  UNION ALL 
  SELECT 'INDIVIDUAL'                                 AS TransactionTargetTypeCode, 
         'PAYMENT_RECEIVED'                           AS TransactionTypeCode, 
         PY.PaymentID                                 AS TransactionIdentificationID, 
         PY.DepositDate                               AS TransactionDate, 
         CONCAT(P.FirstName, CONCAT(' ', P.LastName)) AS TransactionTargetName, 
         P.ExternalIdentifier                         AS TransactionTargetIdentificationID, 
         PY.Amount                                    AS TransactionAmount 
    FROM database.Payment PY 
         INNER JOIN database.Person P 
                 ON PY.PersonID = P.PersonID 
                    AND PY.SubType = 6 -- Person  
                    AND PY.DeletedFlag = 0 
  UNION ALL 
  SELECT 'INDIVIDUAL'                                 AS TransactionTargetTypeCode, 
         'PAYMENT_POSTED'                             AS TransactionTypeCode, 
         PY.PaymentID                                 AS TransactionIdentificationID, 
         PY.DepositDate                               AS TransactionDate, 
         CONCAT(P.FirstName, CONCAT(' ', P.LastName)) AS TransactionTargetName, 
         P.ExternalIdentifier                         AS TransactionTargetIdentificationID, 
         PAA.Amount                                   AS TransactionAmount 
    FROM database.Payment PY 
         INNER JOIN database.Person P 
                 ON PY.PersonID = P.PersonID 
                    AND PY.SubType = 6 -- Person  
                    AND PY.DeletedFlag = 0 
         INNER JOIN database.PaymentApplyAllocation PAA 
                 ON PAA.PaymentID = PY.PaymentID 
                    AND PAA.DeletedFlag = 0 
  UNION ALL 
  SELECT 'INDIVIDUAL'                                 AS TransactionTargetTypeCode, 
         'APTC'                                       AS TransactionTypeCode, 
         BLC.BLChargeID                               AS TransactionIdentificationID, 
         DATE(BLSG.UpdateTS)                          AS TransactionDate, 
         CONCAT(P.FirstName, CONCAT(' ', P.LastName)) AS TransactionTargetName, 
         P.ExternalIdentifier                         AS TransactionTargetIdentificationID, 
         BLC.Amount                                   AS TransactionAmount 
    FROM database.BLCharge BLC 
         INNER JOIN database.RateCharge RC 
                 ON BLC.RateChargeID = RC.RateChargeID 
                    AND RC.CalcCD = 17 
                    AND RC.DeletedFlag = 0 
                    AND BLC.DeletedFlag = 0 
                    AND BLC.Amount <> 0 
         INNER JOIN database.BLStatementGroup BLSG 
                 ON BLC.BLStatementGroupID = BLSG.BLStatementGroupID 
         INNER JOIN database.BLOperation BLO 
                 ON BLSG.BLOperationID = BLO.BLOperationID 
         INNER JOIN database.BLTransaction BLT 
                 ON BLO.BLTransactionID = BLT.BLTransactionID 
         INNER JOIN database.Person P 
                 ON BLT.PersonID = P.PersonID; 

CREATE VIEW Reports.FMSRPT03
AS 
  SELECT T.TransactionIdentificationID, 
         T.TransactionTargetTypeCode, 
         T.TransactionTypeCode, 
         T.TransactionDate, 
         T.TransactionTargetIdentificationID, 
         T.TransactionTargetName, 
         T.TransactionAmount, 
         ( CASE T.TransactionTypeCode 
             WHEN 'INVOICE' THEN T.TransactionDate 
             ELSE NULL 
           END ) AS InvoiceDate, 
         ( CASE T.TransactionTypeCode 
             WHEN 'INVOICE' THEN T.TransactionIdentificationID 
             ELSE NULL 
           END ) AS InvoiceReferenceID, 
         ( CASE T.TransactionTypeCode 
             WHEN 'INVOICE' THEN T.TransactionAmount 
             ELSE NULL 
           END ) AS InvoiceAmount, 
         ( CASE T.TransactionTypeCode 
             WHEN 'APTC' THEN T.TransactionAmount 
             ELSE NULL 
           END ) AS APTCAmount, 
         ( CASE T.TransactionTypeCode 
             WHEN 'ADJUSTMENT' THEN T.TransactionAmount 
             ELSE NULL 
           END ) AS AdjustmentAmount, 
         ( CASE T.TransactionTypeCode 
             WHEN 'ADJUSTMENT' THEN T.TransactionDate 
             ELSE NULL 
           END ) AS AdjustmentDate, 
         ( CASE T.TransactionTypeCode 
             WHEN 'PAYMENT_RECEIVED' THEN T.TransactionAmount 
             ELSE NULL 
           END ) AS PaymentReceivedAmount, 
         ( CASE T.TransactionTypeCode 
             WHEN 'PAYMENT_RECEIVED' THEN T.TransactionDate 
             ELSE NULL 
           END ) AS PaymentReceivedDate, 
         ( CASE T.TransactionTypeCode 
             WHEN 'PAYMENT_POSTED' THEN T.TransactionAmount 
             ELSE NULL 
           END ) AS PaymentPostedAmount, 
         ( CASE T.TransactionTypeCode 
             WHEN 'PAYMENT_POSTED' THEN T.TransactionDate 
             ELSE NULL 
           END ) AS PaymentPostedDate, 
         ( CASE T.TransactionTypeCode 
             WHEN 'DISBURSEMENT' THEN T.TransactionAmount 
             ELSE NULL 
           END ) AS DisbursementAmount, 
         ( CASE T.TransactionTypeCode 
             WHEN 'DISBURSEMENT' THEN T.TransactionDate 
             ELSE NULL 
           END ) AS DisbursementDate 
    FROM finance.TransactionLog T; 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB Dev Tools

From novice to tech pro — start learning today.