from (select m.id, case when rn = 1 then m.email end email1,
case when rn = 2 then m.email end email2,
case when rn = 3 then m.email end email3
from m) n group by id
By using Pivot, it will cut the CASE and MAX, just like this:
select * from m pivot (max(email) for rn in(1, 2, 3))
with l as
(select '333' id, 'email1' email, 1 xsequence
from dual
union
select '333' id, 'email2' email, 2 xsequence
from dual
union
select '333' id, 'email3' email, 3 xsequence
from dual),
m as
(select distinct l.id, l.email,
rank() over(partition by l.id order by l.xsequence) rn
from l)
select * from m pivot (max(email) for rn in(1, 2, 3))
If you want to select them for other joined query, you could set the column alias to get rid of the number(rn), since you can not pick/select the column started with numbers.
select * from m
pivot (max(email) for rn in(1 as e1, 2 as e2, 3 as e3))
I made a video to show the process, thanks for watching!
You can online search for Pivot documentation and examples.
"Oracle 11g introduced the new PIVOT clause that allows you to write cross-tabulation queries which transpose rows into columns, aggregating data in the process of the transposing. As a result, the output of a pivot operation returns more columns and fewer rows than the starting data set.
In this syntax, following the PIVOT keyword are three clauses:
pivot_clause specifies the column(s) that you want to aggregate. The pivot_clause performs an implicitly GROUP BY based on all columns which are not specified in the clause, along with values provided by the pivot_in_clause.
pivot_for_clause specifies the column that you want to group or pivot.
pivot_in_clause defines a filter for column(s) in the pivot_for_clause. The aggregation for each value in the pivot_in_clause will be rotated into a separate column."
Just found Windows 10’s Your Phone app on my $300 ThinkPad Yoga book, which I really like. The App links your android phone with PC. Of course it works with iPhone but it's said it works best for Android users, letting you text from your PC, sync your notifications, and wirelessly transfer photos back and forth, also screen your phone to TV.
OK, Sounds pretty good, usually I USB connect my Huawei Pro20 with PC to transfer files, specially pictures and songs. So with Phone app we can do those wirelessly, WIRELESSLY, yes, nowadays it's a very popular thing.
Setup Your Phone is extremely easy, just click Windows Logo on your PC and scroll down the bottom, click it and follow along, I made a silly video, you can watch if you like to support my starting YouTube Channel.
Also a quick tip about the Windows OneDrive, OneDrive is Microsoft's cloud storage for consumers, and it is built into Windows 10. For free, it comes with 15 GB of storage, and there are a couple of paid tiers to increase that storage. 15GB is plenty for normal people:), but make sure you are aware of the OneDrive sync options.
The OneDrive cloud icon in the Windows taskbar notification area, right click it will show some options/tasks you can do. I also added a little talk about this on the below clip.
Well, let's leave our phones somewhere and focus on the PC:))
I have been having a NetEase free email account (something@163.com) for long time and mainly use for subscription registration (less important than Hotmail/Gmail:) and usually link the account with Microsoft Windows default Inbox.
Then suddenly not working and complaining about the password not correct and I tried quite time and failed. After search on line and found the problem so I would like to share it. (Long Live Internet Search!)
Basically the problem is that the password is not the pass loign to NetEase email account and it's the one you generate form NetEase account.
1. login to NetEase account and Setting -> POP3/SMTP/IMAP
2. Make sure the receiving/sending servie is open
开启服务：
服务器地址：
POP3服务器: pop.163.com
SMTP服务器: smtp.163.com
IMAP服务器: imap.163.com
安全支持：
POP3/SMTP/IMAP服务全部支持SSL连接
3. Go to授权密码管理, and get the passcode and use this code fro your Inbox setup
4. Go to Windows Inbox Accounts -> Add account
Using the bottom two options: Other account POP, IMAP or Advance setup
It's easy just to use Other account POP, IMAP, click it and enter the information
From time to time, collages ask me about doing max consecutive count, let's say we want to know a donor most continuous donation year total, we used to do the hard way* until we tried using the match_recognize ().
Courtesy to https://oracle-base.com/articles/12c/pattern-matching-in-oracle-database-12cr1, my favorite Oracle knowledge website.
"Introduced in Oracle 8i, Analytic Functions, also known as windowing functions, allow developers to perform tasks in SQL that were previously confined to procedural languages. Oracle 12c has added the MATCH_RECOGNIZE clause into the analytic function syntax to make pattern matching from SQL simpler. This article gives a flavour of what can be done using the MATCH_RECOGNIZE clause, but you will need to refer to the documentation to understand the true level of complexity possible."
So here is the new baby:))
select max(runs)
from
(select *
from (select distinct g.gift_donor_id, g.gift_year_of_giving from gift g where g.gift_donor_id = '00000xxxxx')
match_recognize (
order by gift_year_of_giving
measures
first(gift_year_of_giving) as first_run,
last(gift_year_of_giving) as last_run,
count(*) as runs,
match_number() as grp
pattern ( strt consecutive* )
define
consecutive as gift_year_of_giving = ( prev (gift_year_of_giving ) + 1 )
))
From Oracle:
What does this query do? The following explains each line in the MATCH_RECOGNIZE clause:
PARTITIONBY divides the data from the Ticker table into logical groups where each group contains one stock symbol.
ORDERBY orders the data within each logical group by tstamp.
MEASURES defines three measures: the timestamp at the beginning of a V-shape (start_tstamp), the timestamp at the bottom of a V-shape (bottom_tstamp), and the timestamp at the end of the a V-shape (end_tstamp). The bottom_tstamp and end_tstamp measures use the LAST() function to ensure that the values retrieved are the final value of the timestamp within each pattern match.
ONEROWPERMATCH means that for every pattern match found, there will be one row of output.
AFTERMATCHSKIPTOLASTUP means that whenever you find a match you restart your search at the row that is the last row of the UP pattern variable. A pattern variable is a variable used in a MATCH_RECOGNIZE statement, and is defined in the DEFINE clause.
PATTERN (STRT DOWN+ UP+) says that the pattern you are searching for has three pattern variables: STRT, DOWN, and UP. The plus sign (+) after DOWN and UP means that at least one row must be mapped to each of them. The pattern defines a regular expression, which is a highly expressive way to search for patterns.
DEFINE gives us the conditions that must be met for a row to map to your row pattern variables STRT, DOWN, and UP. Because there is no condition for STRT, any row can be mapped to STRT. Why have a pattern variable with no condition? You use it as a starting point for testing for matches. Both DOWN and UP take advantage of the PREV() function, which lets them compare the price in the current row to the price in the prior row. DOWN is matched when a row has a lower price than the row that preceded it, so it defines the downward (left) leg of our V-shape. A row can be mapped to UP if the row has a higher price than the row that preceded it.
*Hard way:):
with years as
(
select distinct g.id, g.giving_year
from gift, allocation a
where g.gift_associated_allocation = a.allocation_code and a.athletics_ind = 'Y'--upper(a.short_name) like '%ATHLETICS%'
order by g.id, g.gift_year
)
select id, ayear from_yr, byear to_year, yearcon
from
(
select
a.id,
a.year ayear,
b.year byear,
(b.year - a.year)+1 yearcon,
dense_rank() over (partition by a.id order by (b.year - a.year) desc) rank
from
years a
join years b on a.id = b.id and b.year > a.year
where
b.year - a.year =
(select count(*)-1
from years a1
where a.id = a1.id
and a1.year between a.year and b.year)
)
where rank = 1 and yearcon>=10
order by 4 desc
Personally I have great feeling about Google technology until today!:((
As an outdoor person, I love to blog my travel, hiking and often post a lot of images.
Due to some unpleasant reason I need to remove some images from my blog, I thought all my uploaded images should be easily managed by me, so I removed some images form my blog and google photo, but the images still exist with the link like:
https://1.bp.blogspot.com/-7T5MQDsXSVE/XVbUdA38OvI/AAAAAAAAR7I/Qr4UsMF6FBEXlKtmQIgtMZbFDMGLuaj1gCLcBGAs/s1600/IMG_20190731_123441.jpg
So I spent about two hours to figure out how to remove the images, then I found out that you have to request removal of images such as in:
https://www.google.com/webmasters/tools/removals?pli=1
Well, I have to say that sucks! Just want to mark it for any future use or anyone have same experience with me.
After an image is removed from a website, it may still appear in search results for a little while.
To remove these images from search results, follow these steps:
Search on images.google.com for the image you want to find.
Select the image link by right clicking on the image thumbnail and choosing Copy link address. Note: Different browsers may have different names for copying link location.
In the box next to "Request removal," paste the URL.
Click Request removal.
If you see the message "We think the image or web page you're trying to remove hasn't been removed by the site owner," follow the steps on the screen to give us more information.
If you see the message "This content is no longer live on the website," click Request removal.
Why Google doesn’t remove most images
Most images that show up in Google’s search results are from websites that aren’t owned by Google. Since we aren’t the owners of these sites, we can’t remove the images from the web.
Even if we delete the image from Google’s search results, the image still exists and can be found on other search engines, or if people visit the URL directly.
About 17 years ago, my 1st IT job is maintaining a sql load program for hospital medical records at Alberta Health. Today we have an imodule routine to fetch the records and load into database.
My colleague had some trouble with the data with embedded line breaks before ending the recordes, and ask me if I could help. I could not help to jump on my "first love" :)) special feeling about sqlloader:))
if you have a special character at the end of each line: the stream record format
Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). This allows us to create an input data file that has some special character at the end of each line. The newline is no longer special.
if you don't have a special character at the end of each line: the CONTINUEIF statement.
Here is the sample data for us:
"100045678","address is
super street
18999 Edmonton Alberta
T6R4Y3" ............................................
"2","the degree is MBA
UT
1998" ............................................
The each field inside data looks enclosed with double quotes "xxx", so we could simple add the CONTINUEIF to control the end of records.
From Oracle:
Using CONTINUEIF to Assemble Logical Records
Use CONTINUEIF if the number of physical records to be combined varies. The CONTINUEIF clause is followed by a condition that is evaluated for each physical record, as it is read. For example, two records might be combined if a pound sign (#) were in byte position 80 of the first record. If any other character were there, the second record would not be added to the first.
The full syntax for CONTINUEIF adds even more flexibility:
Table 8-2 describes the parameters for the CONTINUEIF clause.
Therefore we ask the loader to continue the record if see a break line not followed by a double quote, which means it's not the end of records!
LOAD DATA
INFILE 'giftdata.dat'
CONTINUEIF LAST != '"'
INTO TABLE conversion
APPEND
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(
"ID",
"DESCRIPTION" CHAR(500)
)
Playing website is my hobby since year 2000 when we set up an IT company. It's interesting to try to stay current while my daily job is mainly database stuff:). So I keep revamping my website from WordPress, Joomla, now Drupal, just to learning some new stuff.
Imaging 2000 until now, how the technology changed. It only takes 10 minutes to have a good, professional looking website and so many free themes you could choose. I had some popular Drupal themes, but I had problem to make the rotating banners working....
Documentation! Documentation! I had to be patient to read carefully on the documentation. Sometimes I was just lazy to read :(((. I missed whole bunch modules and now I would like to share those steps from Drupal site(https://www.drupal.org/node/1786134).
"Rotating Banners requires two other modules and a pair of scripts to function.
The dependent modules are Media, http://drupal.org/project/media, and Styles, http://drupal.org/project/styles.
The two server scripts are jquery.cycle.all.min.js and jquery.easing.1.3.js.
The first script can be downloaded here: http://jquery.malsup.com/cycle/download.html.
Once you download it, rename it to jquery.cycle.js.
The second can be downloaded here: http://gsgd.co.uk/sandbox/jquery/easing/
Once you download it, rename it to jquery.easing.js.
Install rotating Banners through the Administration/Modules page.
Then copy both scripts to the following directory: …/sites/all/modules/rotating_banner/includes/.
This will create an interface in the Admin/Structure/Blocks menu."
Basically we need media, style, and 2 jQuery scripts, and media needs Ctools module! I totally missed Requirement section! I am thinking I am close to retirement:(
The weird thing I found after installing Rotating Banners/Ctools, some modules got unchecked such as Toolbar, Locale etc., so I had to enable them again.
I really like the listagg function, but it can not be used with distinct.
Sometimes I want remove the duplicate values return form listagg function and I don't like to use subquery, etc.
Finally I found a great solution using regular expression! really cool and I thought maybe useful to someone:)) select'1223-1223-1223-1345' t1, rtrim( REGEXP_REPLACE('1223-1223-1223-1345', '([^-]*)(-\1)+($|-)', '\1\3'), '-') t2 from dual;
1223-1223-1223-1345 will be 1223-1345, but be sure the list is order by the item!
Here is a example for me:
select regexp_replace( (listagg(a.short_name, ',' ) within group (order by a.short_name)), '([^,]*)(,\1)+($|,)','\1\3')
from gift g, allocation a where g.gift_donor_id = l.gift_donor_id and g.gift_associated_allocation = a.allocation_code and a.alloc_school = 'LS') distinct_allocs
Explanation is following: Courtesy to Srinivasan - bean farmer
REGEXP_REPLACE extends the functionality of the REPLACE function by letting you search a string for a regular expression pattern. By default, the function returns source_char with every occurrence of the regular expression pattern replaced with replace_string. The string returned is in the same character set as source_char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB.
This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer to Appendix C, "Oracle Regular Expression Support".
source_char is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB or NCLOB.
pattern is the regular expression. It is usually a text literal and can be of any of the datatypes CHAR, VARCHAR2, NCHAR, or NVARCHAR2. It can contain up to 512 bytes. If the datatype of pattern is different from the datatype of source_char, Oracle Database converts pattern to the datatype of source_char. For a listing of the operators you can specify in pattern, please refer to Appendix C, "Oracle Regular Expression Support".
replace_string can be of any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. If replace_string is a CLOB or NCLOB, then Oracle truncates replace_string to 32K. The replace_string can contain up to 500 backreferences to subexpressions in the form \n, where n is a number from 1 to 9. If n is the backslash character in replace_string, then you must precede it with the escape character (\\). For more information on backreference expressions, please refer to the notes to "Oracle Regular Expression Support", Table C-1.
position is a positive integer indicating the character of source_char where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character of source_char.
occurrence is a nonnegative integer indicating the occurrence of the replace operation:
If you specify 0, then Oracle replaces all occurrences of the match.
If you specify a positive integer n, then Oracle replaces the nth occurrence.
match_parameter is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect on replace_string. You can specify one or more of the following values for match_parameter:
'i' specifies case-insensitive matching.
'c' specifies case-sensitive matching.
'n' allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character.
'm' treats the source string as multiple lines. Oracle interprets ^ and $ as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line.
'x' ignores whitespace characters. By default, whitespace characters match themselves.
If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify 'ic', then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.
If you omit match_parameter, then:
The default case sensitivity is determined by the value of the NLS_SORT parameter.
A period (.) does not match the newline character.
About 20 years ago, my 1st flight with Air Canada, I still remember that's Dec 8, a very cold winter day, ground covered with all snow. I arrived in Edmonton, but I lost one of my bag with bottles of expensive Chinese Mao Tai if you know some liquor from China:). That time my spoken English and listening are so limited, but I had to communicate with Air Canada to get my bag back. After a few weeks my bag was dragged to my apartment and finally I got it back even though the Mao Tai was opened, I guess it was checked by some officer:(
1996 I went to my home town through Shanghai with Air Canada, my home town is about 6 hours bus from Shanghai at that time without highway yet. Again I lost one new luggage, which I just bought for this trip. I left my home town contact information and went home; a few days later my new luggage came dirty on crowded bus, mot much complained, I felt fortune to get it back in just a few days.
2005 I visited China with whole family by Air Canada and again we missed one luggage on the way back to Canada! After a few month registering case on Air Canada website and talking to agents, I almost had no hope to get my luggage back (only compensation by Air Canada is $100!). Well, it was finally located and sent it back to me. When I opened my out-of-shape luggage, most gift/stuff was squeezed. Well, what can I say, it came back after a few month travelling around world, I wish I could did this free travelling!
Until now such losing luggage experience gave me a big stress every time I travel with checked luggage, I was so worried and always triple check to make sure I keep those luggage receipts in case any luggage gets lost. Recently I travel to China a lot to visit my parents and I believe Air Canada improved the process about the checked luggage, but unfortunately I encounter some new headache about cancelled and delayed air flights.
This Summer (2013) we booked tickets with Air Canada and we were stuck at Vancouver! That evening all passenger were on board and airplane is driving out and ready to take off, after 30 minutes waiting Captain told us there were some air-conditioning issue to fix before taking off. So we were patiently waiting and then were told that airplane had to go back for checking, shortly after reaching the dock we were notified to get off and wait inside. Soon it announced that the flight cancelled.
What a chaos and we had some miscommunication and finally whole family ran to some hotel outside airport in the Vancouver city at 2am in the morning and had to get back airport for re-check-in for another airplane departing at next day 9am. A lot of passenger were mad, especially those with connection flights, one of lady was so upset and ask all of us refuse to aboard, but nobody listened to her, since everyone was so tired and wanted to leave as soon as we could and got over with this. I was pretty sad to see that lady was screaming and kicking the coupon paper which Air Canada offers for this chaos, it's 15% off next purchase, come on, I don't think that lady will take Air Canada again:(
We were glad to take off next day and arrived safely in Beijing even it's was 12 hours delay, I have sympathy for those who missed next connecting flight, train or bus. When coming back the flight was delay again! We were late at Vancouver, and could not catch the booked flight to Edmonton. This time we were experienced with no panic and we were staying in the Fairmount hotel inside Vancouver airport, not bad, my daughters were excited to stay this nice hotel for a few hours.
That's my stories with Air Canada, I just want to say Air Canada, how are you doing? I wish you getting better and I heard news doing well. Do I ride with Air Canada again?
Yes, I think so, there are many reasons, just to speak a couple :))
1. I am a Canadian, hohoho
2. I just know a high school classmate, working at Air Canada Montreal office, Mr Yu, hahaha
......
Do I choose Air Canada if there is other option? I don't know honestly....
A while ago set up a group forum using phpbb, overall phpbb3 is not bad even though it lacks of some popular features and you need some module for some popular features.
As more and more posts on our forum, I thought it might be a good idea to mark those posts with over a certain number of views. I checked and phpbb3 comes with popular topic based on posts, I want to have popular posts based on views.
Thanks and hates to http://startrekguide.com/mods, I found that some simple update of a few php files could get what I wanted:)) since I am so lazy and web stuff is just one of my hobbies. Why I hate? After I followed the instruction to update those files, I found the crazy warning about "Cannot modify header information - headers already sent by (output started t /includes/acp/acp_board.php:1)"!
What the heck? I changed file back (didn't save a copy since I was thinking those changes are so simple:(). Still same error pops up, double checked the space, commas, etc, still same error! It gets me frustrated, so I decided to change configure php file (even it says don't touch this file:( ) to uncomment the debug mode line to see what's really going on. Damn, even worse, whole phpbb forum is not running!
Thanks to the Internet search, sounds like I was having some encoding type issue, I edited those files in notepad to make sure same as UTF-8, but still same thing! Searched again, I was educated that I need to save as UTF-8 without BOM, the notepad doesn't have this option so called BOM:(
After downloaded notepad++ and edit the files and applied the changes according to http://startrekguide.com/mods, now I havd to say thanks. After that everything was working properly:)
I thought I should write this out and again LONG LIVE INTERNET SEARCH! (mostly google or baidu?:))
Byte order mark
From Wikipedia, the free encyclopedia
The byte order mark (BOM) is a Unicode character used to signal the endianness (byte order) of a text file or stream. It is encoded at U+FEFFbyte order mark (BOM). BOM use is optional, and, if used, should appear at the start of the text stream. Beyond its specific use as a byte-order indicator, the BOM character may also indicate which of the several Unicode representations the text is encoded in.[1]
Having a few packages to send email around, often I find exception error is hard to trace since I was just using SQLerrm function in exception, which only shows the error without telling the source.
1st I thought I could look for ways to find where exactly the error occurs, such as showing function/procedure name, then after a little online search, I found dbms_utility package has a nice function: FORMAT_ERROR_BACKTRACE, which displays the error line number, better than function/procedure name!
This procedure displays the call stack at the point where an exception was raised, even if the procedure is called from an exception handler in an outer scope. The output is similar to the output of theSQLERRMfunction, but not subject to the same size limitation.
"In a real-world application, the error backtrace could be very long. Generally, debuggers and support people don't really want to have to deal with the entire stack; they are mostly going to be interested in that top-most entry. The developer of the application might even like to display that critical information to the users so that they can immediately and accurately report the problem to the support staff. In this case, it is necessary to parse the backtrace string and retrieve just the top-most entry." - by Steven Feuerstein
We had the Cisco Linksys router at home for quite time, maybe 2,3 years. Since we moved into the current house, the upstairs wireless singal is kind of weak, especaily for Dell/HP laptops. So I decided buy a new router.
After some homework, I bought WestDigital Mynet N750, was thinking about N900 with DLNA support and 7 ports, but the comments on N750 looks better than N900. Anyway, I may check out N900 later some time, so far N750 singal is pretty good.
With 2 routers I have now I decided to setup 2 routers with 2 ssids, one is for N, one is for mixed B/G since I am still using 1st Gen iPod (ancient, isn't it?) and a HP mini laptop (very slow). Even though my network skill is very rusty, but I thought it should be easy to set up.
Pretty easy to set up and the old iPod is connected to the 2nd router, obtaining right IP address, just could not get into Internet complainting about DNS problem, then set the DNS to the 1st router default gateway. Then it is all working:). This reminds me of old days when I was taking Network Specialist courses.
Some key points I could think of:
Any router had external and internal IP address, internal IP is easy and most time is just DHCP, external IP is what ever the IP talking outside the own network, for 2nd router the external IP is the IP within 1st router range, and for 1st router is the IP with my Internet Service Provider network range.
DNS is just like chain pass from internal to external, from router 2 go to router 1, then router to ISP DNS server to find real route to the web pages you want to see.
And also dont forget the 2 routers are in different subnets (1.1 and 0.1, you can do whatever number you want, just keep 192.168 or if you prefer the other private network start with 10 and 172).
Here is the diagram of my home network of 2 routers with 2 ssids:)
