SQL not working

[us1aslam1us]

Hi all,

Here is something unusual with me.I am working on Sybase OC to pull data from database and just making a straight move into a seq file.

Here is my SQl, whenever i am using this in the user-defined query in syabase OC,I am getting this warning.Can anybody help me with this.


SQL:

SELECT DISTINCT
ft.SBSB_CK AS IDSB_SBSB_CK ,
ft.SBSB_ID AS IDSB_SBSB_ID,
ft.GRGR_CK AS IDSB_GRGR_CK,
ft.GRGR_ID AS IDSB_GRGR_ID ,
ft.GRGR_NAME AS IDSB_GRGR_NAME,
ft.SGSG_CK AS IDSB_SGSG_CK,
ft.SGSG_ID AS IDSB_SGSG_ID,
ft.MEME_SFX AS IDSB_MEME_SFX,
ft.CSPD_CAT AS IDSB_CSPD_CAT,
ft.CSPI_ID AS IDSB_CSPI_ID,
Substring(ltrim(rtrim(ft.SGSG_NAME)),1,20) AS IDSB_SGSG_NAME,
fg.CICI_ID AS IDSB_CICI_ID,
ft.CSCS_ID AS IDSB_CSCS_ID,
MAX(ft.MEIA_REQ_DT) AS IDSB_MEIA_REQ_DT,
it.CSPI_ITS_PREFIX AS IDSB_PDPD_MCTR_CTYP,
fa.SBAD_ADDR1 AS IDSB_ADDR1,
fa.SBAD_ADDR2 AS IDSB_ADDR2,
fa.SBAD_ADDR3 AS IDSB_ADDR3,
fa.SBAD_CITY AS IDSB_CITY,
fa.SBAD_STATE AS IDSB_STATE,
fa.SBAD_ZIP AS IDSB_ZIP,
fa.SBAD_COUNTY AS IDSB_COUNTY,
fa.SBAD_COUNTRY AS IDSB_COUNTRY,
fg.GRGR_ORIG_EFF_DT AS ORIG_EFF_DT

FROM
CDS_IDIN_IN_INDIC ft,
CMC_GRGR_GROUP fg,
CMC_SBSB_SUBSC fs,
CMC_SBAD_ADDR fa,
CMC_CSPI_CS_PLAN it

WHERE
ft.GRGR_CK=fg.GRGR_CK
AND fs.SBSB_CK=ft.SBSB_CK
AND fa.SBSB_CK=ft.SBSB_CK
AND ((ft.MEIA_REQ_DT BETWEEN fg.GRGR_ORIG_EFF_DT AND fg.GRGR_TERM_DT)
OR (ft.MEIA_REQ_DT>=fg.GRGR_ORIG_EFF_DT AND
ft.MEIA_REQ_DT>=fg.GRGR_RNST_DT))
AND fs.SBAD_TYPE_MAIL=fa.SBAD_TYPE
AND ft.GRGR_CK=it.GRGR_CK
AND CSCS_ID=it.CSCS_ID
AND ft.CSPD_CAT=it.CSPD_CAT
AND ft.CSPI_ID=it.CSPI_ID
AND (ft.MEIA_REQ_DT BETWEEN it.CSPI_EFF_DT AND it.CSPI_TERM_DT);


here is the warning:

WARNINGS 1:

TEMP_ID_INDIC..Sybase_OC_0: Sybase Server warning 208 (severity 16): CDS_IDIN_IN_INDIC not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

TEMP_ID_INDIC..Sybase_OC_0: ct_results has fetched an error

SQL statement:

SELECT DISTINCT

IDSB_SBSB_CK=ft.SBSB_CK,

IDSB_SBSB_ID=ft.SBSB_ID,

IDSB_GRGR_CK=ft.GRGR_CK,

AND THE WHOLE QUERY IS CONTINUED

Attempting to Cleanup after ABORT raised in stage TEMP_ID_INDIC..Sybase_OC_0





THANKS

SAM

[chulett]

One option would be to not do this as User Defined SQL and see if it works. Other than that, there are a couple of things you could try for grins.

One would be to remove the whitespace, the blank lines before the FROM and the WHERE clauses. I've seen times where simple stuff like that can 'confuse' things. You may also need to remove the trailing semi-colon, don't know much about Sybase but Oracle doesn't want want you to include it.

Also double-check that the number of columns in your query are also defined on the Columns tab in the stage - they have to match exactly and it's easy to miss one when working with a big hunk of User Defined fun like that one.

[kcbland]

CDS_IDIN_IN_INDIC not found. Specify owner.objectname or use sp_help to check whether the object exists (sp_help may produce lots of output).

This is a big clue. It even tells you how to troubleshoot it. I guess I can help out here and suggest that you verify the spelling, because the message indicates SYBASE (Sorry You Bought A Slow Engine) doesn't recognize the name. Verify that the owner.objectname is valid. Maybe you need to specify the owner name.

[chulett]

Be curious if that's the case. Even though it wasn't said, I was working under the assumption that the SQL "runs fine in TOAD" or whatever the equivalent is for Sybase, and this was specifically a User-Defined SQL issue.

[us1aslam1us]

Be curious if that's the case. Even though it wasn't said, I was working under the assumption that the SQL "runs fine in TOAD" or whatever the equivalent is for Sybase, and this was specifically a User-Defined SQL issue.

Hi chu,

I changed it to generated SQL and now it is working fine ,Now i only need to get the max of timestamp in the transformer.And now i am unable to use max function. any otherway around.

Thanks

[ketfos]

Hi,
Why are you not able to use max function? Any reason or error you get.

Ketfos

[ray.wurlod]

There is no "max function in a Transformer". If you built your expressions using the Expression Editor you would realize this.

You can form a maximum value in an Aggregator stage, or in SQL from a database table.