Re: How to avoid case sensitivity in group by

[Matt]

Drill is not SQL Server, and not expected to work identically. 

Using the upper() and lower() functions is a common approach, unless you find 
options to set the collation sort order in the Drill docs. 

> On Feb 8, 2017, at 1:13 PM, Dechang Gu  wrote:
> 
> Sanjiv,
> 
> Can you share the query and the output you get through sql server?
> 
> 
> Thanks,
> 
> Dechang
> 
> 
> From: Sanjiv Kumar 
> Sent: Wednesday, February 8, 2017 5:01:27 AM
> To: user@drill.apache.org
> Subject: Re: How to avoid case sensitivity in group by
> 
> ​lower and upper is not a solution which i am aspecting. ​I want to display
> same output as i get through sql server management studio
> 
>> On Wed, Feb 8, 2017 at 5:15 PM, Sanjiv Kumar  wrote:
>> 
>> Drill Environment:- OS:- Window 10
>>   Version:- 1.9
>>   Mode:- embedded mode
>> 
>> I have a column name 'State' in db, which have data like(e.g:- Florida,
>> florida,new jersey, etc).
>> 
>> My problem is while using count(State) and group by in query,
>> Florida and florida are showing two seperate rows.
>> 
>> So how to avoid case sensitivity so that both  Florida and florida act as
>> 1 rows while firing count(Sate).?
>> 
>> 
>> --
>> Thanks & Regards.
>> Sanjiv Kumar.
>> 
> 
> 
> 
> --
> Thanks & Regards.
> Sanjiv Kumar.

Re: How to avoid case sensitivity in group by

[Dechang Gu]

Sanjiv,

Can you share the query and the output you get through sql server?


Thanks,

Dechang


From: Sanjiv Kumar 
Sent: Wednesday, February 8, 2017 5:01:27 AM
To: user@drill.apache.org
Subject: Re: How to avoid case sensitivity in group by

​lower and upper is not a solution which i am aspecting. ​I want to display
same output as i get through sql server management studio

On Wed, Feb 8, 2017 at 5:15 PM, Sanjiv Kumar  wrote:

> Drill Environment:- OS:- Window 10
>Version:- 1.9
>Mode:- embedded mode
>
> I have a column name 'State' in db, which have data like(e.g:- Florida,
> florida,new jersey, etc).
>
> My problem is while using count(State) and group by in query,
> Florida and florida are showing two seperate rows.
>
> So how to avoid case sensitivity so that both  Florida and florida act as
> 1 rows while firing count(Sate).?
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>



--
Thanks & Regards.
Sanjiv Kumar.

Re: how to show decimal upto 2 points

[Charles Givre]

Hi Sanjiv, 
You have a few options…
You can use the ROUND() function IE:

SELECT ROUND( `col1`, 2 ) AS col1 FROM… 

OR you can use the TO_CHAR() function like this:

