Yikes..please help!

[VonDobsky]

Hello all, this is definitely not for the faint hearted.
Please have it at, I am definitely trying to figure this out right now. Someone did this from 7 into 8 and decided to just copy SQL into Report Studio, rename the queries and didn't validate a thing. He's since quit..so now I am attempting to figure out his 4 reports..this is one of four.   :P

Thanks so much...


select CAST(SUBSTR("CFCSCCHLINEHIST"."ACTIVITYDATE", 5 , 2) AS INTEGER), (CURRENT DATE + -30 DAY), "CFCSREGIONZONE"."CALL_CENTER_NAME", "CFCSRTRUSER_VI"."C_USER", rtrim("CFCSRTRUSER_VI"."C_USER_FIRST") || ' ' || rtrim("CFCSRTRUSER_VI"."C_USER_LAST"), "CFCSCCHLINEHIST"."ACCOUNTNMBR", "CFCSCCHLINEHIST"."CASSCATEGORYCODE" || "CFCSCCHLINEHIST"."QUEUECODE", "CFCSACCT"."DELINQUENCYCATEGORYCODE" || "CFCSACCT"."WORKLISTQUEUECODE", "CFCSCCHLINEHIST"."ACTIVITYDATE", ( DAYS( CURRENT DATE ) - DAYS( "CFCSACCT"."REPODATE" ) ), "CFCSACCT"."REPODATE", "CFCSCCHLINEHIST"."LETTERCODE"
from (("Z"."CFCSACCT" "CFCSACCT" inner join "Z"."CFCSREGIONZONE" "CFCSREGIONZONE" on "CFCSACCT"."BRANCHNMBR" = "CFCSREGIONZONE"."BRANCH") inner join "Z"."CFCSCCHLINEHIST" "CFCSCCHLINEHIST" on "CFCSACCT"."ACCOUNTNMBR" = "CFCSCCHLINEHIST"."ACCOUNTNMBR") LEFT OUTER JOIN "Z"."CFCSRTRUSER_VI" "CFCSRTRUSER_VI" on "CFCSCCHLINEHIST"."LOGINIDNMBR" = "CFCSRTRUSER_VI"."C_USER"
where left("CFCSCCHLINEHIST"."LETTERCODE", 4) in ('3500', '3501', '6169', '6499', '4051', '3516', '3520', '3502', '3504', '3512', '5879', '3529', '3524', '4423', '3506', '3518', '3514', '3510', '3879', '3527', '3517', '3521', '3503', '3505', '3513', '5881', '3525', '3526', '4424', '3507', '3511', '3515', '3880', '3528', '7114', '6998', '7000', '7001', '4110', '6471', '6473', '6465', '6466', '6469', '6480', '6477', '6475', '6479', '6467', '6472', '6470', '6468', '6478', '6680', '6476', '6500', '7642', '7643', '6501') and "CFCSCCHLINEHIST"."CASSCATEGORYCODE" <> 'R' and "CFCSCCHLINEHIST"."PLACECALLEDCODE" = 'H' and "CFCSCCHLINEHIST"."ACTIVITYCODE" = 'LN' and "CFCSREGIONZONE"."CALL_CENTER_NAME" = ? and (( DAYS( CURRENT DATE ) - DAYS( "CFCSACCT"."REPODATE" ) ) > 0 or "CFCSACCT"."REPODATE" is null and "CFCSACCT"."DISPOSALREDEEMDATE" is null) FOR FETCH ONLY

[blom0344]

This is native DB2 SQL I can tell you. I am a former DB2 DBA and I'd like to help you out once I know what the question is   ;D

[VonDobsky]

Okay..thanks. The question is that it does not run..getting a sqlscroll -232 error. It's an Automated Repo Report where it reports on notices sent out within the past 30 days.

Report works GREAT when you open it in Impromptu Administrator, but when you login into Series7 server, it will NOT work in Upfront. And obviously the same thing in Cognos 8...I had someone refresh the ReportSet for me on Production server an hour ago...but it did not help.

Anyone run into this before?? I can send the calculations/filters if that helps...it's really  not that complicated of a report, just odd how it's behaving.

-Thanks

[blom0344]

As I wrote , this is native DB2 SQL (amongst others because of the for fetch only clause and days function). If you want this to work in Cognos 8 then you need to use this as the basis of a C8 query. The query obviously is using  a prompt as well  (?) and you need to work in the proper prompt C8 style.

So , look into working with native SQL and how to define prompts. The report itself is not the issue, it is the query you need to work on..

[VonDobsky]

DB2 SQL..okay..I have noticed in the data items there are functions such as DayName(.. and Cast_abc( and I know those DB2 specific functions as well. I am currently working on the query..Can you explain why this works now in Upfront and not in C8? Did Impromptu support DB2 sql before?

[blom0344]

When publishing a C8 package you need to make sure that database specific functions are made available for the specific database..

[VonDobsky]

Hi
Yup all is well...report is finished. It was a few of the calculations it didn't understand..
Thanks for your help..'preciate it.