I have read the many posts on the optional prompts and from them I have created the following formula for the Where clause of a numeric data type object named “Plan Number”:
(@Select(Plan\Plan Number) IN @Prompt('Enter plan number or enter * for all plans','n','Plan\Plan Number',multi,free) ) OR ('*' in @Prompt('Enter plan number or enter * for all plans','n','Plan\Plan Number',multi,free))
I am using a DB2 database and keep getting the error “The data types of the operands for the operation are not compatible SQLSTATE = 42818.”
I can remove everything from and including the “OR” to the end of the statement and I can parse. I can change the ‘’ right after the “OR” to a number and it parses. I believe that it is the alpha '’ with my numeric data type that is causing the error.
I have not been able to find a post that shows how to use a wildcard with the numeric data type. If you can help I would really appreciate it!!!
@Select(Plan\Plan Number) IN @Prompt('Enter plan number or enter -1 for all plans','N','Plan\Plan Number',multi,free)
OR
-1 in @Prompt('Enter plan number or enter -1 for all plans','N','Plan\Plan Number',multi,free)
Hi Adreas, the solution which you gave to jenP is really fantastic. But I am facing a problem in my Query. I have a custom LOV in my universe which takes data (alpha numeric) from an excel sheet. The WHERE clause in my object definition looks like:
What is the condition which I need to give at the data provider level? If I give INV_LOV=INV_LOV and run, and click at the Values button at prompt, it opens up “Access Personal Data” window and then gives a list of values which are there in excel to select. But, if I enter “*” at the prompt, it gives list of all the invoices which are there in invoice table in univ and not those which are there in excel. What should I do ? Do i need to make changes to universe ?? Any help would be appreciated. THANKS IN ADVANCE !!!
One option:
Create a separate data provider from your MS Excel spreadsheet, then link the data providers and use a complex filter.
Another option:
With Business Objects v6 you can use the result set of on query (your MS Excel spreadsheet) as input for a condition of a second query (your universe query).
You can mimick this feature of v6 in v5 using VBA.
I have exactly the same request except that users want to use ALL/all/All instead of any other symbols. We use DB2 as well. This is number type, but ALL/all/All is character. If I change the data type for branch to char, the values will be 3 in length, as it was defined as integer (3). However, I do have a problem for numbers that have only 1 or 2 digits. For example, if is 30, after changing it into character, users have to enter 030, otherwise, there won’t be data returned. If I use substring to get rid of the leading zeroes, users may enter 030, as it is okay if I don’t have ALL as default value.
Here is my syntax for
=
CASE WHEN substr(char(IASMSFSV.VDALE_ACCOUNT.NUM_AE),1,2)=‘00’ THEN substr(char(IASMSFSV.VDALE_ACCOUNT.NUM_AE),3,1)
WHEN substr(char(IASMSFSV.VDALE_ACCOUNT.NUM_AE),1,1)=‘0’ THEN substr(char(IASMSFSV.VDALE_ACCOUNT.NUM_AE),2,2)
ELSE substr(char(IASMSFSV.VDALE_ACCOUNT.NUM_AE),1,3) END
I use this object in the prompt, I got the All/all/ALL work in the prompt.
Our environment: DB2/XI R2
Any suggestions and insight will be great. Thank you so much.
Lilly
P.S. I can just use -1 to replace ALL/all/All, but I think users might not like it.
I wonder how I can achieve the same using “All” instead of -1. The object that has Number data type has leading zeros (1 or 2). How can I get the prompt work using “All” as default, and it is still okay if users enter either 030 or 30?
Lilly, the gist of this thread was that the data type needed to stay the same. In this case, someone was prompting for a numeric field, so the “all” value also needed to be numeric.
You don’t say whether you are prompting for a numeric or an alphanumeric field. If it’s alpha, then you could use the string “ALL” or “*” or whatever you’d like.
Thank you for your reply. I need to prompt for alphanumeric, not numeric. I can get “All”. “ALL”,“all” work fine. However, Branch Number is of Number data type. After using All values, it has to be changed into Char. First, the index won’t work anymore. Secondly, users need to enter values for Branch Number like “030”. The question is: is it possible to enter “30” as well?
You could use a CASE or IF statement and test if the entered value is equal to ALL and if not then convert it to a numeric datatype using database/report functions.
This doesn’t make any sense. If you want to prompt for a number, you have to prompt for a number all the way. Else, if you prompt for alpha, then they could enter anything.
If you prompt for numeric, 030 is the same as 30. But if your underlying data is alphanumeric - and you need an exact match – then you’ll have to reformat the result, using your database functions.
These may not be good answers – it’s not clear quite what you’re asking.
(BOB member since 2002-06-20)
(BOB member since 2004-06-30)
(BOB member since 2002-06-17)
(BOB member since 2004-03-02)
(BOB member since 2002-06-17)