#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2011
    Posts
    11
    Rep Power
    0

    Parameter queries


    Hello all. I have an sql query that is working fine for me when I run it as a straight query. I was showed how to make an xml report out of this query which i've done below.

    <report title="Account Balance" description="Reports the account balances" active="1">
    <query>
    select demo.demographic_no, demo.last_name, demo.first_name, demo.code FROM
    (SELECT demographic_no, last_name, first_name, chart_no FROM demographic WHERE patient_status='AC' AND code IN ('{code}')) AS demo LEFT OUTER JOIN (SELECT id, demographic_no FROM billing_on_cheader1) AS ch1 ON demo.demographic_no = ch1.demographic_no INNER JOIN (SELECT demographic_no, appointment_date FROM appointment WHERE appointment_date = '{date}') GROUP BY demographic_no) AS appt ON demo.demographic_no = appt.demographic_no GROUP BY demographic_no
    </query>
    <param id="date" type="list" description="Date">
    <param-query> SELECT DISTINCT appointment_date, appointment_date FROM appointment WHERE appointment_date >= curdate() AND appointment_date < (curdate() + INTERVAL 5 DAY);</param-query>
    </param>
    <param id="code" type="list" description="With payment codes">
    <param-query> SELECT 'IN', ('IN', 'AC', 'NB'), IN, ALL;</param-query>
    </param>
    </report>


    my problem is that I don't understand how the parameter query works. I was able to do the first one (so the user can choose the date they want to use in the query from a list). The other parameter I want the user to be able to choose is a list of codes (IN, AC, NB). I want a little box that lets them choose to use either 'IN' or 'IN', 'AC', 'NB' in that code field. I've tried to do this as you can see above but it keeps give me an error. Any suggestions?

    Thanks!

    Alexandra,
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Not that this is the right forum to be discussing this, but isn't <param> simply used to define parameters for plugins embedded with an html <object>?

    What are you basing this upon, or are you just making it up?

    And that query is a thing of horror!
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,207
    Rep Power
    4279
    thread moved to xml forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo