Invalid Column Name

[ShadowMage]

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).

[Ingolme]

You should use backticks rather than quotation marks to delimit field names. `SalesLeader`

[ShadowMage]

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

 

[Ingolme]

I guess Microsoft's SQL works differently than standard SQL. Have you tried without any delimiters at all?

[ShadowMage]

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...

[Ingolme]

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.

[ShadowMage]

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... 😅

[ShadowMage]

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).

[dsonesuk]

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.

[ShadowMage]

Do you have any recommended validators you prefer?

[dsonesuk]

Not really, just did a quick Google search  and used first one i found that identified the problem.

[ShadowMage]

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!

[dsonesuk]

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

[ShadowMage]

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.

[ishan_shah]

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)

[dsonesuk]

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.