How to give a list of value in a where clause in sql (on oracle) ?

By on July 24, 2018

I have a list of guild but I don’t want to call that sql for each guild. (check previous post for the structure)
This one is a little easier, you just have to use the IN (PARAMETER, OPERAND, KEYWORD) instead of equal (=) and you give your list of guild you want in it.

select * where
from USER_TABLE u inner join JOB_ASSIGN assign on u.USER_ID = assign.USER_ID, GUILD_TABLE guild where assign.guildId = guild.id AND assign.guildid = ?

If you want to hand pick the number of guild you can use the KEYWORD IN:
select * where
from USER_TABLE u inner join JOB_ASSIGN assign on u.USER_ID = assign.USER_ID, GUILD_TABLE guild where assign.guildId = guild.id AND assign.guildid IN (12,13,14)

 

Related : What to do if I want give a list of value in my where clause parameters?

Posted in | Tagged , , , | Leave a response

April 2018 Book: Unshakeable

By on April 29, 2018

April was a busy month, I’ve only managed to finish one book. I’ve finished Unsheakable by Tony Robin, a follow-up of is other book Money: Master the game that is shorter and can be read independently.

Unshakeable

A lot of what is said in Money: master the game is re-told in unshakable, it makes me feel that Robin wanted to a shorter book (225 pages vs the 600+ pages for Money: master the game) to reach more people and promote is partnership with Creative Planning which was left out of the other book.

Its start off making you aware of how bad the financial industry is treating you and how to do it on your own or find a good advisor.

First he goes how bad are mutual fund which are outperform by the index most of the time after including the fee you pay (95% of the time according to the book).

He then goes to show how most financial advisor don’t have your interest at heart because they are actually broker and will push product that may give them bigger commission.

Other topic are covered like 401K, how to prepare and take advantage of market correction and bear market, tax efficiency and more.

The last section is about real wealth and how it is emotional, psychological and spiritual. How living a fulfilling life is more important than just success.

This section in my opinion what makes this book and money (it has a similar chapter) stand out compare to most other financial help book out there.

My final take is that if you’re interested to read about your finance this book is a good start, it contains the message of it previous book and is a much lighter read. It will get you started, I found so chapter tedious at the beginning even though he made a good effort of keeping the book interesting.

Posted in , | Tagged , , , , | Leave a response

Books Q1 2018: Sapien, Digital Fortress, Essentialism & Ready Player One

By on April 9, 2018

It’s been three month, so I decided to give a little update on the book of 2018, this is a follow up of my list of book of 2017.

Sapien

Recommended by Bill Gate and many podcast I’ve listened too, Sapien is a brief (a synopsis if you wish) of the history of mankind.
He deconstruct different revolution that the Sapien had and he touch on the drawback of the Sapien expansion. From pre-historic time where the Sapien clashed with the neanderthal, where they drove extinct the mega-fauna of every ecological system they entered, to modern time.
I really liked that he approached some lesser know fact, like that hunter gathered had it better in term of life expectancy, quality of life, health that human in the agricultural revolution.
I love those kinds of read even though it’s a long one.

 

Digital Fortress

My father is a big Dan Brown fan and lend me some of is book, I started with this one because it seems more in my line of work (computer, cryptography) than the others (The Da Vinci Code & other).
Writen in 1998, what caught my attention was some of event in the book happens recently.
One of the plot point of the book is that a character will release an undecryptable algorithm to the world if the NSA don’t reveal that they have a super computer that read everybody mail and can decrypt all of the current cryptographic algorithm.
Sound like what Snowden did, letting know that the NSA was spying on their own citizen and their allied.

Note to self: I should write a goodread review on this one.
https://www.goodreads.com/book/show/11125.Digital_Fortress

Essentialism

Essentialism.jpg

I’ve bought the book from Charlie Hoehn recommendation. When I’ve backed his kickstarter project and he gave the backers the opportunity to tell him what they were struggling with and try to give them advice.
so here my mail

Hi Charlie,

I think my biggest problem right now is not being afraid to say no.
I say yes to everything I’m mildly interested and then I can’t keep up, it’s especialy hard saying no to my spouse and my kids.

Thanks and I can’t wait to get my copy of the book,
Chris

Is respond was swift and short: Buy the book Essentialism and keep reading it.
After reading it, I understand why he said that, the second I’ve finished the book I had to read it again.

What is it about?
It’s about focusing on your priorities instead of letting yourself get sidetracked by the bombardment of secondary demands dictating our everyday lives.
The secret is to concentrate your efforts on your immediate task and prevent others to distract you away from the job at hand.
The author states in so many words that if you don’t make the choice, others will make it for you and that someone else’s choice may mean as an example that you won’t get that report in on time.

Ready player one

Ready player one.jpg

