MySQL Nested Case SQL statements (CASE WHEN OR THEN ELSE END )

Following is an example of nested MySQL Case When or then else End statement.

select
  CASE
  When (table.field1 = '01')
    THEN
      CASE
      When (SUBSTRING_INDEX(table.field1, ' ', 1) = 'abc')
         THEN '01abc'
         ELSE 'abc'
      END
  WHEN (table.field1 = '02')
    THEN
      CASE
      When ((SUBSTRING_INDEX(table.field1, ' ', 1)) = 'def')
        THEN '02def'
        ELSE 'def'
      END
  When (table.field1 = '03')
    THEN
      CASE
      When ((SUBSTRING_INDEX(table.field1, ' ', 1)) = 'efg')
        THEN '03efg'
      ELSE 'efg'
      END
  WHEN (table.field1 = '04')
    THEN
      CASE
      When ((SUBSTRING_INDEX(table.field1, ' ', 1)) = 'pqr')
        THEN '04pqr'
      ELSE 'pqr'
      END
  END
from table ;

Is this the beginning of the end of BitCoin, Etherum and LiteCoin ? Did it really crashed today evening ??

The BitCoin was traded at $14,212 at 6:40 PM CST, December 19 2017. The price is almost 27% less than its highest value. At the time of the post of this blog, the BitCoin is now backed up to $17,149 

The Etherum was traded at $705 at 6:40 PM CST, December 19 2017. The price is almost 17% less than its highest value. At the time of the post of this blog, the Etherum is now backed up to $772 The

LiteCoin was traded at $277 at 6:40 PM CST, December 19 2017. The price is almost 37% less than its highest value of $371. At the time of the post of this blog, the LiteCoin is now backed up to $320 

Where as the Bitcoin Cash is enjoying a ride of $3100 per bitcoin cash which is almost 41% higher than its yesterday's price.

Fix for xcrun: error: invalid active developer path after updating your mac OS to Sierra

If you are geting the following error with Git or any other tools while running it on from your terminal
 xcrun: error: invalid active developer path (/Library/Developer/CommandLineTools), missing xcrun at: /Library/Developer/CommandLineTools/usr/bin/xcrun. 

 using the following command to fix it
xcode-select --install

Git Cheat Sheet

A. To List your local branchs :

•   git branch

B. How to get or fetch a remote  Branch 
   You first need to fetch and check out the remote branc

•    git fetch remote remote_branch_name (eg :  git fetch origin story2525/mytest_remote_branch)
• git checkout remote_branch_name

C. How to delete a  Branch both locally and remotely:  

1. Delete the  remote branch   

•   git push origin :branch_name or
•   git push origin --delete branch_name

2. Delete a local branch

