• Replies 8 replies
  • Subscribers 338 subscribers
  • Views 59 views
  • Users 0 members are here
Related

After upgrade to 12.1 sql query not working for report

Ed Snavely
$organization

After upgrading from Tessitura 11 to 12.1 one of our reports is not functioning. I have narrowed it down to a sql table that is returning a null value, for T_PMAP.gl_hold_no but can view  the table and have verify that values do exist. Does anyone have an idea what might be wrong with the sql statement below?

 

SELECT T_PERF.perf_code,
T_PERF.perf_dt,

T_PMAP.gl_hold_no,
T_TRANSACTION.trn_amt,
T_BATCH.post_no

FROM T_TRANSACTION

LEFT OUTER JOIN T_FEE ON T_TRANSACTION.fee_no = T_FEE.fee_no
LEFT OUTER JOIN T_FUND ON T_TRANSACTION.fund_no = T_FUND.fund_no
LEFT OUTER JOIN T_PMAP ON T_TRANSACTION.pmap_no = T_PMAP.pmap_no


JOIN T_CAMPAIGN ON T_TRANSACTION.campaign_no = T_CAMPAIGN.campaign_no
LEFT OUTER JOIN T_BATCH ON T_TRANSACTION.batch_no = T_BATCH.batch_no
LEFT OUTER JOIN TR_BATCH_TYPE ON T_BATCH.batch_type = TR_BATCH_TYPE.id
LEFT OUTER JOIN T_PERF ON T_TRANSACTION.perf_no = T_PERF.perf_no


