[SOLVED] SQL REPORT CODE PROBLEM

September 09, 2010, 10:46 AM

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,

September 10, 2010, 10:33 AM

Kofi

Why you not end SQL with ";"?

Is maybe not problem but try?

Kofi

September 10, 2010, 01:13 PM

ABT

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.

September 12, 2010, 12:42 AM

Arif

Thanks for the tips. I will try on Monday. MY SQL works in SQL management studio.

September 13, 2010, 10:03 AM

Arif

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
PRINT
*
END

September 16, 2010, 02:58 PM

Francis Mariani

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

September 16, 2010, 04:25 PM

Arif

quote:
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.

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,

September 16, 2010, 04:41 PM

Francis Mariani

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.

September 17, 2010, 10:04 AM

Arif

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!