ARI Soft - Issue with Aliased Columns in SQL Query

Home

News

Forum

About Us

Demos

Products

F.A.Q.

Featured Demos

ARI Smart Content demo

ARI Quiz demo

WordPress Quiz plugin

WordPress Lightbox plugin

e-mail: info@ari-soft.com

Shopping Cart

You currently have 0 items in your cart.

Show Cart

Recent Events

23/11/2024 Black Friday 2024

BIG SALE, 30% discount for all our extensions. Use BF24 coupon code. Hurry up the discount is valid till 3 December.

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

more...

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

Welcome, Guest
Please Login or Register. Lost Password?

ARI Soft Forum

Joomla components

ARI Data Tables

Issue with Aliased Columns in SQL Query

(1 viewing) (1) Guest

Page: 1

TOPIC: Issue with Aliased Columns in SQL Query

Forum Tools

#72167

Issue with Aliased Columns in SQL Query 1 Year, 2 Months 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

fshehzad

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 1 Year, 2 Months ago Karma: 765

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

admin