sql SORT BY on a UNION query

-2

Actually i try to sort my sql but it's not sorting

where i need to add SORT BY TSTATUS

 $sql="

(

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        NULL as col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'cmg' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.cmg_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

      ( tt.TSTATUS < 9 )

      {$customer_list}             

) UNION (

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        tt.col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'ar' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.ar_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

        ( tt.TSTATUS < 10 )

        {$customer_list} 

)";

edited 20 hours ago

GMB

3,576519

asked 22 hours ago

Learning

306

3

There is no ORDER BY, without it, you'll not get a sorted result.
– HoneyBadger
22 hours ago

I added after where condition but it's not working so i asked where to add ORDER BY
– Learning
21 hours ago

add a comment |

-2

Actually i try to sort my sql but it's not sorting

where i need to add SORT BY TSTATUS

 $sql="

(

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        NULL as col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'cmg' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.cmg_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

      ( tt.TSTATUS < 9 )

      {$customer_list}             

) UNION (

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        tt.col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'ar' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.ar_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

        ( tt.TSTATUS < 10 )

        {$customer_list} 

)";

edited 20 hours ago

GMB

3,576519

asked 22 hours ago

Learning

306

3

There is no ORDER BY, without it, you'll not get a sorted result.
– HoneyBadger
22 hours ago

I added after where condition but it's not working so i asked where to add ORDER BY
– Learning
21 hours ago

add a comment |

-2

Actually i try to sort my sql but it's not sorting

where i need to add SORT BY TSTATUS

 $sql="

(

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        NULL as col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'cmg' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.cmg_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

      ( tt.TSTATUS < 9 )

      {$customer_list}             

) UNION (

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        tt.col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'ar' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.ar_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

        ( tt.TSTATUS < 10 )

        {$customer_list} 

)";

edited 20 hours ago

GMB

3,576519

asked 22 hours ago

Learning

306

Actually i try to sort my sql but it's not sorting

where i need to add SORT BY TSTATUS

 $sql="

(

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        NULL as col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'cmg' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.cmg_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

      ( tt.TSTATUS < 9 )

      {$customer_list}             

) UNION (

    SELECT

        tt.tstatus,

        tt.ticketnbr,

        tt.col1,

        tt.col2,

        tt.col3,

        tt.col4,

        tt.col5,

        tt.col6,

        tt.col7,

        tt.col8,

        'ar' as tickettype,

        CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

    FROM

        aradmin.ar_troubleticket tt

        LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

    WHERE

        ( tt.TSTATUS < 10 )

        {$customer_list} 

)";

sql

edited 20 hours ago

GMB

3,576519

asked 22 hours ago

Learning

306

edited 20 hours ago

GMB

3,576519

asked 22 hours ago

Learning

306

edited 20 hours ago

GMB

3,576519

edited 20 hours ago

GMB

3,576519

edited 20 hours ago

GMB

3,576519

asked 22 hours ago

Learning

306

asked 22 hours ago

Learning

306

asked 22 hours ago

Learning

306

3

There is no ORDER BY, without it, you'll not get a sorted result.
– HoneyBadger
22 hours ago

I added after where condition but it's not working so i asked where to add ORDER BY
– Learning
21 hours ago

add a comment |

3

There is no ORDER BY, without it, you'll not get a sorted result.
– HoneyBadger
22 hours ago

I added after where condition but it's not working so i asked where to add ORDER BY
– Learning
21 hours ago

There is no ORDER BY, without it, you'll not get a sorted result.
– HoneyBadger
22 hours ago

I added after where condition but it's not working so i asked where to add ORDER BY
– Learning
21 hours ago

add a comment |

3 Answers
3

active

oldest

votes

You just need to add the ORDER BY clause at the very end of the query. In standard SQL the ORDER BY is applied to the whole resultset.

Also I believe that you do not need the surrounding parenthesis around the UNIONed subqueries, I removed them.

Query :

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    NULL as col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'cmg' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.cmg_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

  ( tt.TSTATUS < 9 )

  {$customer_list}

UNION

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    tt.col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'ar' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.ar_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

   ( tt.TSTATUS < 10 )

   {$customer_list}           

ORDER BY tstatus

edited 20 hours ago

answered 21 hours ago

GMB

3,576519

add a comment |

Just append ORDER BY tt.tstatus at the end of the query to get the sorted result.

answered 21 hours ago

Hp_issei

30818

where to append that's my problem
– Learning
21 hours ago

After {$customer_list})
– Hp_issei
21 hours ago

NO it's not working
– Learning
21 hours ago

add a comment |

Create a dummy table of the UNION results and sort by the attribute you need. Try this :

