Joined: 06 Dec 2006
Location: United States
Online Status: Offline
Posts: 13
Topic: Command / Stored proc as datasource Posted: 23 Mar 2008 at 2:54pm
I have a query like th following. After going thro' the doc I feel the best way is to go with either COMMAND or stored proc as datasource where the query can be entered as it is and parameters can be set. I would like to get Brian's opinion on this. thanks
SELECT COUNT(TABLE_NAME1.COLNAME6),
TABLE_NAME1.COLUMN_NAME1,
TABLE_NAME1.COLUMN_NAME2,
TABLE_NAME1.COLUMN_NAME4,
TABLE_NAME1.COLUMN_NAME5,
TABLE_NAME1.COLUMN_NAME3
FROM TABLE_NAME1
LEFT OUTER JOIN TABLE_NAME2
ON TABLE_NAME1.PAGE=TABLE_NAME2.ID
WHERE (TABLE_NAME1.COLUMN_NAME4='EN'
AND TABLE_NAME1.COLUMN_NAME5='ES'
AND TABLE_NAME1.COLUMN_NAME1=1
AND TABLE_NAME1.TIMESTAMP >='3/1/2008'
AND TABLE_NAME1.TIMESTAMP <='3/28/2008'
AND TABLE_NAME1.TYPE in ( 2 ,4,8,9,10 )
AND TABLE_NAME2.COLUMN1 LIKE '%URLSTR%'
or( TABLE_NAME1.COLUMN3 IS NULL
AND FFID = ( SELECT FILEID FROM TABLE_NAME4 FT, TABLE_NAME2 APG
WHERE FT.ID = FFID
AND FT.COLUMN3= APG.ID
and APG.URL LIKE '%URLSTR%'
)
)
)
GROUP BY TABLE_NAME1.COLUMN_NAME1,
TABLE_NAME1.COLUMN_NAME2,
TABLE_NAME1.COLUMN_NAME4,
TABLE_NAME1.COLUMN_NAME5,
TABLE_NAME1.COLUMN_NAME3
Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Posted: 23 Mar 2008 at 11:08pm
Yes, I agree. You want the database to process as much of the information on the server as possible. Doing it as a stored procedure would be ideal because it can get the parameter values from the report and do all the work on the database server.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum