none
Why does this query prompt for the parameters twice? RRS feed

  • Question

  • It didn't before, but after I added a second left join, it started prompting for the parameters twice.

    Is there any way to change the query to prevent this?

    Query:

    SELECT C.CaseNumber, ST.Description, C.SubjectId, S.LastName, S.MailStreet1, S.MailStreet2, S.MailCity, S.MailState, S.MailZip, C.SourceCaseNumber, C.Comments
    FROM ([Beth Afirms] AS C LEFT JOIN tblSubjects AS S ON C.SubjectId = S.ID) LEFT JOIN CaseStatus AS ST ON C.CaseStatus = ST.CaseStatusCode
    WHERE (C.CaseNumber >= [From Case #] AND C.CaseNumber <= [To Case #]) AND C.SourceCaseNumber = [Source Case #];

    Tuesday, November 28, 2017 2:51 AM

Answers

  • Explicitly declaring the parameters seems to fix it:

    PARAMETERS [From Case #] Text ( 10 ), [To Case #] Text ( 10 ), [Source Case #] Text ( 50 );
    SELECT C.CaseNumber, ST.Description, C.SubjectId, S.LastName, S.MailStreet1, S.MailStreet2, S.MailCity, S.MailState, S.MailZip, C.SourceCaseNumber, C.Comments
    FROM ([Beth Afirms] AS C LEFT JOIN tblSubjects AS S ON C.SubjectId = S.ID) LEFT JOIN CaseStatus AS ST ON C.CaseStatus = ST.CaseStatusCode
    WHERE (C.CaseNumber >= [From Case #] AND C.CaseNumber <= [To Case #]) AND C.SourceCaseNumber = [Source Case #];

    Tuesday, November 28, 2017 5:51 PM

All replies

  • Hi,

    Not sure but you might try using a subquery rather than joining the new table to the old query. For example, if this used to work:

    SELECT...
    FROM...
    LEFT JOIN...
    ON...
    WHERE...

    Then try something like:

    SELECT T.*
    FROM (SELECT... 'the old query here as above) AS T
    LEFT JOIN NewTable
    ON...

    Just a thought...

    Tuesday, November 28, 2017 3:06 AM
  • Explicitly declaring the parameters seems to fix it:

    PARAMETERS [From Case #] Text ( 10 ), [To Case #] Text ( 10 ), [Source Case #] Text ( 50 );
    SELECT C.CaseNumber, ST.Description, C.SubjectId, S.LastName, S.MailStreet1, S.MailStreet2, S.MailCity, S.MailState, S.MailZip, C.SourceCaseNumber, C.Comments
    FROM ([Beth Afirms] AS C LEFT JOIN tblSubjects AS S ON C.SubjectId = S.ID) LEFT JOIN CaseStatus AS ST ON C.CaseStatus = ST.CaseStatusCode
    WHERE (C.CaseNumber >= [From Case #] AND C.CaseNumber <= [To Case #]) AND C.SourceCaseNumber = [Source Case #];

    Tuesday, November 28, 2017 5:51 PM
  • Hi,

    Good work! Good luck with your project.

    Tuesday, November 28, 2017 6:04 PM