Data Stage 7.5 -SQL statement not updated in SQL Tab

ketfos · Post by **ketfos** » Tue Aug 17, 2004 5:07 pm

Hi ,
I have a job.
OracleOCi8 ---- Transformer --- OracleOCi8
In the input stage, I select Fully generated SQL Query in Query type list box. Then I click Build.
I get Build Fully Genrated Query WIndow.
I select my table and click on Select to select columns.
I hit the query tab in above screen to view the query.

(select name from customer)
Then I hit Ok to close this screen.
Then I hit VIew Data and can correctly view it.

Now I want to change column name from name to email.

I go to OracleOCi8 stage.
Select the column tab. Click on Load tab.
Select column name email from list. Click on View Data.
(select email from customer)
It shows the data correctly.

When I click on SQL tab to view my SQL, it is old sql before i made the change.
(Select name from customer)

HAs anybody encountered this in DS 7.5?

Ketfos

ray.wurlod · Post by **ray.wurlod** » Tue Aug 17, 2004 8:12 pm

There has been a long-term glitch in some of the controls used in DataStage clients.

I always make sure that I don't click OK while still positioned in any field where I've made a change - always move to a different row of a grid (it's the LostFocus event the triggers the change back to the design), or move to any other tab than where I've just made a change.

This may not be the workaround you need, but at least it won't take long to try. :D

ketfos · Post by **ketfos** » Wed Aug 18, 2004 10:34 am

Hi,
I tried as suggested by Ray. It didnot make any difference.
Then I saved my changes and closed the job
Then I opened this job again.
When I view the data, I get the correct records based upon the changes in the column selections I had done.
But when I review the SQL statement, it still reflects the old SQL statement.

Ketfos

Mike · Post by **Mike** » Wed Aug 18, 2004 5:55 pm

I think the only way to update the SQL statement in a fully-generated query is to go back into the GUI interface by hitting the build button again. After you make changes to the query within the GUI, you can then re-sychronize the column grid to the modified query.

I think you need to use a column-generated query if you want the query to change based on changes in the column grid.

Mike

chulett · Post by **chulett** » Wed Aug 18, 2004 7:36 pm

I think you are correct, Mike. The "Fully Generated Query" option is to be avoided, in my opinion. It's only there because they needed to say they could provide that functionality, even though it is a PITA to use. As you said, you can't simply modify a portion of your query, you have to rebuild the entire thing to incorporate any changes.

I preach the use of Column Generated SQL and sparingly fall back to Custom SQL and then only if absolutely required.

tonystark622 · Post by **tonystark622** » Thu Aug 19, 2004 7:16 am

I preach the use of Column Generated SQL and sparingly fall back to Custom SQL and then only if absolutely required.

Amen, brother Craig!

datastage · Post by **datastage** » Thu Aug 19, 2004 8:45 am

another agreeer here: stick with column generated; try to avoid fully generated; don't be afraid of using user-defined, but choose column generated over user defined when you have a choice.