SELECT * FROM (

              SELECT

                tt.tstatus,

                tt.ticketnbr,

                tt.col1,

                tt.col2,

                NULL as col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'cmg' as tickettype,

              CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

              aradmin.cmg_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

        WHERE

          ( tt.TSTATUS < 9 )

          {$customer_list} 



      ) UNION (

        SELECT

          tt.tstatus,

          tt.ticketnbr,

          tt.col1,

          tt.col2,

          tt.col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'ar' as tickettype,

                CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

                aradmin.ar_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

                  ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

              WHERE

                ( tt.TSTATUS < 10 )

                {$customer_list} 

          )) dummy ORDER BY dummy.tstatus

edited 20 hours ago

answered 21 hours ago

nbirla

41326

I got a error SQL command not properly ended in
– Learning
21 hours ago

What is the exact error you are getting?
– nbirla
20 hours ago

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53943171%2fsql-sort-by-on-a-union-query%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

You just need to add the ORDER BY clause at the very end of the query. In standard SQL the ORDER BY is applied to the whole resultset.

Also I believe that you do not need the surrounding parenthesis around the UNIONed subqueries, I removed them.

Query :

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    NULL as col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'cmg' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.cmg_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

  ( tt.TSTATUS < 9 )

  {$customer_list}

UNION

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    tt.col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'ar' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.ar_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

   ( tt.TSTATUS < 10 )

   {$customer_list}           

ORDER BY tstatus

edited 20 hours ago

answered 21 hours ago

GMB

3,576519

add a comment |

You just need to add the ORDER BY clause at the very end of the query. In standard SQL the ORDER BY is applied to the whole resultset.

Also I believe that you do not need the surrounding parenthesis around the UNIONed subqueries, I removed them.

Query :

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    NULL as col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'cmg' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.cmg_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

  ( tt.TSTATUS < 9 )

  {$customer_list}

UNION

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    tt.col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'ar' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.ar_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

   ( tt.TSTATUS < 10 )

   {$customer_list}           

ORDER BY tstatus

edited 20 hours ago

answered 21 hours ago

GMB

3,576519

add a comment |

You just need to add the ORDER BY clause at the very end of the query. In standard SQL the ORDER BY is applied to the whole resultset.

Also I believe that you do not need the surrounding parenthesis around the UNIONed subqueries, I removed them.

Query :

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    NULL as col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'cmg' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.cmg_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

  ( tt.TSTATUS < 9 )

  {$customer_list}

UNION

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    tt.col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'ar' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.ar_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

   ( tt.TSTATUS < 10 )

   {$customer_list}           

ORDER BY tstatus

edited 20 hours ago

answered 21 hours ago

GMB

3,576519

You just need to add the ORDER BY clause at the very end of the query. In standard SQL the ORDER BY is applied to the whole resultset.

Also I believe that you do not need the surrounding parenthesis around the UNIONed subqueries, I removed them.

Query :

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    NULL as col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'cmg' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.cmg_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

  ( tt.TSTATUS < 9 )

  {$customer_list}

UNION

SELECT

    tt.tstatus,

    tt.ticketnbr,

    tt.col1,

    tt.col2,

    tt.col3,

    tt.col4,

    tt.col5,

    tt.col6,

    tt.col7,

    tt.col8,

    'ar' as tickettype,

    CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

FROM

    aradmin.ar_troubleticket tt

    LEFT JOIN ARADMIN.TT_CUSTOMERLOGENTRY cl 

        ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

WHERE

   ( tt.TSTATUS < 10 )

   {$customer_list}           

ORDER BY tstatus

edited 20 hours ago

answered 21 hours ago

GMB

3,576519

edited 20 hours ago

answered 21 hours ago

GMB

3,576519

answered 21 hours ago

GMB

3,576519

answered 21 hours ago

GMB

3,576519

add a comment |

Just append ORDER BY tt.tstatus at the end of the query to get the sorted result.

answered 21 hours ago

Hp_issei

30818

where to append that's my problem
– Learning
21 hours ago

After {$customer_list})
– Hp_issei
21 hours ago

NO it's not working
– Learning
21 hours ago

add a comment |

Just append ORDER BY tt.tstatus at the end of the query to get the sorted result.

answered 21 hours ago

Hp_issei

30818

where to append that's my problem
– Learning
21 hours ago

After {$customer_list})
– Hp_issei
21 hours ago

NO it's not working
– Learning
21 hours ago

add a comment |

Just append ORDER BY tt.tstatus at the end of the query to get the sorted result.

answered 21 hours ago

Hp_issei

30818

Just append ORDER BY tt.tstatus at the end of the query to get the sorted result.

answered 21 hours ago

Hp_issei

30818

answered 21 hours ago

Hp_issei

30818

answered 21 hours ago

Hp_issei

30818

answered 21 hours ago

Hp_issei

30818

where to append that's my problem
– Learning
21 hours ago

After {$customer_list})
– Hp_issei
21 hours ago

NO it's not working
– Learning
21 hours ago

add a comment |

where to append that's my problem
– Learning
21 hours ago

After {$customer_list})
– Hp_issei
21 hours ago

NO it's not working
– Learning
21 hours ago

