0

I have this problem, I have a table product like this with two columns target and actual, how can I build a query and tell it : if target == '' then select from actual. To be clearer, this is my table product with the two columns : 

actual | target
---------------
  p12  | <null>
  p14  | h20
  p16  | <null>
  p16  | <null>
  p16  | <null>
  p16  | <null>
  p16  | <null>

So I would like to select for example a value 'xx', how can I build this condition in SQLAlchemy (or sql) telling it to look for it in column target but if the cell is empty (is null) then look for it inside the actual column cell.

Is this possible?

Improve this question

3 Answers 3

Reset to default
3

sounds like you want COALESCE(), which takes several arguments and returns the first which is not null (or null if all args are null)

Presuming a reasonable setup:

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class Product(Base):
    __tablename__ = "product"
    id = sa.Column(sa.Integer, primary_key=True)
    actual = sa.Column(sa.String)
    target = sa.Column(sa.String)

use sqlalchemy.func.coalesce():

>>> print session.query(sa.func.coalesce(Product.target, Product.actual).label('x'))
SELECT coalesce(product.target, product.actual) AS x 
FROM product
>>> session.query(sa.func.coalesce(Product.target, Product.actual).label('x')).all()
[(u'p12'), (u'h20'), (u'p16'), (u'p16'), (u'p16'), (u'p16'), (u'p16')]

edit: if your missing values are not null, but some other value, you should use a CASE expression. 

>>> print session.query(sa.case([(Product.target == '', Product.actual)], else_=Product.target))
SELECT CASE WHEN (product.target = :target_1) THEN product.actual ELSE product.target END AS anon_1 
FROM product
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

one question please, is there a function that does the same but with empty values (value = '')?
0

SQL has a case statement. In this case it would be something like:

CASE MYTARGET WHEN TARGET IS NOT NULL THEN TARGET ELSE ACTUAL

I am not a db expert but I have used this construct. Mytarget is a new column

Improve this answer

Comments

0

use IFNULL(Target,Actual) instead of Target

ISnull is SQL, ifnull is mysql, sorry about that.

Improve this answer

1 Comment

ISNULL() takes only one parameter, did you IFNULL()?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.