title: Postgresql parameterized query for Crystal Reports SQL Command
body: <p>I'm using the following Postgresql select statement in a Crystal report as a SQL Command and it works beautifully. Now I want to change it so that it will use dates passed to it as parameters instead of them being hard-coded like they are below (the parameters are year, monthstart and monthend).</p>
  <p>The program I'm using to run the report (Spire) passes parameters like this ?ReportStart or ?ReportEnd when a report is run. I tried replacing the monthstart date '2020-04-01' with ?ReportStart (like this: ?ReportStart::date as monthstart) but I get an error stating &quot;column &quot;reportstart&quot; does not exist&quot;.</p>
  <p>I also tried 1$, 2$ and 3$ which I read about in the Postgresql PREPARE documentation but it seems I don't quite understand how to write that properly.</p>
  <pre><code>``` with params as (select '2019-01-01'::date as year, '2020-04-01'::date as monthstart, '2020-04-30'::date as monthend) SELECT h.salesperson_no, h.territory_code, h.cust_no, h.cust_name, i.part_no, i.description,
     SUM(i.committed_qty) FILTER
      (WHERE h.invoice_date &gt;= p.year AND h.invoice_date &lt; p.year + interval '1 year') as LYQty,
     SUM(i.committed_qty * i.unit_price) FILTER
      (WHERE h.invoice_date &gt;= p.year AND h.invoice_date &lt; p.year + interval '1 year') as LYSales,
     SUM(i.committed_qty * i.unit_price) FILTER
      (WHERE h.invoice_date &gt;= p.year AND h.invoice_date &lt; p.monthend - interval '1 year') as LYTDSales,
     SUM(i.committed_qty) FILTER
      (WHERE h.invoice_date &gt;= p.year + interval '1 year' AND h.invoice_date &lt; p.year + interval '2 years') as YTDQty,
     SUM(i.committed_qty * i.unit_price) FILTER
      (WHERE h.invoice_date &gt;= p.year + interval '1 year' AND h.invoice_date &lt; p.year + interval '2 years') as YTDSales,
     SUM(i.committed_qty) FILTER 
       (WHERE h.invoice_date &gt;= p.monthstart AND h.invoice_date &lt;= p.monthend) as MTDQty,
     SUM(i.committed_qty * i.unit_price) FILTER
       (WHERE h.invoice_date &gt;= p.monthstart AND h.invoice_date &lt;= p.monthend) as MTDSales FROM params p
     CROSS JOIN sales_history h
     LEFT JOIN sales_history_items i
            ON i.invoice_no = h.invoice_no WHERE i.part_no is not null AND h.invoice_date &gt;= p.year AND h.invoice_date &lt; p.year + interval '2 years' GROUP BY h.salesperson_no, h.territory_code, h.cust_no, h.cust_name, i.part_no, i.description ```
  </code></pre>
  <p>Any guidance for properly adding parameters to a select statement in postgresql would be greatly appreciated.</p>
  <p>P.S. I apologize that you have to scroll right on this code snippet. For some reason I can't get my code to stay all in one box without removing some of the hard returns.</p>