What?
I need to refer to how to create a function in Oracle PL/SQL and sites on the net just attempt to overcomplicate everything and have forgotten how it is to be new to Oracle. I need a function in it's simplest form and if I want to torture my successors, I'll complicate the function myself.

How?
Functions are supposed to return a single value, which is all I need in this case. In my example, I need to submit an Active Directory (AD) username and receive a student ID number instead, all with the aim to improve performance on some SSRS queries which accept either an ID number or an AD name as user parameters.

Syntax:
copyraw
CREATE [OR REPLACE] FUNCTION function_name
    [ (parameter [,parameter]) ]

    RETURN return_datatype

IS | AS
    [declaration_section]

BEGIN
    executable_section

[EXCEPTION
    exception_section]

END [function_name];
  1.  CREATE [OR REPLACE] FUNCTION function_name 
  2.      [ (parameter [,parameter]) ] 
  3.   
  4.      RETURN return_datatype 
  5.   
  6.  IS | AS 
  7.      [declaration_section] 
  8.   
  9.  BEGIN 
  10.      executable_section 
  11.   
  12.  [EXCEPTION 
  13.      exception_section] 
  14.   
  15.  END [function_name]
Category: Oracle PL/SQL :: Article: 409

What?
I have a mySQL database table of room assets that has a field containing the ID numbers of images relevant to this room.

The Problem?
When I select specifying the statement "WHERE IN (c.RoomImages)", this is interpreted as a string and when converted to a number only retrieves the first value before the first comma. Consider the following, the first query is how MySQL interprets the query and the second is what I want it to do:
copyraw
SELECT value FROM my_table WHERE my_id IN ('1, 2, 3')
SELECT value FROM my_table WHERE my_id IN ('1', '2', '3')
  1.  SELECT value FROM my_table WHERE my_id IN ('1, 2, 3') 
  2.  SELECT value FROM my_table WHERE my_id IN ('1', '2', '3') 

Category: MySQL :: Article: 408

Just putting a note as I have spent ages looking for a solution and getting it to work in my environment.

What?
Need to be able to omit HTML tags in certain fields of a mySQL database.

Why?
We are preparing to migrate old content to a new system. From a MediaWiki CMS to a SaaS called Service-Now. The previous interlinking between images could no longer be used.


MySQL is just the best
Unfortunately I make a living using Microsoft and Oracle products. I shouldn't say unfortunately as I don't see myself doing any other job and it beats daytime television any day.

I use this quite a lot so I thought I'd put an article here somewhere. Based on the following concept:
copyraw
RowID     column_to_return_as_string   
--------- --------------------------
1         Me
2         Myself
3         I

-- to be returned as
RowID     my_field_name   
--------- --------------------------
1         Me,Myself,I
  1.  RowID     column_to_return_as_string 
  2.  --------- -------------------------- 
  3.  1         Me 
  4.  2         Myself 
  5.  3         I 
  6.   
  7.  -- to be returned as 
  8.  RowID     my_field_name 
  9.  --------- -------------------------- 
  10.  1         Me,Myself,

Category: Databases :: Article: 403

Why?
I recently made a joomla module that displays the lastest members to signup. It goes a little further and counts activated accounts for the past day, week, month and year (the below examples count all accounts irrespective of being activated or not). It needs to pick up trends as well and compare for example todays, with yesterdays up to the same hour.

How?
I used to use a lot more PHP but since becoming an analyst, I do more at the database level now. What follows should be usable mySQL statements to get all the numbers:


I have googled, binged and asked but to no avail. Some self-proclaimed IT experts in forums said "why would you want to do that?". Unhelpful Bellends. It's a bit like asking me "Why can't I turn on my computer" and me replying "why would you want to do that?".

Anyway, I was looking for something like the record separator in Oracle SQL*Plus where a row of data (blank or made of symbols) separates two sets of data from within the same select query based on a column that's different. So for example, I have data like the following:
copyraw
SELECT
	DATENAME(dw, StartDate) AS 'Day'
FROM 
	Timetable
ORDER BY
	StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC

-- Yields
/*
Day              
---------------- 
Monday
Monday
Monday
Tuesday
Tuesday
Wednesday
Thursday
Thursday
Thursday
Thursday
Friday
Friday
*/
  1.  SELECT 
  2.      DATENAME(dw, StartDate) AS 'Day' 
  3.  FROM 
  4.      Timetable 
  5.  ORDER BY 
  6.      StartDate ASC, AnotherOrderByCol ASC, AndAnotherOrderByCol ASC 
  7.   
  8.  -- Yields 
  9.  /* 
  10.  Day 
  11.  ---------------- 
  12.  Monday 
  13.  Monday 
  14.  Monday 
  15.  Tuesday 
  16.  Tuesday 
  17.  Wednesday 
  18.  Thursday 
  19.  Thursday 
  20.  Thursday 
  21.  Thursday 
  22.  Friday 
  23.  Friday 
  24.  */ 

Category: Transact-SQL :: Article: 394

