Ultralite Query Help

I am using ASA v 8.0.2.4285 and I am developing for Palm.

I am creating a filter in place that allows the user to
filter the resulting list based on 3 criteria. Each of the 3
criteria has a "Select All" option and several other
options.

When "Select All" is chosen this effectivly removes this
criteria from the query. I am trying to devise the most
efficient way to write my ESQL. Te

Start with:

EXEC SQL DECLARE MyCursor CURSOR FOR
SELECT * from MyTable where criteria1 = c1 and criteria2 =
c2 and criteria3 = c3;

This works if all three selections are made, but I would
like to have only 1 cursor and I don't want to put "like" in
the WHERE clause due to performance degradation.

Any suggestions on how to do this effectively?

Thanks,
Phil.
0
Phil
8/19/2003 8:31:14 PM
sybase.sqlanywhere.ultralite 2982 articles. 0 followers. Follow

4 Replies
463 Views

Similar Articles

[PageSpeed] 39

Phil wrote in news:3f428929.92e.846930886@sybase.com:

> I am using ASA v 8.0.2.4285 and I am developing for Palm.

Yu can try something like this:

create variable id varchar(30);
create variable uid varchar(30);
create variable d_name varchar(30);

set d_name = 'tbowman';
set id = null;
set uid = null;

select *
  from mbt_request
 where institution_userid =
       ifnull( d_name, institution_userid, d_name )
   and institution_id = ifnull( id, institution_id, id )
   and trn_uid = ifnull( uid, trn_uid, uid )

So your code would use the IFNULL function.
If your input parameter is NULL, return the column name, else return 
what the user entered as criteria.

   and trn_uid = trn_uid  (will always be true)
   
   so doing this usually works:
   and trn_uid = ifnull( uid, trn_uid, uid )
