Retrive column name from the table instead of column value which is having leading space.

Database

Retrive column name from the table instead of column value which is having leading space.

I need column names from the table which is having leading space. To find out this, created stored procedure. The procedure first selecting the columns from the table whch is of char datatype then using cursor we are passing the column names and checking whether it have any leading space, if it has leading space, it should insert the column name in another temp table but the procedure inserting the column value(data) instead of column name.

Can anyone pls help me to get the column name instead of the column value which is having leading space.

Thanks in advance!

6 REPLIES
N/A

Re: Retrive column name from the table instead of column value which is having leading space.

Without knowing the source of your SP it's hard to tell what's wrong with it.

Dieter

Re: Retrive column name from the table instead of column value which is having leading space.

source of the procedure is DBName, tablename

I hard code the tablename , the proc will check for the data type of the field, if it is char then it will take the field data for validation(checking whether it has leading space or not) if it does have leading space, the procedure inserting the field name into another temp table but instead of that right now I am getting field value.

N/A

Re: Retrive column name from the table instead of column value which is having leading space.

I ment the source code, you do something wrong in your insert.

Dieter

Re: Retrive column name from the table instead of column value which is having leading space.

INSERT STATEMENT:

SET Q1 = 'INSERT INTO DLWORK_DB01.TEMP1(COLUMNNAME) SELECT(' || FLD_NM || ') FROM '|| ' DLWORK_DB01.CLOSURE_CODE' || ' WHERE ' || ' SUBSTR('||FLD_NM ||',1,1) = '|| ' " " '|| ' SAMPLE1 ' ;

N/A

Re: Retrive column name from the table instead of column value which is having leading space.

Your query should fail because you compare to " " instead of ' ' and there's no blank netween SAMPLE and 1.

Quote FLD_NM in your select, two single quotes result in one single quote, this should work:

SET Q1 = 'INSERT INTO DLWORK_DB01.TEMP1(COLUMNNAME) SELECT(''' || FLD_NM || ''') FROM '|| ' DLWORK_DB01.CLOSURE_CODE' ||
' WHERE ' || ' SUBSTR('||FLD_NM ||',1,1) = '' '' SAMPLE 1' ;

You might also switch to TOP 1 or better EXISTS, which might provide better performance.

Dieter

Re: Retrive column name from the table instead of column value which is having leading space.

Its working now. I got the column names. Thanks a lot Dieter.