OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Trying to use CASE WHEN -- Need help

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
jtballer
Newbie
Newbie


Joined: 25 Mar 2006
Posts: 4

PostPosted: Sat Mar 25, 2006 8:39 am    Post subject: Trying to use CASE WHEN -- Need help Reply with quote

I'm currently using BASE to query an external database and return the values to CALC. My company operates off of Periods rather than calendar months to report sales so I need to evaluate each transaction or record by it's date and assign a value for what period the transaction took place.
ex:
DATE between 01/01/2006 AND 01/28/2006 = Period 1
01/29/2006 - 02/28/2006 = Period 2
03/01/2006 - 03/29/2006 = Period 3

etc;
etc;

While looking through the forum I could only find one example of CASE WHEN posted by Drew Jensen. He posted the following example

Quote:
Code:

SELECT
"COUNT",
"TXCODE",
CASE TXCODE 
   WHEN  'NEW' THEN '1' ELSE  '-1'  END AS "REC",
CASE
   WHEN "COUNT" = 2 AND "TXCODE" = 'NEW' THEN '2'
   WHEN "COUNT" = 2 AND "TXCODE" = 'INACTIVE'  THEN '-2'
   WHEN "COUNT" = 1 AND "TXCODE" = 'NEW' THEN '1'
   WHEN "COUNT" = 1 AND "TXCODE" = 'INACTIVE'  THEN '-1'
END AS "MEM",
'1' AS "TX",
CASE
   WHEN "COUNT" = 2 AND "TXCODE" = 'NEW' THEN '40.00'
   WHEN "COUNT" = 1 AND "TXCODE" = 'NEW' THEN '25.00'
   WHEN "TXCODE" = 'INACTIVE'  THEN '0.00'
END AS "AMT"
FROM "Table2"


I have tried to do a similar example but I get this error message:
Syntax error in SQL expression
parse error, expecting `BETWEEN' or `IN' or `SQL_TOKEN_LIKE'


This is my code:
Code:

SELECT "ProductLine", "ItemNumber",
CASE
WHEN "ProductLine"='POL2' THEN 'PolkAudio'
END AS "Company"
FROM "IM1_InventoryMasterfile"


At this point i'm not even trying to evaluate the date, I just want to get one CASE statement working and then I'll go from their.
Any help would be much appreciated.

Thanks
-Jake
Back to top
View user's profile Send private message
trenkler
Power User
Power User


Joined: 21 May 2005
Posts: 55

PostPosted: Sat Mar 25, 2006 9:05 am    Post subject: Reply with quote

Maybe you have to push "SQL" button in menu when trying this command.

juraj
Back to top
View user's profile Send private message
jtballer
Newbie
Newbie


Joined: 25 Mar 2006
Posts: 4

PostPosted: Sat Mar 25, 2006 3:37 pm    Post subject: Reply with quote

Thanks for the quick response Trenkler. I wasn't familiar with the "SQL" button on the menu bar until you suggested it. So I tried selecting the "SQL" button that says "Run SQL Command Directly" and the selecting the "Run Query Button". Now I'm getting this error:

SQL Status: 37000
Error code: 1015

[ProvideX][ODBC Driver]Expected lexical element not found: FROM


Any ideas?
Thanks again for your help.
-Jake
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sat Mar 25, 2006 4:10 pm    Post subject: Reply with quote

What type od database are you connecting to?
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
jtballer
Newbie
Newbie


Joined: 25 Mar 2006
Posts: 4

PostPosted: Sat Mar 25, 2006 4:17 pm    Post subject: Reply with quote

I'm connecting to a software called MAS200 built by Best Software. I'm not 100% sure what kind of database i'm connectiong to but I have been told it's a proprietary database similar to Access, hopefully better. There is an SQL version of MAS200 but I know we are not using that. I connect with the traditional ODBC drivers if that helps at all.
Thanks
-Jake
Back to top
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Mar 26, 2006 4:36 am    Post subject: Reply with quote

OK, well you need to understand that Base is just a front end to the datasource. In the case of this type of selection statement Base simply passes along to the datasource ( MAS ) in your case and it must understand the syntax. Apparently that isn't the case.

Perhaps you can get a list of which commands are available for the Sage Software proprietary database. I have never been able to find one in the past - but then I have never been either a customer, nor an authorized reseller.

I know you can do simpel selects. So you could do that and get the data into calc and then perform the transformations mybe.
_________________
Blog - http://baseanswers.spaces.live.com/
Back to top
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group