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!
Without knowing the source of your SP it's hard to tell what's wrong with it.
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.
SET Q1 = 'INSERT INTO DLWORK_DB01.TEMP1(COLUMNNAME) SELECT(' || FLD_NM || ') FROM '|| ' DLWORK_DB01.CLOSURE_CODE' || ' WHERE ' || ' SUBSTR('||FLD_NM ||',1,1) = '|| ' " " '|| ' SAMPLE1 ' ;
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.