SQL Query in MAxDB..

[Former Member]

Hi Friends,

Could you please let me know what should i change to execute the query in MaxDB?

select distinct t1.*, iif(t2.id is NULL, 0, 1) as hassub from content t1 left join content t2 on (t1.id=t2.parent_id) where t1.parent_id=" & id & " order by t1.parent_id,t1.sort

Thanks for ur help..

Rgds,

Pradeep

[lbreddemann]

Hi Pradeep,

please make sure to consult the MaxDB documentation before [http://maxdb.sap.com/documentation].

For what you want to archieve here (replacing NULL values with zeros), the [DECODE|http://maxdb.sap.com/doc/7_6/cf/63316bc03511d2a97100a0c9449261/content.htm] function should work.

Concerning the

where t1.parent_id=" & id & "

please don't do that. For things like that BIND variables had been invented a long time ago.

Just concatenating strings to form a statement is one of the major roads to SQL-injection and shared cursor cache failure.

regards,

Lars

[Former Member]

Hi Lars,

Sorry I couldnt get back as i was away for a while. Well below sql statement works pretty well in MySQL. But not in MAXDB. Someone told me that in SQLStudio does work little bit differently.

select distinct t1.*, if(t2.id is not NULL, 1, 0) hassub from content t1 left join content t2 on (t1.id=t2.parent_id) where t1.parent_id=" + id + " order by t1.parent_id,t1.sort

What i am working on currently is : I am trying to pull menu items from database and displaying it on a JSP page. So i dont think it will be too much load on database.

I am currently on SQL Studio 7.6.

Thanks and Rgds,

Pradeep

[lbreddemann]

Hi Pradeep,

yep - long time no hear.

Have you read my last reply?

You're still using "IF" instead of "DECODE"...

Changing the "&" (ampersands) against "+" (plus-signs) does not make your habit to build your SQL any better.

Don't use concatenation to create SQL statements! Use BIND variables instead!

Anyhow - it's your program after all.

Follow my advice or leave it - but at least read it before asking the same question over again...

regards,

Lars

[Former Member]

Yeah.. I will take care of it. Thanks.

But how to make this statement working?!! Since SQL statement itself not working..i am really confused what to do next!

[lbreddemann]

>

> Yeah.. I will take care of it. Thanks.
> 
> But how to make this statement working?!! Since SQL statement itself not working..i am really confused what to do next!

Man... read my reply !.

As I wrote, use 'DECODE' instead of 'IF'...

select distinct t1.*, DECODE(t2.id, NULL, 0, 1) hassub 
from 
     content t1 
    left join content t2 on (t1.id=t2.parent_id) 
where 
   t1.parent_id=" + id + " 
order by t1.parent_id, t1.sort

Be aware that I've changed the NOT NULL test to a NULL test and therefore switched the positions of the wanted output values (from 1,0 to 0,1).

To run the statement you will have to fill in some value for "+ id +" first, e.g.

   t1.parent_id= 10 

If this does not help you - well, then you'll probably need more help than what I can provide via this forum...

regards,

Lars