Home News Contact Us Forum About Us Demos Products F.A.Q.
Shopping Cart
You currently have 0 items in your cart.


Recent Events
  • 31/12/2023 New Year SALE

    We are glad to announce New Year SALE. 25% discount for all our extensions. Use NY24 coupon code. Hurry up the discount is valid till 7 January.

  • 21/11/2023 BLACK FRIDAY 23 is coming

    BIG SALE, 35% discount for all our extensions. Use BF23 coupon code. Hurry up the discount is valid till 27 November.


2Checkout.com, Inc. is an authorized retailer of goods and services provided by ARI Soft. 2CheckOut




Follow us on twitter



Welcome, Guest
Please Login or Register.    Lost Password?

Issue with Aliased Columns in SQL Query
(1 viewing) (1) Guest
Go to bottomPage: 1
TOPIC: Issue with Aliased Columns in SQL Query
#72167
Issue with Aliased Columns in SQL Query 2 Months, 2 Weeks ago Karma: 0
Hello,

I’m encountering an issue with a SQL query where I’m using CONCAT to combine different fields. Here’s a snippet of the query:

Code:

SELECT
`No`,
CONCAT(Name) AS 'Carrier Name',
`Trade_Name`,
REPLACE(REPLACE(Min, '1.5', 'Restricted'), '5', 'Unrestricted') AS 'Authority Type',
REPLACE(REPLACE(Status, 'A', 'Active'), 'S', 'Suspended') AS Status,
CONCAT(Street, ', ', City, ', ', State, ' ', LEFT(ZIP, 5)) AS Address,
CONCAT('(', LEFT(Telephone, 3), ') ', SUBSTRING(Telephone, 4, 3), '-', RIGHT(Telephone, 4)) AS Telephone
FROM #__tablename



The data displays correctly, but when I attempt to sort or search based on the columns (e.g., Address or Telephone, etc), I encounter SQL errors. It appears that the ARI Table is processing the entire CONCAT expression as the column name instead of using the alias.

SQL ERROR: Unknown column 'Carrier Name' in 'where clause'

Could you advise on how to resolve this issue? Is there a way to make the sorting and searching operations work with these calculated columns?

Thanks
Last Edit: 2024/08/14 20:16 By fshehzad.Reason: more info
The administrator has disabled public write access.
 
#72170
Re:Issue with Aliased Columns in SQL Query 2 Months, 2 Weeks ago Karma: 759
Hello,

Try to quote all alias names with '. It will look like:

Code:


SELECT
`No`,
CONCAT(Name) AS 'Carrier Name',
`Trade_Name`,
REPLACE(REPLACE(Min, '1.5', 'Restricted'), '5', 'Unrestricted') AS 'Authority Type',
REPLACE(REPLACE(Status, 'A', 'Active'), 'S', 'Suspended') AS 'Status',
CONCAT(Street, ', ', City, ', ', State, ' ', LEFT(ZIP, 5)) AS 'Address',
CONCAT('(', LEFT(Telephone, 3), ') ', SUBSTRING(Telephone, 4, 3), '-', RIGHT(Telephone, 4)) AS 'Telephone'
FROM #__tablename



If it doesn't help could you reproduce the problem on a test site and provide a temporary access to it by email or send an SQL dump of table with fake data so we can reproduce the problem?

Regards,
ARI Soft
The administrator has disabled public write access.
 
Go to topPage: 1