Order By Command Not Working

Still would not go.  I did it just as shown.

What if you do this?

SELECT DISTINCT

                  b.customer_no,

                  e.perf_code,

                  e.perf_dt,

                  f.description AS perf_name,

                  i.description AS prod_season_name,

                  f.text3 AS studio_teacher,

                  d.fname,

                  d.lname,

                  DATEDIFF(year, CONVERT(nvarchar(30),

                  z.key_value, 126),

                  CONVERT(nvarchar(30),

                  GETDATE(), 126)) AS age_in_years,

                  ISNULL(dbo.AF_FORMAT_STRING(t.phone, '(@@@) @@@-@@@@'), '') AS phone_1,

                  x.description AS PriceType,

                  assoc.fname AS associated_fname,

                  assoc.lname AS associated_lname,

                  assoc.sort_name AS associated_sort_name, --ADDITION HERE

                  T_SPECIAL_REQ.notes

                  FROM         T_SUB_LINEITEM AS a INNER JOIN

                  T_ORDER AS b ON a.order_no = b.order_no INNER JOIN

                  T_LINEITEM AS c ON a.li_seq_no = c.li_seq_no INNER JOIN

                  T_CUSTOMER AS d ON b.customer_no = d.customer_no INNER JOIN

                  VS_PERF AS e ON a.perf_no = e.perf_no INNER JOIN

                  T_PROD_SEASON AS h ON e.prod_season_no = h.prod_season_no INNER JOIN

                  T_INVENTORY AS f ON e.perf_no = f.inv_no INNER JOIN

                  T_INVENTORY AS i ON h.prod_season_no = i.inv_no INNER JOIN

                  T_ADDRESS AS g ON d.customer_no = g.customer_no AND g.primary_ind = 'Y' INNER JOIN

                  TR_PRICE_TYPE AS x ON a.price_type = x.id INNER JOIN

                  T_SPECIAL_REQ ON a.li_seq_no = T_SPECIAL_REQ.li_seq_no LEFT OUTER JOIN

                  TX_CUST_KEYWORD AS z ON b.customer_no = z.customer_no AND z.keyword_no = 1 LEFT OUTER JOIN

                  T_PHONE AS t ON b.customer_no = t.customer_no AND t.type = 1 LEFT OUTER JOIN

                  T_CUSTOMER AS assoc ON assoc.customer_no = T_SPECIAL_REQ.assoc_customer_no

                  WHERE     (e.season = @season_no) AND

                  (@prod_season_no = h.prod_season_no) AND

                  (@perf_no = e.perf_no) AND

                  (a.comp_code <> 32 OR

                  a.comp_code IS NULL)

                  ORDER BY assoc.sort_name

                  --associated_lname DESC, associated_fname DESC

Can you share with me exactly how your using that code in your report.  I do see it in the table however, it is like my report simply will not render that way. 

SELECT DISTINCT

                  b.customer_no, e.perf_code, e.perf_dt, f.description AS perf_name, i.description AS prod_season_name, f.text3 AS studio_teacher, d.fname, d.lname,

                  DATEDIFF(year, CONVERT(nvarchar(30), z.key_value, 126), CONVERT(nvarchar(30), GETDATE(), 126)) AS age_in_years,

                  ISNULL(dbo.AF_FORMAT_STRING(t.phone, '(@@@) @@@-@@@@'), '') AS phone_1, x.description AS PriceType, assoc.fname AS associated_fname,

                  assoc.lname AS associated_lname, T_SPECIAL_REQ.notes

                  FROM         T_SUB_LINEITEM AS a INNER JOIN

                  T_ORDER AS b ON a.order_no = b.order_no INNER JOIN

                  T_LINEITEM AS c ON a.li_seq_no = c.li_seq_no INNER JOIN

                  T_CUSTOMER AS d ON b.customer_no = d.customer_no INNER JOIN

                  VS_PERF AS e ON a.perf_no = e.perf_no INNER JOIN

                  T_PROD_SEASON AS h ON e.prod_season_no = h.prod_season_no INNER JOIN

                  T_INVENTORY AS f ON e.perf_no = f.inv_no INNER JOIN

                  T_INVENTORY AS i ON h.prod_season_no = i.inv_no INNER JOIN

                  T_ADDRESS AS g ON d.customer_no = g.customer_no AND g.primary_ind = 'Y' INNER JOIN

                  TR_PRICE_TYPE AS x ON a.price_type = x.id INNER JOIN

                  T_SPECIAL_REQ ON a.li_seq_no = T_SPECIAL_REQ.li_seq_no LEFT OUTER JOIN

                  TX_CUST_KEYWORD AS z ON b.customer_no = z.customer_no AND z.keyword_no = 1 LEFT OUTER JOIN

                  T_PHONE AS t ON b.customer_no = t.customer_no AND t.type = 1 LEFT OUTER JOIN

                  T_CUSTOMER AS assoc ON assoc.customer_no = T_SPECIAL_REQ.assoc_customer_no

                  WHERE     (e.season = @season_no) AND (@prod_season_no = h.prod_season_no) AND (@perf_no = e.perf_no) AND (a.comp_code <> 32 OR

                  a.comp_code IS NULL)

                  ORDER BY associated_lname DESC, associated_fname DESC

            </CommandText>

There is a new column in t_customer called sort_name, which is what I am using.

I tried and it still does not work.  Anything else I might try.  Where you able to get it to work on your side.

Triland L. McConico

Try:   ORDER BY d.lname, d.fname

Hi,

I have a report where I'm trying to sort by last name then first name.  I cannot get the Order By command to work.  Can someone share with me what I might be doing wrong?  Syntax Below:

ORDER BY d.lname, d.fname DESC

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

