SQL server and Informix compatibility

Posted in 2012

Topics: Data Types & Schema Design, Triggers, Constraints & Referential Integrity, Clustering, Grid & MACH11

LEY PATRICK — Wed, 25 Jul 2012 08:30:15 — source: IIUG Forums & Mailing Lists

Hi all I was posted with the following challenge on converting the following MS SQL table into Informix(v11.7 on Linux) equivalent. Sample of MS Sql server table: /****** Object: Table dbo.Table1 Script Date: 07/08/2012 16:30:03 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO CREATE TABLE dbo.Table1( MRN char(10) NOT NULL, PatientCode char(3) NOT NULL, MaterialNo varchar(9) NOT NULL, OriginalCode char(2) NOT NULL, PriceOne money NOT NULL, ServingCode datetime NOT NULL, StrucNo char(10) NULL, ConfirmAmt char(1) NOT NULL, TotUpFrontPayment money NOT NULL, TotBearCostRS money NOT NULL, TotWaiver money NOT NULL, TariffService money NOT NULL, AdminFees money NOT NULL, DayServingCode AS (datepart(day,ServingCode)), MthServingCode AS (datepart(month,ServingCode)), YearServingCode AS (datepart(year,ServingCode)), DayStruk AS (substring(StrucNo,(5),(2))), MthStruk AS (substring(StrucNo,(3),(2))), YearStruk AS (left(StrucNo,(2))), AmtDue AS (case sign(((((PriceOne+TariffService)+AdminFees)-TotUpFrontPayment)-TotBearCostRS)-To tWaiver) when (-1) then (0) when (0) then (0) when (1) then ((((PriceOne+TariffService)+AdminFees)-TotUpFrontPayment)-TotBearCostRS)-TotWaiv er end), AmtO AS ((JmlBarang*PriceOne+JmlService*TariffService)+AdminFees), AmtDueGuarantor AS ((((CONVERT(float,PriceOne,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees ,(0)))*CONVERT(float,TotUpFrontPayment,(0)))*JmlBarang+((CONVERT(float,TariffSer vice,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,T otUpFrontPayment,(0)))*JmlService)+(CONVERT(float,AdminFees,(0))/CONVERT(float,( PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,TotUpFrontPayment,(0))), TotORS AS ((((CONVERT(float,PriceOne,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees ,(0)))*CONVERT(float,TotBearCostRS,(0)))*JmlBarang+((CONVERT(float,TariffService ,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,TotBe arCostRS,(0)))*JmlService)+(CONVERT(float,AdminFees,(0))/CONVERT(float,(PriceOne +TariffService)+AdminFees,(0)))*CONVERT(float,TotBearCostRS,(0))), TotWaive AS ((((CONVERT(float,PriceOne,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees ,(0)))*CONVERT(float,TotWaiver,(0)))*JmlBarang+((CONVERT(float,TariffService,(0) )/CONVERT(float,(PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,TotWaiver ,(0)))*JmlService)+(CONVERT(float,AdminFees,(0))/CONVERT(float,(PriceOne+TariffS ervice)+AdminFees,(0)))*CONVERT(float,TotWaiver,(0))), AcceptedNo char(10) NOT NULL, CONSTRAINT CiPK_Table1 PRIMARY KEY CLUSTERED ( MRN ASC, PatientCode ASC, MaterialNo ASC, OriginalCode ASC, ServingCode ASC, ConfirmAmt ASC, AcceptedNo ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY ) ON PRIMARY At a glance on the table, there are few things that I see :- 1. MS sql server allow user to create field based on some expression or function which extract from other field or put some condition during creation of table and auto fill up it's value. 2. The closest thing that I can think off(in informix) is to used default value for each field. But from my understanding default value only support literal or some basic stuff like user,current and etc. Functions like substring, case, day, month, year can not be used with DDL statement. To archived the same objective, I have to write an insert trigger which mimic the functionality of ms sql server. Can somebody enlighten me on my understanding or show me other better way to accomplish the task ? Many thanks ahead. Patrick

↪ replying to LEY PATRICK

Art Kagel — Wed, 25 Jul 2012 11:29:22 — source: IIUG Forums & Mailing Lists

You are thinking too hard. Create a 'real' table without the calculated columns and a different name (say table1_real) then create a VIEW with the calculated columns added. The view may need an INSTEAD OF trigger to handle inserts and updates. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Jul 25, 2012 at 4:30 AM, LEY PATRICK <patrickley@gmail.com> wrote: > Hi all > > I was posted with the following challenge on converting the following > MS SQL table into Informix(v11.7 on Linux) equivalent. > > Sample of MS Sql server table: > > /****** Object: Table dbo.Table1 Script Date: 07/08/2012 16:30:03 ******/ > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > SET ANSI_PADDING ON > GO > CREATE TABLE dbo.Table1( > MRN char(10) NOT NULL,> > PatientCode char(3) NOT NULL, > MaterialNo varchar(9) NOT NULL, > OriginalCode char(2) NOT NULL, > PriceOne money NOT NULL, > ServingCode datetime NOT NULL, > StrucNo char(10) NULL, > ConfirmAmt char(1) NOT NULL, > TotUpFrontPayment money NOT NULL, > TotBearCostRS money NOT NULL, > TotWaiver money NOT NULL, > TariffService money NOT NULL, > AdminFees money NOT NULL, > DayServingCode AS (datepart(day,ServingCode)), > MthServingCode AS (datepart(month,ServingCode)), > YearServingCode AS (datepart(year,ServingCode)), > DayStruk AS (substring(StrucNo,(5),(2))), > MthStruk AS (substring(StrucNo,(3),(2))), > YearStruk AS (left(StrucNo,(2))), > AmtDue AS (case > > sign(((((PriceOne+TariffService)+AdminFees)-TotUpFrontPayment)-TotBearCostRS)-To tWaiver) > when (-1) then (0) when (0) then (0) when (1) then > > ((((PriceOne+TariffService)+AdminFees)-TotUpFrontPayment)-TotBearCostRS)-TotWaiv er > end), > AmtO AS ((JmlBarang*PriceOne+JmlService*TariffService)+AdminFees), > AmtDueGuarantor AS > > ((((CONVERT(float,PriceOne,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees ,(0)))*CONVERT(float,TotUpFrontPayment,(0)))*JmlBarang+((CONVERT(float,TariffSer vice,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,T otUpFrontPayment,(0)))*JmlService)+(CONVERT(float,AdminFees,(0))/CONVERT(float,( PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,TotUpFrontPayment,(0))), > TotORS AS > > ((((CONVERT(float,PriceOne,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees ,(0)))*CONVERT(float,TotBearCostRS,(0)))*JmlBarang+((CONVERT(float,TariffService ,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,TotBe arCostRS,(0)))*JmlService)+(CONVERT(float,AdminFees,(0))/CONVERT(float,(PriceOne +TariffService)+AdminFees,(0)))*CONVERT(float,TotBearCostRS,(0))), > TotWaive AS > > ((((CONVERT(float,PriceOne,(0))/CONVERT(float,(PriceOne+TariffService)+AdminFees ,(0)))*CONVERT(float,TotWaiver,(0)))*JmlBarang+((CONVERT(float,TariffService,(0) )/CONVERT(float,(PriceOne+TariffService)+AdminFees,(0)))*CONVERT(float,TotWaiver ,(0)))*JmlService)+(CONVERT(float,AdminFees,(0))/CONVERT(float,(PriceOne+TariffS ervice)+AdminFees,(0)))*CONVERT(float,TotWaiver,(0))), > AcceptedNo char(10) NOT NULL, > > CONSTRAINT CiPK_Table1 PRIMARY KEY CLUSTERED > > ( > MRN ASC, > PatientCode ASC, > MaterialNo ASC, > OriginalCode ASC, > ServingCode ASC, > > ConfirmAmt ASC, > AcceptedNo ASC > > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, > ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY > > ) ON PRIMARY > > At a glance on the table, there are few things that I see :- > 1. MS sql server allow user to create field based on some expression or > function which extract from other field or put some condition during > creation > of table and auto fill up it's value. > > 2. The closest thing that I can think off(in informix) is to used default > value for each field. But from my understanding default value only support > literal or some basic stuff like user,current and etc. Functions like > substring, case, day, month, year can not be used with DDL statement. > > To archived the same objective, I have to write an insert trigger which > mimic > the functionality of ms sql server. > > Can somebody enlighten me on my understanding or show me other better way > to > accomplish the task ? > > Many thanks ahead. > > Patrick > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --20cf30244ca308c00704c5a5ca7c