--
David Fishburn
NEW - Certified ASA Developer Version 8
Sybase - iAnywhere Solutions
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with EACH
post (dbeng8 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm 

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com


0
David
8/20/2003 1:43:14 AM
David,

  I am having some problems converting this into the proper
ESQL code.

Code Snippet 1:
EXEC SQL BEGIN DECLARE SECTION;
		
	TCHAR	one[30];
	TCHAR	two[30];
	TCHAR	three[30];
	
EXEC SQL END DECLARE SECTION;

one=null;
two=null;
three=null;

EXEC SQL DECLARE CustCursor CURSOR FOR
	SELECT sccn,shipname
	FROM Cust
	WHERE route_id = ifnull(one,route_id, :dbCustRoute)
			AND dayAssignment = :dbCustDay
			AND chainid = :dbCustChain;

With the above I cannot even run the preprocessor
completely. The sqlpp runs without errors on the sqc file,
but the ulgen command stops after the "Analyzing access
plans" command.

Code snippet 2:
EXEC SQL DECLARE CustCursor CURSOR FOR
	SELECT sccn,shipname
	FROM Cust
	WHERE route_id = ifnull(null,route_id, :dbCustRoute)
			AND dayAssignment = :dbCustDay
			AND chainid = :dbCustChain;

If I remove the one variable from the ifnull statement and
just put in "null" the preprocessor is fine and ulgen
completes successfully.

Do I need to declare the variables differently, or is the
"ifnull" command even supported properly in ESQL?

Thanks,
Phil Lachmann

> Phil wrote in news:3f428929.92e.846930886@sybase.com:
>
> > I am using ASA v 8.0.2.4285 and I am developing for
> Palm.
>
> Yu can try something like this:
>
> create variable id varchar(30);
> create variable uid varchar(30);
> create variable d_name varchar(30);
>
> set d_name = 'tbowman';
> set id = null;
> set uid = null;
>
> select *
>   from mbt_request
>  where institution_userid =
>        ifnull( d_name, institution_userid, d_name )
>    and institution_id = ifnull( id, institution_id, id )
>    and trn_uid = ifnull( uid, trn_uid, uid )
>
> So your code would use the IFNULL function.
> If your input parameter is NULL, return the column name,
> else return  what the user entered as criteria.
>
>    and trn_uid = trn_uid  (will always be true)
>
>    so doing this usually works:
>    and trn_uid = ifnull( uid, trn_uid, uid )
> --
> David Fishburn
> NEW - Certified ASA Developer Version 8
> Sybase - iAnywhere Solutions
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD
> number with EACH post (dbeng8 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
>
0
Phil
8/20/2003 3:07:18 PM
Phil wrote:

> one=null;
> two=null;
> three=null;
> 
> EXEC SQL DECLARE CustCursor CURSOR FOR
> 	SELECT sccn,shipname
> 	FROM Cust
> 	WHERE route_id = ifnull(one,route_id, :dbCustRoute)
> 			AND dayAssignment = :dbCustDay
> 			AND chainid = :dbCustChain;
> 

In ESQL, you cannot pass a "NULL" value directly through a host 
variable.  You must use an indicator variable.

So, you need to add to your DECLARE section:

    short ind_one;

Then set this variable to "-1" and in your cursor, replace
"one" with ":one:ind_one".

Note that your cursor wasn't actually using the "one" variable because 
in ESQL you must prepend a host variable with ":" to indicate that its a 
host variable and not a database identifier (such as a column name).  I 
assume that analyzer was having a problem looking for identifier "one" 
in your reference schema.

See the 8.0.2 online docs:

UltraLite User's Guide
   10. The Embedded SQL Interface
     Indicator variables
       - Using indicator variables to handle NULL

Hope this helps,
greg.fenton
-- 
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

0
Greg
8/20/2003 3:38:06 PM
This works great after I used the indicator variables.

Thanks to both David and Greg for the timely responses!

Phil.
0
Phil
8/20/2003 7:21:30 PM
Reply:

Similar Artilces:

Ultralite Help
Hello, I am trying to create a database on my Windows CE device. I have never done this before. So, I am wondering what the procedure is (i.e. what files do I need to copy) and also in particular what does the DSN look like. tia, chance "chance" <chance@crwmail.com> wrote in news:43972b4b@forums-1-dub of sybase.public.sqlanywhere.ultralite: c> I am trying to create a database on my Windows CE device. I have never c> done this before. So, I am wondering what the procedure is (i.e. what c> files do I need to copy) and also in particular what does...

Ultralite Project query
Hi - I need to add a Sybase statement to the Ultralite project that reads - SELECT Name FROM Table WHERE Name LIKE '%abc%' where abc needs to be passed in at runtime in the placeholder ?. Can the above query be entered as: SELECT Name FROM Table WHERE Name LIKE '%?%' Will this work? Please help me, Thanks, preeti preeti wrote in news:22498C8C06CE1580005ED1EB85256CC6.005ED1FB85256CC6 @webforums: > SELECT Name FROM Table WHERE Name LIKE '%?%' No, I think you would do this: SELECT Name FROM Table WHERE Name LIKE ? Then when y...

Help with ASA-Ultralite
I'm new at ASA and Ultralite, and have just been hired as VB Programmer and given this project. I've been reading for days now, and need some help. I have a VB application with MobilVB installed. The database is an ASA database .db extension given to me by the client to make some changes. Looking back, the last programmer (fired before my hire), had the ULParm object pointing to a .udb database. By what process did it get from a .db to a .udb database? My application won't run with it as a .db for me to check my changes. It says can't find the Ultralite databas...

about ultralite
Dear All, Can i develop a ultralite program not using sybase asa database, can i using sql server or oracle directly.. Regards, Hans Hello, > Can i develop a ultralite program not using sybase asa database, > can i using sql server or oracle directly.. I think, you need asa just as a reference database, used by UltraLite, generating the source files etc. But with a working UltraLite application, which has a similar schema like your database (MSSQL, ORA etc.), you can use the MobiLink server to synchronize with. -- Best Regards, Tamas Beri mailto:tamas.beri@leadsys.h...

Error with Ultralite, help me ?
--------------31D2D648F057E83C4C4034F2 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Environment, Code Warrior 5.0 ASA Version 6.0.3.2961 Palm 5 I have some problems with the file .sqc, this has a longer size. I added a code about cursors and this doesn't work OK. From Code Warrior, I can see that there are some registers (function GetOpcionDetCount), but, when I open the cursor(function OpenDetOpcionList) y try to obtain the next register(MoveNextDetOpcionList), this no return nothing. NOTE: The file .cpp has segments greater 64KB, a...

DateTime Query in Ultralite
Hi I am using Palm Object Library for my application how to query the ultralite db with DateTime in the WHere Clause. The query i use is like EXEC SELECT * FROM ABC WHERE STARTTIME = :mytime here mytime is a host variable whose variable will be changing as per user enteres. If i use mytime as CDatetime type or CString as host variable type i get -157 error (type conversion error) Are there any exapmles or sample using this type of DateTime query Bass wrote in news:402cddd9.661b.846930886@sybase.com: B> Hi I am using Palm Object Library for my application how to B> qu...

Ultralite
Is it possible, by any means to open an .udb file from powerbuilder 10.2 either programmatically or from the database painter??? Maybe installing some kind of drivers as an example.. Thanks in advance, Chris ...

About UltraLite
Dear sirs, I want to gather some information about UltraLite from these forum. 1) What tools or language can develop a UltraLite application? 2) Can you tell me the size of a general UltraLite application? 3) How to calculate the licences fee of a UltraLite database engine and the application? Please help! Thanks in advance, Bill Lui E-mail : luichiming@hotmail.com >1) What tools or language can develop a UltraLite application? UltraLite supports C/C++ and Java. If you are developing for WinCE then the supported compiler is Visual C. If you are developing for t...

