Using a CR SQL Designer query in Show SQL Query?

[xp8103]

As a new CR user, this may be a misplaced question. Creating this in CRSD, it works as the source of a CR but does not work when copied into the Show SQL Query area. It requires that the variable @Conn_No be declared but will not hold it when I add the declare statement. This actually started as a SQL sp since SQL views do not seem to allow variable delcaraiton and parameters. But it seems that using an sp in CR is somewhat limiting so I am looking for options.
Right now, the table Statute_Node contains lists of statute entries. The type_code tells the kind of entry, the ID is the value of the type_code, the connect_number is a sequencer. The level_no keeps track of the structure of a particular grouping of type_codes (don't ask me, I didn't create it...) My ultimate goal is to allow the user to see a report where the records are listed in a hierarchical manner (as the statutes are designed anyhow).

Declare
@Conn_No numeric(38,17),
@The_Title varchar(50),
@Level_No numeric(18,9)

SELECT @Conn_No = connect_no, @The_Title = ID, @Level_No = level_no FROM Statute_Node
WHERE type_code = 'MRSTitle' and ID = '{?The_Title}'

SELECT * FROM Statute_Node
WHERE connect_no >= @Conn_No
AND
connect_no <
(SELECT MIN(connect_no) FROM Statute_Node
WHERE connect_no > @conn_no
AND
level_no < = @Level_No)

ORDER BY connect_no ASC, level_no ASC

 

[MalcolmW]

I'm not clear why you don't just do this with a stored procedure, as this would simplify the reporting issues and improve performance. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[xp8103]

I can see your confusion. I have successfully created an sp that works in CR, parameter and all. However, all this does is get the information from one table. To be able to link it to others and use all the other data is necessary too. I would like to do it without creating a new SP each time the report requirements change.
Am I making sense?

 

[MalcolmW]

The SQL Designer can be used to create and save queries, but as you have found out, you are not able to port the SQL directly into the Report Designer. In short, that is because the Report Designer is more limited (in terms of SQL functionality/flexibility) than the SQL Designer.
So, you are faced with a choice of using the SQL Designer, or rewriting your SQL statement to eliminate the multiple selects and the use of variables, or creating a more complex stored procedure that returns the complete data set required for the report.
I'm biased in favour of using stored procedures, because they are fast and tend to produce predictable results. I have not had many happy experiences with the SQL Designer. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net