Error - Operand should contain 1 column

Dev Shed Forums Sponsor:

January 31st, 2012, 03:25 AM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

Error - Operand should contain 1 column

Hi all,

I've dug around to try and find a similar issue to this, but no joy - all similar ones seem to resolve around a parenthesis issue that I don't think I've got.

Basically I'm trying to get this statement to work:

Quote:

SELECT p.*,
FLOOR(prodratingtotal / prodnumratings) AS prodavgrating, pi.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio,
(SELECT orderid, op.ordprodname, op.ordprodid, SUM(op.ordprodqty) AS qty FROM isc_orders o JOIN isc_order_products op on op.orderprodid=o.orderid WHERE orddate BETWEEN 1271853079 AND 1272637707 GROUP BY op.ordprodid) AS qty,
(SELECT discountpercent FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS prodgroupdiscount,
(SELECT discountmethod FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS discountmethod
FROM (SELECT DISTINCT ca.productid, FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
FROM isc_categoryassociations ca
INNER JOIN isc_products p ON p.productid = ca.productid WHERE p.prodvisible = 1 AND p.prodbrandid = 51) AS ca
INNER JOIN isc_products p ON p.productid = ca.productid
LEFT JOIN isc_product_images pi ON (pi.imageisthumb = 1 AND p.productid = pi.imageprodid)
ORDER BY p.prodcode ASC, p.prodcode ASC

I know that it works fine when it is like this:

Quote:

SELECT p.*,
FLOOR(prodratingtotal / prodnumratings) AS prodavgrating, pi.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio,
(SELECT discountpercent FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS prodgroupdiscount,
(SELECT discountmethod FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS discountmethod
FROM
(SELECT DISTINCT ca.productid, FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
FROM
isc_categoryassociations ca
INNER JOIN isc_products p ON p.productid = ca.productid WHERE p.prodvisible = 1 AND p.prodbrandid = 51) AS ca
INNER JOIN isc_products p ON p.productid = ca.productid
LEFT JOIN isc_product_images pi ON (pi.imageisthumb = 1 AND p.productid = pi.imageprodid)
ORDER BY p.prodcode ASC, p.prodcode ASC

So I'm basically trying to get this line in there (which again works on its own):

Quote:

(SELECT orderid, op.ordprodname, op.ordprodid, SUM(op.ordprodqty) AS qty FROM isc_orders o JOIN isc_order_products op on op.orderprodid=o.orderid WHERE orddate BETWEEN 1271853079 AND 1272637707 GROUP BY op.ordprodid) AS qty,

But it keeps throwing

Quote:

Error Code: 1241. Operand should contain 1 column(s)

Baffled...

Any help greatly appreciated!

January 31st, 2012, 04:52 AM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

Ok, I've taken a couple of little bits out, the 2 seperaett working queries are:

Quote:

SELECT p.*,
FLOOR(prodratingtotal / prodnumratings) AS prodavgrating, pi.*,
SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio,
(SELECT discountpercent FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS prodgroupdiscount,
(SELECT discountmethod FROM isc_customer_group_discounts disc WHERE disc.discounttype='PRODUCT' AND disc.customergroupid='9' AND disc.catorprodid=p.productid) AS discountmethod
FROM (SELECT DISTINCT ca.productid, FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
FROM isc_categoryassociations ca
INNER JOIN isc_products p ON p.productid = ca.productid WHERE p.prodvisible = 1) AS ca
INNER JOIN isc_products p ON p.productid = ca.productid
LEFT JOIN isc_product_images pi ON (pi.imageisthumb = 1 AND p.productid = pi.imageprodid)
ORDER BY p.prodcode ASC, p.prodcode ASC

and:

Quote:

SELECT orderid, op.ordprodname, op.ordprodid, SUM(op.ordprodqty) AS qty
FROM isc_orders o
JOIN isc_order_products op on op.orderprodid=o.orderid
WHERE orddate
BETWEEN 1271853079 AND 1272637707
GROUP BY op.ordprodid

The 2 queries need to be joined on op.ordprodid = p.productid

January 31st, 2012, 05:45 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

when you put a subquery into the SELECT clause, it can return only one column

here's your original query, formatted for human beings rather than database engines --

Code:

SELECT p.*
     , FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
     , pi.*
     , SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / 
       SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio
     , ( SELECT orderid
              , op.ordprodname
              , op.ordprodid
              , SUM(op.ordprodqty) AS qty 
           FROM isc_orders o 
           JOIN isc_order_products op 
             on op.orderprodid=o.orderid
          WHERE orddate BETWEEN 1271853079 AND 1272637707 
         GROUP 
             BY op.ordprodid ) AS qty
     , ( SELECT discountpercent 
           FROM isc_customer_group_discounts disc 
          WHERE disc.discounttype='PRODUCT' 
            AND disc.customergroupid='9' 
            AND disc.catorprodid=p.productid) AS prodgroupdiscount
     , ( SELECT discountmethod 
           FROM isc_customer_group_discounts disc 
          WHERE disc.discounttype='PRODUCT' 
            AND disc.customergroupid='9' 
            AND disc.catorprodid=p.productid ) AS discountmethod
  FROM ( SELECT DISTINCT 
                ca.productid
              , FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
           FROM isc_categoryassociations ca
         INNER 
           JOIN isc_products p 
             ON p.productid = ca.productid 
          WHERE p.prodvisible = 1 
            AND p.prodbrandid = 51 ) AS ca
INNER 
  JOIN isc_products p 
    ON p.productid = ca.productid
LEFT 
  JOIN isc_product_images pi 
    ON pi.imageisthumb = 1 
   AND p.productid = pi.imageprodid
ORDER 
    BY p.prodcode ASC
     , p.prodcode ASC

the red part shows you where you are allowed to return only one column

note also that it had better be only one row as well, or that will blow up too

a subquery in the SELECT clause is allowed to return only one value, which is why it's called a scalar subquery

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

January 31st, 2012, 06:02 AM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

Firstly, thanks for making it more readable!!

Secondly - ok, so it can only return 1 column there, that's fine, the only values I'm interested in are the

Quote:

SUM(op.ordprodqty) AS qty

ones. However, this is a pile of rows, not a single value - presumably as you put it, why it's 'blowing up'

The main query is essentially returning products (information & images) that fit a certain criteria; the second query is finding out how many were sold in a certain period. The 2 qeuries should join on op.ordprodid = p.productid as they are common, so is there simply a better way of writing what I have done?

The problem for me lies in the fact that I'm having to do the extra join & group in the middle of it all? That's why I was trying to run the subquery as an new output qty.

So is it possible to pull it out as a seperate query instead of having it as a subquery?

Sorry if this is a dumb question, typically my SQL statements don't have to get anywhere near as complicated as this one has gotten...

********************************************
EDIT (forum doesn't seem to like me replying to myself too often!)
********************************************
Ok, so played with that some more, doesn't fail now, but o comes out as NULL - this is where I was hoping the sum would pop up

Code:

SELECT p.*
     , FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
     , pi.*
     , SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', 2), ' ', 1) / 
       SUBSTRING_INDEX(SUBSTRING_INDEX(pi.imagefilestdsize, 'x', -1), ' ', 2) as ratio
     , ( SELECT SUM(op.ordprodqty) as qty
           FROM isc_orders o         
          INNER
           JOIN isc_order_products op 
             ON o.orderid=op.ordprodid
          WHERE o.orddate BETWEEN 1271853079 AND 1272637707
          GROUP 
             BY op.ordprodid ) AS o
     , ( SELECT discountpercent 
           FROM isc_customer_group_discounts disc 
          WHERE disc.discounttype='PRODUCT' 
            AND disc.customergroupid='9' 
            AND disc.catorprodid=p.productid) AS prodgroupdiscount
     , ( SELECT discountmethod 
           FROM isc_customer_group_discounts disc 
          WHERE disc.discounttype='PRODUCT' 
            AND disc.customergroupid='9' 
            AND disc.catorprodid=p.productid ) AS discountmethod
  FROM ( SELECT DISTINCT 
                ca.productid
              , FLOOR(prodratingtotal / prodnumratings) AS prodavgrating
           FROM isc_categoryassociations ca
         INNER 
           JOIN isc_products p 
             ON p.productid = ca.productid 
          WHERE p.prodvisible = 1 
            ) AS ca
INNER 
  JOIN isc_products p 
    ON p.productid = ca.productid
LEFT 
  JOIN isc_product_images pi 
    ON pi.imageisthumb = 1 
   AND p.productid = pi.imageprodid
ORDER 
    BY p.prodcode ASC
     , p.prodcode ASC

January 31st, 2012, 11:21 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

Quote:

Originally Posted by tonybarnes

The main query is essentially returning products (information & images) that fit a certain criteria; the second query is finding out how many were sold in a certain period. The 2 qeuries should join on op.ordprodid = p.productid as they are common, so is there simply a better way of writing what I have done?

there probably is

the first thing you should do is get rid of the subqueries in the SELECT clause, and turn them into subqueries that you join to

January 31st, 2012, 11:33 AM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

Do you know where I would find an example of how to do that? Or have any pointers?

Like I said, my SQL isn't great - I can't even figure out why a lot of the function is built like that, seems extraneous (I'm not even convinced some aspects are working thanks to some columns just being stuffed full of 'nulls'!) - but I am modifying someone elses code, and half stuck with it.

It would be nice to not have to go back to the start (a large chunk of the above SQL is actually generated in PHP, so 'small' changes are actually quite involved), but if needs must I will.

Thanks for help

January 31st, 2012, 12:42 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

okay, i'll walk you through an example

let's start with the two subqueries marked in blue here --

Code:

SELECT p.*
     , ...
     , ( SELECT discountpercent 
           FROM isc_customer_group_discounts disc 
          WHERE disc.discounttype='PRODUCT' 
            AND disc.customergroupid='9' 
            AND disc.catorprodid=p.productid) AS prodgroupdiscount
     , ( SELECT discountmethod 
           FROM isc_customer_group_discounts disc 
          WHERE disc.discounttype='PRODUCT' 
            AND disc.customergroupid='9' 
            AND disc.catorprodid=p.productid ) AS discountmethod
  FROM isc_products p 
INNER
  JOIN ...

these are known as correlated subqueries because within each subquery, there is a reference to a column outside the subquery, namely p.productid

correlated subqueries, by the way, are notoriously inefficient

let's re-write these as a join

Code:

SELECT p.*
     , ...
     , disc.discountpercent AS prodgroupdiscount
     , disc.discountmethod 
  FROM isc_products p 
INNER
  JOIN isc_customer_group_discounts disc 
    ON disc.catorprodid = p.productid
   AND disc.discounttype = 'PRODUCT' 
   AND disc.customergroupid = '9'

not only is this leaner (we've eliminated two subqueries and replaced them with one), but the join is more efficient

Comments on this post

tonybarnes agrees!

February 1st, 2012, 03:23 AM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

lol - over the years various parts of the code I'm looking at has been accused of being strange/inefficient - I guess this is just another spot. Does make it stupidly confusing as well for less able coders.

Right, well I tried splitting them out like you suggested (did on a different computer so not got code to hand) - half worked. Had all the right columns coming up, just no results.

Will play about with it some more, but had a thought during the night that it would like be a lot quicker to add some fields to the isc_products table, and generate stored stuff in there in an earlier function.

Either way will post up what I finish up with - many thanks for your help, SQL is somehow the most confusing language that I ever have to write anything in - not least because error checking gives such (IMO) pathetic/incomprehensible reasons as to why something is not working!

February 7th, 2012, 01:45 AM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

Ok, finally had chance to come back to this, have definitely decided to strip out into more than 1 function, as was going nowhere.

To make things easier I've added a few new columns to the isc_products table so I can store the value that this subquery would have returned by running 1 query ahead of time if a validatin test is passed.

Issue is I now can't get it to update the result!

Code:

UPDATE isc_products p
      INNER JOIN (
          SELECT op.ordprodid AS id, SUM(op.ordprodqty) AS soldxamount
            FROM isc_orders o
            JOIN isc_order_products op on op.orderprodid=o.orderid  
           WHERE orddate
         BETWEEN 1271853079 AND 1272637707
        GROUP BY op.ordprodid)
   ON p.productid = op.orderprodid
  SET p.soldxamount = soldxamount

Its throwing

Quote:

Error Code: 1248. Every derived table must have its own alias

This code works in the sense that it pulls out the right values:

Code:

    SELECT orderid, op.ordprodname, op.ordprodid, SUM(op.ordprodqty) AS qty
    FROM isc_orders o
    JOIN isc_order_products op on op.orderprodid=o.orderid
   WHERE orddate
 BETWEEN 1271853079 AND 1272637707
GROUP BY op.ordprodid

So all I want to do is take the qty column that this code is generating, and add it into the isc_products table in the soldxamount column, where op.orderprodid = p.productid

I'm clearly missing something obvious

#10

February 7th, 2012, 05:31 AM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 25,046

Time spent in forums: 3 Months 2 Days 22 h 44 sec
Reputation Power: 3829

Quote:

Originally Posted by tonybarnes

I'm clearly missing something obvious

yup -- every derived table must have its own alias

Code:

UPDATE isc_products p
INNER 
  JOIN ( this is the derived table ) AS this_is_the_alias
    ON p.productid = this_is_the_alias.orderprodid
   SET p.soldxamount = this_is_the_alias.soldxamount

#11

February 7th, 2012, 08:15 AM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

Doh!!

Looking at it now it seems (reasonably

) obvious, many thanks, will stick in later and see what gets spat out - all being well the right bits

Note to self: when trying to get code to work in a language where you're not particularly good at bug hunting syntax problems, choose a better time than 5am to work....

Second note to self: pay attention to how other people colour stuff in the code sections, makes it easier to read

#12

February 8th, 2012, 11:32 PM

tonybarnes

Registered User

Join Date: Jan 2012

Posts: 10

Time spent in forums: 1 h 28 m 5 sec
Reputation Power: 0

Hmm, still no disco -

Code:

UPDATE isc_products p
INNER 
    JOIN (
          SELECT op.ordprodid AS id, SUM(op.ordprodqty) AS soldxamount
            FROM isc_orders o
            JOIN isc_order_products op ON op.orderprodid=o.orderid  
           WHERE orddate
         BETWEEN 1271853079 AND 1272637707
        GROUP BY op.ordprodid) AS up
     ON p.productid = up.productid
    SET p.soldxamount = up.soldxamount

Saying that 'up' is an unknown column

Also damn MySQL workbench is refusing to do backups, which makes running these queries more 'scary' than I'd like!

#13

February 9th, 2012, 04:16 AM

SimonJM

Contributing User

Dev Shed Intermediate (1500 - 1999 posts)

Join Date: Mar 2006

Posts: 1,914

Time spent in forums: 1 Month 5 Days 11 h 34 sec
Reputation Power: 1297

In your derived data, which you are aliasing as up, you do not return a column with the name that you are using for the JOIN condition:
Rows:

Code:

op.ordprodid AS id, SUM(op.ordprodqty) AS soldxamount

JOIN

Code:

ON p.productid = up.productid

__________________
The moon on the one hand, the dawn on the other:
The moon is my sister, the dawn is my brother.
The moon on my left and the dawn on my right.
My brother, good morning: my sister, good night.
-- Hilaire Belloc

Last edited by SimonJM : February 9th, 2012 at 04:18 PM. Reason: Correction: swap 'column' for 'row' - thanks Rudy!