The vendor installs the application which includes creating the database and schema (SQL Server 2008 R2). The schema defined a certain column as varchar(128) that the application code expects to return an integer. If one of the entries for a client is a non-integer value, then HTTP 500 Internal Server Error failures prevent users from being able to login to the web site. The fix is relatively easy: update the problem value to an integer that is not already in use. This SQL returns “9999” even though the highest value is “12467” because the rules for sorting varchar are different than the int data type.<\/p>\n
SELECT TOP 1 IdNum FROM table1\u00c2\u00a0ORDER BY\u00c2\u00a0IdNum\u00c2\u00a0desc<\/p><\/blockquote>\n
No biggie, just convert it to an int.<\/p>\n
SELECT TOP 1 CONVERT(int, IdNum) as\u00c2\u00a0IdNum\u00c2\u00a0FROM table1\u00c2\u00a0ORDER BY\u00c2\u00a0IdNum\u00c2\u00a0desc<\/p><\/blockquote>\n
This works fine. The strangeness comes into play when the aliasing “as IdNum” uses anything other than the actual name of the column. I originally tried to rename it to compare to the first query so in my output I could compare both. If the alias is different from the column name, then the CONVERT gives in the varchar sorting result not the int as specified. These all fail.<\/p>\n
SELECT TOP 1 CONVERT(int, IdNum) as\u00c2\u00a0IdNumConverted FROM table1\u00c2\u00a0ORDER BY\u00c2\u00a0IdNum\u00c2\u00a0desc<\/p>\n
SELECT TOP 1 CONVERT(int, IdNum) “IdNum2” FROM table1\u00c2\u00a0ORDER BY\u00c2\u00a0IdNum\u00c2\u00a0desc<\/p><\/blockquote>\n
The CAST function behaves the same way. Something about the alias name appears able to break these conversions.<\/p>\n
Removing “TOP 1” does not fix the behavior.<\/p>\n","protected":false},"excerpt":{"rendered":"
The vendor installs the application which includes creating the database and schema (SQL Server 2008 R2). The schema defined a certain column as varchar(128) that the application code expects to return an integer. If one of the entries for a client is a non-integer value, then HTTP 500 Internal Server Error failures prevent users from […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"activitypub_content_warning":"","activitypub_content_visibility":"","activitypub_max_image_attachments":4,"activitypub_interaction_policy_quote":"anyone","activitypub_status":"","footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"font":"","enabled":false},"version":2}},"categories":[153],"tags":[],"class_list":["post-8052","post","type-post","status-publish","format-standard","hentry","category-database-oracle"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p1rUBW-25S","jetpack-related-posts":[],"jetpack_likes_enabled":true,"_links":{"self":[{"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/posts\/8052","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/comments?post=8052"}],"version-history":[{"count":0,"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/posts\/8052\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/media?parent=8052"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/categories?post=8052"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ezrasf.com\/wplog\/wp-json\/wp\/v2\/tags?post=8052"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}