Thought inc.

The Dynamic O/R Mapping Company
     

CocoBase Technical Support Forums
For access to developer site with software download and doc's, please request CocoBase download and password
will be emailed to you. Response to posts will appear when answered by THOUGHT Support team.
NOTE:You must register separately with forum in order to post your questions, please click on register icon below.
Home   Frequently Asked Questions   Search   Edit your profile   Registration is free!  
Email This Page to Someone!
Show a Printable Version
CocoBase Enterprise O/R Forums > CocoBase Questions
Conditional Select and Nested Condition
< Previous Thread     Next Thread >
Author
Thread    Post New Thread     Post A Reply
ianc
Member

Registered: Oct 2004
Posts: 4

Hi everyone. I'm just new with cocobase.

1.) Was wondring if this statement is possible with cocobase.

SELECT (CASE WHEN FIELD1 = :VAR1 THEN 1
WHEN FIELD2 = :VAR2 THEN 2
WHEN FIELD2 = :VAR3 THEN 2
END) as ALIAS1
FROM TABLE

Either one or a combination of the variables may have values

2.) I cant seem to implement nested conditions in cocobase. May be I'm not yet familiar with how it works. Can someone help me how to implement this WHERE CLAUSE

SELECT <FIELD LIST>
FROM <TABLE NAME LIST>
WHERE COL1 = :VAR1
AND ((COL2 = :VAR2 AND (COL3 = :VAR3 OR COL4 = :VAR4)) OR (COL5 = :VAR5 AND (COL6 = :VAR6 OR COL7 = :VAR7)))

Either one or a combination of the variables may have values

Thanks


10-19-2004 05:28 PM
Click Here to See the Profile for ianc    Find more posts by ianc        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

Problem 1)
This is problematic to implement directly in the map because you are binding values in the 'field' section and not in the where clauses.

This can be accomplished through some runtime query qualifiers, but it isn't something you'd be able to just edit in the GUI.

Also have you considered other approaches such as using a stored procedure since this behavior isn't really selecting from a table as much as it is 'computing' a value?

We support stored procedure handling in a portable way - is using a procedure one of your options, or do you need to have us help you to prototype this with the select syntax you've specified?

Problem 2)
As for the nested values, that's what that field level controls in the where clause. Have you tried using that feature yet? Increasing the level of a where clause adds a nested ( syntax, decreasing it closes with a ) .

We automatically handle the case where attributes may or may not be included in the query system - this is how our query by example/ejbql/query builder systems all work.

THOUGHT Support

10-19-2004 06:26 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
ianc
Member

Registered: Oct 2004
Posts: 4
Conditional Select and Nested Condition

In problem one, are you saying that I cant have bind variables in the SELECT clause? I might have used a wrong example but the CASE WHEN clause can return another COLUMN value as well.

In problem two, yes I have tried using altering the field level to no avail. The final result of the condition containing VAR5 to VAR7 should have the same level as the final result containing VAR1 to VAR4. But even if i try altering the levels, the resulting SQL nests the condition differently.

Another question, is it legal to put 2 variables in the RHS side of the where clause? The cocobase user guide says "only one field label is allowed per RHS enry. If more than one field label is used, only the first entry will be PROCESSED". Does that mean ONLY the first field label would determine if this clause/entry is included in the final SQL but ALL values will be binded to the other variables as well?

Ex. FIELD1 = :VAR1 and (FIELD2 = :VAR2 or FIELD3 = :VAR3)


Thanks again

10-19-2004 07:10 PM
Click Here to See the Profile for ianc    Find more posts by ianc        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19
Conditional Select and Nested Condition

You can have bind variables in the select - it wouldn't be a very useful O/R tool if it didn't support that :) Bind values normally are in the 'where' portion of the sql such as:

select a,b,c from mytable where a = :a

That's what's normal. The "field" section is normally 'fixed' for a variety of reasons. The field section corresponds to an object shape, and that's pretty fixed typically.

As for the levels, if you need to 'close' a level, you might need to add a place holder that kind of manipulates this such as:

AND 1 = 1

The next update of the system is going to support a where clause 'close' entry that lets you not have to include this workaround.

There is only one field field per where clause entry, but the same variable can be included in more than one where clause entry to accomplish the scenario you've described above.

Does that make sense?

THOUGHT Support

10-19-2004 07:17 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
ianc
Member

Registered: Oct 2004
Posts: 4

Thanks. I'll try to use the placeholder workaround you suggested.

10-19-2004 07:27 PM
Click Here to See the Profile for ianc    Find more posts by ianc        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

Sounds good.

We'll also ask engineering about the possibility of getting you an early access update that supports the new place holder supported format.

THOUGHT Support

10-19-2004 07:29 PM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
ianc
Member

Registered: Oct 2004
Posts: 4

Thanks very much for your suggestion on using placeholder.

It worked

10-19-2004 10:37 PM
Click Here to See the Profile for ianc    Find more posts by ianc        Edit/Delete Message    Reply w/Quote    IP: Logged
admin
Administrator

Registered: Apr 2001
Posts: 19

Super - glad to hear that. It's not the most elegant solution, but it is a simple workaround for the current release until the more elegant solution is available.

THOUGHT Support

10-20-2004 10:56 AM
Click Here to See the Profile for admin    Find more posts by admin        Edit/Delete Message    Reply w/Quote    IP: Logged
All times are PST (US)    Post New Thread     Post A Reply
Forum Jump:
< Previous Thread     Next Thread >

Forum Rules:
Who Can Read The Forum? Any registered user or guest.
Who Can Post New Topics? Any registered user.
Who Can Post Replies? Any registered user.
Changes: Messages can be edited by their author. Messages can be deleted by their author.
Posts: HTML code is OFF. Smilies are OFF. vB code is OFF. [IMG] code is OFF.

Admin Options:
Open / Close Thread
Move Thread
Delete Thread
Edit Thread

< Contact Us - THOUGHT Inc. >

Copyright © Jelsoft Enterprises Limited 2000.
Copyright 2001 All Rights Reserved, THOUGHT Inc.