Need help with variable in select clause.

[mwiggin1]

I have the following query:

SELECT DISTINCT t1.section_id, t1.section_descr, t1.page_number, t1.reviewed, t1.parent_id, t4.txt_entry, (SELECT MIN(actual_id) FROM dbo.ufn_GetParentSections(1800)) AS doc_grp_id FROM dbo.tblCCMContract t1 INNER JOIN dbo.jctCCMSectionWBS t2 ON t1.section_id = t2.section_id INNER JOIN dbo.tblCCMWBS t3 ON t2.wbs_id = t3.wbs_id LEFT OUTER JOIN dbo.tblCCMTxtEntries t4 ON t1.txt_entry_id = t4.txt_entry_id WHERE (t3.wbs_id = 29 OR t3.wbs_id = 21) ORDER BY parent_id

Which works fine with the literal value 1800. But when I do this:

SELECT DISTINCT t1.section_id, t1.section_descr, t1.page_number, t1.reviewed, t1.parent_id, t4.txt_entry, (SELECT MIN(actual_id) FROM dbo.ufn_GetParentSections(t1.section_id)) AS doc_grp_id FROM dbo.tblCCMContract t1 INNER JOIN dbo.jctCCMSectionWBS t2 ON t1.section_id = t2.section_id INNER JOIN dbo.tblCCMWBS t3 ON t2.wbs_id = t3.wbs_id LEFT OUTER JOIN dbo.tblCCMTxtEntries t4 ON t1.txt_entry_id = t4.txt_entry_id WHERE (t3.wbs_id = 29 OR t3.wbs_id = 21) ORDER BY parent_id

I get an error. Is there any way for me to pass t1.section_id to this function in a sub query?

[chazzy]

hmm
I'm thinking that what ever DBMS you're using doesn't know if that key will actually return anything if you just put a column in it.

What error are you getting exactly?

[mwiggin1]

I am using MSSQL. My error is just a syntax error ...

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '.'.

[chazzy]

What's the input on the function and what's the datatype on the column? I'm not overly familiar with MS SQL, but is it expecting the same datatype as the column is providing?

Other than that, can you create a view using the function definition and join on that view, instead of calling the function? It goes back to my previous idea: I'm thinking that what ever DBMS you're using doesn't know if that key will actually return anything if you just put a column in it.