4 Replies Latest reply on Feb 16, 2009 1:06 PM by Raghavendra Bhagavan.S.M

    Report Generation Help

    Raghavendra Bhagavan.S.M
      Hi,

      I am trying to generate report from SWEPDM, By writing a query in SQL, This query Executes but results are not displayed.

      I need to list all the part numbers and their descriptions in the report.

      Here is the query.. Kindly help me where i am wrong.

      ________________________________________________________________
      SELECT DISTINCT VariableValue.ValueCache AS Number,VariableValue.ValueCache AS Description
      FROM Variable INNER JOIN
      VariableValue ON Variable.VariableID = VariableValue.VariableID
      WHERE (Variable.VariableName LIKE N'Number') AND (VariableValue.ValueCache <> N'')
      (Variable.VariableName LIKE N'Description') AND (VariableValue.ValueCache <> N'')
      ORDER BY VariableValue.ValueCache ASC
      __________________________________________________________________

      Your help is highly appreciated.

      Regards,
      Bhagavan.
        • Report Generation Help
          Hi,

          Try this one:

          SELECT DISTINCT Vv1.ValueCache AS Number, Vv2.ValueCache AS Description
          FROM VariableValue Vv1, VariableValue Vv2, Variable V1, Variable V2
          WHERE V1.VariableName LIKE '%Number' AND V1.VariableID = Vv1.VariableID AND Vv1.ValueCache <> ''
          AND V2.VariableName LIKE 'Description' AND V2.VariableID = Vv2.VariableID AND Vv2.ValueCache <> ''
          AND Vv1.DocumentID = Vv2.DocumentID
          ORDER BY Vv1.ValueCache

          --------

          This query might not be optimal, but it seems to work. I don't like using 'INNER JOIN' so I put all the conditions in WHERE clause.

          Hope that it helps,
          Kim
          • Report Generation Help
            Joy Garon
            Hello Raghavendra,

            Let's break the problem down to exactly what you are trying to do:
            You wish to report the value of the variable 'Number' and the value of the variable 'Description' for each document in the vault.
            When we think of the tables involved in the above statement we get:
            - Documents (DocumentID)
            - Variable (V1 - VariableID for Number)
            - Variable (V2 - VariableID for Description)
            - VariableValue (vv1 - ValueCache for Number)
            - VariableValue (vv2 - ValueCache for Description)

            What I do is to use the Query Designer to create the first Join, then I copy the JOIN and modify the statements to create the unique alaises. I reopen the query in the Query Designer to now display all the tables and modify as needed. (see attached image)

            SELECT DISTINCT Vv1.ValueCache AS Number, Vv2.ValueCache AS Description
            FROM Documents AS D INNER JOIN
            VariableValue AS Vv1 ON D.DocumentID = Vv1.DocumentID INNER JOIN
            VariableValue AS Vv2 ON D.DocumentID = Vv2.DocumentID INNER JOIN
            Variable AS V1 ON V1.VariableID = Vv1.VariableID INNER JOIN
            Variable AS V2 ON V2.VariableID = Vv2.VariableID
            WHERE (V1.VariableName = N'Number') AND (V2.VariableName = N'Description')
            ORDER BY Number

            This method is preferred over using a Where clause as it is more efficient.

            Regards,
            Joy

            p.s. The query below will halp you to see the values per document. If you are using configs, you may also need to get and display the config name which will mean adding tables and joins to your original query.

            SELECT DISTINCT D.DocumentID, Vv1.ValueCache AS Number, Vv2.ValueCache AS Description
            FROM Documents AS D INNER JOIN
            VariableValue AS Vv1 ON D.DocumentID = Vv1.DocumentID INNER JOIN
            VariableValue AS Vv2 ON D.DocumentID = Vv2.DocumentID INNER JOIN
            Variable AS V1 ON V1.VariableID = Vv1.VariableID INNER JOIN
            Variable AS V2 ON V2.VariableID = Vv2.VariableID
            WHERE (V1.VariableName = N'Number') AND (V2.VariableName = N'Description')
            ORDER BY DocumentID