SQL for datastage

devsonali · Post by **devsonali** » Thu Jun 07, 2007 8:49 am

Hi all
I was wondering if there is anyonline documentation for SQL that goes in ODBC stages.
Thanks

kduke · Post by **kduke** » Thu Jun 07, 2007 7:42 pm

You can extract it using the Reporting Assistant. It would take me a while to hack a solution otherwise. How bad you need it?

chulett · Post by **chulett** » Thu Jun 07, 2007 9:37 pm

Perhaps we should clarify the question. Devsonali, what exactly are you seeking? What 'online documentation' are you looking for in regards to SQL in ODBC stage - what kind of SQL you can use there? Or as Kim is asking, some way to create 'online documentation' from existing SQL your jobs are using?

Please help us understand your question.

ray.wurlod · Post by **ray.wurlod** » Fri Jun 08, 2007 1:30 am

The generated SQL in an ODBC stage will be fully compliant with the grammar specified in the ODBC 3.0 standard.
User-defined SQL can be anything legal for the database, as it is passed through unchanged by the driver. However, the parameter markers must correctly bind to the columns in the Columns grid.

BIuser · Post by **BIuser** » Fri Jun 08, 2007 5:59 am

I have attempted to use an ODBC Connector and specified funtions such as CONVERT, DATEPART AND GETDATE() and i keep getting an error such as "Procedure undefined". The SQL statement runs correct in Microsoft SQL Query Analyzer but when the code is put into the ODBC Connector it doesnt recognise the funcitons.

Maybe Devonsali is having the same problem as me ?

If someone can assist me with this question too - I'd appreciate it.

Thanks
bArbara

chulett · Post by **chulett** » Fri Jun 08, 2007 6:08 am

Maybe - but when you have a question, you should start your own thread. If you think it may be related to another issue, link to the other thread, don't hijack the other person's. Please.

chulett · Post by **chulett** » Fri Jun 08, 2007 6:16 am

Ray - I'm not sure that's all true. We seem to have had postings lately where SQL that "works fine" in their favorite tool cannot be handled by the ODBC drivers. Barbara's and Mr IfThenElse for example.

And I know for a fact that the ODBC stage doesn't enforce the column binding like other stages do. Well... let me qualify. If you use parameter markers, they must be arranged so as to bind to the correct columns in the stage. I don't recall if any errors will be generated (ala OCI) or if your results will be... less than optimal.

However, it does not require that all columns be bound into the query, a fact I've taken advantage of in more than one case.

BIuser · Post by **BIuser** » Fri Jun 08, 2007 6:26 am

Sorry for "hijacking" someone else's post.
I'm new.

Will start my own thread then.