title: Hibernate Criteria SubSelect in select clause
body: <p>Hello guy's having a bit of a problem writing a subselect query with criteria API
  sublselect should take a parameter from selected q</p>
  
  <pre><code>SELECT c.file_id as fid, s.person as person, s.fax_no as fax, s.phone_no as phone,(SELECT SUM( b.ammount )
  FROM billing b
  WHERE b.constants_file_id = c.file_id
  ) - (
  SELECT
  CASE
  WHEN b.partioa_pay IS NULL
  THEN  "0"
  ELSE SUM( b.partioa_pay )
  END
  FROM billing b
  WHERE b.constants_file_id = c.file_id ) AS totalout
  FROM constants c
  LEFT JOIN firm_management s ON c.firm_management_firm_managment_id = s.firm_managment_id
  WHERE s.firm_managment_id = ?
  </code></pre>
  
  <p>As you can see the totalout suselected against c.file_id
  I was playing with this for 2 days with no luck maby sombody in stackoverflow community who has a strong java and hibernate knowledge would be able to help me..
  This is what I got getting exeption 2012-01-17 01:48:06,808 ERROR [org.hibernate.util.JDBCExceptionReporter] - (session F2C61D08758E0AA09CA6C99A5B6F2145, thread 113 invoke ByDefenceFirm.getByDefenceFirm)</p>
  
  <pre><code>Criteria crit = session.createCriteria(Constants.class, "co");
                crit.createAlias("co.provider", "pr", Criteria.INNER_JOIN)
           .createAlias("co.firmManagement", "ins", Criteria.LEFT_JOIN)
           .createAlias("co.law", "lw", Criteria.LEFT_JOIN)
           .createAlias("co.billing", "bl", Criteria.LEFT_JOIN)
           .setProjection(Projections.projectionList()
          .add(Projections.property("co.fileId"))
      .add(Projections.property("co.status"))
      .add(Projections.property("co.asi"))
      .add(Projections.property("pr.name"))
      .add(Projections.property("ins.companyName"))
      .add(Projections.property("lw.shortName")));
  
                DetachedCriteria valueCrit = DetachedCriteria.forClass(Billing.class, "bl")
                            .setProjection(Projections.sum("bl.ammount"))
                            .setProjection(Projections.property("bl.constants"))
                                    .add(Restrictions.eqProperty("bl.constants", "co.fileId"));        
                crit.add(Property.forName("co.fileId").eq(valueCrit));
                crit.setProjection(Projections.property("co.billing"));
                crit.add(Restrictions.eq("lw.lawOfficeId", prid));
  resultList = crit.list(); 
  </code></pre>
  
  <p>If anybody have any suggestions would really appreciated it.</p>