User-Defined SQL behaves differently in 7.5.1A

poorna_76 · Post by **poorna_76** » Thu Jul 28, 2005 2:15 pm

I have User-Defined SQL in ODBC stage.
The same query exists in 2 projects.
One with ver 7.1 version and the same job in another project with ver 7.5.1A

*************
Select CT.PARTITION_ID,
CT.AS_OF_DT,
SUBSTRING(CT.CASE_NUM,1,10) As CASE_NUM_10,
CT.CASE_REPORTED_SFX

From T3_PARTITION_CNTL PCN,
T4_CASE_TRACKING CT

Where PCN.PARTITION_ID = '91'
AND CT.PARTITION_ID = PCN.PARTITION_ID
And CT.AS_OF_DT = PCN.PARTITION_END_DT
And CT.CASE_TYPE_CD In ('ADCI', 'ADCR', 'ADCU')
--And CASE_NUM Like '123%'
And CT.CASE_REPORTED_SFX > ' '

Order By CT.CASE_NUM_10 ASC,
CT.CASE_REPORTED_SFX ASC;

**************************

When this query is executed from 7.1 ver , records are sorted by CT.CASE_NUM_10.

When the same query is executed from 7.5.1A ver , records are not sorted by CT.CASE_NUM_10.

Iam guessing,
may be in 7.5.1A, DataStage is treating everything as comment after this
(And CASE_NUM Like '123%' ) statement.

Has any one faced this issue/any thoughts?

Thanks in Advance.

kduke · Post by **kduke** » Thu Jul 28, 2005 8:45 pm

Remove the -- comment and you tell us what happens.

ray.wurlod · Post by **ray.wurlod** » Thu Jul 28, 2005 10:14 pm

The SQL statement in DataStage is a single string. So, yes, it's treating everything after the -- as comment. Remove the comment completely and it should work OK. It should always have been thus - you've been relying on a bug in the earlier version that's been fixed!

chulett · Post by **chulett** » Thu Jul 28, 2005 10:25 pm

Not really. As long as there's a return at the end of the commented string, the comment should end there. In any tool, I would think.

If it's really the case that everything from there down is now commented out in 7.5.1A, alot of the jobs that other people have written here will break as well when we upgrade. I'm not in the habit of leaving commented out code in my queries, but I know a couple of other developers on the team who seem to do it on a regular basis.

kduke · Post by **kduke** » Thu Jul 28, 2005 11:02 pm

I agree with Craig. I have done this in the past and it ignored the single line and worked as expected in PLSQL.

poorna_76 · Post by **poorna_76** » Fri Jul 29, 2005 8:14 am

kduke wrote:I agree with Craig. I have done this in the past and it ignored the single line and worked as expected in PLSQL.

Hi Kim,

Thank you all for the replies.

When i remove the comment , the records(are sorted) order is same in both versions.

But when i leave the comment as it is,
its working fine in 7.1 version.

Does not work in 7.5.1A

I agree its better to remove the comment in the query,
but do we need to contact Ascentail/IBM about this?

Thanks in Advance.

chulett · Post by **chulett** » Fri Jul 29, 2005 8:26 am

If that's the case I'd say that's a new bug, not something corrected, and it should definitely get presented to IBM/Ascential support.

I'm curious, though - not only should it not be sorted but that last constraint (And CT.CASE_REPORTED_SFX > ' ') should also not be happening. Is that true?

poorna_76 · Post by **poorna_76** » Fri Jul 29, 2005 8:40 am

chulett wrote:If that's the case I'd say that's a new bug, not something corrected, and it should definitely get presented to IBM/Ascential support.

I'm curious, though - not only should it not be sorted but that last constraint (And CT.CASE_REPORTED_SFX > ' ') should also not be happening. Is that true?

Thats true Craig.

The statements after the comment are not being executed in 7.5.1A, at all.

Thanks

ogmios · Post by **ogmios** » Fri Jul 29, 2005 10:24 am

The problem with the comments in SQL has been already in DataStage since version 4. Look in your database for the real statement being executed and you see that DataStage removes the newlines before executing the query... but does not remove the comments, hence with "-- comments" anything behind the first one does not get taken into consideration by the database.

Ogmios

chulett · Post by **chulett** » Fri Jul 29, 2005 11:16 am

I really don't think that can be true, Ogmios... at least not for us when working with Oracle via the OCI stage. Perhaps it's different when using ODBC, couldn't say as we typically don't.

There are quite a number of jobs here with comments in them, some even at the very beginning to note what is going on - if they were not working as expected I would sure hope we would have noticed by now.

ogmios · Post by **ogmios** » Fri Jul 29, 2005 12:08 pm

chulett wrote:I really don't think that can be true, Ogmios... at least not for us when working with Oracle via the OCI stage. Perhaps it's different when using ODBC, couldn't say as we typically don't.

We use OCI, DB2 and ODBC stages... since we've been bitten a few times a couple of years ago we made a "company" rule not to include comment-until-eol in any DataStage SQL. So maybe your right on OCI

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Fri Jul 29, 2005 4:35 pm

What about running the SQL directly via SQL Plus or TOAD.

poorna_76 · Post by **poorna_76** » Mon Aug 01, 2005 7:27 am

Sainath.Srinivasan wrote:What about running the SQL directly via SQL Plus or TOAD.

Hi Sainath,

We ran the same query in SQL Server - QueryAnalyser,
the results were correct.

QueryAnalyser ignores only the particular line that is commented out.
The remaining lines after that statement are executed without any issue.

Thanks

chulett · Post by **chulett** » Wed Aug 24, 2005 3:12 pm

Just wanted to followup on this as the possibility of this being a problem has been haunting me...

I have 7.5.1A installed on an HP/UX test server right now, putting things through their paces. I can find no problems when using heavily commented User Defined SQL in our weapon of choice - the OCI stage. :D

Looks like your issue may be an ODBC issue only. I don't have time to test it right now but we do have a handful of jobs that use ODBC to connect to SQL Server. At worst case, if this even becomes an issue, I'll have any comments removed.

newtier · Post by **newtier** » Fri Sep 30, 2005 12:26 pm

We had a couple surprising experiences while testing the upgrade to 7.5.1a (from version 6.x). Both were explained as changes made by an engineer without a spec to make the change. I recommend opening and issue with IBM/Ascential to let them determine the root cause. Our experiences:

1) Comments were changed so that they only support /* */ style (no longer supported the ( -- ) style - issue opened
2) "Group by" used to generate a "Where" clause. In 7.5.1a it started generating a "Having" clause.

IBM/Ascential sent a fix for the latter.