texasreddog
asked on
case is_date and is_string functions in Oracle
I'm trying to come up with a way to check if a field that I'm selecting from a database is a date or a string that I can do within a case statement. here's part of the select:
select /*+ full(i) */
decode(upper(i.TYPE), 'NEW', :NEW_BASE_NAME, :USED_BASE_NAME) BASE_NAME,
i.context_guid,
o.OWNER_GUID,
substr(o.IA_DEALER_ID, 3) DEALER_ID,
STOCK_NUM,
to_char(i.INVENTORY_DATE, 'MM/DD/YYYY') INVENTORY_DATE,
i.VEHICLE.SUB_TRIM_LEVEL V_SUB_TRIM_LEVEL,
the problem is that i.INVENTORY_DATE is sometimes returning an integer (1, 300) and it is not an actual date. i.VEHICLE.SUB_TRIM_LEVEL is sometimes coming back as a date, rather than a string. I want to put case statements around these two fields, so I can make sure that i.INVENTORY_DATE is coming out as a date, and if not, return NULL, and if i.VEHICLE.SUB_TRIM_LEVEL is coming out as a date and not a string, then return NULL. how can I accomplish this with a case statement? I'm assuming it is not going to be very simple, since there are no is_date or is_string functions in Oracle. thanks!
select /*+ full(i) */
decode(upper(i.TYPE), 'NEW', :NEW_BASE_NAME, :USED_BASE_NAME) BASE_NAME,
i.context_guid,
o.OWNER_GUID,
substr(o.IA_DEALER_ID, 3) DEALER_ID,
STOCK_NUM,
to_char(i.INVENTORY_DATE, 'MM/DD/YYYY') INVENTORY_DATE,
i.VEHICLE.SUB_TRIM_LEVEL V_SUB_TRIM_LEVEL,
the problem is that i.INVENTORY_DATE is sometimes returning an integer (1, 300) and it is not an actual date. i.VEHICLE.SUB_TRIM_LEVEL is sometimes coming back as a date, rather than a string. I want to put case statements around these two fields, so I can make sure that i.INVENTORY_DATE is coming out as a date, and if not, return NULL, and if i.VEHICLE.SUB_TRIM_LEVEL is coming out as a date and not a string, then return NULL. how can I accomplish this with a case statement? I'm assuming it is not going to be very simple, since there are no is_date or is_string functions in Oracle. thanks!
ASKER
ok, the regexp_instr statement seems to work, but I need to add another case to one of these statements. I tried this:
case when regexp_instr(i.VEHICLE.SUB
then i.VEHICLE.SUB_TRIM_LEVEL
when regexp_instr(i.VEHICLE.SUB
then i.VEHICLE.SUB_TRIM_LEVEL
when is_number(i.VEHICLE.SUB_TR
then i.VEHICLE.SUB_TRIM_LEVEL
else NULL end V_SUB_TRIM_LEVEL
I tested by inserting a '10' into i.vehicle.sub_trim_level. the first part of the case statement seems to work, but it is still displaying a 10 when I select it from the table. thus it doesn't look like it is not checking the second or third conditions in this case. how do I make it check all three cases, so I will for sure only get a string back that begins with a letter? '10' should show 'Y' for is_number and also 1 for the second regular expression, since the first character is a 1.
case when regexp_instr(i.VEHICLE.SUB
then i.VEHICLE.SUB_TRIM_LEVEL
when regexp_instr(i.VEHICLE.SUB
then i.VEHICLE.SUB_TRIM_LEVEL
when is_number(i.VEHICLE.SUB_TR
then i.VEHICLE.SUB_TRIM_LEVEL
else NULL end V_SUB_TRIM_LEVEL
I tested by inserting a '10' into i.vehicle.sub_trim_level. the first part of the case statement seems to work, but it is still displaying a 10 when I select it from the table. thus it doesn't look like it is not checking the second or third conditions in this case. how do I make it check all three cases, so I will for sure only get a string back that begins with a letter? '10' should show 'Y' for is_number and also 1 for the second regular expression, since the first character is a 1.
'10' matches the first condition...
the first looks for xx/xx/xxxx where x is a digit. If it does not find that pattern then return i. vehicle_sub_trim_level.
'10' is not that pattern. so it gets returned.
what are the rest of your conditions trying to do? the first 3 all return the same result, I would write them as a single condition with "OR" instead of 3 separate conditions.
right now what they do is:
2nd condition- if the first character is not a digit then return the string
3rd condition - if the "is_number" function returns N then return the string.
for all else return null.
not sure why you need the 2nd condition, the 3rd condition would satisfy that.
what is "is_number" ? I can guess what it is supposed to do, does it work?
the first looks for xx/xx/xxxx where x is a digit. If it does not find that pattern then return i. vehicle_sub_trim_level.
'10' is not that pattern. so it gets returned.
what are the rest of your conditions trying to do? the first 3 all return the same result, I would write them as a single condition with "OR" instead of 3 separate conditions.
right now what they do is:
2nd condition- if the first character is not a digit then return the string
3rd condition - if the "is_number" function returns N then return the string.
for all else return null.
not sure why you need the 2nd condition, the 3rd condition would satisfy that.
what is "is_number" ? I can guess what it is supposed to do, does it work?
ASKER
the is_number condition just checks to see if the data is a number. I see what you are saying. I'm going to make the conditions together, but use and instead of or, to make it more strict. I think using and will work.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
if i.VEHICLE.SUB_TRIM_LEVEL is a string then a string that happens to be human readable as a date is still a string. Is there a particular string or format you would want to detect and NULL?
as for the inventory_date, if it's an integer sometimes and a date others, then I assume it's really just a string. If so, then reformatting it with to_char won't help. If you want to assume all strings that look like xx/xx/xxxx are dates, then try this...
CASE
WHEN REGEXP_INSTR(i.inventory_d
THEN
i.inventory_date
ELSE
NULL
END
inventory_date
if you're looking for something similar in sub_trim_level then it would be the reverse...
CASE
WHEN REGEXP_INSTR(i.i.vehicle.s
0
THEN
i.i.vehicle.sub_trim_level
ELSE
NULL
END
v_sub_trim_level