Hopefully the title doesn't put you off but after much Googling and Bing-ing, I still couldn't figure out how to do this. Hopefully this article will help you more than my search engine skills do.

My Setup
Windows XP Workstation
      ...needs to open...
Excel 2007 SP2
      ...with ODBC to...
MySQL v5+
      ...hosting database...
ActivityLog
      ...contains activity, staffID, resourceID, start time, end time...


Report Specification
PivotTable Report
      ...resources in row (along the side)...
      ...staff in columns (along the top)...
      ...persondays in values (the number my bosses want - 7h 24m or 26640s is 1 person day)...
With date range as parameters
      ...ouch...
      ...and it was so easy up to here...


Amazing, I have just spent all morning on Microsoft websites to determine what number is the TimeDataRetrieval column displaying. Thank you I know it's an INT. There is just a serious lack of documentation as to what this database is and how it populates its data.

View: ExecutionLog
InstanceNamenvarchar(38)NOT NULLName of the report server instance that handled the request. Usually YOURSERVERNAME\MSSQLSERVER
ReportIDuniqueidentifierNULLThe ID of the report (looks like a hexadecimal SSID). It's the unique ID of the report but not unique in the table (can be referenced many times).
UserNamenvarchar(260)NULLWindows authenticated username and domain of the person running the report (eg. MYDOMAIN\myusername)
RequestTypebitNOT NULLUser or System. Can be 1 or 0. This was zero "0" when I would run a report as a user.
Formatnvarchar(26)NULLThis is the rendering format. Mostly RPL if viewed in MS Internet Explorer.
ParametersntextNULLParameters and the values they were submitted with.
TimeStartdatetimeNOT NULLTime report started to run.
TimeEnddatetimeNOT NULLTime report finished running? Need to check what finished?
TimeDataRetrievalintNOT NULLMilliseconds spent retrieving the data.
TimeProcessingintNOT NULLMilliseconds spent processing the report.
TimeRenderingintNOT NULLMilliseconds spent rendering the report.
SourceintNOT NULLSource of the report exection (1=Live, 2=Cache, 3=Snapshot, 4=History)
Statusnvarchar(32)NOT NULLeither rsSuccess or an error code; if multiple errors occur, only the first error is recorded
ByteCountbigintNOT NULLSize of rendered reports in bytes.
RowCountbigintNOT NULLNumber of rows returned from queries.


Situation:
I have a silly database table (not mine) storing CMIS Facility week numbers and their starting dates. For those of you unfamiliar with this system, the reason week numbers are different to normal people's week numbers is because these are academic week numbers. So I can't use the built-in functions.

The current structure looks similar to this:

copyraw
ID        SetID            WeekNumber             StartDate
--------- ---------------- ---------------------- ----------------------------
1         2011/2012        1                      2011-07-18 00:00:00
...
52        2011/2012        52                     2012-07-09 00:00:00
  1.  ID        SetID            WeekNumber             StartDate 
  2.  --------- ---------------- ---------------------- ---------------------------- 
  3.  1         2011/2012        1                      2011-07-18 00:00:00 
  4.  ... 
  5.  52        2011/2012        52                     2012-07-09 00:00:00 

Joe you're an idiot!
You might say to me why not run the CMIS Facility application and add a new set, it will put these dates in automatically.

Herein lies the problem
The reason I'm doing this is for another system which decided to "cleverly" use the exports from CMIS Facility so that all the weeks correspond to the rest of the academic data. Unfortunately the developer wrote a system he felt would last the rest of his PhD degree, it's a shame he started in his last year. He used functions to mktime and simulate the dates. A function goes in with a normal calendar date and returns an academic week number and the week commencing date.

Problem?
The 1st of January 2010 was a Friday. The 1st of January 2011 was a Saturday. The developer felt that as long as you adjust the script each year you could make the system last another year. Shame he also forgot the academic year ends halfway in a normal people's calendar, so you actually have to adjust this twice a year.

Category: MySQL :: Article: 342

The Why
So I find myself writing increasingly complex SQL scripts and it's at the stage where we need to optimize the queries because some scripts are noticeably slow (as observed by the customer...) and then others not.

The What
I'm going to run these benchmark tests against a system that is both up and running via the front-end and back-end. It's MediaWiki CMS used by Wikipedia.org and the like. I like queries against this database because it involves linking a lot of tables and outputting... just articles and their titles.

I have another table holding the audit trail of content approvers on the system. Approvers can e-sign an article (approving it) by clicking on a button. We want to bring back the articles that aren't listed in the audit table (articles yet to be approved).


Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

RSS Feed

Related Articles

Joes Revolver Map

Joes Word Cloud

license   google   need   value   find   error   data   parameter   system   script   table   create   file   added   joomla   mysql   used   report   where   code   user   creator   name   order   display   field   website   form   server   note   function   date   work   zoho   page   client   source   following   windows   first   version   would   case   database   time   files   uploaded   using   deluge   list   JoelLipman.Com

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF
© 2024 Joel Lipman .com. All Rights Reserved.