Converting an @variable to uppercase

building reports desktopintelligence
1

Hi! I have been trying variations of all the code I’ve found on this site to get the user input to become uppercase regardless of what they type in the prompted field to no avail. Here is my UNTOUCHED code beginning with the where clause below:

WHERE
( I2PADM.LD_LEG_DETL_T.LD_LEG_ID=I2PADM.LD_LEG_T.LD_LEG_ID(+) )
AND ( I2PADM.SHPM_T.SHPM_ID(+)=I2PADM.LD_LEG_DETL_T.SHPM_ID )
AND ( I2PADM.SHPM_T.SHPM_ID=I2PADM.ELMT_T.SHPM_ID )
AND ( A2_STOP_T.STOP_ID(+)=I2PADM.LD_LEG_DETL_T.PICK_STOP_ID )
AND ( I2PADM.LD_LEG_T.LD_LEG_ID=V_NA_ETA.LD_LEG_ID(+) )
AND ( V_YARDUNIT.CURRENTYARDLOCATIONID=A2_V_YARDLOCATION.ID(+) )
AND ( A2_V_YARDLOCATION.YARDAREAID=A2_V_YARDAREA.ID(+) )
AND ( V_YARDUNIT.CURRENTLOADID(+)=V_YARDUNITLOAD.ID )
AND ( I2PADM.LD_LEG_T.LD_LEG_ID=V_NA_LOAD_CALC_DATES.LD_LEG_ID(+) )
AND ( I2PADM.LD_LEG_T.CUR_OPTLSTAT_ID=I2PADM.STAT_T.STAT_ID(+) )
AND ( I2PADM.LD_LEG_T.LD_LEG_ID=V_NA_EVENT_DATES.LD_LEG_ID(+) )
AND ( to_char(I2PADM.LD_LEG_T.LD_LEG_ID)=V_YARDUNITLOAD.LOADIDENTIFIER(+) )
AND ( I2PADM.LD_LEG_DETL_T.LD_LEG_ID=V_NA_NEW_MIN_DT.LD(+) )
AND ( V_NA_LOAD_CALC_DATES.SCHD_PU>= @Prompt(‘3. From Scheduled Date’,‘D’,‘V na Load Calc Dates\Schd Pu Date’,mono,free) )
AND ( (V_NA_LOAD_CALC_DATES.SCHD_PU) -1< @Prompt(‘4.To Scheduled Date’,‘D’,‘V na Load Calc Dates\Schd Pu Date’,mono,free)

)
AND (
substr(I2PADM.LD_LEG_DETL_T.FRM_SHPG_LOC_CD,1,((Instr(I2PADM.LD_LEG_DETL_T.FRM_SHPG_LOC_CD,’-MMMM’)-1))) = @variable(‘1. Enter Supplier Code’)
AND V_NA_LOAD_CALC_DATES.SCHD_PU BETWEEN V_NA_LOAD_CALC_DATES.SCHD_PU AND V_NA_LOAD_CALC_DATES.SCHD_PU
)

Please note that I am trying to get what is entered as a supplier code by the user to become uppercase. It appears that the word ‘upper’ does not work with @variable.

Any help would be greatly appreciated.

Thanks.

Rita

sturgra (BOB member since 2006-11-08)

2

I can’t see why not. Is there a particular error that you are getting? Maybe give us your “touched” version?

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

3

Also one more point, you must have UPPER() function on both sides of the = sign to make sure that it works always whether the database column value is stored either in uppercase or lowercase.

upper(table.column) = upper(@variable(…))

And It should work…

BO_Chief :us: (BOB member since 2004-06-06)

4

BO_Chief…your solution worked (THANK YOU VERY MUCH)…however now I realize that I have to check the box “DO NOT GENERATE SQL”. Is there any way to do this change with the created variable at the universe level?

Thanks for all your help!

sturgra (BOB member since 2006-11-08)

5

