Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2014
    Posts
    6

    Smile Unanswered: Need duplicate records in sub query

    Hi i am running the following query and i am getting message that sub query return duplicate records and that why query is failing. Basically i need duplicate records because i need to show them in report. My query is as follows

    select pmnum
    ,SITEID,
    (select description from locations where pm.location = locations.location and pm.siteid=locations.siteid) as site,
    (select description from commodities where commodities.commodity= pm.commoditygroup) as workcategory,
    description, (select wonum from workorder where workorder.pmnum = pm.pmnum
    and targstartdate < '2013-02-01') as jan,
    (select wonum from workorder where workorder.pmnum = pm.pmnum and
    workorder.status<>'CAN' and targstartdate >= '2013-02-01' and
    targstartdate < '2013-03-01') as feb,
    (select wonum from workorder where workorder.pmnum = pm.pmnum and
    workorder.status<>'CAN' and targstartdate >= '2013-03-01' and
    targstartdate < '2013-04-01') as mar
    (select name from companies where companies.company = pm.vendor) as contractor
    from pm where ((PM.siteid = 'AAA'))



    so in simple words i want to create different columns for whole year


    JAN FEB MARC -- -- --


    Is there any other way i can achieve this task. Thanks

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Hi,

    Try adding a "Top 1" to see if the desired result is obtained:

    Code:
    -- ...
    (select top 1 wonum from workorder 
     where workorder.pmnum = pm.pmnum and targstartdate < '2013-02-01') as jan,
    Hope this helps.

  3. #3
    Join Date
    Feb 2014
    Posts
    6

    Smile Need duplicate records in Subquery

    Thanks for your reply. But when i will use TOP 1 it will eliminate duplicate record. I need to get duplicate record but i want to show it in a report. Is there any other way i can achieve this result. Basically i need to get all jobs for whole year thats why i am using sub select.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I had to guess on a few things missing from your specifications, so this probably is only a start, but try:
    Code:
    SELECT pm.pmnum
    ,  pm.SITEID
    ,  site.description AS 'site'
    ,  workcategory.description AS workcategory
    ,  pm.description AS pmDescription
    ,  Max(CASE WHEN 01 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Jan
    ,  Max(CASE WHEN 02 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Feb
    ,  Max(CASE WHEN 03 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Mar
    ,  Max(CASE WHEN 04 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Apr
    ,  Max(CASE WHEN 05 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS May
    ,  Max(CASE WHEN 06 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Jun
    ,  Max(CASE WHEN 07 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Jul
    ,  Max(CASE WHEN 08 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Aug
    ,  Max(CASE WHEN 09 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Sep
    ,  Max(CASE WHEN 10 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Oct
    ,  Max(CASE WHEN 11 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Nov
    ,  Max(CASE WHEN 12 = Month(workorder.targetstartdate) THEN workorder.wonum END) AS Dec
    ,  companies.name as contractor 
       FROM pm
       JOIN locations AS [site]
          ON ([site].location = pm.location 
    	  AND [site].siteid = pm.siteid)
       INNER JOIN commodities  AS workcategory
          ON (workcategory.commodity = pm.commoditygroup)
       INNER JOIN companies as contractor
          ON (contractor.company = pm.vendor)
       INNER JOIN workorder
          ON (workorder.pmnum = pm.pmnum)
       WHERE  PM.siteid = 'AAA'
       GROUP BY pm.pmnum
    ,     pm.SITEID
    ,     site.description
    ,     workcategory.description
    ,     pm.description
    ,     companies.name
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Oct 2003
    Location
    London
    Posts
    341
    Ignore me... my response was garbage
    Last edited by christyxo; 02-28-14 at 10:30. Reason: typo

  6. #6
    Join Date
    Feb 2014
    Posts
    6

    Smile Need duplicate records in subquery

    Hi Pat,
    Thanks for your responce. Its working good but there is only one problem that is when ever there is any duplicate entry for any specific month it is ignoring it. Suppose if there are two workorders for the same month consider October with same PM it is ignoring first work order and pulling out second one according to query that is maximum one. Is there any way that we can bring both of them in our records because my target is to bring all records related with same pm in same month. Cheers

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There is certainly a way to get what you want, but you haven't been specific enough for me to guess the details of what that is... We can start by "unwrapping" the query back to a simple form so that you can verify that it returns EXACTLY the data that you want, in other words every bit that you want but not one bit more!

    Once we confirm that the query gets the data that you want, then we can work on making it pretty!

    Please confirm that this returns EXACTLY the rows that you want:
    Code:
    SELECT pm.pmnum
    ,  pm.SITEID
    ,  site.description AS 'site'
    ,  workcategory.description AS workcategory
    ,  pm.description AS pmDescription
    ,  workorder.targetstartdate
    ,  workorder.wonum
    ,  companies.name as contractor 
       FROM pm
       JOIN locations AS [site]
          ON ([site].location = pm.location 
          AND [site].siteid = pm.siteid)
       INNER JOIN commodities  AS workcategory
          ON (workcategory.commodity = pm.commoditygroup)
       INNER JOIN companies as contractor
          ON (contractor.company = pm.vendor)
       INNER JOIN workorder
          ON (workorder.pmnum = pm.pmnum)
       WHERE  PM.siteid = 'AAA'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Feb 2014
    Posts
    6

    Smile Need duplicate records in sub query

    Hi Pat,
    Thanks for your reply. Yes i need these columns in my result plus all months so output would be like this



    pmnm description Category JAN FEB MAR APR ------ DEC


    Your previous query was showing accurate results except it was not showing me duplicate records for same pm. One pmnum can have multiple wonum in the same month. So what i need is duplication if pm if required it should not restrict to bring only one record , if there is only one wonum again one pm its ok but but if there is more than one wonum against same pm in same month then it should display results

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    An idea. Not tesed.

    Example 1:
    Note 1: I used INNER JOIN for site, workcategory and contractor.
    But, subqueries like in your original query might be possible, if they were not duplicated.

    Note 2: I'm not sure wheather referencing pm.pmnum in subqueries may be possible or not.
    Code:
    SELECT pm .pmnum
         , pm .siteid
         , loc.description AS site
         , com.description AS workcategory
         , pm .description
         , jan.wonum AS jan
         , feb.wonum AS feb
         , mar.wonum AS mar
         , apr.wonum AS apr
    /* repeat from may to dec */
         , cmp.name  AS contractor 
    
     FROM  pm
     INNER JOIN
           locations   AS loc
      ON   loc.location = pm.location
      AND  loc.siteid   = pm.siteid
     INNER JOIN
           commodities AS com
      ON   com.commodity = pm.commoditygroup
     INNER JOIN
           companies   AS cmp
      ON   cmp.company = pm.vendor
     LEFT  OUTER JOIN
           (SELECT wo.wonum
                 , ROW_NUMBER() OVER() AS r_num
             FROM  workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.status <> 'CAN'
               AND wo.targstartdate < '2013-02-01'
           ) AS jan
     FULL  OUTER JOIN
           (SELECT wo.wonum
                 , ROW_NUMBER() OVER() AS r_num
             FROM  workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.status <> 'CAN'
               AND wo.targstartdate BETWEEN '2013-02-01' AND '2013-02-28'
           ) AS feb
      ON   feb.r_num = jan.r_num
     FULL  OUTER JOIN
           (SELECT wo.wonum
                 , ROW_NUMBER() OVER() AS r_num
             FROM  workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.status <> 'CAN'
               AND wo.targstartdate BETWEEN '2013-03-01' AND '2013-03-31'
           ) AS mar
      ON   mar.r_num = COALESCE(feb.r_num , jan.r_num)
     FULL  OUTER JOIN
           (SELECT wo.wonum
                 , ROW_NUMBER() OVER() AS r_num
             FROM  workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.status <> 'CAN'
               AND wo.targstartdate BETWEEN '2013-04-01' AND '2013-04-30'
           ) AS apr
      ON   apr.r_num = COALESCE(mar.r_num , feb.r_num , jan.r_num)
    /*
      For May to December FROM workorder
    */
      ON   0=0
    
     WHERE pm.siteid = 'AAA'
    ;

  10. #10
    Join Date
    Feb 2014
    Posts
    6

    Need duplicate records in suquery

    i am facing this error
    The ranking function "ROW_NUMBER" must have an ORDER BY clause.

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Consider this only as an example, but does this code do what you need?
    Code:
    DECLARE @t TABLE (
       id       INT
    ,  d        DATE);
    
    INSERT INTO @t
       VALUES (1, '2000-01-01'), (1, '2000-01-15'), (1, '2000-02-01'), (1, '2000-03-01')
    ,     (2, '2000-01-01'), (2, '2000-02-01'), (2, '2000-02-15'), (2, '2000-03-01')
    ,     (3, '2000-01-01'), (3, '2000-02-01'), (3, '2000-03-15'), (3, '2000-03-01');
    
    WITH cte AS (
       SELECT id, d, Row_Number() OVER (
          PARTITION BY id, DateAdd(month, 0, DateDiff(month, 0, d))
          ORDER BY d) AS o
          FROM @t
       )
    SELECT id
    ,  Jan = Min(CASE WHEN  1 = Month(d) THEN d END)
    ,  Feb = Min(CASE WHEN  2 = Month(d) THEN d END)
    ,  Mar = Min(CASE WHEN  3 = Month(d) THEN d END)
    ,  Apr = Min(CASE WHEN  4 = Month(d) THEN d END)
    ,  May = Min(CASE WHEN  5 = Month(d) THEN d END)
    ,  Jun = Min(CASE WHEN  6 = Month(d) THEN d END)
    ,  Jul = Min(CASE WHEN  7 = Month(d) THEN d END)
    ,  Aug = Min(CASE WHEN  8 = Month(d) THEN d END)
    ,  Sep = Min(CASE WHEN  9 = Month(d) THEN d END)
    ,  Oct = Min(CASE WHEN 10 = Month(d) THEN d END)
    ,  Nov = Min(CASE WHEN 11 = Month(d) THEN d END)
    ,  Dec = Min(CASE WHEN 12 = Month(d) THEN d END)
       FROM cte
       GROUP BY id, o
       ORDER BY id, o
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by sardarkhan View Post
    i am facing this error
    The ranking function "ROW_NUMBER" must have an ORDER BY clause.
    The solution for this issue may be simple and clear.
    Why didn't you tried the query with adding ORDER BY clauses.

    The query I showed didn't relate duplicated rows of jan, feb, so on...,
    so any order might be possible.
    If I may try to add ORDER BY clauses,
    "ORDER BY wo.targstartdate" may be a candidate.
    Last edited by tonkuma; 03-03-14 at 13:07.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Very simple example demonsrtating "LEFT JOIN ... FULL JOIN ... FULL JOIN" idiom, tested on DB2.

    Test data:
    Code:
    SELECT *
     FROM  @pm;
    ------------------------------------------------------------------------------
    
    PMNUM  DESCRIPTION             
    ------ ------------------------
         1 workorder: Jan, Mar     
         2 No workorder            
         3 workorder: Feb, Mar, Apr
    
      3 record(s) selected.
    
    
    SELECT *
     FROM  @workorder;
    ------------------------------------------------------------------------------
    
    PMNUM  WONUM  TARGSTARTDATE
    ------ ------ -------------
         1      1 2013-01-01   
         1      2 2013-01-15   
         1      3 2013-03-01   
         1      4 2013-03-15   
         1      5 2013-03-20   
         3      6 2013-02-02   
         3      7 2013-02-11   
         3      8 2013-02-20   
         3      9 2013-03-10   
         3     10 2013-04-01   
         3     11 2013-04-05   
    
      11 record(s) selected.

    Query:
    Note: LATERAL keyword was used to be able to reference pm.pmnum in subqueries.
    Code:
    SELECT pm.pmnum
         , pm.description
         , jan.wonum AS jan
         , feb.wonum AS feb
         , mar.wonum AS mar
         , apr.wonum AS apr
     FROM  @pm AS pm
     LEFT  OUTER JOIN
           LATERAL
           (SELECT wonum
                 , ROW_NUMBER() OVER( ORDER BY targstartdate ) AS r_num
             FROM  @workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.targstartdate BETWEEN '2013-01-01' AND '2013-01-31'
           ) AS jan
     FULL  OUTER JOIN
           LATERAL
           (SELECT wonum
                 , ROW_NUMBER() OVER( ORDER BY targstartdate ) AS r_num
             FROM  @workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.targstartdate BETWEEN '2013-02-01' AND '2013-02-28'
           ) AS feb
      ON   feb.r_num = jan.r_num
     FULL  OUTER JOIN
           LATERAL
           (SELECT wonum
                 , ROW_NUMBER() OVER( ORDER BY targstartdate ) AS r_num
             FROM  @workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.targstartdate BETWEEN '2013-03-01' AND '2013-03-31'
           ) AS mar
      ON   mar.r_num = COALESCE(feb.r_num , jan.r_num)
     FULL  OUTER JOIN
           LATERAL
           (SELECT wonum
                 , ROW_NUMBER() OVER( ORDER BY targstartdate ) AS r_num
             FROM  @workorder AS wo
             WHERE wo.pmnum = pm.pmnum
               AND wo.targstartdate BETWEEN '2013-04-01' AND '2013-04-30'
           ) AS apr
      ON   apr.r_num = COALESCE(mar.r_num , feb.r_num , jan.r_num)
      ON   0=0
    ;
    Resuls:
    Code:
    PMNUM  DESCRIPTION              JAN    FEB    MAR    APR   
    ------ ------------------------ ------ ------ ------ ------
         1 workorder: Jan, Mar           1      -      3      -
         1 workorder: Jan, Mar           2      -      4      -
         1 workorder: Jan, Mar           -      -      5      -
         2 No workorder                  -      -      -      -
         3 workorder: Feb, Mar, Apr      -      6      9     10
         3 workorder: Feb, Mar, Apr      -      7      -     11
         3 workorder: Feb, Mar, Apr      -      8      -      -
    
      7 record(s) selected.
    Last edited by tonkuma; 03-03-14 at 14:02. Reason: Add Note for Query.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    sardarkhan,

    I want to understand/clarify your requirements more concretely.
    (Before going to queries.)

    (1) Could you confirm the validity of my test data?
    If it was not valid, would you provide amended test data?
    Quote Originally Posted by tonkuma View Post
    Test data:
    Code:
    SELECT *
     FROM  @pm;
    ------------------------------------------------------------------------------
    
    PMNUM  DESCRIPTION             
    ------ ------------------------
         1 workorder: Jan, Mar     
         2 No workorder            
         3 workorder: Feb, Mar, Apr
    
      3 record(s) selected.
    
    
    SELECT *
     FROM  @workorder;
    ------------------------------------------------------------------------------
    
    PMNUM  WONUM  TARGSTARTDATE
    ------ ------ -------------
         1      1 2013-01-01   
         1      2 2013-01-15   
         1      3 2013-03-01   
         1      4 2013-03-15   
         1      5 2013-03-20   
         3      6 2013-02-02   
         3      7 2013-02-11   
         3      8 2013-02-20   
         3      9 2013-03-10   
         3     10 2013-04-01   
         3     11 2013-04-05   
    
      11 record(s) selected.
    (2) Were the following expected results from the test data?
    Resuls:
    Code:
    PMNUM  DESCRIPTION              JAN    FEB    MAR    APR   
    ------ ------------------------ ------ ------ ------ ------
         1 workorder: Jan, Mar           1      -      3      -
         1 workorder: Jan, Mar           2      -      4      -
         1 workorder: Jan, Mar           -      -      5      -
         2 No workorder                  -      -      -      -
         3 workorder: Feb, Mar, Apr      -      6      9     10
         3 workorder: Feb, Mar, Apr      -      7      -     11
         3 workorder: Feb, Mar, Apr      -      8      -      -
    
      7 record(s) selected.

  15. #15
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that both of this issue and the issue in this thread http://www.dbforums.com/microsoft-sq...ml#post6614100
    might be in a category of problems solved by "FULL OUTER JOIN ... FULL OUTER JOIN ..." idiom.

Tags for this Thread

Posting Permissions

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