Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2005
    Posts
    9

    Unanswered: Field Group header

    Hi

    I am new in SQL SERVER

    I have a table return values like that

    HTML Code:
    Row1 || Row2 || ERPID    || ParentID || LevelID || Category || SubCategory || DDate            || publish
    1    || 1    || 10152159 || 1015     || 2159    || LOCTITE  || LOCTITE1    || 29/01/2015 12:10 || 0
    1    || 2    || 10152134 || 1015     || 2134    || LOCTITE  || LOCTITE2    || 29/01/2015 12:10 || 0
    1    || 3    || 10152157 || 1015     || 2157    || LOCTITE  || LOCTITE3    || 29/01/2015 12:10 || 0
    2    || 1    || 10062003 || 1006     || 2003    || COMPUTER || COMPUTER1   || 29/01/2015 12:10 || 1
    2    || 2    || 10062148 || 1006     || 2148    || COMPUTER || COMPUTER2   || 29/01/2015 12:10 || 1
    2    || 3    || 10062026 || 1006     || 2026    || COMPUTER || COMPUTER3   || 29/01/2015 12:10 || 1
    3    || 1    || 10142003 || 1014     || 2003    || PARTS    || PARTS1      || 29/01/2015 12:10 || 0
    3    || 2    || 10142164 || 1014     || 2164    || PARTS    || PARTS2      || 29/01/2015 12:10 || 0
    4    || 1    || 10202175 || 1020     || 2175    || PRINTERS || PRINTERS1   || 29/01/2015 12:10 || 1
    4    || 2    || 10202177 || 1020     || 2177    || PRINTERS || PRINTERS2   || 29/01/2015 12:10 || 1
    4    || 3    || 10202181 || 1020     || 2181    || PRINTERS || PRINTERS3   || 29/01/2015 12:10 || 1
    4    || 4    || 10202186 || 1020     || 2186    || PRINTERS || PRINTERS4   || 29/01/2015 12:10 || 1
    5    || 1    || 10012004 || 1001     || 2004    || SCANERS  || SCANERS1    || 29/01/2015 12:10 || 0
    5    || 2    || 10012012 || 1001     || 2012    || SCANERS  || SCANERS2    || 29/01/2015 12:10 || 1
    5    || 3    || 10012015 || 1001     || 2015    || SCANERS  || SCANERS3    || 29/01/2015 12:10 || 1
    5    || 4    || 10012016 || 1001     || 2016    || SCANERS  || SCANERS4    || 29/01/2015 12:10 || 0
    5    || 5    || 10012029 || 1001     || 2029    || SCANERS  || SCANERS5    || 29/01/2015 12:10 || 1
    5    || 6    || 10012032 || 1001     || 2032    || SCANERS  || SCANERS6    || 29/01/2015 12:10 || 0
    and I want to look like that

    HTML Code:
    Row1 || Row2 || ERPID    || ParentID || LevelID || Category || SubCategory || DDate            || publish
    1    || 1    || 10151015 || 1015     || 1015    || LOCTITE  ||             || 29/01/2015 12:10 || 0
    1    || 1    || 10152159 || 1015     || 2159    || LOCTITE  || LOCTITE1    || 29/01/2015 12:10 || 0
    1    || 2    || 10152134 || 1015     || 2134    || LOCTITE  || LOCTITE2    || 29/01/2015 12:10 || 0
    1    || 3    || 10152157 || 1015     || 2157    || LOCTITE  || LOCTITE3    || 29/01/2015 12:10 || 0
    2    || 2    || 10061006 || 1006     || 1006    || COMPUTER ||             || 29/01/2015 12:10 || 1
    2    || 1    || 10062003 || 1006     || 2003    || COMPUTER || COMPUTER1   || 29/01/2015 12:10 || 1
    2    || 2    || 10062148 || 1006     || 2148    || COMPUTER || COMPUTER2   || 29/01/2015 12:10 || 1
    2    || 3    || 10062026 || 1006     || 2026    || COMPUTER || COMPUTER3   || 29/01/2015 12:10 || 1
    3    || 3    || 10141014 || 1014     || 1014    || PARTS    ||             || 29/01/2015 12:10 || 0
    3    || 1    || 10142003 || 1014     || 2003    || PARTS    || PARTS1      || 29/01/2015 12:10 || 0
    3    || 2    || 10142164 || 1014     || 2164    || PARTS    || PARTS2      || 29/01/2015 12:10 || 0
    4    || 4    || 10201020 || 1020     || 1020    || PRINTERS ||             || 29/01/2015 12:10 || 1
    4    || 1    || 10202175 || 1020     || 2175    || PRINTERS || PRINTERS1   || 29/01/2015 12:10 || 1
    4    || 2    || 10202177 || 1020     || 2177    || PRINTERS || PRINTERS2   || 29/01/2015 12:10 || 1
    4    || 3    || 10202181 || 1020     || 2181    || PRINTERS || PRINTERS3   || 29/01/2015 12:10 || 1
    4    || 4    || 10202186 || 1020     || 2186    || PRINTERS || PRINTERS4   || 29/01/2015 12:10 || 1
    5    || 5    || 10011001 || 1001     || 1001    || SCANERS  ||             || 29/01/2015 12:10 || 0
    5    || 1    || 10012004 || 1001     || 2004    || SCANERS  || SCANERS1    || 29/01/2015 12:10 || 0
    5    || 2    || 10012012 || 1001     || 2012    || SCANERS  || SCANERS2    || 29/01/2015 12:10 || 1
    5    || 3    || 10012015 || 1001     || 2015    || SCANERS  || SCANERS3    || 29/01/2015 12:10 || 1
    5    || 4    || 10012016 || 1001     || 2016    || SCANERS  || SCANERS4    || 29/01/2015 12:10 || 0
    5    || 5    || 10012029 || 1001     || 2029    || SCANERS  || SCANERS5    || 29/01/2015 12:10 || 1
    5    || 6    || 10012032 || 1001     || 2032    || SCANERS  || SCANERS6    || 29/01/2015 12:10 || 0
    How I can do that?

    Thank you

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Care to share your SQL code?
    My guess is that you have an INNER join between your parent and child categories. Making this in to an OUTER join may get you close to what you want.
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2005
    Posts
    9
    Quote Originally Posted by gvee View Post
    Care to share your SQL code?
    My guess is that you have an INNER join between your parent and child categories. Making this in to an OUTER join may get you close to what you want.
    I use OUTER in my sql code here is the code

    Code:
    SELECT     
    DENSE_RANK() OVER (ORDER BY Ctgry1.Descr ASC) AS Row1, 
    Row_Number() OVER (partition BY Ctgry1.Descr ORDER BY Ctgry1.Descr, T1.Descr ASC) AS Row2, 
    left(t1.ID,4)+right(t1.levelid,4) AS ERPID,
    T1.ID AS Ctgry1ID, Ctgry1.ID AS ParentID, T1.LevelID, Ctgry1.Descr AS Category, T1.Descr AS SubCategory, 
    GetDate() AS DDate, CASE WHEN T1.ID IN
        (SELECT     ID
          FROM          Ctgry1 LEFT JOIN
                                 Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
                                 PrLLines ON (PrLLines.MaterialAA = Material.AA)
          WHERE      PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) NOT IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 1 WHEN LEFT(T1.ID, 4) + RIGHT(T1.ID, 4) IN
        (SELECT DISTINCT ParentID + ParentID
          FROM          Ctgry1 LEFT JOIN
                                 Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
                                 PrLLines ON (PrLLines.MaterialAA = Material.AA)
          WHERE      PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) NOT IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 1 WHEN LEFT(T1.ID, 4) IN
        (SELECT DISTINCT LEFT(ID, 4)
          FROM          Ctgry1 LEFT JOIN
                                 Material ON (Material.Ctgry1 = Ctgry1.ID) LEFT JOIN
                                 PrLLines ON (PrLLines.MaterialAA = Material.AA)
          WHERE      PrLLines.PrListAA = 1868 AND LEFT(material.HrZoom1, 4) IN (1012, 1014, 1016, 1017, 1023, 1015)) THEN 0 ELSE 0 END AS publish
    FROM Ctgry1 
    LEFT OUTER JOIN Ctgry1 AS T1 ON T1.ParentID = Ctgry1.ID
    WHERE (T1.Descr IS NOT NULL)

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    What happens if you remove the where clause?
    This is effectively turning your join in to an INNER
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    probably the most common error with outer joins. I TRY to get the point across all of the time that when you use explicit joins, keep all predicates for that table in the ON clause only, even if its an inner join as someone may change it to an outer in the future, then you end up with incorrect data and a slow query.
    Dave

  6. #6
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    Please follow basic Netiquette and post the DDL we need to answer this. Follow industry and ANSI/ISO standards in your data. You should follow ISO-11179 rules for naming data elements. You should follow ISO-8601 rules for displaying temporal data. We need to know the data types, keys and constraints on the table. Avoid dialect in favor of ANSI/ISO Standard SQL.

    We do not care about the display; that is done in a presentation layer, never in a query. Postting pictures is both rude and useless! What you did post has repeated groups, split attributes, and many other fundamental design errors. In fact, you did not even get the date formats correc

    You have a “d_date” which makes no sense. The column values are timestamps and not a date. But more than that, the “_date” is called an attribute property, “d_” is the attribute. What kind of attribute is a “d_”?? . You failed to use the ISO-8601 date format, which is the only one allowed in ANSI/ISO Standard SQL Finally, do you really the time or is the date of this attribute enough?

    There is no such crap a “category” in RDBMS. You have to have a <something in particular>_category” in a valid data model. Then you split out the vague “subcategory”; wrong name and wrong concept. Have you ever been to a library and see the Dewy Decimal Classifications system? The “<something>_category” is one single attribute, not a pair of independent attributes like (longitude, latitude) pairs.

    I suspect that “parent_id” and “level_id” suffer the same design error. But did not provide specs with the DDL that you also skipped.

    Your “row_1” and “row_2” look at a repeated group on top of attribute splitting. But even worse, they look like physical locations on a physical printout! Again, we do not do display formatting in SQL; that is a presentation layer.

    Your “publish” is verb; attributes are nouns. But this looks like an assembly language bit flag. We do not use flags in RDBMS; we discover the current state of being with predicates.

    OUTER JOINs are rare in a correctly designed schema because the DRI assures matches among the table. The fact that you used them is not automatically wrong, but it is really awful “code smell” in the code.

    And you need to read and download the PDF for:
    https://www.simple-talk.com/books/sq...l-code-smells/

    If you can post better spe3cs, we can help you get rid of this mess.

  7. #7
    Join Date
    Jun 2005
    Posts
    9
    I have to thank you all for your help.

    You help me allot to solve my problem.

    Thank you.

Posting Permissions

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