 |
|||||
|
As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Join the TIBCO Community
Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community. | |||||
Read-Only TopicGo | Search | Notify | Tools |
| [SOLVED] SQL REPORT CODE PROBLEM | Login/Join |
| Guru |
I am writing a SQL report using SQL code. For some reason when I TEST SQL I see the HTML page with the message waiting for respons. I am not able to pass following SQL. When I use metadata from the same SQL connection, I have no problem writing the reports. When I run this code, my sql profiler doesnt show me the procecss with the code either. I have a feeling that IBI is not passiing the sql to the SQL. Is there any command or option I need to select to pass the following SQL ENGINE SQLMSS SET DEFAULT_CONNECTION SUGARCRM
SQL SQLMSS PREPARE SQLOUT FOR
select
l.date_entered,
'"' + case when Len(l.id)=6 then '2- Web Tour Request'
when Len(l.created_by)=4 then '3- School Entered'
else '1- Inbound Calls' end + '"' channel,
'"' +lc.brand_dd_c+ '"' brand,
rtrim(year(l.date_entered-.2083)) + right('0' + rtrim(month(l.date_entered-.2083)),2) + right('0' + rtrim(day(l.date_entered-.2083)),2) date_entered,
'"'+u2.user_name+'"' modified_by,
'"' + u2.last_name + '"' modified_by_lastname,
'"' + u2.first_name+ '"' modified_by_firstname,
rtrim(year(l.date_modified-.2083)) + right('0' + rtrim(month(l.date_modified-.2083)),2) + right('0' + rtrim(day(l.date_modified-.2083)),2)
date_modified,
l.id lead_id,
l.last_name lead_lastname,
l.first_name lead_firstname,
'"' + l.primary_address_street+ '"' street,
'"' + l.primary_address_city+ '"' city,
'"' + upper(l.primary_address_state)+ '"' statecode,
'"' + l.primary_address_postalcode+ '"' postalcode,
l.phone_home,l.phone_mobile,l.phone_work,l.phone_other,
'"' + l.status+ '"' lead_status,
'"' +e.email_address+ '"' email_address,
rtrim(year(lc.statuschange_date_c)) + right('0' + rtrim(month(lc.statuschange_date_c)),2) + right('0' + rtrim(day(lc.statuschange_date_c)),2)
statuschange_date_c,
c.children,
a.schools,
'"' + CONVERT ( nvarchar(30) ,a2.school,4)+'"' school,
p.id prospect_id,
'"'+lead_source+'"' lead_source,
m.meetings
from leads l
join leads_cstm as lc on l.id = lc.id_c
left outer join users as u2 on u2.id = l.modified_user_id
left outer join (select leads_lcg_87ccenleads_ida clead_id, count(1) children
from leads_lcg_children_c
group by leads_lcg_87ccenleads_ida) as c on c.clead_id = l.id
left outer join (select leads_accofd41tsleads_ida alead_id, count(1) schools, min(id) minid
from leads_accounts_c
group by leads_accofd41tsleads_ida) as a on a.alead_id = l.id
left outer join (select id, leads_accoa89cccounts_idb school
from leads_accounts_c) as a2 on a2.id = a.minid
left outer join (select bean_id,email_address
from email_addr_bean_rel,email_addresses
where email_addresses.id = email_addr_bean_rel.email_address_id
and email_addr_bean_rel.primary_address=1
and email_addr_bean_rel.deleted='0') as e on e.bean_id = l.id
left outer join (select lead_id, id
from prospects
where prospects.lead_id is not null) as p on p.lead_id = l.id
left outer join (select lead_id, count(meeting) meetings
from (select meeting_id, lead_id, 1 meeting
from meetings_leads
group by meeting_id, lead_id) as m2
group by lead_id) as m on m.lead_id = l.id
where l.deleted = '0'
and (
l.date_entered-.2083 between cast(getdate() as numeric)- 3 and cast(getdate() as numeric)- 2
or
lc.statuschange_date_c = cast(getdate() as numeric)- 2
)
order by l.date_entered-.2083
select
l.date_entered,
'"' + case when Len(l.id)=6 then '2- Web Tour Request'
when Len(l.created_by)=4 then '3- School Entered'
else '1- Inbound Calls' end + '"' channel,
'"' +lc.brand_dd_c+ '"' brand,
rtrim(year(l.date_entered-.2083)) + right('0' + rtrim(month(l.date_entered-.2083)),2) + right('0' + rtrim(day(l.date_entered-.2083)),2) date_entered,
'"'+u2.user_name+'"' modified_by,
'"' + u2.last_name + '"' modified_by_lastname,
'"' + u2.first_name+ '"' modified_by_firstname,
rtrim(year(l.date_modified-.2083)) + right('0' + rtrim(month(l.date_modified-.2083)),2) + right('0' + rtrim(day(l.date_modified-.2083)),2)
date_modified,
l.id lead_id,
l.last_name lead_lastname,
l.first_name lead_firstname,
'"' + l.primary_address_street+ '"' street,
'"' + l.primary_address_city+ '"' city,
'"' + upper(l.primary_address_state)+ '"' statecode,
'"' + l.primary_address_postalcode+ '"' postalcode,
l.phone_home,l.phone_mobile,l.phone_work,l.phone_other,
'"' + l.status+ '"' lead_status,
'"' +e.email_address+ '"' email_address,
rtrim(year(lc.statuschange_date_c)) + right('0' + rtrim(month(lc.statuschange_date_c)),2) + right('0' + rtrim(day(lc.statuschange_date_c)),2)
statuschange_date_c,
c.children,
a.schools,
'"' + CONVERT ( nvarchar(30) ,a2.school,4)+'"' school,
p.id prospect_id,
'"'+lead_source+'"' lead_source,
m.meetings
from leads l
join leads_cstm as lc on l.id = lc.id_c
left outer join users as u2 on u2.id = l.modified_user_id
left outer join (select leads_lcg_87ccenleads_ida clead_id, count(1) children
from leads_lcg_children_c
group by leads_lcg_87ccenleads_ida) as c on c.clead_id = l.id
left outer join (select leads_accofd41tsleads_ida alead_id, count(1) schools, min(id) minid
from leads_accounts_c
group by leads_accofd41tsleads_ida) as a on a.alead_id = l.id
left outer join (select id, leads_accoa89cccounts_idb school
from leads_accounts_c) as a2 on a2.id = a.minid
left outer join (select bean_id,email_address
from email_addr_bean_rel,email_addresses
where email_addresses.id = email_addr_bean_rel.email_address_id
and email_addr_bean_rel.primary_address=1
and email_addr_bean_rel.deleted='0') as e on e.bean_id = l.id
left outer join (select lead_id, id
from prospects
where prospects.lead_id is not null) as p on p.lead_id = l.id
left outer join (select lead_id, count(meeting) meetings
from (select meeting_id, lead_id, 1 meeting
from meetings_leads
group by meeting_id, lead_id) as m2
group by lead_id) as m on m.lead_id = l.id
where l.deleted = '0'
and (
l.date_entered-.2083 between cast(getdate() as numeric)- 3 and cast(getdate() as numeric)- 2
or
lc.statuschange_date_c = cast(getdate() as numeric)- 2
)
order by l.date_entered-.2083
END
TABLE FILE SQLOUT
PRINT This message has been edited. Last edited by: Kerry, WebFOCUS 7.6.10 Windows HTML | ||
|
| Platinum Member |
Why you not end SQL with ";"? Is maybe not problem but try? Kofi Client Server 8.1.05: Apache; Tomcat;Windows Server 2012 Reporting Server 8.1.05; Oracle; MS SQL; Windows Server 2012 | |||
|
| Master |
Try running with only one SQL statement, I think there was a recent thread dealing with multiple result sets in pass-through SQL but I am not sure what that issue was or the resolution. Also, look at your first 2 lines. I am not familiar with that syntax and there may be something at fault with it. Compare with one of my (known good) .fex files with pass-through SQL:
SET EMGSRV=OFF
SET SQLENGINE = SQLMSS
SQL SET SERVER Anesthesia
-RUN
SQL
SELECT
LTrim(RTrim(CONVERT(char(10),DATEPART(m, REPORTDATAQ.SERVICEDATE)))) + '/' +
LTrim(RTrim(CONVERT(char(10),DATEPART(d, REPORTDATAQ.SERVICEDATE)))) + '/' +
LTrim(RTrim(CONVERT(char(10),Right(DATEPART(yyyy, REPORTDATAQ.SERVICEDATE), 2)))) AS 'DATE'
, REPORTDATAQ.OPERATINGROOM AS 'LOCATION'
, LTrim(RTrim(CONVERT(char(10),DATEPART(hh, CONVERT(varchar,CONVERT(char(10),REPORT_003.ANESTHESIASTART,8),114))))) + ':' +
LTrim(RTrim(CONVERT(char(10),RIGHT('0' + CAST(DATEPART(MI,REPORT_003.ANESTHESIASTART) AS Varchar(2)), 2)))) AS 'AS'
, REPORTDATAQ.PATIENTNAME AS 'NAME'
, LTrim(RTrim(CONVERT(char(10),DATEPART(m, REPORTDATAQ.BIRTHDATE)))) + '/' +
LTrim(RTrim(CONVERT(char(10),DATEPART(d, REPORTDATAQ.BIRTHDATE)))) + '/' +
LTrim(RTrim(CONVERT(char(10),Right(DATEPART(yyyy, REPORTDATAQ.BIRTHDATE), 2)))) AS 'DOB'
, DateDiff(d, REPORTDATAQ.BIRTHDATE, REPORTDATAQ.SERVICEDATE) / 365 AS 'AGE'
, REPORTDATAQ.MEDICALRECORDNUMBER AS 'MRN'
, REPORTDATAQ.PRIMARYSURGEON AS 'SURGEON'
, REPORTDATAQ.PERFORMEDPROCEDURE AS 'PROCEDURE'
, REPORTDATAQ.PRIMARYTECHNIQUE AS 'ANES TECH'
, REPORTDATAQ.ASASTATUS AS 'ASA'
, LTrim(RTrim(CONVERT(char(10),DATEPART(hh, CONVERT(varchar,CONVERT(char(10),REPORT_003.PROCEDURESTART,8),114))))) + ':' +
LTrim(RTrim(CONVERT(char(10),RIGHT('0' + CAST(DATEPART(MI,REPORT_003.PROCEDURESTART) AS Varchar(2)), 2))))
AS 'PR S'
, LTrim(RTrim(CONVERT(char(10),DATEPART(hh, CONVERT(varchar,CONVERT(char(10),REPORT_003.PROCEDUREEND,8),114))))) + ':' +
LTrim(RTrim(CONVERT(char(10),RIGHT('0' + CAST(DATEPART(MI,REPORT_003.PROCEDUREEND) AS Varchar(2)), 2))))
AS 'PRF'
, LTrim(RTrim(CONVERT(char(10),DATEPART(hh, CONVERT(varchar,CONVERT(char(10),REPORT_003.ANESTHESIAEND,8),114))))) + ':' +
LTrim(RTrim(CONVERT(char(10),RIGHT('0' + CAST(DATEPART(MI,REPORT_003.ANESTHESIAEND) AS Varchar(2)), 2))))
AS 'AF'
, LTrim(RTrim(CONVERT(char(10),DATEPART(hh, CONVERT(varchar,CONVERT(char(10),REPORT_003.PATIENTOUTOFROOM,8),114))))) + ':' +
LTrim(RTrim(CONVERT(char(10),RIGHT('0' + CAST(DATEPART(MI,REPORT_003.PATIENTOUTOFROOM) AS Varchar(2)), 2))))
AS 'PT OUT OR'
FROM REPORTDATAQ
INNER JOIN REPORT_003 ON REPORTDATAQ.INTERNALCASEID = REPORT_003.INTERNALCASEID
WHERE ((REPORTDATAQ.SERVICEDATE = CONVERT(char(10),getdate()-1,112))
AND REPORTDATAQ.OPERATINGROOM Not Like 'Del%')
AND REPORT_003.ANESTHESIASTART Is Not Null
AND (DateDiff(d,REPORTDATAQ.BIRTHDATE,REPORTDATAQ.SERVICEDATE)/365) > 17.999
ORDER BY
REPORTDATAQ.OPERATINGROOM
, REPORT_003.ANESTHESIASTART
;
TABLE
ON TABLE SET ONLINE-FMT EXL2K
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='Letter',
SQUEEZE=ON,
ORIENTATION=LANDSCAPE,
$
TYPE=REPORT,
GRID=ON,
FONT='ARIAL',
SIZE=10,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
$
TYPE=REPORT,
COLUMN=N1,
WRAP=OFF,
JUSTIFY=RIGHT,
$
TYPE=REPORT,
COLUMN=N2,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N3,
WRAP=OFF,
JUSTIFY=RIGHT,
$
TYPE=REPORT,
COLUMN=N4,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N5,
WRAP=OFF,
JUSTIFY=RIGHT,
$
TYPE=REPORT,
COLUMN=N6,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N7,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N8,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N9,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N10,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N11,
WRAP=OFF,
JUSTIFY=LEFT,
$
TYPE=REPORT,
COLUMN=N12,
WRAP=OFF,
JUSTIFY=RIGHT,
$
TYPE=REPORT,
COLUMN=N13,
WRAP=OFF,
JUSTIFY=RIGHT,
$
TYPE=REPORT,
COLUMN=N14,
WRAP=OFF,
JUSTIFY=RIGHT,
$
TYPE=REPORT,
COLUMN=N15,
WRAP=OFF,
JUSTIFY=RIGHT,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N1,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N2,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N3,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N4,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N5,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N6,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N7,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N8,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N9,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N10,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N11,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N12,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N13,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N14,
$
TYPE=TITLE,
COLOR='BLACK',
BACKCOLOR= RGB(200 200 200),
JUSTIFY=CENTER,
SIZE=10,
COLUMN=N15,
$
TYPE=TABHEADING,
BACKCOLOR=RGB(233 231 220),
$
TYPE=TABFOOTING,
BACKCOLOR=RGB(233 231 220),
$
TYPE=HEADING,
BACKCOLOR=RGB(233 231 220),
COLOR='BLACK',
JUSTIFY=CENTER,
$
TYPE=FOOTING,
BACKCOLOR=RGB(233 231 220),
COLOR='BLACK',
SQUEEZE = ON,
$
ENDSTYLE
END
-RUN
P.S. I know my column styling method is redundant. ------------------------------------ WF Environment: ------------------------------------ Server/Client, ReportCaster, Dev Studio: 7.6.11 Resource Analyzer, Resource Governor, Library, Maintain, InfoAssist OS: Windows Server 2003 Application/Web Server: Tomcat 5.5.25 Java: JDK 1.6.0_03 Authentication: LDAP, MRREALM Driver Output: PDF, EXL2K, HTM ------------------------------------ Databases: ------------------------------------ Oracle 10g DB2 (AS/400) MSSQL Server 2005 Access/FoxPro | |||
|
| Guru |
Thanks for the tips. I will try on Monday. MY SQL works in SQL management studio. WebFOCUS 7.6.10 Windows HTML | |||
|
| Guru |
Okay I tried this but this doesnt work. Does anyone know if there is any limitation in SQL script or passthrough about Joins? Because I have no problem writing a smiple sql and getting out put. If I use my SQL and create SQL view on my SQL server it works just fine. Is there a way to make complex SQL joins work in IBI so I dont have to maintain code at two different places. When I write following code: ENGINE SQLMSS SET DEFAULT_CONNECTION SUGARCRM SQL SQLMSS PREPARE SQLOUT FOR select 10000 * TABLENAME END -RUN TABLE FILE SQLOUT * END WebFOCUS 7.6.10 Windows HTML | |||
|
| Expert |
left outer join (select leads_accofd41tsleads_ida alead_id, count(1) schools, min(id) minid
from leads_accounts_c
group by leads_accofd41tsleads_ida) as a on a.alead_id = l.id
Most likely joins like these do not get passed - you shouldn't expect WebFOCUS to be able to pass complex stuff like this. You should create a DB View that contains all these joins. Also, read "Using the Adapter for Microsoft SQL Server" in Adapter Administration for UNIX, Windows, OpenVMS, i5/OS, and z/OS V7.6.10 Francis  Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
Yes sir I just realized that this join was causing the problem. i ended up creating and view and using view as metadata to produce this report. Thank you for pointint this out. Regards, WebFOCUS 7.6.10 Windows HTML | |||
|
| Expert |
That is definitely the way to go. I created views of very complicated joins for MS CRM tables and they sure made life a lot easier. Francis Give me code, or give me retirement. In FOCUS since 1991 Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server | |||
|
| Guru |
Yes sir. Thanks for the advice and help. Moving forward I will built reports useing views where I have to use complex joins. Its awesome to be able to do that; however, the problem is there will be two sets of codes (View & Report). But views do make life a lot easier. Thanks again! WebFOCUS 7.6.10 Windows HTML | |||
|
| Powered by Social Strata |
 | Please Wait. Your request is being processed... |
Read-Only TopicCopyright © 1996-2020 Information Builders
| View $GS_USERNAME's Public Profile |
| Add $GS_USERNAME to my Ignore List |
| View Recent Posts by $GS_USERNAME |
| Notify me of New Posts by $GS_USERNAME |