DSXchange

Can any one help to resolve this error?
SQLSTATE=S1000, DBMS.CODE=950088
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected verb. Token was "SELECT". Scanned command was SELECT |

This job worked fine previously and i am trying to run after re-indexing Datastage!![size=12][/size]

Welcome aboard.

If the query parser is complaining about SELECT then there is something wrong with the table name in your UV stage. Can you please post the entire error message?

DSD.BCIOpenRef call to SQLPrepare failed.
Statement was:SELECT column1,column2,column3,column4 FROM "abc"
WHERE
column1 = :1
AND
((column2 = 0 AND column3 =0)
OR
( column3 <= :2 AND column2 > :3))
SQLSTATE=S1000, DBMS.CODE=950088
[DataStage][SQL Client][UNIVERSE]DataStage/SQL: syntax error. Unexpected verb. Token was "SELECT". Scanned command was SELECT |

This query is in reference link and the table name using is "bcd" for an example.

I need you to post the dictionary listing of the UV table. To get this, execute the command Either the table name itself, or one or more of the column names (or both), just isn't working.

This is my TableDefinition

<a href="http://s290.photobucket.com/albums/ll26 ... leDict.jpg" target="_blank"><img src="http://i290.photobucket.com/albums/ll26 ... leDict.jpg" border="0" alt="Photobucket"></a>

There's no columns there called "column1", "column2", etc. Like with any other database you must use the column names defined for the table. Also, these must coincide in number, order and data type with the columns defined in your job design. If you import the table definition into DataStage, then the UV stage into which you load that imported table definition will generate correct SQL.

Sorry for that!!

The column names are column1,column2,column3,column4 resp/-

First 3 columns are primary key field

The design is we are generating the hash file in 1 job and in another job using UniVerse stage we are invoking the same hash file.

I am able to read the data but not able to run the job!!!
Here it goes with the error message and table structure.
/* Error
BCIOpenRef call to SQLPrepare failed.

Are the columns in precisely the same order in all jobs and all stages? Navigation in hashed files is by position, not by column name. This includes the order within the key column.

Note: correct terminology is "hashed" file, not "hash" file.

What columns have you defined in the job that is executing this SQL? There must be three columns marked as Key - this is NOT the primary key but, rather, the search key. These three columns (in their order) will provide the three values with which to replace the three parameter markers in the WHERE clause. These values will be provided from the Key Expression field in the Transformer stage.

The table whose file dictionary you listed was "ResidentialMaturityCode" yet the name you give in the SQL query is "pilresidmatlovmap". I daresay that the query parser is complaining about a non-existent table name. This is borne out by the fact that it gave up parsing immediately after the SELECT token - it had not found a valid "FROM" clause from which to select.

But my hash file which i created is pilresidmatlovmap

So what is the file dictionary for THAT ?

Previously my design, created the hash file via UniVerse stage and also accessed the hash file via Universe stage.For this we needed some permisions to run the job.
So,next we tried to change the design as the one specified as above which is running in some other environment.

May be i am not clear about using the UniVerse stage.Thats why not able to debug!!
But my hash file which i created is pilresidmatlovmap.

I also ran with the option by giving the hash file name as the table name and ran one more time by removing the table name which no longer exists now as because the table name was created previously with the older design as part of hash file creation.

Still the same error persists!!!

You are very unclear about what has happened, what is happening and what will happen. Please answer the following questions.

• 1. Is the hashed file still created? If so, what is the name of that hashed file?
  
  2. Is there a VOC pointer to it in the project (either because the hashed file was created in the account or because a SETFILE command has been executed)? If so, what is the name of that VOC pointer?
  
  3. Please provide a file dictionary listing of the hashed file pilresidmatlovmap.

Yes ..Its been created in the name 'pilresidmatlovmap'

That leaves questions 2 and 3 to be answered.