A good friend of mine recommended this book last year, I already bought it, but didn’t get around reading it. With the movie adaptation coming out this Easter, I had to read it. In the year 2045, global oil reserve are depleted and for the many the only way to escape this harsh world is to dive in the OASIS, a virtual reality/MMO where you can play, go to school, conduct business, etc.
The creator of the OASIS, just died recently and added a easter egg in it that will give control to the OASIS.
We follow the story of the young Wade, trying to find that legendary artifact along with some friend against an evil global corporation.
If you’re a child of the 80s-90s or into video game in general you will love this one. It’s full of reference to both, pop culture and many more easter egg.

Have any of you read these book, let me know what you liked/disliked about them or learned any special.

Posted in , , | Tagged , , , , , , , | Leave a response

How to turn a list from a select into a column of another select (Oracle) ?

By on April 2, 2018

A old process we had was fetching a list from a query for each player one by one, and adding it in a excel file with other data needed on the player.  

Seeing that process was slow, we decided to turn it into a query.

If we take a close look at the model we have 4 tables, 3 that will be used in the query.

The player table (PLAYER_TABLE), the guild table (GUILD_TABLE), a job table (JOB_TABLE) and a table to link the 3 tables together call job assignation (JOB_ASSIGN). 

(note this is a simple representation, the original had around 30+ fields for each of the equivalent of the player and guild table)

What we want is to display all the info of the player and all the job the player have for each guild in one row.

So one row will look like

user_id server classname lv job list
543123 1 ROGUE 23
diplomat, carpenter, florist
34533 1 MAGE 66
alchemist, diplomat
234334 2 MAGE 34 alchemist, ring maker
776565 3 WARRIOR 5 brewer

All the job are in varchar so what we need it’s to put them in a string separated with a comma instead of having a row for each of them.

Selecting a value from another select

This is a start but the value is a string we need to make a select of the job table and get the list.

this would work for the total of job for the player.

but first we need to know how to select in a select (sql select in a select)

From Stackoverflow example:

SELECT  TypesAndBread.Type, TypesAndBread.TBName,
        (
        SELECT  Count(Sandwiches.[SandwichID]) As SandwichCount
        FROM    Sandwiches
        WHERE   (Type = 'Sandwich Type' AND Sandwiches.Type = TypesAndBread.TBName)
                OR (Type = 'Bread' AND Sandwiches.Bread = TypesAndBread.TBName)
        ) As SandwichCount
FROM    TypesAndBread

This work fine if the select return only on value per entry (player) if there multiple entry and we want to put them in a single line we need something else.

Displaying a list of result (varchar) on a single line.

If we want to display a series of string (varchar) together we need to concatenate them together.

We could try to do something with the concat method, but since Oracle 11g R2 there a another method to concatenate a sql result into a string oracle with a delimiter called LISTAGG. 

SELECT job.JOB_ASSIGN_ID,

   LISTAGG( job.JOB_NAME, ',') WITHIN GROUP (ORDER BY job.JOB_ASSIGN_ID)

from JOB_TABLE job  

GROUP BY job.JOB_ASSIGN_ID;

Note: db exception on GROUP BY if your select have more than one row for job.JOB_ASSIGN_ID.

Then you can add this select has a column on the previous select where we calculated the number of job. 

select u.USER_ID, u.SERVER, u.CLASSNAME, u.LV, 

(SELECT LISTAGG( job.JOB_NAME, ',') WITHIN GROUP (ORDER BY  assign.USER_ID) As userJobList

from JOB_TABLE job join JOB_ASSIGN assign on job.JOB_ASSIGN_ID = assign.JOB_ASSIGN_ID

where assign.USER_ID= u.USER_ID

GROUP BY assign.USER_ID) As userJobList 

from USER_TABLE u inner join JOB_ASSIGN assign on u.USER_ID = assign.USER_ID where assign.guildid = 12 

Note that you can put compare on column inside the inner select, that how I've separated the value per user.

Posted in | Tagged , , , | Leave a response

The 20+ books I’ve read in 2017

By on January 29, 2018

In January 2017 I’ve decided I would read 16 books, my goal is to hit 100 books read before I turn 40. I’ve already read 34 in 2013-2014, 26 in 2015, 12 in 2016, so I’m only 8 shorts of my goal, so I’m going to try to read 12 book this year since I’m going to have 40 this year. The rules are simple, more that’s 20-30 pages (my average is around 300-400 pages), audio books are fair game, but all book must be book I’ve never read.

1-Deep work

Having problems focusing at home and work, this book was a great way to work in a more meaningful and focused way. I’ve basically quit Twitter and facebook this year because of it. This helped the avoid distraction a bit, but I have kids and has you might know they are distractions machine. Hopefully I will manage to keep the distraction down, despite having them around.

