Home: Perl Programming Help: DBI:
Use the Vertica LIMIT function in perl



pra_perl
Novice

May 18, 2015, 7:00 AM


Views: 17129
Use the Vertica LIMIT function in perl

Hello,

I am trying to use the Vertica Limit function to restrict my records. This is my first time using Vertica.

I am using this code
(I pass a value of 1000) from my excel code to perl.( as in TOP 1000)

if ($num) {
$select = "Select Top $num $Qry ";}
else {
$select = "Select $Qry ";}

$from= "FROM ${VIEW}_NEW_VIEWS.NewTable as M ";
$sql = $select.$from.$Where;

which is incorrect since in Vertica, I cannot use Top.
I need to get it as
SELECT prod_time, down_time from _NEW_VIEWS.NewTable
WHERE
LIMIT 1000

How do I get the LIMIT 1000 after
$sql = $select.$from.$Where; or

modify this
if ($num) {
$select = "Select Top $num $Qry ";}

Secondly..how to pass a vertica integer value in perl?
I used

if ($cn ne "") { $Where = $Where ."AND m.gID = $cn ";}

but it is expecting
WHERE m.gID = '3'

Thanks,

Prasad


FishMonger
Veteran / Moderator

May 18, 2015, 10:56 AM


Views: 17125
Re: [pra_perl] Use the Vertica LIMIT function in perl


Quote
How do I get the LIMIT 1000 after
$sql = $select.$from.$Where;



Code
$sql = $select.$from.$Where . "LIMIT 1000";



pra_perl
Novice

May 19, 2015, 7:23 AM


Views: 17120
Re: [FishMonger] Use the Vertica LIMIT function in perl

Thanks for the reply. I modified your solution to my code and it worked.

Another issue, I have a integer field, defined as INTEGER , N18.
In my CGI, I have it as
if ($cn ne "") { $Where = $Where ."AND m.gID = $cn ";}
but it gives me error on Vertica.

Does Vertica allow values as in gID = 3 or gID = '3'?

Please let me know the syntax.

Thanks,

prasad


FishMonger
Veteran / Moderator

May 19, 2015, 7:42 AM


Views: 17117
Re: [pra_perl] Use the Vertica LIMIT function in perl

I've never heard of Vertica before, so I can't say what it allows or doesn't allow.

A quick google search shows that the Vertica software company was bought out by HP. You may need to find a forum dedicated to that software package.

In normal SQL, integer fields don't use quotes when assigning the value.


(This post was edited by FishMonger on May 19, 2015, 7:43 AM)


pra_perl
Novice

May 19, 2015, 11:42 AM


Views: 17113
Re: [FishMonger] Use the Vertica LIMIT function in perl

Thanks again. I resolved it. Vertica accepts integer values without quotes.