[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.
RonL.
<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
</tip>
More information about the thelist
mailing list