•   git branch -d branch_name   (use -D if your branch hasn't been fully merge yet)         

D. How to rename your remote Branch : 

1. Rename branch locally

• git branch -m old_branch new_branch

2. Delete the old remote branch   

•   git push origin :old_branch            

3.  Push the new Branch  and update the upstream to point it to the new remote branch.

•  git push --set-upstream origin new_branch

E. Git Logs :

git log --oneline | grep searchString

git log --pretty=format:"%cn committed %h on %cd"| grep searchString

F. Force Push :

 git push origin refactor/mybranch —force

G: Change the remote

git remote set-url origin <>


H. Ammend message for a committed and pushed changes

 git commit --amend 
To push the changes to the branch (myBranch) on the remote repository (origin) use: 
git push --force-with-lease origin myBranch

H. Reset your branch to origin version, revert all your local commit and changes.
Plese stash your changes before you do it. git stash

git reset --hard origin/yourbranch

 I. undo the last commit and unstage all the files:

git reset HEAD~;

j. Undo the commit and completely remove all changes(Becareful !)


git reset --hard HEAD~;

K. Undo previous merge:
check the previous log :
git log
Then revert back the merge  corresponding to the commit hash
 git revert -m 1
And finally push your changes:
git push

L. Roll Back your git push

git push -f origin last_good_commit_hash:yourbranch

 example : git push -f origin 4d875f7e3e8:develop

How to check if the given string is palindrome (Recursive vs Non-recursive )

A palindrome is a word, phrase, number, or other sequence of characters which reads the same backward or forward, such as "level" or "kayak" or  "amanaplanacanalpanama" 

Following is the program to check if the given string is a palindrome.  

package com.mytech.today;


public class PalindromeClient {


    public static boolean isPalendrome (String s) {

        if(s== null) {
            throw new RuntimeException("null value passed");
        }

        int n = s.length();
        if(n==1) return true;
        else {

            for (int i=0; i < n/2 ; i++) {
                if(s.charAt(i)!=s.charAt(n-i-1)) {
                    return false;
                }
            }
        }
        return true;
    }

    public static boolean isPalendromeRecurrsive (String s) {

        if (s.length()<2) { return true;}
        else if (s.charAt(0)==s.charAt(s.length()-1)) {
            return isPalendrome(s.substring(1, s.length()-1));
        }
        else return false;

    }

    public static void main(String[] args) {
        System.out.println(isPalendrome("levvel"));
        System.out.println(isPalendrome("manaplanacanalpanama"));

        System.out.println(isPalendrome("a"));
        System.out.println(isPalendrome("jptt aefa afdaf"));

        System.out.println(isPalendromeRecurrsive("levvel"));
        System.out.println(isPalendromeRecurrsive("manaplanacanalpanama"));

        System.out.println(isPalendromeRecurrsive("a"));
        System.out.println(isPalendromeRecurrsive("jptt aefa afdaf"));
    }
}

Replace Line Break and Spaces on Text Area using java script !

A simple way to replace line break and spaces on a text area content can be done simply by replacing the line break value and the space value with the corresponding html tag as following .

function replaceLineBreaksAndSpaces(textValue) {
   textValue = replaceSpaceCharacter(replaceLineBreak(textValue));
   return textValue;
}
   
function replaceLineBreak(textValue) {

    textValue = textValue.replace(/\r?\n/g, '  <br />');
    return textValue;

}

function replaceSpaceCharacter(textValue) {

    textValue = textValue.replace(/  /g, ' &nbsp;');
    return textValue;
}

Java Script function to Convert your local Date to UTC format Date

There might be a case that you might need to save date on UTC format while the user input date might be regional date time format. The following function converts the date on the UI layers itself  from the input reginonal date to UTC Date.

function convertToUTCFormat(date){
var year = "" + date.getUTCFullYear();
var month = "" + (date.getUTCMonth() + 1);if (month.length == 1) { month = "0" + month; } //months range is [0-11]
var day = "" + date.getUTCDate(); if (day.length == 1) { day = "0" + day; }
var hour = "" + date.getUTCHours();if (hour.length == 1) { hour = "0" + hour; }
var minute = "" + date.getUTCMinutes(); if (minute.length == 1)  minute = "0" + minute; } var second = "" + date.getUTCSeconds(); if (second.length == 1) { second = "0" + second; }

return year + "-" + month + "-" + day + " " + hour + ":" + minute + ":" + second+":000"; }

java.lang.IllegalStateException: Imbalanced frame stack! (exit() called too many times) !

If you started using SpringSource Insight,It's highly recommended to increase the memory of the tc Runtime Instance to more than what your application requires on its own . The following is the exception you might see in case there is not enough memory.
type Exception report
message

descriptionThe server encountered an internal error () that prevented it from fulfilling this request.
exception
java.lang.IllegalStateException: Imbalanced frame stack! (exit() called too many times)
com.springsource.insight.intercept.trace.ThreadLocalFrameBuilder.exit(ThreadLocalFrameBuilder.java:61)
com.springsource.insight.collection.DefaultOperationCollector.exit(DefaultOperationCollector.java:111)
com.springsource.insight.collection.DefaultOperationCollector.exitAbnormal(DefaultOperationCollector.java:85)
com.springsource.insight.plugin.annotation.InsightOperationAnnotationCollectionAspect.ajc$afterThrowing$com_springsource_insight_plugin_annotation_InsightOperationAnnotationCollectionAspect$3$5840edd2(InsightOperationAnnotationCollectionAspect.aj:50)
com.concur.midtier.webservices.xmlhttp.servlets.ReqRespMessageListener.service(ReqRespMessageListener.java:165)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

note The full stack trace of the root cause is available in the VMware vFabric tc Runtime 2.6.2.RELEASE/7.0.22.A.RELEASE logs.

There are few options to resolve this issue.

1. Increase the max heap
   For example: -Xmx512m
2. or max PermGen size JVM option.
   For example: -XX:MaxPermSize=256m
3. Increase the Spring insight max frame
   For example: -Dinsight-max-frames=6000

OR you can have all of the above JVM parameters.

Get the current Value from a TextField in extJS !

Get the Current(or the latest) Value from the textField:

var currentValue= Ext.getCmp('expName').value;

Get the value that was initially loaded (or initialized)
var loadedValue=Ext.getCmp('expName').getValue();


On a separate note, You can define a label with a html message into it.
For example :
  {xtype:'label', 
   id:'duplicate_warning_msg',
   html:''Duplicate value detected !', hidden:true 
   }

You can make it visible or hide it with setVisible method as following.
Ext.getCmp('duplicate_warning_msg').setVisible(true);

Get the the difference between two dates in MS SQL