2-Whole brain child

Interesting take on what helps a kid develops is brain in the early years. One of my big takeaway is that the amount of word that a family speak around the child have a huge impact on the kids development and that TV doesn’t count, it’s the interaction with people and the word spoken that help the child.

3-The subtle art of not giving a f*ck

A great book about life without being preachy. Just writing about it makes me realized I should reread my note about it.

4-Fahrenheit 451 (Fr)

Some friends at work kind of started a book club, they’ve already read 1984 and Brave new world so I suggested this book, which is kind of similar to the two others. It’s a classic

5-Nobody want to read your shit

Another gem by Steven Pressfield, focus on writing, but it advice can probably be applied to most creative endeavor.

6-Dale Carnegie – the 5 essential people skill (audio)

Not sure where to find the book, it a series on how to speak to your employees and colleagues.

7-Instinct de survie (French) ( or The Escapist if you want the english version)

Written by GABRIEL FILIPPI and BRETT POPPLEWELL (English version). The Escapist tells the life story of veteran climber Gabriel Filippi and his struggle with by survivor’s guilt and post-traumatic stress disorder. Probably my favorite book of the year, it becomes a page turner the second he start telling stories about is climb. Side note, I actually bought the book directly from the author at a Banff Film Festival viewing.

8-La mort d’Ivan Ilitch (Fr)

Another book from our book club at work, a classic written by the legendary Russian writer Tolstoy. While this story is really shorter that is magnum opus War & Peace, it’s still a great book. I could relate to the character, despite the story taking place 100 years in the past.

9-This will make you smarter (audio)

A create book on critical thinking, scientific method and how to address people stuck in their ways (climate change denier for instance).

10-How to talk so kids will listen…

A new version of a parenting book written in the 80’s with a new section by the daughter of the author where she talks about where it’s got her, and how she is now raising her kid.

11-Money master the game (Canadian edition. French translation)

Tony Robbin book on money, great read, a lot of principle found in the intelligent investor. Interview with investors at the top of their games. I usually try to read the original, but I got this one in French from the library.

12-Your first dollars (ebook)

Ed Dale one of (or the) authors of “the 30 days challenge,” which taught how to make a niche site in 30 days. The book seems to take elements of “the challenge” but mostly focus on the psychology of doing things. A lot of gems even if your not planning to start a niche site or business. It’s short too, less than a hundred pages if I recall.

13-The Daily stoic

The Daily Stoic offers 366 days of Stoic insights and exercises, I’ve usually read two page days every day (one in the morning and one at night)

14-Peaceful parent, happy kids

A parenting book, I might need to re-read it because we have problem getting my daughter to go to bed before 8pm

15-Barking up the wrong tree

I’m having a hard time reviewing this one in one sentence. It’s kind of a self-help book, but instead of preaching, the authors take preconception we have about success and smash them into pieces with fact and statistics.

He has a blog to that I was unaware of definitely worth reading.

16-Le horla (Fr) (Maupassant)

A classic from French literature that was selected in our book club at work.

17-Your move (ebook)

Ramit new ebook about building an online business, a lot of gem about providing value and business in our day and age.

18-Celtes et Gaulois (small 50 pages) (French)

Small Book about various aspect of the Gallic life (deity, cities, weapon, family structure and more)

19-Vercingétorix (Camille Julian) (French)

Written in 1909 by Camille Julian, this book tell the story of the leader that united the Gallic Tribes to rise against Roman occupation after Caesar thought he had Gaul in is hand.

20-La véritable histoire de Marc-Aurèle (The real story of Marcus-Aurelius) (French)

A quick book (around 160 pages) about the life of Marcus-Aurelius through letters sent by his mentor and other correspondent and though is diary call nowadays “Meditation”.

Read but not completely (skim or to short to count)

Google Analytic (3rd edition)
S.Q.P.R. (a Roman history book starting from Cicero)
Play for a living (collection of quote and beautiful artwork, was from Charlies Hoehn Kickstarter)

Hope there some book you want to read in the list, let me know what book you’ve read in 2017 and want do you want to read in 2018.

Posted in , | Tagged , , | Leave a response

SVN moving files lose history

By on October 19, 2014

My coworker was moving files around in our SVN repo and we end up losing the history of the file. It seem it was because of our IDE (eclipse svn at the time), after some searching we manage to find a way to do it by using the svn command rename & move.

Doing everything by command line can be a nightmare if you are looking for a external tool Tortoise SVN should do the trick, it has the rename command and you can use Tortoise to repair the move operation.

The following will tell SVN that the new file is actually the renamed old one:

  1. Open the “Check for Modifications” Window.
  2. Mark both the old file name (listed as missing) and the new one (listed as unversioned)
  3. Right click this selection and chose “Repair Move”.

