Sqlite where clause

this drives me creasy, the sql below does not display any rows
BUT without the where clause it displayed lots of rows
what’s wrong with the WHERE clause ?

select ldg_year, ldg_type, account_no, sum(ldg_amount)
from ledger
WHERE ldg_type = ‘Dr’
group by ldg_year, ldg_type, account_no
order by ldg_year, account_no;

I bet its your GROUP BY that is eliminating the rows you’re looking for.

I removed ldg_type from the select, group by and order by clause and still no rows

Try including ldg_type in the SELECT part and removing the WHERE clause, and seeing what’s pulled out. Perhaps leg_type is ‘Dr.’ or something similar.

yes that works, what does not work is if ldg_type is in the Where clause,
column ldg_type is defined as TEXT, any column which is defined as TEXT does not work in the where clause.
I can do WHERE leg_type like “%” that displays some rows, but as soon as I say WHERE ldg_type = “Dr”, no rows are displayed.

[quote]any column which is defined as TEXT does not work in the where clause.
I can do WHERE leg_type like “%” that displays some rows, [/quote]
Those two statements contradict each other!
I wonder if you’ve got some extraneous spaces in there. Try WHERE trim(ldg_type)=“Dr”.

or use ‘%Dr%’

True, although that’d find everyone with a title of ‘Mandrill’ as well… (Not sure if that’s a real title, but you get my point - %Mr% would also find %Mrs%…)

TRIM(name) fixes the problem, I don’t understand, but it works.
have never seen that with other sql’s

thanks

It’s because you’ve got either leading or trailing white space in your data. “Dr” is different from "Dr " - the TRIM() command removes that white space.

there should not be any white spaces, but anyway, using TRIM works even without a % sign

thanks

Yes, % is a SQLite wildcard. If you do a query like this:

select ldg_year from ledger WHERE ldg_type LIKE 'Dr%'

that’ll say ‘give me all rows where ldg_type starts with Dr’ - i.e. is like ‘Dr’ followed by any other characters. You don’t need to include % signs if you’re not using LIKE.
There will definitely be white space in there if

WHERE ldg_type = 'Dr'

does not return rows but

WHERE trim(ldg_type) = 'Dr'

does, so do check the data that’s gone in.

depends on which field you are looking… for title field, people tend put all variety of Mr e.g., Mr, Mr. and others.

you are right Hamish, I don’t know where the space in front of ’ Dr’ comes from,
have to investigate that

thanks

[quote=126354:@Hamish Symington]Yes, % is a SQLite wildcard. If you do a query like this:

select ldg_year from ledger WHERE ldg_type LIKE 'Dr%'

that’ll say ‘give me all rows where ldg_type starts with Dr’ - i.e. is like ‘Dr’ followed by any other characters. You don’t need to include % signs if you’re not using LIKE.
There will definitely be white space in there if

WHERE ldg_type = 'Dr'

does not return rows but

WHERE trim(ldg_type) = 'Dr'

does, so do check the data that’s gone in.[/quote]

Actually like and = differ in more respects than % and _
LIKE is case insensitive
= is not

So
select * from table where field like ‘abc’
is not exactly the same as
select * from table where field = ‘abc’

the fist will match Abc, aBc, abC and other variations but the second will only match ‘abc’