COMMAND AND PARAMETERS

[Former Member]

i have a command file

i need to have multiple parameters in the command. also need the option to select ALL values from the command parameter what is the syntax?

i have one parameter WHERE "FSLEDG"."ESRECO"={?ALLOWANCE STATUS}

i need to add a second one.

I add it to the WHERE "FSLEDG"."ESRECO"={?ALLOWANCE STATUS}

AND "FGLEDG"."EGDIVI"={?COMPANY}

i receive an error COLUMN AAA NOT IN SPECIFIED TABLES.

when i hardcode the value AND "FGLEDG"."EGDIVI"= AAA i dont receive an error.

SELECT

"FSLEDG"."ESTRCD",

"FSLEDG"."ESCUAM",

"FSLEDG"."ESIVDT",

"FSLEDG"."ESRECO",

"FGLEDG"."EGAIT1",

"FSLEDG"."ESCUNO",

"FGLEDG"."EGAIT7",

"FGLEDG"."EGVTXT",

"FCHACC"."EATX15",

"FGLEDG"."EGAIT3",

"FSLEDG"."ESCINO",

"FSLEDG"."ESDIVI",

"FGLEDG"."EGDIVI",

"FSLEDG"."ESDTP5",

"FGLEDG"."EGVSER"

FROM {oj ("I5M3AD"."M3DDBPRD"."FSLEDG" "FSLEDG" LEFT OUTER JOIN "I5M3AD"."M3DDBPRD"."FGLEDG" "FGLEDG" ON ((((("FSLEDG"."ESCONO"="FGLEDG"."EGCONO") AND ("FSLEDG"."ESDIVI"="FGLEDG"."EGDIVI")) AND ("FSLEDG"."ESVSER"="FGLEDG"."EGVSER")) AND ("FSLEDG"."ESVONO"="FGLEDG"."EGVONO")) AND ("FSLEDG"."ESYEA4"="FGLEDG"."EGYEA4")) AND ("FSLEDG"."ESJRNO"="FGLEDG"."EGJRNO")) LEFT OUTER JOIN "I5M3AD"."M3DDBPRD"."FCHACC" "FCHACC" ON (("FGLEDG"."EGCONO"="FCHACC"."EACONO") AND ("FGLEDG"."EGDIVI"="FCHACC"."EADIVI")) AND ("FGLEDG"."EGAIT3"="FCHACC"."EAAITM")}

WHERE "FSLEDG"."ESRECO"={?ALLOWANCE STATUS}

AND ("FGLEDG"."EGAIT1"='32400' OR "FGLEDG"."EGAIT1"='32500' OR "FGLEDG"."EGAIT1"='51725')

ORDER BY "FSLEDG"."ESCINO"

[Former Member · Accepted solution]

Sharon,

Syntax wise you're pretty close. If "FGLEDG"."EGDIVI" is a string data type then {?COMPANY} needs to be wrapped in single qoutes... "FGLEDG"."EGDIVI"= '{?COMPANY}'.

As far as using an "ALL" option... Well it all depends on how you are doing it. If you're using a dynamic LoV to prompt for your values then you'll need to "append" the all option to you LoVs. Something Like This...

SELECT
-1 AS Value,
'ALL' AS Description
UNION 
SELECT
TableID AS Value,
FieldName AS Description
FROM TabeName

The trick part is getting it to work in the command... Here's he easiest way to do it.

DECLARE @x INT
SET @x = {?My Parameter}
IF @x = -1
BEGIN
SELECT statement without the parameter in the WHERE clause
END 
ELSE
BEGIN
SELECT statement with FieldName = @x
END

and remember... unless you are using CR 2008, you can't pass a multi-valued parameter to a Command... At least without jumping through a few hoops.

HTH,

Jason

[Former Member · Accepted solution]

I knew you would be able to help... your the best. who would have thought you needed single quote around the parameter in a string command, i gather with a number you dont.

Im using CR2008. i need to trim the dataset and speed the report up. any suggestions for that.

i am hitting our ledger table which has over 1mill recs. .....

[Former Member · Accepted solution]

I'll be at work in about an hour. I'll take a look at your query then.

[Former Member · Accepted solution]

Well... I'm not sure what you used to build this query originally but I'm not too familiar with that syntax... and if I remember correctly you are reporting from a SQL Server database.

I also changed the join between I5M3AD.M3DDBPRD.FSLEDG and I5M3AD.M3DDBPRD.FGLEDG to an INNER JOIN. The criteria in the WHERE clause negates the LEFT OUTER JOIN and an INNER JOIN should add a little speed when running against big tables.

As far as speeding thing up when accessing tables with 1 mil + rows...

#1) You want to identify the columns that are "indexed" and use those as much as possible for you filtering and sorting. The concept behind indexing is that the database will maintain additional information about that column that makes it easier to search... Not unlike the index or table of contents of a book. If you find yourself needing to filter on the same field for multiple reports (or even just 1 report that needs to be run often) you may be able to convince you DBA to index that column.

#2) If you can, avoid using text fields when filtering... Especially wild card searches. Databases can do number comparisons much faster than text comparisons. Wild cards are even worse... WAY worse!

#3) Hardware... I know it's not in your control to determine what type of server the db is living on, but there's a reason big companies have their databases on big servers with parallel processors... They're fast! Sometimes you can do everything right and it will still take lots of time to execute a query. The key is to make sure that the query is run during off hours so that it doesn't interfere with the users of the system.

#4) Dirty reads. SQL Server has a feature called "table hints" that allows you to override SQL Servers own execution plan. As a general rule you should avoid using table hints. SQL Server will typically create a far better execution plan than you can.

