Re: sql question:

Yes, indeedy, Mr. K. Recently, I tasked myself (how's that for a silly example
of turning a noun into a verb?) with expressing some dates of varying quality on a webpage so that they can be easily and quickly sorted and evaluated. My dates describe earthquakes, tsunamis, etc. and many of these are known only to a month or a day or whatever many hundreds of years ago. With that in mind, we assign a date to our event as well as a "temporal accuracy". This temporary accuracy is a bitfield (but just expressed as a regular number in our database), and it contains information on the accuracy of the second, minute, hour, day, month and year of that event. Of course, these bits can be combined in any way we like.

For your viewing pleasure, here is a test function that I wrote that returns
an HTML date string for a given event date in our database. If a certain
component of that date is unknown, then it is expressed in red and italics in the HTML tag. Enjoy!

create function testfunc1(tsevent_id in number) return varchar2 as   html_date_string varchar2(300); begin
select
  '<br>' ||
  decode(to_number(bitand(temporal_accuracy,32)) - 32,

      0,'<FONT COLOR="#cc0000"><I>' || to_char(ngdc_date,'yyyy') || '</I></FONT>',

      to_char(ngdc_date,'yyyy')) ||
  decode(to_number(bitand(temporal_accuracy,16)) - 16,

      0,'<FONT COLOR="#cc0000"><I>' || to_char(ngdc_date,'/mm') || '</I></FONT>',

      to_char(ngdc_date,'/mm')) ||
  decode(to_number(bitand(temporal_accuracy,8)) - 8,

      0,'<FONT COLOR="#cc0000"><I>' || to_char(ngdc_date,'/dd') || '</I></FONT>',

      to_char(ngdc_date,'/dd')) ||
  decode(to_number(bitand(temporal_accuracy,4)) - 4,

      0,'<FONT COLOR="#cc0000"><I>' || to_char(ngdc_date,' hh24') || '</I></FONT>',

      to_char(ngdc_date,' hh24')) ||
  decode(to_number(bitand(temporal_accuracy,2)) - 2,

      0,'<FONT COLOR="#cc0000"><I>' || to_char(ngdc_date,':mi') || '</I></FONT>',

      to_char(ngdc_date,':mi')) ||
  decode(to_number(bitand(temporal_accuracy,1)) - 1,

      0,'<FONT COLOR="#cc0000"><I>' || to_char(ngdc_date,':ss') || '</I></FONT>',

      to_char(ngdc_date,':ss')) into html_date_string   from haz2.tsevent where id = tsevent_id;   return html_date_string;
end;
/

Tom

Thomas Kyte wrote:
>
> In article <0N1Y8.89801$Bt1.4501707_at_bin5.nnrp.aus1.giganews.com>, "Chris says...
> >
> >hi all:
> >
> >I'm trying to figure out SQL to do the following:
> >I have an application that tracks SQL that is being sent to the database,
> >and one of it's features is the ability to identify whether a query is an
> >insert, update, delete, select, select with all rows returned, the query is
> >the first in a user session....and many other criteria. Because of the
> >nature of SQL, i.e. many of the above could be true, the deisgners made each
> >flag a 'bit'. So an example is:
> >4 is a select
> >8 is insert
> >16 is update
> >32 is first query in session
> >64 is delete
> >128 is a cancelled query
> >256 is database cancelled query
> >
> >
>
> where bitand( flag_column, 64 ) = 64
>
> will get them.
>
> >
> >Now the SQL that I have to find is 'which of these records is a delete?'
> >The values could be 64, 96, 416, 445, 320 and many others. All in all
> >there are probably 20 possible values and the permutations are to lengthy to
> >put in a 'like', so I need some kind of algorithm. Does anyone have any
> >ideas?
> >
> >email:
> >caiell02_at_comcast.net
> >
> >thanks, Chris
> >
> >
>
> --
> Thomas Kyte (tkyte@oracle.com) http://asktom.oracle.com/
> Expert one on one Oracle, programming techniques and solutions for Oracle.
> http://www.amazon.com/exec/obidos/ASIN/1861004826/
> Opinions are mine and do not necessarily reflect those of Oracle Corp
Received on Tue Jul 16 2002 - 20:09:22 CDT