www.webdeveloper.com
Results 1 to 4 of 4

Thread: Need help with variable in select clause.

  1. #1
    Join Date
    Jun 2004
    Posts
    48

    Need help with variable in select clause.

    I have the following query:

    Code:
    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:

    Code:
    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?

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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?

  3. #3
    Join Date
    Jun 2004
    Posts
    48
    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 '.'.

  4. #4
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles