Devart Forums

Code: Select all

--drop table TEST_CODE1_TB;

--drop table TEST_CODE2_TB;

create table TEST_CODE1_TB (CD NUMBER(2) NULL);

create table TEST_CODE2_TB (CD NUMBER(2)  NULL);

INSERT INTO TEST_CODE1_TB VALUES(-20);

INSERT INTO TEST_CODE2_TB VALUES(-10);

COMMIT;

-- drop public synonym TEST_CODE_TB_SYN;

create or replace view test_code_v
as
 with x as (
   select * from TEST_CODE2_TB
 )
SELECT CAST(
    (CASE 
      WHEN T1.CD < 0 THEN T1.CD      
      when t1.cd = 0 then t2.cd
      when t1.cd < 10 then null 
      ELSE cast('-1' as number(2)) 
    END) AS NUMBER(2)) AS Val
  FROM TEST_CODE1_TB T1 cross join x T2;

Code: Select all

[TestDB]
BlobSize=-1
DataBase=<tnsnames.ora entry>
DriverName=DevartOracle
ErrorResourceFile=
LocaleCode=0000
Password=
Oracle TransIsolation=ReadCommitted
RoleName=Normal
User_Name=<user name>
LongStrings=True
EnableBCD=True
InternalName=
FetchAll=False
CharLength=0
Charset=
UseQuoteChar=False
UseUnicode=True

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 412
  ClientWidth = 852
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  OnCreate = FormCreate
  PixelsPerInch = 96
  TextHeight = 13
  object DBGrid1: TDBGrid
    Left = 288
    Top = 64
    Width = 320
    Height = 120
    DataSource = DataSource1
    TabOrder = 0
    TitleFont.Charset = DEFAULT_CHARSET
    TitleFont.Color = clWindowText
    TitleFont.Height = -11
    TitleFont.Name = 'Tahoma'
    TitleFont.Style = []
  end
  object SQLQuery1: TSQLQuery
    MaxBlobSize = -1
    Params = <>
    SQL.Strings = (
      'select * from test_code_v')
    SQLConnection = SQLConnection1
    Left = 56
    Top = 344
    object SQLQuery1VAL: TFMTBCDField
      FieldName = 'VAL'
      Precision = 32
    end
  end
  object DataSource1: TDataSource
    DataSet = ClientDataSet1
    Left = 80
    Top = 160
  end
  object DataSetProvider1: TDataSetProvider
    DataSet = SQLQuery1
    Left = 80
    Top = 272
  end
  object ClientDataSet1: TClientDataSet
    Aggregates = <>
    Params = <>
    ProviderName = 'DataSetProvider1'
    Left = 80
    Top = 216
  end
  object SQLConnection1: TSQLConnection
    ConnectionName = 'TestDB'
    DriverName = 'DevartOracle'
    Params.Strings = (
      'BlobSize=-1'
      'DataBase=xxx'
      'DriverName=DevartOracle'
      'ErrorResourceFile='
      'LocaleCode=0000'
      'Password='
      'Oracle TransIsolation=ReadCommitted'
      'RoleName=Normal'
      'User_Name=xxx'
      'LongStrings=True'
      'EnableBCD=True'
      'InternalName='
      'FetchAll=False'
      'CharLength=0'
      'Charset='
      'UseQuoteChar=False'
      'UseUnicode=True')
    Connected = True
    Left = 80
    Top = 104
  end
end

Code: Select all

unit MainUnit;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, DBXDevartOracle, Data.DB, Data.SqlExpr, Data.FMTBcd, Vcl.Grids, Vcl.DBGrids,
  Datasnap.DBClient, Datasnap.Provider;

type
  TForm1 = class(TForm)
    SQLQuery1: TSQLQuery;
    DataSource1: TDataSource;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    DBGrid1: TDBGrid;
    SQLConnection1: TSQLConnection;
    SQLQuery1VAL: TFMTBCDField;
    procedure FormCreate(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.FormCreate(Sender: TObject);
begin
 SQLConnection1.Connected := true;
 ClientDataSet1.Active := true;
end;
end.