Add new value to existing LOV

[Former Member]

I need to add "Current Date" as LOV of one object, which displays today's date. Could you please help me to do that?

Thanks & Regards,

Muntasir

[former_member142702 · Accepted solution]

What kind of database do you build your universe on?

Regards,

Stratos

[Former Member · Accepted solution]

Database id Oracle.

Object query:

SELECT DISTINCT

SNAP_DT_DLY_RNG_VW.RANGE_NAME

FROM

DIM_DATE_RANGE_VW SNAP_DT_DLY_RNG_VW

If i need to update SQL, Please let me know where i need to put it?

[former_member142702 · Accepted solution]

WHat kind of data type is the RANGE_NAME field? Is this a string or a date?

I assume that this is the SQL query for the LOV, correct?

Regards,

Stratos

[Former Member · Accepted solution]

Thanks for quick reply.

its string.

Yes, its SQL for LOV.

When i try in Oracle toad

SELECT DISTINCT

SNAP_DT_DLY_RNG_VW.RANGE_NAME

FROM

DIM_DATE_RANGE_VW SNAP_DT_DLY_RNG_VW

UNION

SELECT 'SYSDATE' Current_Date FROM DUAL

it is sowing sysdate + existing values, not current date as display name..i dnt know why?

Also, i it is the correct query, then how can i include it into universe to change LOV SQL?

[former_member142702 · Accepted solution]

SELECT 'Current date' FROM DUAL

UNION

(SELECT DISTINCT

SNAP_DT_DLY_RNG_VW.RANGE_NAME

FROM

DIM_DATE_RANGE_VW SNAP_DT_DLY_RNG_VW)

Go to the universe designer, select the object properties entry in the context menu (right mouse button) and in the Properties tab press Edit. Press on the SQL icon, etner your SQL statement and check the option Do not generate SQL statement before running. Close the windows by pressing OK.

Still you have to handle the string value 'Current date' in your prompt.

The SQL where clause will look like this

((@prompt(<definition of your prompt here>)='Current date' AND SNAP_DT_DLY_RNG_VW.RANGE_NAME=XXXX) OR (@prompt(<definition of your prompt here>)=SNAP_DT_DLY_RNG_VW.RANGE_NAME))

Replace XXXX with an oracle stament that will convert current_date to a format that is comparable to the contents of your RANGE_NAME field.

Regards,

Stratos

[Former Member · Accepted solution]

Thanks a lot..You are awsome..

One more doubt:

I am not using prompt for this LOV. Do i still need to add th sql in prompt?

Thanks & Regards,

M.A.Jilani

[former_member142702 · Accepted solution]

The where clause is for the filter you are using. I guess that the LOV is used in one of your user prompts, correct?

Regards,

Stratos