CR10 on Remedy 6.3

[thisisboni]

Hi,

I am on CR 10 , Remedy 6.3 (Oracle 9i dB back end) ... I am trying to use the remedy odbc driver for conversions of 'zulu' time (Epoch!!) and other drop down stuff !
The problem is - when I am reporting off 2 tables having any common field name (like create_date etc...) I get the following error

Failed to Open Rowset
Details:42sriver]column found in multiple tables:


Can anyone assist me how to get around this thingie please ? please let me know if more info is needed

Thanks
Sam

 

[satinsilhouette]

Just guessing, but the two tables aren't linked together? You may need to alias one table or alias field names.

Thanks so much!
satinsilhouette

 

[thisisboni]

Hi satinsilhouette,

tx for the reply -- but what do u mean when say 'alias field names' ?
The tables are linked as inner joins but when I pull the query from CR - I get them as a 'where' clause and not 'INNER JOIN' in the 'FROM' !

thanks,
Samik

 

[satinsilhouette]

Could you paste your sql quer from CR?

Thanks so much!
satinsilhouette

 

[thisisboni]

here we go -->

SELECT DISTINCT "AST_Asset"."Asset ID+", "AST_Asset"."Name", "AST_Asset"."Create-date", "SHR_BMS_Attachments"."Attachment ID"
FROM "AST:Asset" "AST_Asset", "SHR:BMS:Attachments" "SHR_BMS_Attachments"
WHERE ("AST_Asset"."Asset ID+"="SHR_BMS_Attachments"."Case ID") AND "AST_Asset"."Create-date">={ts '2006-01-01 00:00:00'}


--tx
Sam

 

[satinsilhouette]

Your from statement does not have any join at all. And from the looks of the query this is a crystal query, so go into Database Expert and click on the link tab and see if the two tables have a link between them.

Thanks so much!
satinsilhouette

 

[thisisboni]

yup the 'asset_id+' from 'ast_asset' table is linked (link options --> Join Type : Inner Join, Enforce Join : Not Enforced , Link Type : = ) to the 'Case ID' of the 'SHR_BMS_Attachments' table

tx,
Sam

 

[satinsilhouette]

But I am not seeing it come through in your SHOW SQL QUERY you posted.

Thanks so much!
satinsilhouette

 

[thisisboni]

thats exactly what I am after - could it be an issue with the rememdy ODBC driver (settings etc..) ? I am currently running 2 identical reports - one using native Oracle driver and the other Remedy ODBC driver - will post the queries up once done

tx,
Sam

 

[satinsilhouette]

k

Thanks so much!
satinsilhouette

 

[thisisboni]

k here is the prob now!

its 2 views I am reporting off:

Using the native Oracle drivre its fine and found below is the qry from CR

SELECT DISTINCT "AST_ASSET"."CREATE_DATE", "HPD_HELPDESK"."ASSET_ID_3", "AST_ASSET"."ASSET_ID_"
FROM "ARADMIN"."AST_ASSET" "AST_ASSET" INNER JOIN "ARADMIN"."HPD_HELPDESK" "HPD_HELPDESK" ON "AST_ASSET"."ASSET_ID_"="HPD_HELPDESK"."ASSET_ID_3"
WHERE "AST_ASSET"."ASSET_ID_"='000000000049686'

Using Remedy ODBC Driver I get the error

Failed to Open Rowset
Details:42sriver]column found in multiple tables:


the qry using Remedy ODBC is

SELECT DISTINCT "AST_Asset"."Create_date", "HPD_HelpDesk"."Asset_ID_3", "AST_Asset"."Asset_ID_"
FROM "AST_Asset" "AST_Asset", "HPD_HelpDesk" "HPD_HelpDesk"
WHERE ("AST_Asset"."Asset_ID_"="HPD_HelpDesk"."Asset_ID_3") AND "AST_Asset"."Asset_ID_"='000000000049686'

The links etc.. are exact on the 2 reports

tx,
Sam

 

[satinsilhouette]

Looks like the Remedy ODBC driver doesn't support that linkage. Can you stick with the native driver? Or get the most recent dll for Remedy. And possibly report this anomoly to Remedy.

Thanks so much!
satinsilhouette

 

[thisisboni]

hmmm yeah looks like I need to do something in those lines -- the main reason for using the Remedy ODBC driver is that it does all those conversions for you ! (time, drop fown fields etc.. ) !!

tx anyways,
Sam

 

[RachaelD]

Failed to open rowset" when I'm reporting from a Remedy database usually means I haven't qualified the data yet.

Also, it's been my experience that Remedy doesn't allow for table joining. If you need information from two different Remedy tables you will probably need the Remedy Developer to do a join within the database that you can report against.