Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123

    Unanswered: Sorry for the mess but could you?

    Sorry for the mess but could someone please tell me if my problem is in the SQL?
    What I am doing is passing the Line variable (MMColParamLine) in the URL and even though the value given from a previous page is 02 or 03 it is passed as 2 or 3.

    I created a vbscript case statement that gives the 2, 3, ...., 10, etc value the value of 02, 03, ..., 10, etc.

    The rsTotal recordset works properly for all values I pass. The reDetail is not for values that are less than 10. Values of 10 and above work fine.
    EquipNum is of type varchar

    rsTotal
    SELECT Sum(
    CASE
    WHEN TRANSTYPE = 'IR' THEN -[QTY]*[AVGUNITCOST]
    WHEN TRANSTYPE = 'IC' THEN [QTY]*[AVGUNITCOST] END ) as RTC
    FROM ptblIssue
    WHERE ( ([FROMCOSTCENTER] = 'MMColParam1')
    OR ([COSTCENTER] = 'MMColParam1') )
    AND ( (ISSUEDATETIME >= 'MMColParamStart')
    AND (ISSUEDATETIME <= 'MMColParamEnd') )
    AND (Right(Left(EQNUM,8),2) = ('MMColParamLine')

    rsDetail
    SELECT Max(COSTCENTER) AS CC, FROMACCTCODE, Sum(
    CASE
    WHEN TRANSTYPE = 'IR' THEN Cast(-[QTY]*[AVGUNITCOST] AS Float)
    WHEN TRANSTYPE = 'IC' THEN Cast([QTY]*[AVGUNITCOST] AS Float) END ) as RTC
    FROM ptblIssue
    WHERE ( ([FROMCOSTCENTER] = 'MMColParam1')
    OR ([COSTCENTER] = 'MMColParam1') )
    AND ( (ISSUEDATETIME >= 'MMColParamStart')
    AND (ISSUEDATETIME <= 'MMColParamEnd') )
    AND (Right(Left(EQNUM,8),2)) = ('MMColParamLine')
    GROUP BY FROMACCTCODE
    ORDER BY FROMACCTCODE ASC, RTC DESC

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    what its the data type of EQNUM and could yo uprovide som sample data ESPECIALY EQNUM.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Oct 2002
    Location
    Western part of Georgia, USA
    Posts
    123
    Originally posted by Paul Young
    what its the data type of EQNUM and could yo uprovide som sample data ESPECIALY EQNUM.
    EquipNum is of type nvarchar and looks like 12345-01-000-000

    Thanks to all, but I found my mistake. I did not pay attention to the fact that Dreamweaver has two variables for MMColParamLine. One for each recordset. rsTot_MMColParamLine and rsDetail_MMColParamLine. Once I included both in the vbscript case statement, it all worked fine. To make a long story short, the problem was not in the SQL.

    However, may I ask another question? What is a good book for a beginner. I have learned to use the SQL Case statement to handle certain complex queries. But I have seen Statements that start with Begin and looks as though they are assigning variable names @whatever that allows one more flexibility in programing in SQL rather than returning the recordset and then diceing the data with vb.

    Thanks again for a quick response.

    Lee

Posting Permissions

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