Selecting Special Characters 2

[bjd4jc]

I am not all that familiar with DB2. We are on version 7.2.

We have several tables that have the # sign in column names. They work fine when they are selected from a mainframe program.

However, we are trying to create SQL Stored procedures using some of these fields in the selection cursor. We are running into problems compling them because of the # sign. Is there a way around this?

I know is Access/SQL Server you can use the [] to circumvent this problem.

For example

SELECT [RECORD#] from xxx.Table

any help is appreciated.

Thanks

Brian

 

[MarcLodge]

Brian,

What's the error message(s) you are getting back?

Marc

 

[ddiamond]

try:
SELECT "RECORD#" from xxx.Table

The double quotes work on version 8.2. It may also work on version 7. I can't say for sure because I've never used version 7.

 

[bjd4jc]

Marc,

I will try to get the error messages. The DBA is the one with the issue and I don't have permissions to compile the procedures so I can't get the error myself. I am trying to help him out. The error has something to do with "illegal character" or something like that.


ddiamond,

We have tried the double quotes and they don't work.


Any other suggestions?

 

[ddiamond]

Do you have a ddl script for your table? If I can see how it was created, I may have a better idea what the problem is.

 

[bjd4jc]

It is a rather large table...

CREATE TABLE,"DBA"."VST_PATIENT_VISIT",
( "VST_OID" DBA.OID NOT NULL WITH DEFAULT
,"VST_IDENTITY" INTEGER
NOT NULL GENERATED BY DEFAULT
AS IDENTITY
( START WITH 350025)
,"VST_UPDATE_DTTM" TIMESTAMP NOT NULL WITH DEFAULT
,"VST_UPDATE_OPID" INTEGER NOT NULL WITH DEFAULT
,"VST_VERIFY_FLAG" CHAR (1) NOT NULL WITH DEFAULT
,"VST_VERIFY_DTTM" TIMESTAMP NOT NULL WITH DEFAULT
,"VST_PARTID" CHAR (1) NOT NULL WITH DEFAULT
,"VST_PROPFROM" CHAR (1) NOT NULL WITH DEFAULT
,"VST_CORP_CODE" CHAR (4) NOT NULL
,"VST_EPT_OID" DBA.OID NOT NULL
,"VST_MEDREC#" CHAR (8) NOT NULL
,"VST_VISIT#" DECIMAL (5, 0) NOT NULL
,"VST_ADD_DATE" DATE NOT NULL
,"VST_ADD_OPID" INTEGER NOT NULL
,"VST_CHECKIN_OPID" INTEGER NOT NULL
,"VST_VISIT_DTTM" TIMESTAMP NOT NULL
,"VST_TYPE" CHAR (1) NOT NULL
,"VST_STATUS" CHAR (1) NOT NULL
,"VST_DATA_STATUS" CHAR (1) NOT NULL
,"VST_DISCHARGE_DISP" CHAR (2) NOT NULL
,"VST_CHECKOUT_DISP" CHAR (2) NOT NULL
,"VST_DISCHARGE_DTTM" TIMESTAMP NOT NULL
,"VST_REVIEW_DTTM" TIMESTAMP NOT NULL
,"VST_APPT_ORIG" CHAR (1) NOT NULL
,"VST_WALKIN" CHAR (1) NOT NULL
,"VST_SECND_ACCOM" CHAR (1) NOT NULL
,"VST_APPT_TYPE" CHAR (1) NOT NULL
,"VST_TRANSPLANT_IND" CHAR (1) NOT NULL
,"VST_ADMITTED_FROM" CHAR (2) NOT NULL
,"VST_ADMITTED_THRU" CHAR (1) NOT NULL
,"VST_ADMITTING_HOSP" CHAR (1) NOT NULL
,"VST_DISCHARGE_HOSP" CHAR (1) NOT NULL
,"VST_DIVN_OID" DBA.OID NOT NULL
,"VST_SERV_OID" DBA.OID NOT NULL
,"VST_DEPT_KEY" CHAR (3) NOT NULL
,"VST_DIVN_KEY" CHAR (3) NOT NULL
,"VST_SERV_UNIT" CHAR (4) NOT NULL
,"VST_PROC_CODE" CHAR (5) NOT NULL
,"VST_EARN_CLASS" CHAR (5) NOT NULL
,"VST_DIVN_KEY_REF" CHAR (3) NOT NULL
,"VST_ADJ_REF_OID" DBA.OID NOT NULL
,"VST_ADJ_REF_VISIT#" DECIMAL (5, 0) NOT NULL
,"VST_OXYGEN_IND" CHAR (1) NOT NULL
,"VST_ISO_TYPE1" CHAR (1) NOT NULL
,"VST_ISO_TYPE2" CHAR (1) NOT NULL
,"VST_ISO_TYPE3" CHAR (1) NOT NULL
,"VST_ATTENDANT_IND" CHAR (1) NOT NULL
,"VST_HOSP_CAR_ATT" CHAR (1) NOT NULL
,"VST_HOSP_CAR_REQD" CHAR (1) NOT NULL
,"VST_APPT_NOTICE" CHAR (1) NOT NULL
,"VST_APPT_NOTICE_DT" DATE NOT NULL
,"VST_SUMMARY_TO_PAT" CHAR (1) NOT NULL
,"VST_NOSHOW_LETTER" CHAR (1) NOT NULL
,"VST_PAT_BDATE" DATE NOT NULL
,"VST_PAT_SEX" CHAR (1) NOT NULL
,"VST_PAT_COUNTY" CHAR (2) NOT NULL
,"VST_PAT_STATE" CHAR (2) NOT NULL
,"VST_PAT_ZIP" CHAR (9) NOT NULL
,"VST_PAT_INST" CHAR (2) NOT NULL
,"VST_EMER_IND" CHAR (1) NOT NULL
,"VST_EMER_DATE" DATE NOT NULL
,"VST_ACDT_STATE" CHAR (2) NOT NULL
,"VST_ACDT_WORK" CHAR (1) NOT NULL
,"VST_ACDT_AUTO" CHAR (1) NOT NULL
,"VST_ETC_TRANS" CHAR (1) NOT NULL
,"VST_ETC_AMBU" CHAR (2) NOT NULL
,"VST_ETC_VERIFY" CHAR (1) NOT NULL
,"VST_OID_XREF" DBA.OID NOT NULL
,"VST_VISIT#_XREF" DECIMAL (5, 0) NOT NULL
,"VST_MEDREC#_XREF" CHAR (8) NOT NULL
,"VST_OLD_STATUS" CHAR (1) NOT NULL
,"VST_TEXT_TAG" INTEGER NOT NULL
,"VST_CG_LIST" CHAR (1) NOT NULL
,"VST_I1260_OID" DBA.OID NOT NULL WITH DEFAULT
,"VST_IDX_VISIT#" INTEGER NOT NULL WITH DEFAULT
,"VST_ADMIT_REASON" VARCHAR (100) NOT NULL WITH DEFAULT

,"VST_I1204_OID" DBA.OID NOT NULL WITH DEFAULT
,"VST_ADMIT_SERV_OID" DBA.OID NOT NULL WITH DEFAULT
,"VST_I1206_OID" DBA.OID NOT NULL WITH DEFAULT
,"VST_I1236_OID" DBA.OID NOT NULL WITH DEFAULT
,"VST_EPISODE" CHAR (15) NOT NULL WITH DEFAULT
,"VST_I1313_OID_FROM" DBA.OID NOT NULL WITH DEFAULT
,"VST_I1313_OID_TO" DBA.OID NOT NULL WITH DEFAULT
,"VST_CERNER_CLIENT#" CHAR (5) NOT NULL WITH DEFAULT
,"VST_DISCH_SERV_OID" DBA.OID NOT NULL WITH DEFAULT
,"VST_EPISODE_CARE" VARCHAR (18) NOT NULL WITH DEFAULT
,"VST_CG_CONFIRMED" CHAR (1) NOT NULL WITH DEFAULT
'
,"VST_DIR_RESTRICT" CHAR (1) NOT NULL WITH DEFAULT
'
,"VST_IN_BED_DTTM" TIMESTAMP NOT NULL WITH DEFAULT
,"VST_ADMIT_DTTM" TIMESTAMP NOT NULL WITH DEFAULT
,PRIMARY KEY
("VST_OID"
)
)
WITH RESTRICT ON DROP
IN "HPTHPTD2"."HPTVS2TS"
AUDIT NONE;
LABEL ON TABLE "DBA"."VST_PATIENT_VISIT"
IS 'HPTVSTTB';
LABEL ON COLUMN "DBA"."VST_PATIENT_VISIT"."VST_OID"
IS 'A6';
LABEL ON COLUMN "DBA"."VST_PATIENT_VISIT"."VST_IDENTITY"
IS 'IDENTITY';
LABEL ON COLUMN "DBA"."VST_PATIENT_VISIT"."VST_TEXT_TAG"
IS 'TXT_TAG';
COMMIT;

 

[ddiamond]

I don't see the field RECORD#.

 

[bjd4jc]

Sorry, that was only for example. The actual field in question is VST_IDX_VISIT# or any field that contains a #. I was only giving an example in my first post.

 

[ddiamond]

We have tried the double quotes and they don't work.

Can you post that query that didn't work?

 

[bjd4jc]

SELECT "VST_VISIT#" INTO "OUT_IPR_VISIT#" FROM DBA.VST_PATIENT_VISIT WHERE "VST_IDX_VISIT#"="IN_VISIT#"

we have tried this and several variations of it that I didn't save.

Development center brings back an error that I don't have access to. I made a request to get the exact error message.

 

[MarcLodge]

Brian,
As you have found, there is not a problem with having a # in a column name. Many DBAs use this as shorthand for 'number'. Being a Brit, I've never seen that in tables over here as I think we tend to use 'no', 'num', 'nbr', 'qty' etc.

I'm going to guess therefore that it is not SQL that is objecting to it, but the language that the Stored Procedure is written in. Can you tell me what platform and language the SP is being developed and compiled on.

Marc

 

[bjd4jc]

We are using SQL only stored procedures. They are being compiled by DB2 Development Center from our WinXP workstations. We were wondering if it might be a bug or a shortcoming of that.

I don't have a lot of knowledge/experience with DB2 stuff and we are all just getting started with creating stored procedures here. We have not had any problems with them until we needed this specific procedure.

 

[ddiamond]

I think I see the problem.

SELECT "VST_VISIT#" INTO "OUT_IPR_VISIT#" FROM DBA.VST_PATIENT_VISIT WHERE "VST_IDX_VISIT#"="IN_VISIT#"

"OUT_IPR_VISIT#" must be a variable defined in your stored procedure. That is how select into works. If you are trying to update the field "OUT_IPR_VISIT#" you must use an update statement.

 

[bjd4jc]

Well, we have tried all of the variations of adding/removing quotes. I found out the problem is with the precompiler. I guess the stored procedures need to be precompiled into C code and then compiled and put in a library somewhere to be used.

We didn't figure out how to get around it directly, however the DBA just kind of gave up and decided to create a view that "wraps" the field into a different name for the sake of the stored procedure. So the view selects the VST_IDX_VISIT# field as VST_IDX_VISIT and then the procedure uses the VST_IDX_VISIT field. Seems like a hokey solution but it will work until we can upgrade to version 8.

Thanks for all your ideas.

Brian