0

Can someone help me in below query I always get x.salesChannel as Unknown column

SELECT Column1,
    Column2,
    (
        SELECT CASE
                WHEN `cr`.`CRColumn` is not null
                AND `cr`.`CRColumn` <= `ca`.`caColumn` THEN `cr`.`CRColumn`
                WHEN `cr`.`CRColumn` is not null
                AND `cr`.`CRColumn` > `ca`.`caColumn` THEN `cr`.`CRColumn`
                WHEN `cr`.`CRColumn` is not null
                AND `cr`.`CRColumn` is null THEN `cr`.`CRColumn`
            END as salesChannel
        FROM `table1` `ca`
            LEFT JOIN `Table2` `bc` ON `bc`.`contract_action_id` = `contract`.`id`
            LEFT JOIN `Table3` `cr` ON `cr`.`pnr` = `bc`.`pnr`
    ) AS `x`
FROM `table1` `contract`
WHERE `contract`.`type` = ?
    AND x.salesChannel IS NOT NULL
    AND x.salesChannel IN (?, ?)
JNevill
  • 46,980
  • 4
  • 38
  • 63
Manjari
  • 1
  • 3
  • Your subquery is in your SELECT clause. It will return a single scalar value for every line of table `contract`. `x` is a column alias, not a table alias. So instead you want `x IS NOT NULL AND x IN (? , ?)` would be the appropriate logic. My guess is that your query really doesn't solve whatever problem you are trying to solve though and that subquery should live in your `FROM` clause and have an `ON` clause to join `contract` with `x`? – JNevill Aug 26 '22 at 15:09
  • Could you please suggest me how to add my subquery in FROM clause wrt TypeORM. I am using addSelect to do so – Manjari Aug 26 '22 at 15:17

0 Answers0