Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2017
    Posts
    1

    Unanswered: Pivoting tables in DB2 v9

    We have an MS SQL server db that is being migrated to a DB2 db. So all associated queries have to be modified, if necessary, to run in DB2. We have one query that uses a "PIVOT". Researching this forum verifies that PIVOT is not supported in DB2, but I've seen information saying that the CASE or DECODE functions should work.

    Only problem is that we can't get it to work, so thought i'd post a snippet of the code to see if anyone has an idea of why this isn't working.

    select STG1.ProposalId,STG1.SFDCId,STG2.QuoteId,STG2.MEDI CAL,STG2.DENTAL,STG2.VISION,STG2.TotalPremium
    from
    (select max(ProposalQuote.ProposalId) as ProposalId,
    Demographic.CustomerRefNumber SFDCId
    from
    Demographic DEMOGRAPHIC ,
    Quote QUOTE ,
    QuoteLineItem ,
    ProposalQuote ,
    Proposal
    where Demographic.CustomerRefNumber is not null
    and DEMOGRAPHIC.DemographicId=QUOTE.DemographicId
    and QUOTE.QuoteId = QuoteLineItem.QuoteId
    and QUOTE.QuoteId = ProposalQuote.QuoteId
    and ProposalQuote.ProposalId =Proposal.ProposalId
    and Proposal.Status in ('APP_STARTED', 'CREATED','SENT_TO_CUST')
    group by Demographic.CustomerRefNumber ) STG1 ,
    ( select PP.ProposalId,PP.SFDCId,PP.QuoteId,PP.MEDICAL,PP.D ENTAL,PP.VISION,TT.TotalPremium
    from (
    select ProposalId,SFDCId,QuoteId,MEDICAL,DENTAL,VISION
    from (
    select ProposalId,SFDCId,QuoteId,Type,PlanMarketingName
    from (
    select
    Demographic.CustomerRefNumber SFDCId,
    QuoteLineItem.ContractCode,
    QuoteLineItem.Type,
    QUOTE.QuoteId,
    ProposalQuote.ProposalId,
    PlanMarketingName
    from
    Demographic DEMOGRAPHIC ,
    Quote QUOTE ,
    QuoteLineItem ,
    ProposalQuote ,
    Contract ,
    Proposal
    where Demographic.CustomerRefNumber is not null
    and DEMOGRAPHIC.DemographicId=QUOTE.DemographicId
    and QUOTE.QuoteId = QuoteLineItem.QuoteId
    and QUOTE.QuoteId = ProposalQuote.QuoteId
    and QuoteLineItem.ContractCode= Contract.ContractCode
    and ProposalQuote.ProposalId =Proposal.ProposalId
    and Proposal.Status in ('APP_STARTED', 'CREATED','SENT_TO_CUST')
    group by Demographic.CustomerRefNumber,
    QuoteLineItem.ContractCode,
    QuoteLineItem.Type,
    QUOTE.QuoteId,
    ProposalQuote.ProposalId,
    PlanMarketingName)X
    )G
    pivot
    (
    max(G.PlanMarketingName)
    for G.type in (MEDICAL,DENTAL,VISION)
    ) piv
    ) PP
    ,
    (select
    QUOTE.QuoteId,
    ProposalQuote.ProposalId,
    QUOTE.TotalPremium
    from Quote QUOTE ,
    ProposalQuote ProposalQuote
    where QUOTE.QuoteId = ProposalQuote.QuoteId
    group by QUOTE.QuoteId,
    ProposalQuote.ProposalId,
    QUOTE.TotalPremium ) TT
    where PP.QuoteId=TT.QuoteId
    and PP.ProposalId=TT.ProposalId ) STG2
    where STG1.ProposalId = STG2.ProposalId


    Before the Pivot, the result set looks like this:
    ProposalId SFDCId QuoteId Type PlanMarketingName
    1120 001120B 4544343 DENTAL Prime B
    1120 001120B 4544343 MEDICAL Blue Way HMO 5/20%

    After the Pivot, the result set should like this:
    ProposalId SFDCid QuoteId MEDICAL DENTAL VISION TotalPremium
    1119 001119A 4545644 Blue Way HMO 5/20% NULL NULL 11.17
    1120 001120B 4544343 Bronze Way Preferred 7/0% Prime B NULL 94.39

    So we then substituted this for the SQL code in red bold type in original query above
    SELECT C2.PlanMarketingName,
    max(DECODE(C2.PlanMarketingName,'1',QL2.Type)) As MEDICAL,
    max(DECODE(C2.PlanMarketingName,'2',QL2.Type)) As DENTAL,
    max(DECODE(C2.PlanMarketingName,'3',QL2.Type)) As VISION
    FROM Contract C2, QuoteLineItem QL2
    where QL2.ContractCode = C2.ContractCode
    group by C2.PlanMarketingName

    But what we get is this:
    ProposalId SFDCid QuoteId MEDICAL DENTAL VISION TotalPremium
    1119 001119A 4545644 Blue Way HMO 5/20% NULL NULL


    Tables have following data:
    Demographic
    CustomerRefNumber Demographic ID
    001119A 1
    001120B A

    Quote
    Quote ID Demographic ID
    4545644 1
    4544343 A

    QuoteLineItem
    QuoteID Type ContractCode
    4545644 Medical 9
    4544343 Medical 9
    4544343 Dental 10


    ProposalQuote
    ProposalID QuoteID
    1119 4545644
    1120 4544343

    Proposal
    ProposalID Status
    1119 APP_STARTED
    1120 APP_STARTED

    Contract
    ContractCode PlanMarketingName
    9 Prime B
    10 Blue Way HMO 5/20%

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,539
    Provided Answers: 11
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •