package: interchange
Hi!
I have got a problem with using some SQL-statements. All of this
statements work in the MySQL-Shell and within phpMyAdmin, but not inside a
[query] tag. These are the queries in question, followed by the error I
get:
[query type=list
random='[control matches]'
sql=| SELECT p.sku AS sku, m.times_ordered AS times_ordered
FROM products p, merchandising m
WHERE p.sku = m.sku
AND p.image <> ''
ORDER BY m.times_ordered DESC
LIMIT 10
|]
-> standard /cgi-bin/standard Runtime error: Bad column name (from p.sku
AS sku): 'p.sku AS sku' at /interchange/lib/Vend/SQL_Parser.pm line 838.
SELECT *
FROM products p, merchandising m
WHERE p.sku = m.sku
AND p.image <> ''
ORDER BY m.times_ordered DESC
LIMIT 10
-> standard /cgi-bin/standard Runtime error: Bad column name (from
m.times_ordered DESC
LIMIT 10): 'm.times_ordered DESC
LIMIT 10' at /interchange/lib/Vend/SQL_Parser.pm line
915.
SELECT *
FROM products p, merchandising m
WHERE p.sku = m.sku
AND p.image <> ''
ORDER BY 'm.times_ordered' DESC
LIMIT 10
-> standard /cgi-bin/standard Runtime error: Vend::Table::Shadow::query
can handle only one table at /interchange/lib/Vend/Table/Shadow.pm line
323.
I have even tried the queries without the 'random'-line, to no avail.
Finally i had to use this workaround, which works, but is less efficient
due to the 'SELECT *':
[query type=list
random='[control matches]'
sql=| SELECT *
FROM products p INNER JOIN merchandising m
ON p.sku = m.sku
WHERE p.image <> ''
ORDER BY 'w.times_ordered' DESC
LIMIT 10
|]
Moreover with this solution I cannot access fields that exist in both
tables, like 'featured', because [sql-param featured] is always empty
then. :(
Any idea? (my setup: Interchange 5.4, MySQL 4.0.24, Perl 5.8.2)
Greetings,
Adam
--
May the source be with GNU!
--
To UNSUBSCRIBE, email to [EMAIL PROTECTED]
with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
