Trouble declaring variables
wheresjim, used to MS SQL Server, wanted to add constant text columns to a read-only Informix query without a stored procedure. Art S. Kagel said to simply select the literals with column aliases, and noted statement-local variables are only in recent releases and to post the version. wheresjim confirmed it worked. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I usually use MS SQL Server, but am working on a project where I also
have to integrate with a legacy Informix box. I do not have write
priveleges to the Informix box, just read only, so I cannot create a
stored procedure. Basically what I want to do is insert text into my
query results (I know, it would be better to do this in the handling
code on the back end, but I want to see if this can be done). The code
I use for MSSQL is this:
DECLARE @customstr2 CHAR(4)
SELECT @customstr2 = '.tif'
DECLARE @customstr4 CHAR(40)
SELECT @customstr4 = 'some other text'
SELECT imageid, patid, datecreated, lname, facname, @customstr2 as
customstr2, @customstr4 as customstr4 from imagetable whereimgindex='12345'
I have done some searching and have found that the sql code for
infomrix may look like this, but it doesn't work:
DEFINE CHAR customstr2[4] = ".tif";
DEFINE CHAR customstr4[40] = "some other text";
SELECT imageid, patid, datecreated, lname, facname, customstr2 as
customstr2, customstr4 as customstr4 from imagetable whereimgindex='12345'
Any hints?
wheresjim wrote:
You're working too hard:
SELECT imageid, patid, datecreated,lname, facname, '.tif' as customstr2,
'some other text' as customstr4
from imagetable
where imgindex = '12345';
Also statement local variables in Informix are only supported in the latest
releases, which your server may not be... (it always helps to post version
information if you can get it: SELECT dbinfo('version','full') from
systables where tabid = 1; )
Art S. Kagel
> I usually use MS SQL Server, but am working on a project where I also
> have to integrate with a legacy Informix box. I do not have write
> priveleges to the Informix box, just read only, so I cannot create a
> stored procedure. Basically what I want to do is insert text into my
> query results (I know, it would be better to do this in the handling
> code on the back end, but I want to see if this can be done). The code
> I use for MSSQL is this:
>
> DECLARE @customstr2 CHAR(4)
> SELECT @customstr2 = '.tif'
> DECLARE @customstr4 CHAR(40)
> SELECT @customstr4 = 'some other text'
> SELECT imageid, patid, datecreated, lname, facname, @customstr2 as
> customstr2, @customstr4 as customstr4 from imagetable where> imgindex='12345'
>
> I have done some searching and have found that the sql code for
> infomrix may look like this, but it doesn't work:
>
> DEFINE CHAR customstr2[4] = ".tif";
> DEFINE CHAR customstr4[40] = "some other text";
> SELECT imageid, patid, datecreated, lname, facname, customstr2 as
> customstr2, customstr4 as customstr4 from imagetable where> imgindex='12345'
>
> Any hints?
>
Gahh! You know it's funny because that's the intuitive way to do this, but it didn't work when I tried it on MS SQL Server. Thanks!
wheresjim wrote: > Gahh! > > You know it's funny because that's the intuitive way to do this, but it > didn't work when I tried it on MS SQL Server. Using SQLServer was your first mistake! ;-) Art S. Kagel