Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts

    How to filter record in SQL statement?

    Dear,

    How to filter the OSQTY when it is equal to 0? when i add the vTable1.Qty - vTable3.Qty <>0 at Where Clause it will get wrong sorting of the [Delivered Qty]??? dunno why?
    How to filter record in SQL statement(MS SQL 2000), or i can only use ASP to filter it?

    sample record in database:
    SO ID, Customer ID, Outlet ID, WH ID, Product ID, UoM, Unit Price, Ordered Qty, DO ID, Delivered Qty, OSQTY
    S0010, ACT111, PJ, WH1, AB36U, rll, 71.3, 24, 60312/DO, 24, 0
    S0011, ACT111, PJ, WH1, AB236U, rll, 71.3, 4, 60313/DO, 3, 1

    SELECT TOP 100 PERCENT vTable.[SO ID], vTable.[Customer ID], vTable1.[Outlet ID], vTable1.[Warehouse ID], vTable1.[Product ID], vTable1.UoM,
    vTable1.[Unit Price], vTable1.Qty AS [Ordered Qty], vTable2.[DO ID], vTable3.Qty AS [Delivered Qty], vTable4.Currency, vTable5.Discount, vTable5.Tax,
    vTable1.Qty - vTable3.Qty AS OSQTY
    FROM dbo.t_MYDMS1101SalesOrder_M vTable INNER JOIN
    dbo.t_MYDMS1101SalesOrder_D vTable1 ON vTable.[SO ID] = vTable1.[SO ID] INNER JOIN
    dbo.t_MYDMS1102DeliveryOrder_M vTable2 ON vTable1.[SO ID] = vTable2.[SO ID] INNER JOIN
    dbo.t_MYDMS1102DeliveryOrder_D vTable3 ON vTable2.[DO ID] = vTable3.[DO ID] INNER JOIN
    dbo.t_MYDMS1101Products vTable5 ON vTable1.[Product ID] = vTable5.[Product ID] LEFT OUTER JOIN
    dbo.t_MYDMS1101Customers vTable4 ON vTable.[Customer ID] = vTable4.[Customer ID]
    WHERE (vTable.Status = 'Yes')
    GROUP BY vTable.[Customer ID], vTable.[SO ID], vTable.[Date], vTable1.[Due Date], vTable1.[Outlet ID], vTable1.[Warehouse ID], vTable1.[Product ID],
    vTable1.UoM, vTable1.[Unit Price], vTable1.Qty, vTable2.[SO ID], vTable2.[DO ID], vTable3.[DO ID], vTable3.Qty, vTable4.Currency, vTable5.Discount,
    vTable5.Tax
    ORDER BY vTable.[Customer ID], vTable.[SO ID]
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #2
    New Coder
    Join Date
    Feb 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You should use the Having clause, it is similar to the Where clause except it does the filtering at a different point in the select statement. Where is done before the Order By and Group By, while Having is done after the Order By and Group By clauses.

  • #3
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Yup, it solve the part of my problem.
    But there is another problem is why the record of vTable3 is not sort by sequence???

    Ex:
    T1=Table 1(vTable1)
    T2=Table 2(vTable3 )
    [SO ID] [DO ID] [T1.product ID] [T2.product ID]
    SO0001 DO001 123456 123457
    SO0001 DO002 123457 123456


    y in the group clause, the T2.Product ID is not sorting but the T1.product id is sort by sequence???
    Last edited by NinjaTurtle; 08-13-2004 at 05:31 AM.
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #4
    New Coder
    Join Date
    Feb 2004
    Posts
    13
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If I understand your query and question, it is sorting correctly actually. When you specify 2 Order By's (btw you should always explicitly state whethers its Ascending or Descending instead of relying on the default of ascending), what it does is sort the first one (T1) and then sort the second one (T2) within the first one. So if you add a few more records to your example, it will sort like:

    [SO ID] [DO ID] [T1.product ID] [T2.product ID]
    SO0001 DO001 123456 123456
    SO0001 DO002 123456 123457
    SO0001 DO002 123456 123458
    SO0001 DO002 123457 123456
    SO0001 DO002 123457 123457

    T1 will be sorted in order and then it will sort the T2 within the group of equal T1 recordds (123456 and 123457 in this case).

  • #5
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    yup u r rite, then how to make the SQL Statement to just list the same [Product ID] ONLY?
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++

  • #6
    Regular Coder
    Join Date
    Jun 2002
    Posts
    358
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I solved the promblem, Thank you everyone:

    SELECT TOP 100 PERCENT vTable.[SO ID], vTable.[Customer ID], vTable.[Date], vTable1.[Due Date], vTable1.[Outlet ID], vTable1.[Warehouse ID],
    vTable1.[Product ID], vTable1.UoM, vTable1.[Unit Price], vTable1.Qty AS [Ordered Qty], vTable2.[DO ID], vTable3.Qty AS [Delivered Qty],
    vTable4.Currency, vTable5.Discount, vTable5.Tax, vTable1.Qty - vTable3.Qty AS OSQTY
    FROM dbo.t_MYDMS1101SalesOrder_M vTable LEFT OUTER JOIN
    dbo.t_MYDMS1101SalesOrder_D vTable1 ON vTable.[SO ID] = vTable1.[SO ID] LEFT OUTER JOIN
    dbo.t_MYDMS1102DeliveryOrder_M vTable2 ON vTable.[SO ID] = vTable2.[SO ID] LEFT OUTER JOIN
    dbo.t_MYDMS1102DeliveryOrder_D vTable3 ON vTable2.[DO ID] = vTable3.[DO ID] AND vTable1.[Product ID] = vTable3.[Product ID] LEFT OUTER JOIN
    dbo.t_MYDMS1101Customers vTable4 ON vTable.[Customer ID] = vTable4.[Customer ID] LEFT OUTER JOIN
    dbo.t_MYDMS1101Products vTable5 ON vTable1.[Product ID] = vTable5.[Product ID]
    WHERE (vTable.Status = 'Yes') AND (vTable3.Qty IS NOT NULL)
    GROUP BY vTable.[Customer ID], vTable.[SO ID], vTable.[Date], vTable1.[Due Date], vTable1.[Outlet ID], vTable1.[Warehouse ID], vTable1.[Product ID],
    vTable1.UoM, vTable1.[Unit Price], vTable1.Qty, vTable2.[SO ID], vTable2.[DO ID], vTable3.[DO ID], vTable3.Qty, vTable4.Currency, vTable5.Discount,
    vTable5.Tax
    HAVING (vTable1.Qty - vTable3.Qty <> 0)
    ORDER BY vTable.[Customer ID], vTable.[SO ID
    Thanks.
    =====================================================
    From NinjaTurtle
    ++http://ohmygoh.blogspot.com|http://technology.ohmygoh.com++


  •  

    Posting Permissions

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