WHERE T_TRANSACTION.perf_no>0 AND T_BATCH.post_no = 2382

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Parents
  • Former Member
    Former Member $organization

    You are welcome. I should have mentioned that this is a stopgap solution. The long term solution is replacing the t_pmap table by the new pricing tables as suggested by Paul’s post referred by Chris. I’m not sure the bridging tables will be maintained forever by Tessitura.

     

    Fernando Margueirat
    Business Analyst
    The National Ballet of Canada
    470 Queens Quay West
    Toronto, Ontario
    M5V 3K4
    P: 416 345 9686 x453
    F: 416 345 8323

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ed Snavely
    Sent: October-03-14 9:18 AM
    To: Fernando Margueirat
    Subject: RE: [Tessitura Technical Forum] After upgrade to 12.1 sql query not working for report

     

    This worked !!!!!!

    Thank you

    From: Fernando Margueirat <bounce-fernandomargueirat4910@tessituranetwork.com>
    Sent: 10/2/2014 9:10:40 AM

    Ed

     

    All information from t_pamp was migrated to other table(s) for v. 12. You should replace all your references to t_pmap to the bridge view VB_PMAP in order for them to work correctly.

     

     

    Fernando Margueirat
    Business Analyst
    The National Ballet of Canada
    470 Queens Quay West
    Toronto, Ontario
    M5V 3K4
    P: 416 345 9686 x453
    F: 416 345 8323

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ed Snavely
    Sent: October-01-14 10:02 PM
    To: Fernando Margueirat
    Subject: [Tessitura Technical Forum] After upgrade to 12.1 sql query not working for report

     

    After upgrading from Tessitura 11 to 12.1 one of our reports is not functioning. I have narrowed it down to a sql table that is returning a null value, for T_PMAP.gl_hold_no but can view  the table and have verify that values do exist. Does anyone have an idea what might be wrong with the sql statement below?

     

    SELECT T_PERF.perf_code,
    T_PERF.perf_dt,

    T_PMAP.gl_hold_no,
    T_TRANSACTION.trn_amt,
    T_BATCH.post_no

    FROM T_TRANSACTION

    LEFT OUTER JOIN T_FEE ON T_TRANSACTION.fee_no = T_FEE.fee_no
    LEFT OUTER JOIN T_FUND ON T_TRANSACTION.fund_no = T_FUND.fund_no
    LEFT OUTER JOIN T_PMAP ON T_TRANSACTION.pmap_no = T_PMAP.pmap_no


    JOIN T_CAMPAIGN ON T_TRANSACTION.campaign_no = T_CAMPAIGN.campaign_no
    LEFT OUTER JOIN T_BATCH ON T_TRANSACTION.batch_no = T_BATCH.batch_no
    LEFT OUTER JOIN TR_BATCH_TYPE ON T_BATCH.batch_type = TR_BATCH_TYPE.id
    LEFT OUTER JOIN T_PERF ON T_TRANSACTION.perf_no = T_PERF.perf_no


    WHERE T_TRANSACTION.perf_no>0 AND T_BATCH.post_no = 2382

     

     

     

     

     

     

     

     

     

     

     

     

     

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Reply
  • Former Member
    Former Member $organization

    You are welcome. I should have mentioned that this is a stopgap solution. The long term solution is replacing the t_pmap table by the new pricing tables as suggested by Paul’s post referred by Chris. I’m not sure the bridging tables will be maintained forever by Tessitura.

     

    Fernando Margueirat
    Business Analyst
    The National Ballet of Canada
    470 Queens Quay West
    Toronto, Ontario
    M5V 3K4
    P: 416 345 9686 x453
    F: 416 345 8323

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ed Snavely
    Sent: October-03-14 9:18 AM
    To: Fernando Margueirat
    Subject: RE: [Tessitura Technical Forum] After upgrade to 12.1 sql query not working for report

     

    This worked !!!!!!

    Thank you

    From: Fernando Margueirat <bounce-fernandomargueirat4910@tessituranetwork.com>
    Sent: 10/2/2014 9:10:40 AM

    Ed

     

    All information from t_pamp was migrated to other table(s) for v. 12. You should replace all your references to t_pmap to the bridge view VB_PMAP in order for them to work correctly.

     

     

    Fernando Margueirat
    Business Analyst
    The National Ballet of Canada
    470 Queens Quay West
    Toronto, Ontario
    M5V 3K4
    P: 416 345 9686 x453
    F: 416 345 8323

     

     

     

    From: Tessitura Technical Forum [mailto:forums-technical@tessituranetwork.com] On Behalf Of Ed Snavely
    Sent: October-01-14 10:02 PM
    To: Fernando Margueirat
    Subject: [Tessitura Technical Forum] After upgrade to 12.1 sql query not working for report

     

    After upgrading from Tessitura 11 to 12.1 one of our reports is not functioning. I have narrowed it down to a sql table that is returning a null value, for T_PMAP.gl_hold_no but can view  the table and have verify that values do exist. Does anyone have an idea what might be wrong with the sql statement below?

     

    SELECT T_PERF.perf_code,
    T_PERF.perf_dt,

    T_PMAP.gl_hold_no,
    T_TRANSACTION.trn_amt,
    T_BATCH.post_no

    FROM T_TRANSACTION

    LEFT OUTER JOIN T_FEE ON T_TRANSACTION.fee_no = T_FEE.fee_no
    LEFT OUTER JOIN T_FUND ON T_TRANSACTION.fund_no = T_FUND.fund_no
    LEFT OUTER JOIN T_PMAP ON T_TRANSACTION.pmap_no = T_PMAP.pmap_no


    JOIN T_CAMPAIGN ON T_TRANSACTION.campaign_no = T_CAMPAIGN.campaign_no
    LEFT OUTER JOIN T_BATCH ON T_TRANSACTION.batch_no = T_BATCH.batch_no
    LEFT OUTER JOIN TR_BATCH_TYPE ON T_BATCH.batch_type = TR_BATCH_TYPE.id
    LEFT OUTER JOIN T_PERF ON T_TRANSACTION.perf_no = T_PERF.perf_no


    WHERE T_TRANSACTION.perf_no>0 AND T_BATCH.post_no = 2382

     

     

     

     

     

     

     

     

     

     

     

     

     

     




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!




    This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Children
No Data