SQL HELP

Posted in 1999

Abel_Johnson@doh.state.fl.us — Mon, 25 Oct 1999 18:23:18 -0400 — source: Informix-list mailing list archive (1991-1998)

This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01BF1F37.AC4CAB12 Content-Type: text/plain; charset="iso-8859-1" Using Informix V7.1 SQL, I am trying to concatenate several fields without leaving any imbedded blanks. Here is a clip of my code. insert into forsamis (confidentialcode, agencycasecode, datecaseopened, involvement , householdincome, referredfrom, dateofbirth, zipcode , gender, race, education, closingdate, closingreason) select hipr.last_name||first_name[1,1]||middle_name[1,1]||birthdate , case_number, entry_date, child_adult , household_income, referral_from, birthdate, zip_code , sex, race, educ_status , hfpr.terminate_date, hfpr.terminate_reason from hipr, hfpr where case_number = case_no and entry_date >= '07/01/1999'; The 4th line concatenates into the first variable but, the field hipr.last_name has trailing blanks which I can not seem to eliminate. What is the systax. ------_=_NextPart_001_01BF1F37.AC4CAB12 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> <HTML> <HEAD> <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = charset=3Diso-8859-1"> <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = 5.5.2650.12"> <TITLE>SQL HELP</TITLE> </HEAD> <BODY> Using Informix V7.1 SQL, I am trying = to concatenate several fields without leaving any imbedded blanks. Here = is a clip of my code. insert into forsamis     (confidentialcode, = agencycasecode, datecaseopened, involvement     , householdincome, = referredfrom, dateofbirth, zipcode     , gender, race, = education, closingdate, closingreason)     select = hipr.last_name||first_name[1,1]||middle_name[1,1]||birthdate       , = case_number, entry_date, child_adult       , = household_income, referral_from, birthdate, zip_code       , sex, = race, educ_status       , = hfpr.terminate_date, hfpr.terminate_reason     from hipr, = hfpr     where case_number = =3D case_no       and = entry_date >=3D '07/01/1999'; The 4th line concatenates into the = first variable but, the field hipr.last_name has trailing blanks which = I can not seem to eliminate. What is the systax. </BODY> </HTML> ------_=_NextPart_001_01BF1F37.AC4CAB12--

↪ replying to Abel_Johnson@doh.state.fl.us

Carlson@WHSmith — Mon, 25 Oct 1999 19:12:35 -0400 — source: Informix-list mailing list archive (1991-1998)

Wouldn't that be a use for TRIM?? Such as . . . insert into forsamis (confidentialcode, agencycasecode, datecaseopened, involvement , householdincome, referredfrom, dateofbirth, zipcode , gender, race, education, closingdate, closingreason) >>> select trim(hipr.last_name)||first_name[1,1]||middle_name[1,1]||birthdate , case_number, entry_date, child_adult , household_income, referral_from, birthdate, zip_code , sex, race, educ_status , hfpr.terminate_date, hfpr.terminate_reason from hipr, hfpr where case_number = case_no and entry_date >= '07/01/1999'; John Carlson Informix DBA WHSmith USA Abel_Johnson@doh.state.fl.us wrote: > > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > ------_=_NextPart_001_01BF1F37.AC4CAB12 > Content-Type: text/plain; > charset="iso-8859-1" > > Using Informix V7.1 SQL, I am trying to concatenate several fields without > leaving any imbedded blanks. Here is a clip of my code. > > insert into forsamis > (confidentialcode, agencycasecode, datecaseopened, involvement > , householdincome, referredfrom, dateofbirth, zipcode > , gender, race, education, closingdate, closingreason) > select hipr.last_name||first_name[1,1]||middle_name[1,1]||birthdate > , case_number, entry_date, child_adult > , household_income, referral_from, birthdate, zip_code > , sex, race, educ_status > , hfpr.terminate_date, hfpr.terminate_reason > from hipr, hfpr > where case_number = case_no > and entry_date >= '07/01/1999';> > The 4th line concatenates into the first variable but, the field > hipr.last_name has trailing blanks which I can not seem to eliminate. What > is the systax. > > ------_=_NextPart_001_01BF1F37.AC4CAB12 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> > <HTML> > <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Diso-8859-1"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2650.12"> > <TITLE>SQL HELP</TITLE> > </HEAD> > <BODY> > > Using Informix V7.1 SQL, I am trying = > to concatenate several fields without leaving any imbedded blanks. Here = > is a clip of my code. > > insert into forsamis >     (confidentialcode, = > agencycasecode, datecaseopened, involvement >     , householdincome, = > referredfrom, dateofbirth, zipcode >     , gender, race, = > education, closingdate, closingreason) >     select = > hipr.last_name||first_name[1,1]||middle_name[1,1]||birthdate >       , = > case_number, entry_date, child_adult >       , = > household_income, referral_from, birthdate, zip_code >       , sex, = > race, educ_status >       , = > hfpr.terminate_date, hfpr.terminate_reason >     from hipr, = > hfpr >     where case_number = > =3D case_no >       and = > entry_date >=3D '07/01/1999'; > > > The 4th line concatenates into the = > first variable but, the field hipr.last_name has trailing blanks which = > I can not seem to eliminate. What is the systax. > > </BODY> > </HTML> > ------_=_NextPart_001_01BF1F37.AC4CAB12--

