testing for true greater than 1 3

[1DMF]

hi, probably a silly question i've asked before (I blame the heat for memory loss!), but I've been thinking of ways to streamline my code and DB queries.

some of my code tests for the existence of a record by selecting it, I realise that using a count(1) is much more efficient.

I just wanted confirmation that when using the if statement to check for true, if it is given a number > 1 it is still true.

I.E.

if(cntSQL("Table","WHERE")){do whatever}

or should I explicitly write the condition...

if(cntSQL("Table","WHERE") > 0){do whatever}

my cntSQL routine does the following SQL command...

#Build SQL Statement
my $sel = "SELECT COUNT(1) as COUNT FROM $_[0] WHERE $_[1]";

so the count could be from ZERO to the amount records in table, some checks I do just care a record exists regardless of how many.

thanks 1DMF


"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[raklet]

The check does not have to be explicit. Just check if the variable exists. The variable will eval to true if it contains any real positive or negative number except zero.

my $zero = 0; #change val to 1 or 10 or -5 and see what happens
if ($zero) {
    print "\$zero is not 0 or undef\n";
} else {
    print "\$zero is 0 or undef";
}

 

[1DMF]

cool thanks

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[stevexff]

COUNT(*) (never seen it written with anything other than a '*' as a parameter) is a good way to do it; it always returns a value, even if it's zero. If your WHERE clause only looks at indexed columns you *may* just get away with index-only database access, which will help to speed things up too...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[1DMF]

I read a SQL manual and it says COUNT(*) is not good you should write COUNT(1) to increase performance, here is the blurb...

TIP: Performance Tuning
Since the COUNT function will return the same results regardless of what NOT NULL field(s) you include as the COUNT function parameters (ie: within the brackets), you can change the syntax of the COUNT function to COUNT(1) to get better performance as the database engine will not have to fetch back the data fields.

For example, based on the example above, the following syntax would result in better performance:

SELECT department, COUNT(1) as "Number of employees"
FROM employees
WHERE salary > 25000
GROUP BY department;

Now, the COUNT function does not need to retrieve all fields from the employees table as it had to when you used the COUNT(*) syntax. It will merely retrieve the numeric value of 1 for each record that meets your criteria.

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.

 

[stevexff]

OK, useful tip. Have a star...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]

 

[1DMF]

Thanks steve! - have one yourself!

"In complete darkness we are all the same, only our knowledge and wisdom separates us, don't let your eyes deceive you.