| View previous topic :: View next topic |
| Author |
Message |
jtballer
Newbie
Joined: 25 Mar 2006
Posts: 4
|
 Posted: Sat Mar 25, 2006 8:39 am Post subject: Trying to use CASE WHEN -- Need help |
 |
|
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 |
|
 |
trenkler
Power User
Joined: 21 May 2005
Posts: 55
|
| Posted: Sat Mar 25, 2006 9:05 am Post subject: |
|
|
Maybe you have to push "SQL" button in menu when trying this command.
juraj |
|
| Back to top |
|
|
jtballer
Newbie
Joined: 25 Mar 2006
Posts: 4
|
| Posted: Sat Mar 25, 2006 3:37 pm Post subject: |
|
|
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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
|
| Back to top |
|
|
jtballer
Newbie
Joined: 25 Mar 2006
Posts: 4
|
| Posted: Sat Mar 25, 2006 4:17 pm Post subject: |
|
|
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 |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Mar 26, 2006 4:36 am Post subject: |
|
|
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 |
|
|
|