↪ replying to Abel_Johnson@doh.state.fl.us

Wolfgang Zager — Tue, 26 Oct 1999 13:04:12 +0100 — source: Informix-list mailing list archive (1991-1998)

Abel_Johnson@doh.state.fl.us schrieb: > > This message is in MIME format. Since your mail reader does not understand > this format, some or all of this message may not be legible. > > ------_=_NextPart_001_01BF1F37.AC4CAB12 > Content-Type: text/plain; > charset="iso-8859-1" > > Using Informix V7.1 SQL, I am trying to concatenate several fields without > leaving any imbedded blanks. Here is a clip of my code. > > insert into forsamis > (confidentialcode, agencycasecode, datecaseopened, involvement > , householdincome, referredfrom, dateofbirth, zipcode > , gender, race, education, closingdate, closingreason) > select hipr.last_name||first_name[1,1]||middle_name[1,1]||birthdate > , case_number, entry_date, child_adult > , household_income, referral_from, birthdate, zip_code > , sex, race, educ_status > , hfpr.terminate_date, hfpr.terminate_reason > from hipr, hfpr > where case_number = case_no > and entry_date >= '07/01/1999';> > The 4th line concatenates into the first variable but, the field > hipr.last_name has trailing blanks which I can not seem to eliminate. What > is the systax. > > ------_=_NextPart_001_01BF1F37.AC4CAB12 > Content-Type: text/html; > charset="iso-8859-1" > Content-Transfer-Encoding: quoted-printable > > <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN"> > <HTML> > <HEAD> > <META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; = > charset=3Diso-8859-1"> > <META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version = > 5.5.2650.12"> > <TITLE>SQL HELP</TITLE> > </HEAD> > <BODY> > > Using Informix V7.1 SQL, I am trying = > to concatenate several fields without leaving any imbedded blanks. Here = > is a clip of my code. > > insert into forsamis >     (confidentialcode, = > agencycasecode, datecaseopened, involvement >     , householdincome, = > referredfrom, dateofbirth, zipcode >     , gender, race, = > education, closingdate, closingreason) >     select = > hipr.last_name||first_name[1,1]||middle_name[1,1]||birthdate >       , = > case_number, entry_date, child_adult >       , = > household_income, referral_from, birthdate, zip_code >       , sex, = > race, educ_status >       , = > hfpr.terminate_date, hfpr.terminate_reason >     from hipr, = > hfpr >     where case_number = > =3D case_no >       and = > entry_date >=3D '07/01/1999'; > > > The 4th line concatenates into the = > first variable but, the field hipr.last_name has trailing blanks which = > I can not seem to eliminate. What is the systax. > > </BODY> > </HTML> > ------_=_NextPart_001_01BF1F37.AC4CAB12-- A typical use for the TRIM-function, which was implemented in version 7.2x I think. So in 7.1 You run out of luck. Wolfgang

SQL HELP

Related threads

Your privacy choices