ShadowMage Posted April 7, 2020 Share Posted April 7, 2020 This is a really bizarre issue and I'm not sure if anyone will be able to help, but maybe someone's seen something like this before. So, I have a query: SELECT OrderHed.OrderNum, OrderHed_UD.ShortChar06 AS 'SalesLeader', OrderHed_UD.Character01 AS 'JobName' FROM OrderHed INNER JOIN OrderHed_UD ON OrderHed.SysRowID = OrderHed_UD.ForeignSysRowID WHERE (OrderHed.OpenOrder = 0) This gives me an error message: PHP Warning: odbc_fetch_array(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid column name 'SalesLeader'., SQL state S0022 in SQLGetData (Yes, this is run in PHP) Now, if I just remove the alias from ShortChar06 OR if I entirely remove Character01, the query runs just fine. So both of these queries will run without issue: SELECT OrderHed.OrderNum, OrderHed_UD.ShortChar06, OrderHed_UD.Character01 AS 'JobName' FROM OrderHed INNER JOIN OrderHed_UD ON OrderHed.SysRowID = OrderHed_UD.ForeignSysRowID WHERE (OrderHed.OpenOrder = 0) OR SELECT OrderHed.OrderNum, OrderHed_UD.ShortChar06 AS 'SalesLeader' FROM OrderHed INNER JOIN OrderHed_UD ON OrderHed.SysRowID = OrderHed_UD.ForeignSysRowID WHERE (OrderHed.OpenOrder = 0) Any ideas what might be causing this strange behavior? I can't see anything in the syntax that would cause issues, but something is clearly not functioning. This is happening for a couple of different fields (all of which are from the _UD tables; but not all of the _UD table fields are doing it). Link to comment Share on other sites More sharing options...
Ingolme Posted April 7, 2020 Share Posted April 7, 2020 You should use backticks rather than quotation marks to delimit field names. `SalesLeader` Link to comment Share on other sites More sharing options...
ShadowMage Posted April 7, 2020 Author Share Posted April 7, 2020 If I use the backticks I get this: PHP Warning: odbc_exec(): SQL error: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '`'., SQL state 37000 in SQLExecDirect Link to comment Share on other sites More sharing options...
Ingolme Posted April 7, 2020 Share Posted April 7, 2020 I guess Microsoft's SQL works differently than standard SQL. Have you tried without any delimiters at all? Link to comment Share on other sites More sharing options...
ShadowMage Posted April 7, 2020 Author Share Posted April 7, 2020 1 hour ago, Ingolme said: I guess Microsoft's SQL works differently than standard SQL. Of course it does. It's Microsoft. 😁 No delimiters gives the same result as using single quotes. It's really weird because I can pull the field with an alias as long as I don't have the Character01 field on there, which itself is using an alias. But it's not a matter of using multiple aliases in a query call, because some fields I can have a dozen aliases and they all work, but then they seem to reach a certain point and cease to function. I just don't know how to fully explain what's happening... Link to comment Share on other sites More sharing options...
Ingolme Posted April 7, 2020 Share Posted April 7, 2020 I'm sorry I can't help. That's a very strange problem and I don't know much about Microsoft database software. I guess if I were in this situation I'd start searching Google for invalid column names in Microsoft SQL server to see if somebody else has come across the same problem. Link to comment Share on other sites More sharing options...
ShadowMage Posted April 8, 2020 Author Share Posted April 8, 2020 No worries. Honestly, being such a weird and inconsistent issue, I didn't expect to get an answer. A couple of quick Google searches hadn't turned anything up, so this was a shot in the dark hoping someone else may have had a similar issue. Leave it to Microsoft... 😅 Link to comment Share on other sites More sharing options...
ShadowMage Posted April 8, 2020 Author Share Posted April 8, 2020 I'm just going to throw some more information I discovered on here. Maybe something will spark a possible solution. I discovered that this behavior happens as soon as I add OrderHed_UD.Character01 to a query. I can pull in anything but that field. Same for Character02 and 03. Those three fields cause my queries to throw odd errors in PHP. At one point it even threw an "ambiguous column" error on a clearly defined column (it was JobHead.JobNum and there weren't even any other JobNum fields being selected). Here's the weird thing (or another one anyway): I can run these queries with the above character fields in ODBC Test Utility (its a utility built by Microsoft) and I don't get any errors at all. It displays the results, but the character fields above display their values as TRUNC: "" which I'm assuming means they were truncated to zero characters?? The character fields are all supposedly 1000 character text fields (I don't need that many that's just what was available; these were pre-created in the database for custom data). Link to comment Share on other sites More sharing options...
dsonesuk Posted April 8, 2020 Share Posted April 8, 2020 I've been using DataPA, which uses MS SQL oh joy, I can confirm no quotes, backticks, but you can use square brackets, especially if you want space in column name, if i remember you don't require 'AS' either, even though I still used it. It also throws errors if incorrectly spaced, or misplaced line breaks. That's when i used online MSSQL validators. Link to comment Share on other sites More sharing options...
ShadowMage Posted April 8, 2020 Author Share Posted April 8, 2020 Do you have any recommended validators you prefer? Link to comment Share on other sites More sharing options...
dsonesuk Posted April 8, 2020 Share Posted April 8, 2020 Not really, just did a quick Google search and used first one i found that identified the problem. Link to comment Share on other sites More sharing options...
ShadowMage Posted April 9, 2020 Author Share Posted April 9, 2020 So it looks like the "fix" is to CAST the fields that are causing problems. In this case, it looks like any of the 1000 character fields are causing problems. So a "fixed" query would look like this: SELECT OrderHed.OrderNum, OrderHed_UD.ShortChar06 AS 'SalesLeader', CAST(OrderHed_UD.Character01 AS varchar) AS 'JobName' FROM OrderHed INNER JOIN OrderHed_UD ON OrderHed.SysRowID = OrderHed_UD.ForeignSysRowID WHERE (OrderHed.OpenOrder = 0) Not really sure why this works, but it does. It's weird because one would think if there was a problem with the format of the field I wouldn't be able to pull it at all, but that's not true since, as described in the OP, I can run it without the alias on ShortChar06 and pull/display data from Character01 without any issues. Anyway, thanks for trying to help out! Link to comment Share on other sites More sharing options...
dsonesuk Posted April 9, 2020 Share Posted April 9, 2020 (edited) what if you set varchar length to 1000, as well, just curious NOTE: .in case you didn't know, no length specified it seems to default to 30 Edited April 9, 2020 by dsonesuk Link to comment Share on other sites More sharing options...
ShadowMage Posted April 9, 2020 Author Share Posted April 9, 2020 Explicitly setting the length to 1000 seems to work as well. Looks like it's just the CAST that's doing the trick even though it's supposed to already be text. Also, good to know about the default. Although in my case that doesn't seem to apply. I've several values that are over 30 characters and they're not being truncated. Of course, that doesn't mean that the default isn't some other value that I'll discover later if/when we have longer values. Link to comment Share on other sites More sharing options...
ishan_shah Posted May 4, 2020 Share Posted May 4, 2020 Remove apostrophe ' ' This is the example that i found on Microsoft's website : The following examples return all rows from the Product table. The first example returns total sales and the discounts for each product. In the second example, the total revenue is calculated for each product. USE AdventureWorks2012; GO SELECT p.Name AS ProductName, NonDiscountSales = (OrderQty * UnitPrice), Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount) Link to comment Share on other sites More sharing options...
dsonesuk Posted May 4, 2020 Share Posted May 4, 2020 We gathered that six posts before! You really should consider stop copying giving answers that someone has already answered before, it does nothing to highlight your skills as a up and coming web developer, only as someone who skills depends on the ability of someone around you that you can copy. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now