[thelist] Oracle Date Swizzle?

Luther, Ron ron.luther at hp.com
Wed Aug 27 08:23:34 CDT 2003

Plunkett, Matt suggested:

>>Frustrating because this feels like it should be surmountable. :P  

Hi Matt,

Sorry.  I'm sure it's my problem for not explaining things as completely 
as I usually do ... lotsa 'hectic-nicity' in the air here these days!

{I also tend to chase red herrings down rabbit holes ... like that 'view' 
nonsense I spouted yesterday ... but I usually eliminate those quickly 
enough to zero in on the real issue ... usually.}

>>...why not [have] Oracle convert the date for you?  

Yeah - I was actually in the middle of asking the dba to turn these into 
'real' dates for me when we discovered the "cast" functionality.
>>[how about]
>> SELECT qty
>>   FROM yourtable
>>  WHERE due_date between (TO_DATE('20021001', 'YYYYMMDD') + 15) and 
>>                         (TO_DATE('20021001', 'YYYYMMDD') + 30);

Brio _does_ do SQL ... but it's one of those darn 'gui smart apps' so 
sometimes I need to wrestle with it a bit to get it to see things 'my way'.

The final report will look more like:
             Not_Aged     0-15 days     16-30 days
Order # 1       0             5              0
Order # 2       3             0              0
Order # 3       0             0              6

And now that I can convert that db '20021001' string value to a 10/01/2002 
style date I can do some date arithmetic and whup up on this report!

I think I'll be more looking at something more along the lines of
   SELECT 	Order
	     	, TRUNC(TO_DATE(OrderDate) - SYSDATE,0) as DeltaDays
		, DECODE(SIGN(DeltaDays),-1,OrderQty-ShippedQty,0)as Not_Aged
		, "some long fugly yet similar bugger to get me a range"
		, "lather, rinse, repeat"
   FROM	four tables at the moment
   WHERE	dynamic user-selectable limits

... but I think I'm past the main hurdle ... now that I've found that 
darn 'cast' functionality.

Sorry ... I've been in a bit of a fog for the past month or two ... too 
much to do and now fighting a sinus infection too.


<tip author="RonL." subject="casting return values in BRIO">

Right-click any item in the 'request line' to bring up a contextual 
menu -- (this is Brio-ese for the 'Select' portion of the SQL.)

Select "properties"

Now, from the dialog box that appears, select "Options".

A 'Datatype' drop-down box will appear at the bottom of the dialog box.

Select the datatype you wish to return and click "OK".

It's in plain site. How could anybody miss that?  ;-P


More information about the thelist mailing list