Post reply

Order BY clause in View using Stuff Function.

  • January 28, 2019 at 8:41 am

    #373645

    I have two views.

    My First View definition

    CREATE VIEW FIRST_VIEW AS
    SELECT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER1 FROM T_PROD_TEXT PT
    LEFT JOIN T_PHRASE_LINKAGE PHL
        ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
    INNER JOIN T_PHRASE_TRANSLATIONS PHT
        ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
    WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
    UNION ALL
    SELECT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER2 FROM T_PROD_ALIAS_TEXT PT
    LEFT JOIN T_PHRASE_LINKAGE PHL
        ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
    INNER JOIN T_PHRASE_TRANSLATIONS PHT
        ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
    WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'

    Above view Gives below Output.

    F_PRODUCT        F_TEXT_CODE    F_PHRASE    COUNTER1

    WVTST]PROD00|5     MANU0001     A           7734163
    WVTST]PROD00|5     MANU0002     B           7734162
    ABPROD-BLACK      MANU0001     C           7714721


    created another one view From above view(FIRST_VIEW) using stuff function.
    CREATE VIEW [dbo].[V1_STUFFED] AS
    SELECT V1.F_PRODUCT,
        F_TEXT_CODES = STUFF((SELECT DISTINCT ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT FOR XML PATH('')),1,1,''),
        F_PHRASES = STUFF((SELECT DISTINCT ' |par ' + V3.F_PHRASE FROM FIRST_VIEW V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT FOR XML PATH('')),1,5,'')
    FROM FIRST_VIEW V1 GROUP BY V1.F_PRODUCT
    Above stuff view gives below Output.It combines the F_TEXT_CODES AND F_PHRASES in single row for the Products which contains more then one f_text_codes.

    F_PRODUCT          F_TEXT_CODES      F_PHRASES
    WVTST]PROD00|5    MANU0001,MANU0002   A,B
    ABPROD-BLACK      MANU0001             C      

    But i Required below output.

    F_PRODUCT           F_TEXT_CODES      F_PHRASES
    ABPROD-BLACK       MANU0001             C
    WVTST]PROD00|5     MANU0002,MANU0001   B,A

    In the above Required output the F_TEXT_CODES(MANU0002) displays before(MANU0001)Text_Code because MANU002 Contains the Counter1 value(7734162)    and MANU0001 CONTAINS COUNTER(7734163) for the Product,so F_TEXT_CODES AND F_PHRASE displays for the F_PRODUCT based order of Counter 1.

    How can i use order by clause in view using stuff for the above requirement?Please help.

  • January 28, 2019 at 9:12 am

    #2020290

    jkramprakash - Monday, January 28, 2019 8:41 AM

    I have two views.

    My First View definition

    CREATE VIEW FIRST_VIEW AS
    SELECT PT.F_PRODUCT AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER1 FROM T_PROD_TEXT PT
    LEFT JOIN T_PHRASE_LINKAGE PHL
        ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
    INNER JOIN T_PHRASE_TRANSLATIONS PHT
        ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
    WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'
    UNION ALL
    SELECT PT.F_ALIAS AS F_PRODUCT, PT.F_TEXT_CODE AS F_TEXT_CODE, PHT.F_PHRASE AS F_PHRASE,F_COUNTER COUNTER2 FROM T_PROD_ALIAS_TEXT PT
    LEFT JOIN T_PHRASE_LINKAGE PHL
        ON PT.F_TEXT_CODE = PHL.F_TEXT_CODE
    INNER JOIN T_PHRASE_TRANSLATIONS PHT
        ON PHL.F_PHRASE_ID = PHT.F_PHRASE_ID
    WHERE PT.F_DATA_CODE = 'MANU' AND PHT.F_LANGUAGE = 'EN'

    Above view Gives below Output.

    F_PRODUCT        F_TEXT_CODE    F_PHRASE    COUNTER1

    WVTST]PROD00|5     MANU0001     A           7734163
    WVTST]PROD00|5     MANU0002     B           7734162
    ABPROD-BLACK      MANU0001     C           7714721


    created another one view From above view(FIRST_VIEW) using stuff function.
    CREATE VIEW [dbo].[V1_STUFFED] AS
    SELECT V1.F_PRODUCT,
        F_TEXT_CODES = STUFF((SELECT DISTINCT ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT FOR XML PATH('')),1,1,''),
        F_PHRASES = STUFF((SELECT DISTINCT ' |par ' + V3.F_PHRASE FROM FIRST_VIEW V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT FOR XML PATH('')),1,5,'')
    FROM FIRST_VIEW V1 GROUP BY V1.F_PRODUCT
    Above stuff view gives below Output.It combines the F_TEXT_CODES AND F_PHRASES in single row for the Products which contains more then one f_text_codes.

    F_PRODUCT          F_TEXT_CODES      F_PHRASES
    WVTST]PROD00|5    MANU0001,MANU0002   A,B
    ABPROD-BLACK      MANU0001             C      

    But i Required below output.

    F_PRODUCT           F_TEXT_CODES      F_PHRASES
    WVTST]PROD00|5     MANU0002,MANU0001   B,A
    ABPROD-BLACK       MANU0001             C

    In the above Required output the F_TEXT_CODES(MANU0002) displays before(MANU0001)Text_Code because MANU002 Contains the Counter1 value(7734162)    and MANU0001 CONTAINS COUNTER(7734163) for the Product,so F_TEXT_CODES AND F_PHRASE displays for the F_PRODUCT based order of Counter 1.

    How can i use order by clause in view using stuff for the above requirement?Please help.

    Use the ORDER BY before FOR XML.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 28, 2019 at 9:38 am

    #2020300

    I tired  the ORDER BY before FOR XML but it is showing error message.

  • January 28, 2019 at 9:45 am

    #2020304

    jkramprakash - Monday, January 28, 2019 9:38 AM

    I tired  the ORDER BY before FOR XML but it is showing error message.

    Are we supposed to guess the error message and the code you used?

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2

  • January 28, 2019 at 10:35 am

    #2020312

    Have you tried adding an order by in the stuff:
    F_TEXT_CODES = STUFF((SELECT DISTINCT ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2
    WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY colToOrderBy FOR XML PATH('')),1,1,''),
    If that doesn't work you might have to add a TOP(n):
    F_TEXT_CODES = STUFF((SELECT DISTINCT TOP(10000000) ',' + V2.F_TEXT_CODE FROM FIRST_VIEW V2
    WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY colToOrderBy FOR XML PATH('')),1,1,''),

  • January 29, 2019 at 3:43 am

    #2020376

    SELECT V1.F_PRODUCT,
      F_TEXT_CODES = STUFF((SELECT DISTINCT TOP (1000000)',' + V2.F_TEXT_CODE FROM first_view V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY V2.COUNTER1 FOR XML PATH('')),1,1,''),
      F_PHRASES = STUFF((SELECT DISTINCT TOP(100000) ' |par ' + V3.F_PHRASE FROM first_view V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT ORDER BY V3.COUNTER1 FOR XML PATH('')),1,5,'')
    FROM first_view V1 GROUP BY V1.F_PRODUCT

    I tried your both query but it is showing below error message.
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
    Msg 145, Level 15, State 1, Line 2
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

  • January 29, 2019 at 4:06 am

    #2020378

    jkramprakash - Tuesday, January 29, 2019 3:43 AM

    SELECT V1.F_PRODUCT,
      F_TEXT_CODES = STUFF((SELECT DISTINCT TOP (1000000)',' + V2.F_TEXT_CODE FROM first_view V2 WHERE V1.F_PRODUCT = V2.F_PRODUCT ORDER BY V2.COUNTER1 FOR XML PATH('')),1,1,''),
      F_PHRASES = STUFF((SELECT DISTINCT TOP(100000) ' |par ' + V3.F_PHRASE FROM first_view V3 WHERE V1.F_PRODUCT = V3.F_PRODUCT ORDER BY V3.COUNTER1 FOR XML PATH('')),1,5,'')
    FROM first_view V1 GROUP BY V1.F_PRODUCT

    I tried your both query but it is showing below error message.
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
    Msg 145, Level 15, State 1, Line 2
    ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

    Do you need the distinct in the query? i.e. Is there ever more than one value of F_TEXT_CODE the same for a F_PRODUCT?

  • January 29, 2019 at 4:33 am

    #2020381

    This should do it:
    SELECT V1.F_PRODUCT,
           STUFF((SELECT ',' + V2.F_TEXT_CODE
                    FROM (SELECT V3.F_PRODUCT,
                                 V3.F_TEXT_CODE,
                                 MIN(V3.COUNTER1) COUNTER1
                            FROM FIRST_VIEW V3
                           GROUP BY V3.F_PRODUCT,
                                    V3.F_TEXT_CODE) V2
                   WHERE V1.F_PRODUCT = V2.F_PRODUCT
                   ORDER BY V2.COUNTER1
                     FOR XML PATH('')),1,1,'') AS F_TEXT_CODES,
           STUFF((SELECT ',' + V2.F_PHRASE
                    FROM (SELECT ' |par ' + V3.F_PRODUCT F_PRODUCT,
                                 V3.F_PHRASE,
                                 MIN(V3.COUNTER1) COUNTER1
                            FROM FIRST_VIEW V3
                           GROUP BY V3.F_PRODUCT,
                                    V3.F_PHRASE) V2
                   WHERE V1.F_PRODUCT = V2.F_PRODUCT
                   ORDER BY V2.COUNTER1
                     FOR XML PATH('')),1,1,'') AS F_PHRASES
      FROM FIRST_VIEW V1
     GROUP BY V1.F_PRODUCT

  • January 29, 2019 at 5:20 am

    #2020385

    Thank you.Working fine.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply