Hi Massimo,
Using raw SQL would defeat the purpose of using the DAL. Are there any
plans to enhance the DAL to include constructs for CASE statements?
Thanks
Pallav
On Dec 30 2010, 12:11 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
> this works for me and this time I tried:
>
> >>> db.define_table('a',Field('b'))
> >>> query=db.a
> >>> print db(query)._select("CASE WHEN 1=1 THEN 0 ELSE 1 END");
>
> SELECT CASE WHEN 1=1 THEN 0 ELSE 1 END FROM a WHERE (a.id > 0);
>
> >>> rows = db(query).select("CASE WHEN 1=1 THEN 0 ELSE 1 END");
>
> On Dec 30, 9:16 am, Harkirat <harkira...@gmail.com> wrote:
>
>
>
>
>
>
>
> > I already tried the None approach and it didn't work. It returns
> > "TypeError: 'NoneType' object is not callable"
>
> > Doing the executesql just defeats the purpose of the DAL. We want to
> > code to be database independent.
>
> > I am in no hurry so if you can post a working sample of this whenever
> > you have time that would be great.
>
> > Thanks for all your help in this matter.
>
> > H.
>
> > On Dec 29, 8:12 pm, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > It is definitively possible. It just that I cannot test it right now.
> > > Try:
>
> > > db(query).select(Expression("CASE WHEN 1=1 THEN 0 ELSE 1 END",None));
>
> > > else you can use
>
> > > db.executesql("... any raw sql ...")
>
> > > the latter is the safe way. Always works.
>
> > > On Dec 29, 6:55 pm, Harkirat <harkira...@gmail.com> wrote:
>
> > > > Thanks for the suggestion Massimo! I haven't been able to get it to
> > > > work though.
> > > > That syntax returns an error "TypeError: __init__() takes at least 3
> > > > arguments (2 given)"
> > > > Tried looking at the Expression class defined in DAL.py for what
> > > > arguments it needs but haven't been able to figure this out.
> > > > Any more suggestions?
>
> > > > If this does not work we will most likely have to shift to SQL Alchemy
> > > > and I do like working with the DAL so I am keeping my fingers crossed
> > > > and hoping there is work around for this.
>
> > > > Harkirat
>
> > > > On Dec 29, 11:56 am, mdipierro <mdipie...@cs.depaul.edu> wrote:
>
> > > > > You should be able to doL
>
> > > > > from gluon.dal import Expression
>
> > > > > db(query).select(Expression("CASE WHEN 1=1 THEN 0 ELSE 1 END"));
>
> > > > > Nut I never tried.
>
> > > > > On Dec 29, 8:37 am, Harkirat <harkira...@gmail.com> wrote:
>
> > > > > > Hi All !
> > > > > > I am new to web2py and my company is building an
> > > > > > enterprise
> > > > > > level web application that will run on the desktop and also mobile
> > > > > > apps using web2py! So far it has been a gr8 experience working with
> > > > > > web2py and kudos to all the developers that made such an awesome
> > > > > > framework !
>
> > > > > > I am trying to write some queries in the DAL that require a CASE
> > > > > > statement e.g.
> > > > > > SELECT CASE WHEN 1=1 THEN 0 ELSE 1 END
>
> > > > > > Is there a way to write this in the DAL?
>
> > > > > > Thanks in advance for your help!
>
> > > > > > Harkirat