If you already lost the history on a file you really need, you can always resurrect the old file with svn copy. You “svn copy” the old version into its old place, merge the new file’s history into it then”svn move’’ this file to the new file name.

More info type: 

svn help rename

in the command prompt.

Posted in | Tagged , | Leave a response

List Return a java.lang.UnsupportedOperationException when adding or removing a element

By on October 12, 2014

While trying to remove a element after creating a List from a array using the Array.asList(array[]) method I’ve stumbled on that error.

java.lang.UnsupportedOperationException
at java.util.AbstractList.add(AbstractList.java:131)
at java.util.AbstractList.add(AbstractList.java:91)

Then I remember this, list created by the Arrays.asList method are immutable

Same goes for empty lists. So instead create a arraylist :

List<String> list = new ArrayList<String>(Arrays.asList(split));

Posted in , | Tagged , , | Leave a response

JENKINS: How to fix a java.net.NoRouteToHostException.

By on October 5, 2014

Our build server (jenkins) had that error

 

BUILD FAILED

com.jcraft.jsch.JSchException: java.net.NoRouteToHostException: No route to host

Full stack trace below if you really need it. I assume there was a problem with where Jenkins was deploying the apps or at a issue trying to run integration test on the remote server. After talking to IT it seem I was right. the server were Jenkins deploys the app was down (physically down). We had to restart it and then it worked fine.

Full stack :

BUILD FAILED

com.jcraft.jsch.JSchException: java.net.NoRouteToHostException: No route to host

at com.jcraft.jsch.Util.createSocket(Util.java:341)

at com.jcraft.jsch.Session.connect(Session.java:186)

at com.jcraft.jsch.Session.connect(Session.java:154)

at org.apache.tools.ant.taskdefs.optional.ssh.SSHBase.openSession(SSHBase.java:212)

at org.apache.tools.ant.taskdefs.optional.ssh.SSHExec.execute(SSHExec.java:158)

at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)

at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)

at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)

at java.lang.reflect.Method.invoke(Method.java:597)

at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:106)

at org.apache.tools.ant.Task.perform(Task.java:348)

at org.apache.tools.ant.Target.execute(Target.java:357)

at org.apache.tools.ant.Target.performTasks(Target.java:385)

at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1337)

at org.apache.tools.ant.Project.executeTarget(Project.java:1306)

at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)

at org.apache.tools.ant.Project.executeTargets(Project.java:1189)

at org.apache.tools.ant.Main.runBuild(Main.java:758)

at org.apache.tools.ant.Main.startAnt(Main.java:217)

at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)

at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)

Caused by: java.net.NoRouteToHostException: No route to host

at java.net.PlainSocketImpl.socketConnect(Native Method)

at java.net.PlainSocketImpl.doConnect(PlainSocketImpl.java:333)

at java.net.PlainSocketImpl.connectToAddress(PlainSocketImpl.java:195)

at java.net.PlainSocketImpl.connect(PlainSocketImpl.java:182)

at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:366)

at java.net.Socket.connect(Socket.java:529)

at java.net.Socket.connect(Socket.java:478)

at java.net.Socket.<init>(Socket.java:375)

at java.net.Socket.<init>(Socket.java:189)

at com.jcraft.jsch.Util.createSocket(Util.java:335)

… 20 more

 

Posted in | Tagged , , | Leave a response

Why is String not sorting with the Local ?

By on September 28, 2014

Comparing and sorting Strings might not work out like you expected specially when your dealing with a language other than English.

Normally comparing strings in done with simple Unicode ordering. If you want to have localized ordering (the kind expected by an end user) you need to use a Collator.

Commonly used String methods such as:

can have unexpected result depending on the context you’re using them. The reason is that programmers tend to apply them to tasks they really aren’t meant for, simply out of habit.

The fundamental difference is that localized comparison depends on Locale, while String is largely ignorant of Locale.

The only robust way of doing localized comparison or sorting of Strings, in the manner expected by an end user, is to use a Collator, not the methods of the String class.

For more info check out the code example at http://www.javapractices.com or at http://www.guru99.com/java-strings.html

Posted in , | Tagged , , , | Leave a response

Oracle doesn’t support limit

By on September 20, 2014

My god sometime I bang my head on the wall to do something simple on oracle. So oracle don’t support limit so to do something simple has : 

SELECT f_name, l_name, age from
employee_data ORDER BY age DESC
LIMIT 20

become :

SELECT f_name, l_name, age
FROM
( SELECT f_name, l_name, age, ROWNUM r
FROM
( SELECT f_name, l_name, age FROM employee_data 
      ORDER BY age DESC
)
WHERE ROWNUM <= 40
)
WHERE r >= 21;

 

Posted in , | Tagged , , | Leave a response

Next »