Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Nightmare
Message
General information
Forum:
Visual FoxPro
Category:
Databases,Tables, Views, Indexing and SQL syntax
Title:
Miscellaneous
Thread ID:
00585800
Message ID:
00586259
Views:
14
First, I would never parameterize a view using a reference to a form value. The view will forever be tied to a form that must have that property. I would use a variable. Then before opening or requerying the view, I would initialize that variable, or open it with NODATA. Like the other suggestion, I un-nested the joins. Other than that, I do not see a problem with your SQL. Try creating with the code as I have modified below. I also removed the form reference and made it a variable parameter instead.
CREATE SQL VIEW "VIEW_ELEMENTS" AS ;
  SELECT Elements.clm_ref, Elements.total_amt, Elements.agreed_amt,;
  Elements.admitted_amt, Elements.setoff_amt, Elements.rejected_amt,;
  Elements.multiplier_used, Elements.unquantified,;
  Elements.date_element_received, Elements.claim_elem_code,;
  Elements.claimstatus, Elements.excludingcosts, Elements.withdrawn,;
  Elements.wdn_amt, Elements.wdn_date,;
  Elements.adm_in_lux, Elements.id, Elements.potential,;
  Elements.reject_date, Elements.rej_wave_no, Elements.appeal_date,;
  Elements.struck_o_date, Elements.struck_o_desc, Elements.stg_let_st,;
  Elements.setoff_date, Elements.agreed_date, Elements.admitted_date,;
  Elements.stg_let_rc, Elements.in_rejection_amt,;
  Elements.court_order_date, Elements.in_rejection_letter_date,;
  Elements.in_rejection_letter_post_type,;
  Elements.in_rejection_narrative, Elements.waiver_flag,;
  Elements.waiver_date, Elements.appeal_expiry_date,;
  Elements.appeal_expired_flag, Elements.appeal_expiry_term,;
  Elements.litigant, Elements.luxrej_dt, Elements.location,;
  Elements.amend_date, Elements.amend_id,Elements.wdn_code,;
  Elements.total_amt - ;
       (Elements.agreed_amt ;
        + Elements.rejected_amt ;
        + Elements.in_rejection_amt ;
        + Elements.wdn_amt) AS pending_amt,;
  .F. AS lChanged,;
  VAL(TRANSFORM(IIF(Elements.agreed_amt > 0,
      Elements.agreed_amt - Elements.setoff_amt, 0), ;
      "999999999.99")) AS post_setoff,;
  Elemtype.elem_desc, Wdntypes.wdn_desc ;
  FROM employee!elements ;
    INNER JOIN employee!elemtype ;
       ON Elements.claim_elem_code = Elemtype.elem_code ;
    INNER JOIN employee!wdntypes ;
       ON Elements.wdn_code = Wdntypes.wdn_code ;
 WHERE Elements.clm_ref = ?nClaim_Ref ;
 ORDER BY Elemtype.elem_desc
Mark McCasland
Midlothian, TX USA
Previous
Next
Reply
Map
View

Click here to load this message in the networking platform