HELP!HELP!HELP!HELP!HELP!HELP!
I was using powerbuilder4.0 a month ago I decided that migrate it to PB7.0 When Im using 4.0, in datawindows I was using extra colums additional to stores procedure. I mean that for example; stored proc. returns 4 columns. I was using 2 extra columns to modify something from client. But I cant do the same thing in PB7.0. How can I do that??? please help. Gurcan yucel gyucel@infina.com.tr "I was using 2 extra columns to modify something from client." what does this mean that you added to the result set? If so then just goto the SQL and add a param. Then set the...

Help Help Help Help Help Help
------=_NextPart_000_0074_01C2960E.EBE13A30 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Can someone please help me. I have installed mysql on Unix Solaris and it works fine I have Perl installed previously which is working and with which i have installed other perlmodules before and are working fine. I have also installed DBI and it works fine with the Sybase drivers which i have installed. When i tried to install the Msql-Mysql-modules-1.2219 drivers i get the following error. # make /usr/local/bin/perl -Iblib/arch -Iblib/lib -I/...

Wrong Ultralite query results!
There seems to be a problem getting right results from a query in Ultralite DB on WinCE. V 7.0.2 Following statement works fine in the Windows emulation environment but not on the WinCE ARM target! SELECT * from customer WHERE name LIKE ? ( name is a char[20] ) For the ? we set 'S%'. After opening the query and trying to get through the results with Next() the Ultralite Library throughs an exception ( I do not know which one ) and skips the following code! The same code is working with a numerical field like: SELECT * from customer WHERE number LIKE ? (...

IIS and Ultralite
Hi - This is in continuation with the message I posted on 11/27/2002 - "Upload was not received by..." As suggested by Breck, i am starting a new thread for the same issue. As suggested by Graham I tried starting the Mobilink server at a specific IP on the machine with multiple IPs: dbmlsrv8 -c "dsn=myevaluations" -x http{host=66.242.129.8;port=2439;use_cookies=1;contd_timeout=90;unknown_timeout=90} This is giving raise to the below error in the hotsync window: FAIL Adaptive Server Anywhere Conduit (SQLCODE = -83) 11/29/02 18:13:45 This error me...

Help Help Help Help
i need to create a crosstab report using Crystal reprot for VS 2003 i face aproblem when i need to order my Fileds by their names my data must be like this Basic Cola Housing Gross Deduction1 Deduction2 Deduction3 Net but when i run my report it give my a not sorted data acutally i have acode field which can i order field using it. but i don't knwo how to do thisSo..................? Select Report tab | |-->Select Record Sort expert Choost the fields you want to sort --Select the corresponding sort direction f...

Help with ASA-Ultralite #2
I'm new at ASA and Ultralite, and have just been hired as VB Programmer and given this project. I've been reading for days now, and need some help. I have a VB application with MobilVB installed. The database is an ASA database .db extension given to me by the client to make some changes. Looking back, the last programmer (fired before my hire), had the ULParm object pointing to a .udb database. By what process did it get from a .db to a .udb database? My application won't run with it as a .db for me to check my changes. It says can't find the Ultralite databas...

Web resources about - Ultralite Query Help - sybase.sqlanywhere.ultralite

NEC UltraLite - Wikipedia, the free encyclopedia
The product was originally developed by an NEC Japan telecommunications engineering team that was trying to make an inexpensive lightweight terminal ...

1992 GM Ultralite running concept car - YouTube
... the North American Auto Show in Detroit in January 1992, the centerpeace of the General Motors exhibit, the running fully functional GM Ultralite ...

The GM Ultralite — 1400 lbs, 100 MPG, and You Can’t Have It
... and far less well known than it should have been, and we all taste bitter regret at what could have been . This is the case with the GM Ultralite, ...

NEC debuts VersaPro UltraLite VB laptop, Mate MG all-in-one for Japan
Like your computers to be all-business and only available in Japan? Then you might want to take a gander at NEC's latest duo, which includes ...

New Freescale I.MX6 SoCs Include IoT-focused UltraLite
... including new DualPlus and QuadPlus parts featuring enhanced GPUs and expanded memory support, and a new low-end, IoT focused 528MHz UltraLite ...

Portlandia Meets Ad Agency Snobbery This one hits... - 2matts.com
Portlandia Meets Ad Agency Snobbery This one hits a little too close to home. [via: IFC]

Travelers Have Always Expected Suitcases to be Light and Strong - Adweek
Travelers have always expected suitcases to be light and strong

NEC – New business-oriented PC lineup – 12 types (28 models)
... 34.9mm slim body 15.6 inch laptop PC “Versa Pro Type VX” with USB 3.0 built-in and a super light (875g, 14.9mm thin) laptop PC “VersaPro UltraLite ...

BASE Jumping Articles
Skip to Content Basejumper.com Home Forums Articles Gear Classifieds Photos Videos Email Help Submit an Article My Bookmarks Tags Search Forums ...

New Technology Running Shoes : Reebok RealFlex Run Men's
New technology running shoes like these Reebok RealFlex Run Men's "Give your feet the flexibility they deserve. RealFlex sensors work together ...

Resources last updated: 12/2/2015 6:15:47 AM