In order to avoid checking the option “Do not generate SQL”. You can do this, create a predefined condition explicitly for this report and put all of the code which is going into the WHERE part… like below.


 ( I2PADM.LD_LEG_DETL_T.LD_LEG_ID=I2PADM.LD_LEG_T.LD_LEG_ID(+) )
AND ( I2PADM.SHPM_T.SHPM_ID(+)=I2PADM.LD_LEG_DETL_T.SHPM_ID )
AND ( I2PADM.SHPM_T.SHPM_ID=I2PADM.ELMT_T.SHPM_ID )
AND ( A2_STOP_T.STOP_ID(+)=I2PADM.LD_LEG_DETL_T.PICK_STOP_ID )
AND ( I2PADM.LD_LEG_T.LD_LEG_ID=V_NA_ETA.LD_LEG_ID(+) )
AND ( V_YARDUNIT.CURRENTYARDLOCATIONID=A2_V_YARDLOCATION.ID(+) )
AND ( A2_V_YARDLOCATION.YARDAREAID=A2_V_YARDAREA.ID(+) )
AND ( V_YARDUNIT.CURRENTLOADID(+)=V_YARDUNITLOAD.ID )
AND ( I2PADM.LD_LEG_T.LD_LEG_ID=V_NA_LOAD_CALC_DATES.LD_LEG_ID(+) )
AND ( I2PADM.LD_LEG_T.CUR_OPTLSTAT_ID=I2PADM.STAT_T.STAT_ID(+) )
AND ( I2PADM.LD_LEG_T.LD_LEG_ID=V_NA_EVENT_DATES.LD_LEG_ID(+) )
AND ( to_char(I2PADM.LD_LEG_T.LD_LEG_ID)=V_YARDUNITLOAD.LOADIDENTIFIER(+) )
AND ( I2PADM.LD_LEG_DETL_T.LD_LEG_ID=V_NA_NEW_MIN_DT.LD(+) )
AND ( V_NA_LOAD_CALC_DATES.SCHD_PU>= @Prompt('3. From Scheduled Date','D','V na Load Calc Dates\Schd Pu Date',mono,free) )
AND ( (V_NA_LOAD_CALC_DATES.SCHD_PU) -1< @Prompt('4.To Scheduled Date','D','V na Load Calc Dates\Schd Pu Date',mono,free)

)
AND (
substr(I2PADM.LD_LEG_DETL_T.FRM_SHPG_LOC_CD,1,((Instr(I2PADM.LD_LEG_DETL_T.FRM_SHPG_LOC_CD,'-MMMM')-1))) = @variable('1. Enter Supplier Code')
AND V_NA_LOAD_CALC_DATES.SCHD_PU BETWEEN V_NA_LOAD_CALC_DATES.SCHD_PU AND V_NA_LOAD_CALC_DATES.SCHD_PU
)

Include the changes required in the above code to use UPPER() function wherever necessary.
Use this pre-defined condition in your Query Panel for this report, with this you can avoid the option “Do not generate SQL”

Hope it helps.

BO_Chief :us: (BOB member since 2004-06-06)

6

Hi!

I found this code on creating the variable at the universe level which is what I would prefer to do but it doesn’t seem to work…

Solution 2: Create an object: “Employee Name Condition”
Select: upper(@Prompt(‘1.Enter Employee Name’, ‘A’, ‘Class\object’, multi, free)

Create a Condition Object: “Select Employee Name”
Select: “”
Where: “Employee Name” = “Employee Name Condition”

Hide Object “Employee Name Condition”

My syntax fails parsing on the “Create an object”. This is my code:
upper(@variable(‘1.Enter Supplier’, ‘A’, ‘@Select’Load Leg Detail\Ld Frm Loc)’, multi, free) )).

Please advise…

Thanks.
Rita

sturgra (BOB member since 2006-11-08)

7

@variable function will not have 5 arguments. Instead make it @prompt.

Hope that helps.

BO_Chief :us: (BOB member since 2004-06-06)