0

I'm working on a query that will run via C# to SQL server. I'm trying to make the query flexible and provide parameters for all the inputs.

enter image description here

Here is the query:

SELECT    
  Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project,
  Test_Matrix.tblTestMatrix.Request,
  Test_Matrix.tblTestMatrix.Release,
  Test_Matrix.tblTestMatrix.TestName,
  Test_Matrix.tblTestMatrix.Formulation,
  Test_Matrix.tblTestMatrix.Container,
  Test_Matrix.tblTestMatrix.Closure,
  Test_Matrix.tblTestMatrix.Shipper,
  Test_Matrix.tblTestMatrix.Label_Front,
  Test_Matrix.tblTestMatrix.Label_Back,
  Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other,Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM
Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix
  ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber
  AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request
  AND Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE  (Test_Matrix.tblTestMatrix.Formulation = 'N5200-500') OR
       (Test_Matrix.tblTestMatrix.Container = '') OR
       (Test_Matrix.tblTestMatrix.Closure = '') OR
       (Test_Matrix.tblTestMatrix.Shipper = '') OR
       (Test_Matrix.tblTestMatrix.Label_Front = '') OR
       (Test_Matrix.tblTestMatrix.Label_Back = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
       (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release

When I run this in SSMS it returns data but when looking at it in the query designer I see that the three "other" components that I'm trying to combine into one field name "Other" does not appear to be correct.

enter image description here

I would appreciate some comments and suggestions on what I can do to combine these three input parameters into one output field.

  • What does “not appear to be correct” mean? – Aaron Bertrand Jun 8 at 12:26
  • Please, edit your question, and give some examples (and expected output) about what you are trying to achieve. – Luuk Jun 8 at 14:15
0

I want this query, when the or case is selected and just one formulation N'9870-532' is selected to return only that formulation and of course the other components relating to that formulation.

Query:

SELECT 
Test_Matrix.tblTestMatrix.ResultsFolderNumber AS Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release, Test_Matrix.tblTestMatrix.TestName, Test_Matrix.tblTestMatrix.Formulation, 
Test_Matrix.tblTestMatrix.Container, Test_Matrix.tblTestMatrix.Closure, Test_Matrix.tblTestMatrix.Shipper, Test_Matrix.tblTestMatrix.Label_Front, Test_Matrix.tblTestMatrix.Label_Back, 
Test_Matrix.tblTestMatrix.OtherComp1 + '' + Test_Matrix.tblTestMatrix.OtherComp2 + '' + Test_Matrix.tblTestMatrix.OtherComp3 AS Other, Test_Matrix.tblTestMatrixHeader.FilePath AS FullPath
FROM Test_Matrix.tblTestMatrixHeader INNER JOIN Test_Matrix.tblTestMatrix ON Test_Matrix.tblTestMatrixHeader.ResultsFolderNumber = Test_Matrix.tblTestMatrix.ResultsFolderNumber AND Test_Matrix.tblTestMatrixHeader.Request = Test_Matrix.tblTestMatrix.Request AND 
Test_Matrix.tblTestMatrixHeader.Release = Test_Matrix.tblTestMatrix.Release
WHERE (Test_Matrix.tblTestMatrix.Formulation = N'9870-532') OR
      (Test_Matrix.tblTestMatrix.Container = '') OR
      (Test_Matrix.tblTestMatrix.Closure = '') OR
      (Test_Matrix.tblTestMatrix.Shipper = '') OR
      (Test_Matrix.tblTestMatrix.Label_Front = '') OR
      (Test_Matrix.tblTestMatrix.Label_Back = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp1 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp2 = '') OR
      (Test_Matrix.tblTestMatrix.OtherComp3 = '')
ORDER BY Project, Test_Matrix.tblTestMatrix.Request, Test_Matrix.tblTestMatrix.Release

Results:

enter image description here The return is not containing only Formulation 9870-532 and other associated items, seen above you have 9670-532 as a formulation item.

Note: I answered instead of comment because a comment was too long.

  • You can always edit your own question to add more information - this is the preferred method of doing this - not with comments or non-answers. Also, please don't use screenshots if at all possible on stackexchange. I have a few articles on how to ask questions here on my profile - you might want to look? p.s. welcome to the forum! :-) – Vérace Jun 8 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.