Using Parameters in SQL Query with Sub-Query

Pass parameter to subquery

I feel very stupid for this, but apparently it works to just call p.Id in the subquery. It knows that I'm referencing the parent query's value even though it's in the subquery. Thank you everyone for your help.

SELECT t.[Description],
       t.RequestedCompletionDate,
       t.CommitDate,
       t.StatusId,
       t.PriorityId,
       p.ProjectNumber,
       s.Name AS StatusDescription,
       pr.Name AS PriorityDescription
FROM ProjectTask t
inner join Project p
    on p.Id = t.ProjectId
inner join Project_TaskStatus s
    on s.Id = t.StatusId
inner join Project_Priority pr
    on pr.Id = t.PriorityId
WHERE t.Type = 'ET'
AND t.StatusId NOT IN (4,7)
AND
(
    SELECT StatusId FROM Project WHERE Id = p.Id
)
NOT IN (3, 4)
ORDER BY t.PriorityId,
         t.CommitDate,
         t.RequestedCompletionDate

Using Parameters in SQL query with sub-query

Here's what I do to work around the limitations of Microsoft Query in Excel 2007:

1. A produce a dummy query (SELECT NULL AS Test, for example) in Microsoft Query and insert it into the worksheet.
2. Right-click on the table that MS Query just inserted and click Table->Edit External Data Properties.
3. Click on the Connection Properties button, then click the Definition tab.
4. In the Command Text section, write out or paste in the query that you want, using the usual '?' convention for parameters, then click OK.
5. Click OK to exit the External Data Properties window.
6. Right click on the table again, and select Table->Parameters to bind the parameters in the usual way.

The idea is the bypass the GUI that MS Query provides, which has some arbitrary limitations that the underlying engine does not.

This works for many complex queries, but not all. When I encounter a query that MS Query refuses to digest at all, I either refactor the query (when feasible) or create a VIEW on the SQL server and query against that.

Subquery with 2 parameters in SQL

Use a JOIN, not IN

SELECT t1.*
FROM sample_table AS t1
JOIN sample_table AS t2 
    ON t1.prev_trans_id = t2.transaction_id AND t1.amount != -1 * t2.amount

BTW, it's not mod of the amounts, it's the negation of the amounts that you want to compare.

Is there a way to use parameters in subquery with groupby and having in sql query?

You can add where clause :

select a.* 
from(select alphabet,date,dense_Rank() over (partition by alphabet order by date) RankOrder
     from mytable mt
     where mt.Alphabet = @palphabet and year(mt.date) = @pyear
     ) a
where RankOrder = 1;

I don't think GROUP BY with HAVING clause is required here. As you have a raw data not the aggregate data & you are returning only 1 row for each alphabet by using where RankOrder = 1.

Note : You can't use INT Alphabet as base table contains text value. So, change the type of variable @palphabet.

SQL Sub-query parameters from Excel

Here's some suggestion.

First is to check the formatting of your date columns.

and build your parameter to be like

Cast(into_Start as smalldatetime) between ? and ?

---

Related Topics