How do I make an SQL select query?

megabri

Good morning all,

I'm having an issue where I can't seem to make a SQL select type data set. My data source is maximoDataSource. When I go into the New Data Set menu it only give me 'Scripted Data Set' as an option for data set type. Can anyone tell me how I can use the sql select type, it would be so much easier!

Vineet Joshi

<blockquote class='ipsBlockquote' data-author="'megabri'" data-cid="108295" data-time="1344428207" data-date="08 August 2012 - 05:16 AM"><p>
Good morning all,<br />
<br />
I'm having an issue where I can't seem to make a SQL select type data set. My data source is maximoDataSource. When I go into the New Data Set menu it only give me 'Scripted Data Set' as an option for data set type. Can anyone tell me how I can use the sql select type, it would be so much easier!<br /></p></blockquote>
<br />
<br />
Hi ,<br />
<br />
you have to write the following code in the <strong class='bbc'>open method</strong> of the dataset <br />
<br />
maximoDataSet = MXReportDataSetProvider.create(this.getDataSource().getName(), this.getName());<br />
maximoDataSet.open();<br />
<br />
var sqlText = new String();<br />
<br />
// Add query to sqlText variable.<br />
sqlText ="select wonum from workorder"<br />
<br />
maximoDataSet.setQuery(sqlText);<br />
<strong class='bbc'><br />
Fetch </strong><br />
<br />
if (!maximoDataSet.fetch())<br />
return (false);<br />
<br />
<br />
row["wonum"] = maximoDataSet.getString("wonum");<br />
<br />
<br />
return (true);<br />
<br />
<br />
create the wonum field in the dataset output .you are ready to go .<br />
<br />
for further details please see IBM canned reports.<br />
<br />
<br />
Regards