[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

Strange SQL problem

I use this code:

var sql:string;
    Path: string;
begin
      Path:= ExtractFilePath(Application.ExeName);
      sql:='SELECT PC, IC, COUNT(*) AS CI FROM table.dbf WHERE PC>0 and CNP=1511 GROUP BY PC,IC';
      QR.DatabaseName:=Path;
      QR.Close;
      QR.SQL.Text:= sql;
      QR.Open;
end;

The result is

PC   IC   CI

0     C     3
20   C     3

How you can see my condition PC>0 is ignored.


.... WHERE PC=0 ... with this no result ?!?!?!?
.... WHERE PC=20 ... with this no result ?!?!?!?

What is wrong in my sql ????
0
ginsonic
Asked:
ginsonic
  • 8
  • 3
  • 2
  • +4
1 Solution
 
kretzschmarCommented:
what datatype is PC?
0
 
vadim_tiCommented:
what type is your PC field? may be you have some rounding problem?
0
 
TheRealLokiSenior DeveloperCommented:
if so, you could change it to
='SELECT PC, IC, COUNT(*) AS CI FROM table.dbf WHERE PC >= 1 and CNP=1511 GROUP BY PC,IC'
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
aikimarkCommented:
try

sql:= 'SELECT PC, IC, COUNT(IC) AS CI FROM table.dbf WHERE PC>0 and CNP=1511 GROUP BY PC,IC';

I think vadim_ti has a pertinent question about the datatype of the PC field.
0
 
ginsonicAuthor Commented:
PC and CNP are numeric, IC is string
0
 
ginsonicAuthor Commented:
TheRealLoki, how I say already in my question is strange that don't recognize the values. If condition is PC>0 I get six results ( but not correct becouse three are nul and three are 20). If PC=0 or PC=20 I get 0 results ( but have three for 0 and three for 20 ).
0
 
geobulCommented:
Hi,

Try this one:

sql:='SELECT PC, IC, COUNT(*) AS CI FROM table.dbf WHERE (PC is not null) and (PC>0) and (CNP=1511) GROUP BY PC,IC';

Regards, Geo
0
 
Ivanov_GCommented:
try this:

SELECT PC, IC, COUNT(*)
( SELECT PC, IC
  FROM table.dbf
  WHERE PC>0
    AND PC IS NOT NULL
    AND CNP=1511 ) A
GROUP BY PC, IC
0
 
ginsonicAuthor Commented:
NO RESULT, same problem :(
0
 
geobulCommented:
Hi,

Swap these two lines. It should be:

   ...
   QR.Close;
   QR.DatabaseName:=Path;
   ...

BTW. Your initial SQL statement works fine here and ignores null values for PC field.

Regards, Geo
0
 
ginsonicAuthor Commented:
I updated a dbf sample at:

www.construiesc.ro/Download/MY.dbf

What is interesting, for CNP I posted here just the first 4 digits from 13. So when I made the demo dbf I stop to 1511. Now I can't filter for CNP=1511. Return 0 results. But if I use 1511111111111 ( 13th digits ) run OK.

To start from here my problem? I don't understand whay but look like my CNP must have 13 digits to run. Look like sql search
1511_________ and not 1511 how I say.
0
 
geobulCommented:
Hi,

I don't know how you've filled that table but after I opened it using Database Desktop and re-entered CNP and PC_CONS values everything worked just fine.

Regards, Geo
0
 
ginsonicAuthor Commented:
I receive each month some dbf files. Independent to my wish, these dbf are indexed but I don't receive the cdx file. So I'm forced to crack the dbf. For this I load the dbf in Open Office and I save ( override ) it. So I get a non indexed dbf.

Maybe you know a way to remove the index information from dbf header file.
0
 
ginsonicAuthor Commented:
I need to do this because I get an error when try to open the dbf and don't have cdx on path.
0
 
ginsonicAuthor Commented:
I found next tip:

http://www.swissdelphicenter.ch/torry/showcode.php?id=403

But I can't find where is ErrorIO!?!?
0
 
vadim_tiCommented:
see this query, very interesting

SELECT * FROM "C:\temp\my.dbf"
WHERE (PC_CONS > 199) and (PC_CONS < 200)
AND (CNP > 1510000000000) AND (CNP < 1511000000000)

CNP is defined N 13 0
PC_CONS is defined N 3 0


to have your query working you need to transfer parameters as BCD, see .DFM file that give right results for your query

//.DFM
object Form1: TForm1
  Left = 192
  Top = 133
  Width = 696
  Height = 480
  Caption = 'Form1'
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'MS Sans Serif'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 96
    Top = 88
    Width = 320
    Height = 120
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'MS Sans Serif'
    TitleFont.Style = []
  end
  object Query1: TQuery
    Active = True
    SQL.Strings = (
      'SELECT PC_CONS, IC, COUNT(*) AS CI FROM "C:\temp\my.dbf" '
      'WHERE PC_CONS >:p1 AND CNP=:p2 GROUP BY PC_CONS,IC')
    Left = 16
    Top = 16
    ParamData = <
      item
        DataType = ftBCD
        Name = 'p1'
        ParamType = ptUnknown
        Value = '0'
      end
      item
        DataType = ftBCD
        Name = 'p2'
        ParamType = ptUnknown
        Value = '1511'
      end>
    object Query1PC_CONS: TSmallintField
      FieldName = 'PC_CONS'
    end
    object Query1IC: TStringField
      FieldName = 'IC'
      Size = 1
    end
    object Query1CI: TIntegerField
      FieldName = 'CI'
    end
  end
  object DataSource1: TDataSource
    DataSet = Query1
    Left = 64
    Top = 24
  end
end

//.PAS
unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, DB, Grids, DBGrids, DBTables;

type
  TForm1 = class(TForm)
    Query1: TQuery;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    Query1PC_CONS: TSmallintField;
    Query1IC: TStringField;
    Query1CI: TIntegerField;
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

end.
0
 
ginsonicAuthor Commented:
Thanks to all!
0
 
aikimarkCommented:
ginsonic,

Depending on the underlying database engine you are using, you should be able to use the LIKE operator with the wildcard character to more efficiently limit CNP values:
AND (CNP Like '1511*')
AND (CNP Like '1511%')
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 3
  • 2
  • +4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now