SELECT TO_CHAR( `col1`, ‘#,###.##’ ) AS col1 FROM… 

The biggest difference is that the ROUND() function gets you numeric data types 
whereas TO_CHAR() gets you a VARCHAR. 
Hope this helps, 
— Charles




> On Feb 8, 2017, at 08:17, Sanjiv Kumar  wrote:
> 
> Hi
> 
> Drill Environment:- OS:- Window 10
>   Version:- 1.9
>   Mode:- embedded mode
> I have query i have set planner.enable_decimal_data_type=true and while
> firing select command in drill i got output in decimal value but it showing
> like 11936.3697.
> 
> I want to display only upto 2 or 3 value after decimal. like (11936.36).
> 
> Is there any options are there to show upto decimal upto 2 value.?​
> 
> -- 
> Thanks & Regards.
> Sanjiv Kumar.

FINAL REMINDER: CFP for ApacheCon closes February 11th

[Rich Bowen]

Dear Apache Enthusiast,

This is your FINAL reminder that the Call for Papers (CFP) for ApacheCon
Miami is closing this weekend - February 11th. This is your final
opportunity to submit a talk for consideration at this event.

This year, we are running several mini conferences in conjunction with
the main event, so if you're submitting for one of those events, please
pay attention to the instructions below.

Apache: Big Data
* Event information:
http://events.linuxfoundation.org/events/apache-big-data-north-america
* CFP:
http://events.linuxfoundation.org/events/apache-big-data-north-america/program/cfp

Apache: IoT (Internet of Things)
* Event Information: http://us.apacheiot.org/
* CFP -
http://events.linuxfoundation.org/events/apachecon-north-america/program/cfp
(Indicate 'IoT' in the Target Audience field)

CloudStack Collaboration Conference
* Event information: http://us.cloudstackcollab.org/
* CFP -
http://events.linuxfoundation.org/events/apachecon-north-america/program/cfp
(Indicate 'CloudStack' in the Target Audience field)

FlexJS Summit
* Event information - http://us.apacheflexjs.org/
* CFP -
http://events.linuxfoundation.org/events/apachecon-north-america/program/cfp
(Indicate 'Flex' in the Target Audience field)

TomcatCon
* Event information - https://tomcat.apache.org/conference.html
* CFP -
http://events.linuxfoundation.org/events/apachecon-north-america/program/cfp
(Indicate 'Tomcat' in the Target Audience field)

All other topics and projects
* Event information -
http://events.linuxfoundation.org/events/apachecon-north-america/program/about
* CFP -
http://events.linuxfoundation.org/events/apachecon-north-america/program/cfp

Admission to any of these events also grants you access to all of the
others.

Thanks, and we look forward to seeing you in Miami!

-- 
Rich Bowen
VP Conferences, Apache Software Foundation
rbo...@apache.org
Twitter: @apachecon



(You are receiving this email because you are subscribed to a dev@ or
users@ list of some Apache Software Foundation project. If you do not
wish to receive email from these lists any more, you must follow that
list's unsubscription procedure. View the headers of this message for
unsubscription instructions.)

how to show decimal upto 2 points

[Sanjiv Kumar]

Hi

 Drill Environment:- OS:- Window 10
   Version:- 1.9
   Mode:- embedded mode
I have query i have set planner.enable_decimal_data_type=true and while
firing select command in drill i got output in decimal value but it showing
like 11936.3697.

I want to display only upto 2 or 3 value after decimal. like (11936.36).

Is there any options are there to show upto decimal upto 2 value.?​

-- 
Thanks & Regards.
Sanjiv Kumar.

Re: How to avoid case sensitivity in group by

[Sanjiv Kumar]

​lower and upper is not a solution which i am aspecting. ​I want to display
same output as i get through sql server management studio

On Wed, Feb 8, 2017 at 5:15 PM, Sanjiv Kumar  wrote:

> Drill Environment:- OS:- Window 10
>Version:- 1.9
>Mode:- embedded mode
>
> I have a column name 'State' in db, which have data like(e.g:- Florida,
> florida,new jersey, etc).
>
> My problem is while using count(State) and group by in query,
> Florida and florida are showing two seperate rows.
>
> So how to avoid case sensitivity so that both  Florida and florida act as
> 1 rows while firing count(Sate).?
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>



-- 
Thanks & Regards.
Sanjiv Kumar.

Re: How to avoid case sensitivity in group by

[Arina Yelchiyeva]

You can wrap state column in upper / lower function prior to count.

Kind regards
Arina

On Wed, Feb 8, 2017 at 1:45 PM, Sanjiv Kumar  wrote:

> Drill Environment:- OS:- Window 10
>Version:- 1.9
>Mode:- embedded mode
>
> I have a column name 'State' in db, which have data like(e.g:- Florida,
> florida,new jersey, etc).
>
> My problem is while using count(State) and group by in query,
> Florida and florida are showing two seperate rows.
>
> So how to avoid case sensitivity so that both  Florida and florida act as 1
> rows while firing count(Sate).?
>
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>

How to avoid case sensitivity in group by

[Sanjiv Kumar]

Drill Environment:- OS:- Window 10
   Version:- 1.9
   Mode:- embedded mode

I have a column name 'State' in db, which have data like(e.g:- Florida,
florida,new jersey, etc).

My problem is while using count(State) and group by in query,
Florida and florida are showing two seperate rows.

So how to avoid case sensitivity so that both  Florida and florida act as 1
rows while firing count(Sate).?


-- 
Thanks & Regards.
Sanjiv Kumar.

Re: How to enable decimal value

[Sanjiv Kumar]

Hey..  I got my problem, In select query i am using cast. I have number
value in database and while querying through drill, i have cast it to
bigint, thats why decimal value are not showing in output even after set
planner.enable_decimal_data_type=true.

But after removing cast, expected output came. Thanks for your reply
Khurram.

On Wed, Feb 8, 2017 at 3:15 PM, Sanjiv Kumar  wrote:

> Hello I have decimal value in my table. When query through drill data are
> rounded off in output.
>
> I have enable planner.enable_decimal_data_type = true in system options.
> But still data are not showing in decimal form.
>
> Drill Environment:- OS:- Window 10
>Version:- 1.9
>Mode:- embedded mode
>
> --
> Thanks & Regards.
> Sanjiv Kumar.
>



-- 
Thanks & Regards.
Sanjiv Kumar.

Re: How to enable decimal value

[Khurram Faraaz]

Can you share your query and the results ?

And did you set planner.enable_decimal_data_type=true at session level or 
system level ?


From: Sanjiv Kumar 
Sent: Wednesday, February 8, 2017 3:15:08 PM
To: user@drill.apache.org
Subject: How to enable decimal value

Hello I have decimal value in my table. When query through drill data are
rounded off in output.

I have enable planner.enable_decimal_data_type = true in system options.
But still data are not showing in decimal form.

Drill Environment:- OS:- Window 10
   Version:- 1.9
   Mode:- embedded mode

--
Thanks & Regards.
Sanjiv Kumar.

How to enable decimal value

[Sanjiv Kumar]

Hello I have decimal value in my table. When query through drill data are
rounded off in output.

I have enable planner.enable_decimal_data_type = true in system options.
But still data are not showing in decimal form.

Drill Environment:- OS:- Window 10
   Version:- 1.9
   Mode:- embedded mode

-- 
Thanks & Regards.
Sanjiv Kumar.