The DATEDIFF() function returns the difference between two dates
Syntax: DATEDIFF ( datepart , startdate , enddate )

Following is an example for the DateDiff()
DECLARE @startdate datetime ='2011-11-14 02:00:06.957';
DECLARE @enddate datetime = '2011-11-16 05:00:15.490';
SELECT DATEDIFF(day, @startdate, @enddate) as dayDifference,DATEDIFF(HOUR, @startdate, @enddate)as hourDifference
Output

dayDifference	hourDifference
2	51

Similarly the following are the available datepart Options on the DateDiff() Function.

year(or yy or yyyy)
quarter(or qq or q)
month (or mm or m )
dayofyear(or dy or y )
day (or dd, d )
week(or wk or ww)

hour(or hh)
minute(or mi or n)
second(or ss or s )
millisecond(or ms)
microsecond(or mcs)
nanosecond(or ns)
TZoffset(or tz)
ISO_WEEK(or isowk or isoww)

Get the Row Numbers as a separate column on MS SQL Query

I was trying to execute a new query where I need the Row number as a new column on the result itself. I used the SQL Function ROW_NUMBER() to get the row number.
Following query gives the Report Path with the HitCount along with the added Row Number.

select ROW_NUMBER() OVER(ORDER BY count(ReportPath) DESC) AS 'Row Number', ReportPath,count(ReportPath) as HitCount
FROM [ReportServer].[dbo].[ExecutionLog2]
Group by ReportPath
Order by HitCount desc


Cheers !

Get the Disk Usage Statistics from the WinDirStat !

WinDirStat is an opensource tool to identify howmuch of disk space you are using on your wondow box. It shows disk, file and directory sizes in a treelist as well as graphically in a treemap, much like KDirStat or SequoiaView.

Once i started using it, i feel like life would have been very hard without it.
Why don't you try it yourself.

You can download the tool from http://windirstat.info/

How to parse multi value parameter from SSRS Report !

While writing an SSRS Report, I had to parse a multivalued parameter and stored it on a temp table. After some googling i came up with the following solution. @id is the multi valued parameter that need to be parsed. Following is the sample code that you can used to parse and put the values on a temp table.


-- @uid is the parameter. Lets define the parameter for testing.

declare @uid as nvarchar(200)
set @uid='u01052901,u01052789,u01052897,u0105345,u08023432,u0234324,u23479879'

create table #TempIDTable (
slice varchar(50))
declare @index1 int
declare @u_id nvarchar(4000)
set @index1 = 1
if @uid is null
set @index1 = 0
while @index1 !=0
begin
set @index1 = charindex(',',@uid)
if @index1 !=0
set @u_id = left(@uid,@index1 - 1)
else
set @u_id = @uid
insert into #TempIDTable select @u_id
set @uid= right(@uid,len(@uid) - @index1)
if len(@uid) = 0
break
end

Select * from #TempIDTable
--drop table #TempIDTable

Date Conversion from YYYY-MM-DD HH:MM:SS:MMM to YYYY-MM-DD 00:00:00:000 !

Just keeping it handy !

declare @StartTime as DateTime
set @StartTime= dateadd(dd,0, datediff(dd,0,'2011-06-08 11:18:26.000'))
select @StartTime as StartTime


Schema Spy, A Graphical Database Schema Metadata Browser.

Schemaspy is a java based tool that analyzes the metadata of a schema in a database and generated the visual representation of it. The output is highly user friendly. You can browse through table via child and parent table relationship.

Schema Spy uses the dot executable from graphviz to generate the graphical representation of the schema and its relationship. Graphviz is the graphics visualization software that takes descriptions of graphs in a simple text language, and make diagrams in several useful formats such as images and SVG for web pages, Postscript for inclusion in PDF or other documents; or display in an interactive graph browser.

Following is the steps to execute the SchemaSpy tool (for a sample MYSQL database.)
1. Download and install Graphviz from http://www.graphviz.org/Download..php
2. Download the Schema spay jar file, Schemaspy_x_y_z.jar from http://sourceforge.net/projects/schemaspy/files/
3. Download the corresponding JDBC Connector and have it on the same source folder where the schemaspy_x_y_z.jar resides. For example for a mysql database, I would simply download mysql-connector-java-5.1.5.jar or any other latest version of mysql connector.
4. Now from the source location of your Schemaspy_x_y_z.jar file, execute schemaspy to generate the graphical representation of the schema of your database, as shown on the following syntax

java -jar schemaSpy_x.y.z.jar -t database_type -dp mysql jdbcdatabase_connector -hq -o output_Directory -db databaseName -u username -p password
for example : java -jar schemaSpy_4.1.1.jar -t mysql -dp mysql-connector-java-5.1.5.jar -hq -o out -db bisudatabae -u bishow -p north