Exec-Raw Inner Query Issue

[micahasmith]

(defn do-sql [env sql-vec]
  (with-db (get env->db-spec (keyword env))
           (exec-raw sql-vec :results)))

(db/do-sql (-> args-map :env)
               [(str "
                 select a.vendorid, sum(a.rev) as revsum
                 from (
                   select oi.vendorid, oia.itemcost + oia.shippingcost as rev
                   from orderitem oi
                   inner join orderitem_accounting oia on oia.id = oi.accountingid
                   inner join [order] o on o.orderid = oi.orderid
                   where oi.orderstatusid in (1, 23, 24, 25)
                   and o.partnerid not in (7)
                   and o.datecreated >= ?
                   and o.datecreated <= ? ) a
                 group by a.vendorid
               ") [(java.sql.Timestamp. (-> args-map :start-date .getTime)) (java.sql.Timestamp. (-> args-map :end-date .getTime))]])

This always returns an empty list, and i can't figure out why. If i remove the datecreated params it works-- makes me wonder if there's an issue with passing parameters to a subquery?

Note that i am SUPER new to clojure and java programming.

[micahasmith]

hmmm

(db/do-sql (-> args-map :env)
               [(str "
                 select a.vendorid, sum(a.rev)
                 from (
                   select oi.vendorid, oia.itemcost + oia.shippingcost as rev
                   from orderitem oi
                   inner join orderitem_accounting oia on oia.id = oi.accountingid
                   inner join [order] o on o.orderid = oi.orderid
                   where oi.orderstatusid in (1, 23, 24, 25)
                   and o.partnerid not in (" bad-partnerids ")
                   and o.datecreated >= ?
                   and o.datecreated <= ? ) a
                 group by a.vendorid
               ") ["2015-09-01" "2015-10-01"]])

changing to that date/time format works. so its def not that it can't pass params to a subquery. hmm

[ls4f]

Might be that the database expects you to offer a Date (as opposed to Timestamp) or cast it explicitly. btw I would recommend you take a look at http://rundis.github.io/blog/2015/clojure_dates.html, which made my life infinitely better.

[immoh]

Korma doesn't have any handling related to dates. It depends on the DB vendor and driver what you need to pass.