Link to home
Create AccountLog in
Avatar of Sheldon Livingston
Sheldon LivingstonFlag for United States of America

asked on

Pervasive SQL help please

I need a query that returns data where FieldA is blank AND if (FieldB = 'Special' AND FieldC is null)

So I need all records where FieldA is blank.

In addition I need the records where FieldB is equal to the word "Special" and FieldC, on the same record, is null.

Realize that FieldB can contain other words in which I don't care about the status of FieldC.

Any thoughts?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Well, it depends on your definition of blank, doesn't it?

I would guess: WHERE FieldA = '' AND (FieldB = 'Special' AND FieldC IS NULL OR FieldB <> 'Special' ).
The logic from Guy is right, but change the text [is blank] to [= ''].

Two possible issues:
1-some psql databases use zstring fields, but then populate it with spaces. You may need to provide a string with spaces equal to the field length instead of the empty string.
2-some psql databases do not define null values properly. You may need to use an empty string for the fieldc definition, too.

Both of these are application specific, not database engine specific, so check with your specific data dictionary.
Avatar of Sheldon Livingston

ASKER

Thank you!