That said, you should know that SQL Server "locks" a table, blocking or holding transactions while a query executes against it. This insures that there no "uncommitted" transactions included in the results (a "clean read"). There are times, when you know that you can live with a "dirty read" in exchange for better performance...

Adding WITH (NOLOCK) to the end of the table name in the FROM clause will prevent the table from locking during the read.

SELECT * FROM TableName AS tn  WITH (NOLOCK) WHERE tn.FieldName = 1

Please note! Don't get in the habit of using of using this automatically. Like I said, SQL Server is very good at creating execution plans. Only add WITH (NOLOCK) if you aren't happy with the current execution times or users complain of performance issues while executing.

If you want to know more just Google SQL Server table hints.

#5) Stored Procedures: You can also save time on complex queries by placing them in stored procedures. The reason is that SQL Server (all db really) has to convert the SQL code into an "execution plan". SQL Server will store this plan with a stored procedure and is able to keep reusing it... as opposed to re-creating it every time the query is run. This is only important for very complex queries that generate very complex plans. Otherwise the DB can create a plan in a fraction of a second.

Well here is my "re-worked" version of your SQL

SELECT
fs.ESTRCD,
fs.ESCUAM,
fs.ESIVDT,
fs.ESRECO,
fg.EGAIT1,
fs.ESCUNO,
fg.EGAIT7,
fg.EGVTXT,
fc.EATX15,
fg.EGAIT3,
fs.ESCINO,
fs.ESDIVI,
fg.EGDIVI,
fs.ESDTP5,
fg.EGVSER

FROM I5M3AD.M3DDBPRD.FSLEDG AS fs 
INNER JOIN I5M3AD.M3DDBPRD.FGLEDG AS fg 
		 ON fs.ESCONO = fg.EGCONO 
		AND fs.ESDIVI = fg.EGDIVI
		AND fs.ESVSER = fg.EGVSER 
		AND fs.ESVONO = fg.EGVONO 
		AND fs.ESYEA4 = fg.EGYEA4 
		AND fs.ESJRNO = fg.EGJRNO
LEFT OUTER JOIN I5M3AD.M3DDBPRD.FCHACC AS fc 
		 ON fg.EGCONO = fc.EACONO	
		AND fg.EGDIVI = fc.EADIVI
		AND fg.EGAIT3 = fc.EAAITM

WHERE fs.ESRECO = {?ALLOWANCE STATUS}
  AND fg.EGAIT1 IN (32400, 32500, 51725)
AND fg.EGDIVI = '{?COMPANY}'
ORDER BY fs.ESCINO

HTH,

Jason

[Former Member · Accepted solution]

cant change my joins between the tables, as i dont get all my records. one is not a detail / header table.

the columns that i am joining on are indexed.

Hardware... I know it's not in your control to determine what type of server the db is living on, but there's a reason big companies have their databases on big servers with parallel processors... They're fast! Sometimes you can do everything right and it will still take lots of time to execute a query. The key is to make sure that the query is run during off hours so that it doesn't interfere with the users of the system.

the server is in new jersey, i am in manhattan. the sever is more than capable of handling the query.

cant run the query overnight, as this is run when the end user executes the report during the day.

cant use a sp this is not on the sql server. i would have to log into db2 to create a view, and the command file is working fine.

we have an environment that runs multiple servers in multiple platforms. sql is only one.

[Former Member · Accepted solution]

Gotcha!

Sounds like you have a good handle on things. My previous list of suggestions were meant to be generic... Just a few ideas to look at...

As far as the INNER JOIN vs LEFT OUTER JOIN... AND fg.EGAIT1 IN (32400, 32500, 51725) AND fg.EGDIVI = '{?COMPANY} will prevent any I5M3AD.M3DDBPRD.FSLEDG rows that don't have corresponding I5M3AD.M3DDBPRD.FGLEDG rows (thus providing the effect of an INNER JOIN)...

If you only want to limit I5M3AD.M3DDBPRD.FGLEDG, without effecting I5M3AD.M3DDBPRD.FSLEDG, you'll need to move that part of the selection criteria out of the WHERE clause and in to the join. Something like this

SELECT
fs.ESTRCD,
fs.ESCUAM,
fs.ESIVDT,
fs.ESRECO,
fg.EGAIT1,
fs.ESCUNO,
fg.EGAIT7,
fg.EGVTXT,
fc.EATX15,
fg.EGAIT3,
fs.ESCINO,
fs.ESDIVI,
fg.EGDIVI,
fs.ESDTP5,
fg.EGVSER
 
FROM I5M3AD.M3DDBPRD.FSLEDG AS fs 
LEFT OUTER JOIN I5M3AD.M3DDBPRD.FGLEDG AS fg 
		 ON fs.ESCONO = fg.EGCONO 
		AND fs.ESDIVI = fg.EGDIVI
		AND fs.ESVSER = fg.EGVSER 
		AND fs.ESVONO = fg.EGVONO 
		AND fs.ESYEA4 = fg.EGYEA4 
		AND fs.ESJRNO = fg.EGJRNO
		AND fg.EGAIT1 IN (32400, 32500, 51725)
		AND fg.EGDIVI = '{?COMPANY}'
LEFT OUTER JOIN I5M3AD.M3DDBPRD.FCHACC AS fc 
		 ON fg.EGCONO = fc.EACONO	
		AND fg.EGDIVI = fc.EADIVI
		AND fg.EGAIT3 = fc.EAAITM
 
WHERE fs.ESRECO = {?ALLOWANCE STATUS}

ORDER BY fs.ESCINO

HTH,

Jason