Still would not go.  I did it just as shown.

What if you do this?

SELECT DISTINCT

                  b.customer_no,

                  e.perf_code,

                  e.perf_dt,

                  f.description AS perf_name,

                  i.description AS prod_season_name,

                  f.text3 AS studio_teacher,

                  d.fname,

                  d.lname,

                  DATEDIFF(year, CONVERT(nvarchar(30),

                  z.key_value, 126),

                  CONVERT(nvarchar(30),

                  GETDATE(), 126)) AS age_in_years,

                  ISNULL(dbo.AF_FORMAT_STRING(t.phone, '(@@@) @@@-@@@@'), '') AS phone_1,

                  x.description AS PriceType,

                  assoc.fname AS associated_fname,

                  assoc.lname AS associated_lname,

                  assoc.sort_name AS associated_sort_name, --ADDITION HERE

                  T_SPECIAL_REQ.notes

                  FROM         T_SUB_LINEITEM AS a INNER JOIN

                  T_ORDER AS b ON a.order_no = b.order_no INNER JOIN

                  T_LINEITEM AS c ON a.li_seq_no = c.li_seq_no INNER JOIN

                  T_CUSTOMER AS d ON b.customer_no = d.customer_no INNER JOIN

                  VS_PERF AS e ON a.perf_no = e.perf_no INNER JOIN

                  T_PROD_SEASON AS h ON e.prod_season_no = h.prod_season_no INNER JOIN

                  T_INVENTORY AS f ON e.perf_no = f.inv_no INNER JOIN

                  T_INVENTORY AS i ON h.prod_season_no = i.inv_no INNER JOIN

                  T_ADDRESS AS g ON d.customer_no = g.customer_no AND g.primary_ind = 'Y' INNER JOIN

                  TR_PRICE_TYPE AS x ON a.price_type = x.id INNER JOIN

                  T_SPECIAL_REQ ON a.li_seq_no = T_SPECIAL_REQ.li_seq_no LEFT OUTER JOIN

                  TX_CUST_KEYWORD AS z ON b.customer_no = z.customer_no AND z.keyword_no = 1 LEFT OUTER JOIN

                  T_PHONE AS t ON b.customer_no = t.customer_no AND t.type = 1 LEFT OUTER JOIN

                  T_CUSTOMER AS assoc ON assoc.customer_no = T_SPECIAL_REQ.assoc_customer_no

                  WHERE     (e.season = @season_no) AND

                  (@prod_season_no = h.prod_season_no) AND

                  (@perf_no = e.perf_no) AND

                  (a.comp_code &lt;&gt; 32 OR

                  a.comp_code IS NULL)

                  ORDER BY assoc.sort_name

                  --associated_lname DESC, associated_fname DESC

Can you share with me exactly how your using that code in your report.  I do see it in the table however, it is like my report simply will not render that way. 

SELECT DISTINCT

                  b.customer_no, e.perf_code, e.perf_dt, f.description AS perf_name, i.description AS prod_season_name, f.text3 AS studio_teacher, d.fname, d.lname,

                  DATEDIFF(year, CONVERT(nvarchar(30), z.key_value, 126), CONVERT(nvarchar(30), GETDATE(), 126)) AS age_in_years,

                  ISNULL(dbo.AF_FORMAT_STRING(t.phone, '(@@@) @@@-@@@@'), '') AS phone_1, x.description AS PriceType, assoc.fname AS associated_fname,

                  assoc.lname AS associated_lname, T_SPECIAL_REQ.notes

                  FROM         T_SUB_LINEITEM AS a INNER JOIN

                  T_ORDER AS b ON a.order_no = b.order_no INNER JOIN

                  T_LINEITEM AS c ON a.li_seq_no = c.li_seq_no INNER JOIN

                  T_CUSTOMER AS d ON b.customer_no = d.customer_no INNER JOIN

                  VS_PERF AS e ON a.perf_no = e.perf_no INNER JOIN

                  T_PROD_SEASON AS h ON e.prod_season_no = h.prod_season_no INNER JOIN

                  T_INVENTORY AS f ON e.perf_no = f.inv_no INNER JOIN

                  T_INVENTORY AS i ON h.prod_season_no = i.inv_no INNER JOIN

                  T_ADDRESS AS g ON d.customer_no = g.customer_no AND g.primary_ind = 'Y' INNER JOIN

                  TR_PRICE_TYPE AS x ON a.price_type = x.id INNER JOIN

                  T_SPECIAL_REQ ON a.li_seq_no = T_SPECIAL_REQ.li_seq_no LEFT OUTER JOIN

                  TX_CUST_KEYWORD AS z ON b.customer_no = z.customer_no AND z.keyword_no = 1 LEFT OUTER JOIN

                  T_PHONE AS t ON b.customer_no = t.customer_no AND t.type = 1 LEFT OUTER JOIN

                  T_CUSTOMER AS assoc ON assoc.customer_no = T_SPECIAL_REQ.assoc_customer_no

                  WHERE     (e.season = @season_no) AND (@prod_season_no = h.prod_season_no) AND (@perf_no = e.perf_no) AND (a.comp_code &lt;&gt; 32 OR

                  a.comp_code IS NULL)

                  ORDER BY associated_lname DESC, associated_fname DESC

            </CommandText>

There is a new column in t_customer called sort_name, which is what I am using.

I tried and it still does not work.  Anything else I might try.  Where you able to get it to work on your side.

Triland L. McConico

Try:   ORDER BY d.lname, d.fname

Hi,

I have a report where I'm trying to sort by last name then first name.  I cannot get the Order By command to work.  Can someone share with me what I might be doing wrong?  Syntax Below:

ORDER BY d.lname, d.fname DESC

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!