What?
Some people have been suggesting you can use "CREATE OR REPLACE ... VIEW ... FUNCTION" but my SQL Server 2008 Management Studio doesn't like this and refuses to understand what I'm trying to do.

Why?
As this data seemed to be across various websites, I wanted a page which has all of them in one place. So here you go, I hope it's of some use. If it's wrong then just post a comment at the bottom of this page.


This was called a Data-Scrambling Function but it depends on what you mean by "scrambling". This is a function which merely uses the same characters but switches their order randomly, so I've renamed it DataJumble as opposed to my article on Data-Scrambling.

Why?
We want to scramble sensitive data that we send to suppliers for support or analysis. With inspiration from: "Obfuscating your SQL Server Data" by John Magnabosco but tweaked for our purposes. NOTE that the following has only ever been run on development environments and I would not recommend running this on a production system as I have not tested the performance and database load.

copyraw
Before:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           John Smith           1990-03-21
           2           Fred Bloggs          1988-11-02
           3           Another User         1985-07-11
           4           Yet Another User     1977-06-25

       After:
           StudentID   StudentName          DateOfBirth
           ----------- -------------------- -------------
           1           mStnoh iJh           1926-01-02
           2           lgreg BFdos          1969-03-14
           3           onrUest ehAr         1968-05-05
           4           otentre AhYe Usr     1964-08-08
  1.  Before: 
  2.             StudentID   StudentName          DateOfBirth 
  3.             ----------- -------------------- ------------- 
  4.             1           John Smith           1990-03-21 
  5.             2           Fred Bloggs          1988-11-02 
  6.             3           Another User         1985-07-11 
  7.             4           Yet Another User     1977-06-25 
  8.   
  9.         After: 
  10.             StudentID   StudentName          DateOfBirth 
  11.             ----------- -------------------- ------------- 
  12.             1           mStnoh iJh           1926-01-02 
  13.             2           lgreg BFdos          1969-03-14 
  14.             3           onrUest ehAr         1968-05-05 
  15.             4           otentre AhYe Usr     1964-08-08 

Category: Transact-SQL :: Article: 422

Given Data
Activities by employees with start dates and end dates in a mySQL database.

Objective
copyraw
User		Mon	Tue	Wed	Thu	Fri	Sat	Sun	Total
--------------- ------- ------- ------- ------- ------- ------- ------- ---------
Me		09:00	07:30	08:00	07:00	06:00	00:00	02:00   36:00
Myself		07:30	07:30	07:30	07:30	07:00	00:00	00:00   37:00
I		03:45	03:30	03:00	03:30	00:00	00:00	00:00   23:00
  1.  User        Mon    Tue    Wed    Thu    Fri    Sat    Sun    Total 
  2.  --------------- ------- ------- ------- ------- ------- ------- ------- --------- 
  3.  Me        09:00    07:30    08:00    07:00    06:00    00:00    02:00   36:00 
  4.  Myself        07:30    07:30    07:30    07:30    07:00    00:00    00:00   37:00 
  5.  I        03:45    03:30    03:00    03:30    00:00    00:00    00:00   23:00 

Category: MySQL :: Article: 416

What?
Week Ending Date has been requested a lot more frequently now. It's an odd one but the example below shows how to do this for when the week ends on Friday. Assuming it starts on the previous Saturday.

Why?
Problems with MySQL weeks always starting on Sunday means this isn't very useful. I have another system which starts on Monday and ends on the following Sunday. The solution below is for the opposite, where the week starts on the previous Saturday and ends on the last working day of the week.

The Solution

Why?
We have a stored procedure which contains two select queries. The first query will retrieve a student ID number where the input parameter is the student's username. The second query will return data using the student ID number found in the first query. The stored procedure compiles successfully and without any warnings.

So What?
The problem is that if the student does not have a username but has an ID number, then the first query returns NO ROWS and then the second query errors and the whole stored procedure fails. The same problem happens when using these as a subquery which has an empty result set.

Furthermore
I am using this stored procedure in a SQL Server Reporting Services (SSRS) environment and don't want to return any rows if there are no matches (not a blank row either) as one of my reports uses a row counter to display a "No data found" message when no rows are returned. This does not affect the above problem as the stored procedure simply fails and discontinues processing the overall report.

How?
Browsing the web for a solution, most suggest using NVL() but this only replaces a NULL value with a string of your choice; and even if you NVL all returned fields, no rows are returned, and not a row of NULL/blank/empty values. Sounds confusing? That's just me, the answer was using two NVL functions which allowed the stored procedure to return no rows without erroring (ie. where rownum/rowcount = 0).

Previously
For a basic Oracle function, visit my article Basic Oracle Function Structure. For a more advanced version which uses cursors to work with SSRS, see my article Oracle Stored Procedures in SSRS.

What?
I asked someone for a simple, easy and basic Oracle stored procedure as my MySQL and T-SQL stored procedures work slightly differently and are easier to pull off. After much umming and aah-ing, I have written this article as a note for me to demo a working stored procedure and how to use cursors. I'm told I need to use this for SQL Server Reporting Services (SSRS) which is why I'm going down this route. For an even simpler stored procedure, I'd imagine you just get rid of the cursor.

Why
A standalone query is faster in the eyes of SSRS, however within SSRS we don't have the capability to run standard PL/SQL commands... unless they're run from within a stored procedure. Our aim is to run a small query first to return the ID of the student, and then to use this number in a second query which we're hoping will be quicker than a straightforward table join.


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

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

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