SQL problem with subquery

Question

I get the following error while running this query:

#1241 - Operand should contain 1 column(s)

SELECT *,
       Date_format(date_assigned, '%d-%m-%y')                     AS datum,
       YEAR(date_assigned)                                        AS YEAR,
       YEAR(NOW())                                                AS this_year,
       Datediff(NOW(), date_assigned)                             AS age,
       MONTH(NOW())                                               AS this_month,
       Concat(Period_add(Date_format(date_released, "%Y%m"), -6)) AS cancel_date
       ,
       Date_format(NOW(), '%Y%m')                                 AS
       this_date
FROM   reseller_numbers
WHERE  reseller_id = '31'
       AND MONTH(date_assigned) = '12'
       AND ( ( date_released IS NULL
                OR date_released > NOW() )
              OR ( Dayofyear(date_assigned) <= Dayofyear(date_released)
                   AND ( date_released IS NULL
                          OR date_released > NOW() ) ) )
       AND ( date_released IS NULL
              OR date_released > NOW()
                 AND date_released NOT LIKE '%2999%'
                 AND date_released != '2038-01-01 00:00:00' )
       AND ( date_released NOT IN (SELECT *,
                                          Date_format(DATE_ADD(date_released,
                                                      INTERVAL '-6' MONTH),
                                          '%d%m%y')
                                          AS
                                          cancel_date
                                   FROM   reseller_numbers
                                   WHERE  reseller_id = '31'
                                   HAVING DATE_ADD(date_released,
                                          INTERVAL '-6' MONTH)
                                          <
                                          NOW(
                                          )) )
ORDER  BY date_released DESC
LIMIT  0, 30

Is there something wrong with this query? When I remove the subquery in date_released NOT IN, the query executes just fine.

mikesigs · Accepted Answer · 2010-12-31 07:57:37Z

2

The sub query must return only a single column for the date_released value to match on. Get rid of the "*,"

answered Dec 31, 2010 at 7:57

mikesigs

11.5k3 gold badges37 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

shankhan · Accepted Answer · 2010-12-31 07:56:29Z

1

in date_released you should only select one column

SELECT Date_format(DATE_ADD(date_released,
                                                  INTERVAL '-6' MONTH),
                                      '%d%m%y')
                                      AS
                                      cancel_date
                               FROM   reseller_numbers
                               WHERE  reseller_id = '31'
                               HAVING DATE_ADD(date_released,
                                      INTERVAL '-6' MONTH)
                                      <
                                      NOW(
                                      ))

answered Dec 31, 2010 at 7:56

shankhan

6,5812 gold badges22 silver badges22 bronze badges

Comments

Arsen Mkrtchyan · Accepted Answer · 2010-12-31 07:56:57Z

remove * from subquery

SELECT *,
       Date_format(date_assigned, '%d-%m-%y')                     AS datum,
       YEAR(date_assigned)                                        AS YEAR,
       YEAR(NOW())                                                AS this_year,
       Datediff(NOW(), date_assigned)                             AS age,
       MONTH(NOW())                                               AS this_month,
       Concat(Period_add(Date_format(date_released, "%Y%m"), -6)) AS cancel_date
       ,
       Date_format(NOW(), '%Y%m')                                 AS
       this_date
FROM   reseller_numbers
WHERE  reseller_id = '31'
       AND MONTH(date_assigned) = '12'
       AND ( ( date_released IS NULL
                OR date_released > NOW() )
              OR ( Dayofyear(date_assigned) <= Dayofyear(date_released)
                   AND ( date_released IS NULL
                          OR date_released > NOW() ) ) )
       AND ( date_released IS NULL
              OR date_released > NOW()
                 AND date_released NOT LIKE '%2999%'
                 AND date_released != '2038-01-01 00:00:00' )
       AND ( date_released NOT IN (SELECT Date_format(DATE_ADD(date_released,
                                                      INTERVAL '-6' MONTH),
                                          '%d%m%y')
                                          AS
                                          cancel_date
                                   FROM   reseller_numbers
                                   WHERE  reseller_id = '31'
                                   HAVING DATE_ADD(date_released,
                                          INTERVAL '-6' MONTH)
                                          <
                                          NOW(
                                          )) )
ORDER  BY date_released DESC
LIMIT  0, 30

Chris · Accepted Answer · 2010-12-31 07:56:51Z

0

The subquery must have only one value selected, probably the DATE_FORMAT one. Remove the *.

answered Dec 31, 2010 at 7:56

Chris

1,7532 gold badges12 silver badges16 bronze badges

Collectives™ on Stack Overflow

SQL problem with subquery

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related