where to append that's my problem
– Learning
21 hours ago

After {$customer_list})
– Hp_issei
21 hours ago

NO it's not working
– Learning
21 hours ago

add a comment |

Create a dummy table of the UNION results and sort by the attribute you need. Try this :

SELECT * FROM (

              SELECT

                tt.tstatus,

                tt.ticketnbr,

                tt.col1,

                tt.col2,

                NULL as col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'cmg' as tickettype,

              CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

              aradmin.cmg_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

        WHERE

          ( tt.TSTATUS < 9 )

          {$customer_list} 



      ) UNION (

        SELECT

          tt.tstatus,

          tt.ticketnbr,

          tt.col1,

          tt.col2,

          tt.col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'ar' as tickettype,

                CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

                aradmin.ar_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

                  ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

              WHERE

                ( tt.TSTATUS < 10 )

                {$customer_list} 

          )) dummy ORDER BY dummy.tstatus

edited 20 hours ago

answered 21 hours ago

nbirla

41326

I got a error SQL command not properly ended in
– Learning
21 hours ago

What is the exact error you are getting?
– nbirla
20 hours ago

add a comment |

Create a dummy table of the UNION results and sort by the attribute you need. Try this :

SELECT * FROM (

              SELECT

                tt.tstatus,

                tt.ticketnbr,

                tt.col1,

                tt.col2,

                NULL as col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'cmg' as tickettype,

              CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

              aradmin.cmg_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

        WHERE

          ( tt.TSTATUS < 9 )

          {$customer_list} 



      ) UNION (

        SELECT

          tt.tstatus,

          tt.ticketnbr,

          tt.col1,

          tt.col2,

          tt.col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'ar' as tickettype,

                CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

                aradmin.ar_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

                  ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

              WHERE

                ( tt.TSTATUS < 10 )

                {$customer_list} 

          )) dummy ORDER BY dummy.tstatus

edited 20 hours ago

answered 21 hours ago

nbirla

41326

I got a error SQL command not properly ended in
– Learning
21 hours ago

What is the exact error you are getting?
– nbirla
20 hours ago

add a comment |

Create a dummy table of the UNION results and sort by the attribute you need. Try this :

SELECT * FROM (

              SELECT

                tt.tstatus,

                tt.ticketnbr,

                tt.col1,

                tt.col2,

                NULL as col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'cmg' as tickettype,

              CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

              aradmin.cmg_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

        WHERE

          ( tt.TSTATUS < 9 )

          {$customer_list} 



      ) UNION (

        SELECT

          tt.tstatus,

          tt.ticketnbr,

          tt.col1,

          tt.col2,

          tt.col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'ar' as tickettype,

                CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

                aradmin.ar_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

                  ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

              WHERE

                ( tt.TSTATUS < 10 )

                {$customer_list} 

          )) dummy ORDER BY dummy.tstatus

edited 20 hours ago

answered 21 hours ago

nbirla

41326

Create a dummy table of the UNION results and sort by the attribute you need. Try this :

SELECT * FROM (

              SELECT

                tt.tstatus,

                tt.ticketnbr,

                tt.col1,

                tt.col2,

                NULL as col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'cmg' as tickettype,

              CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

              aradmin.cmg_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

            ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

        WHERE

          ( tt.TSTATUS < 9 )

          {$customer_list} 



      ) UNION (

        SELECT

          tt.tstatus,

          tt.ticketnbr,

          tt.col1,

          tt.col2,

          tt.col3,

                tt.col4,

                tt.col5,

                tt.col6,

                tt.col7,

                tt.col8,

                'ar' as tickettype,

                CASE WHEN cl.parentid IS NOT NULL THEN 1 ELSE 0 END as has_log

              FROM

                aradmin.ar_troubleticket tt

                LEFT JOIN

                  ARADMIN.TT_CUSTOMERLOGENTRY cl 

                  ON ( tt.ticketnbr=cl.parentid AND cl.schema='AR:TroubleTicket' AND cl.status=0 )

              WHERE

                ( tt.TSTATUS < 10 )

                {$customer_list} 

          )) dummy ORDER BY dummy.tstatus

edited 20 hours ago

answered 21 hours ago

nbirla

41326

edited 20 hours ago

answered 21 hours ago

nbirla

41326

answered 21 hours ago

nbirla

41326

answered 21 hours ago

nbirla

41326

I got a error SQL command not properly ended in
– Learning
21 hours ago

What is the exact error you are getting?
– nbirla
20 hours ago

add a comment |

I got a error SQL command not properly ended in
– Learning
21 hours ago

What is the exact error you are getting?
– nbirla
20 hours ago

I got a error SQL command not properly ended in
– Learning
21 hours ago

What is the exact error you are getting?
– nbirla
20 hours ago

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

Some of your past answers have not been well-received, and you're in danger of being blocked from answering.

Please pay close attention to the following guidance:

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtjtk