Dynamic passing into an SQL Expression 1

[cmv131]

Is there anyway to dynamically pass values into SQL expressions for CR-10?

For example - Lets say I have the following SQL Query

SELECT A.field1, B.field2, 
       (case when C.field3 is null  
       then 
       (select D.field4 from tblD D 
        where  D.field4 = C.field4  
       ) 
       else C.field3 end )
From tblA A, tblB B, tblC C

The reference back to the main tables in the CASE statement is what has me stumped. Any way to easily do this in CR10, maybe a formula of some sort? Any help is appreciated.

Thanks

 

[cmv131]

To clarify a little more...In my formula I basicaly want the following and am unsure of how to do this..

If isnull({Tbl.FldA})
then 
(Select FldA from tbl where FldC = 0 and FldB = {Tbl.FldB})
else
{Tbl.FldA}

 

[lbass]

It is not really clear what you are trying to do. Why not try explaining the selection you are trying to achieve instead of starting out with what you think might be the coding for the solution?

-LB

 

[cmv131]

Alright, I will see what I can do to explain a bit better...


I have this query that runs well in T-SQL and want to convert it to a report

SELECT 	distinct S.[Login ID], S.[INACTIVE:], 
	(case when G.[Group ID] is null
	then
		'Not Assigned to a Group'
	else
		G.[Group ID]
	end )	 as [Group], 
	(case when N.NavName is null
	then 	(
		case when (
			select count(Z.NavName) from NAMSYSHTMLNAVIGATORS Z, NAMSYSHTMLASSIGNS Y 
			where Y.HTMLSEQ = Z.[SEQUENCE] and Y._MEMBER_ = 0 and Y._GROUP_ = G.[Seq.Group]
			  ) > 1
		then 'Subquery Returns Too Many Results?'
		else(
			select Z.NavName from NAMSYSHTMLNAVIGATORS Z, NAMSYSHTMLASSIGNS Y 
			where Y.HTMLSEQ = Z.[SEQUENCE] and Y._MEMBER_ = 0 and Y._GROUP_ = G.[Seq.Group]
		)end
		)
	else
		(
			select X.NavName from NAMSYSHTMLNAVIGATORS X, NAMSYSHTMLASSIGNS Q 
			where Q.HTMLSEQ = X.[SEQUENCE] and Q._GROUP_ = G.[Seq.Group] and Q._MEMBER_ = S.[Sequence]
		)
	end) as 'Navigator'
FROM   	(_SMDBA_.[Support Staff] S 
	left outer join _SMDBA_.[Groups Details] G
	on S.[Sequence] = G.[Seq.SupportStaff]) 
		left outer join (NAMSYSHTMLASSIGNS A  
				inner join NAMSYSHTMLNAVIGATORS AS N 
				on A.HTMLSEQ=N.[SEQUENCE] and Type = 0) 
		on  A._MEMBER_=S.[SEQUENCE]		
order by [Login ID]

I have the table relationships correct, and I can get it to show most of the data I want to see. The problem I am running into is with this chunk of code..

(case when N.NavName is null
	then 	(
		case when (
			select count(Z.NavName) from NAMSYSHTMLNAVIGATORS Z, NAMSYSHTMLASSIGNS Y 
			where Y.HTMLSEQ = Z.[SEQUENCE] and Y._MEMBER_ = 0 and Y._GROUP_ = G.[Seq.Group]
			  ) > 1
		then 'Subquery Returns Too Many Results?'
		else(
			select Z.NavName from NAMSYSHTMLNAVIGATORS Z, NAMSYSHTMLASSIGNS Y 
			where Y.HTMLSEQ = Z.[SEQUENCE] and Y._MEMBER_ = 0 and Y._GROUP_ = G.[Seq.Group]
		)end
		)
	else
		(
			select X.NavName from NAMSYSHTMLNAVIGATORS X, NAMSYSHTMLASSIGNS Q 
			where Q.HTMLSEQ = X.[SEQUENCE] and Q._GROUP_ = G.[Seq.Group] and Q._MEMBER_ = S.[Sequence]
		)
	end) as 'Navigator'

As you can see, it dynamically uses the current record in the subquery, and to my knowledge I cannot pass that into a SQL Expression in Crystal since it stores it as part of the SQL Query and the subquery would then return more than one row (even though it wouldn't if I could pass the current record value in)

Hopefully that clears it up a little bit. Thanks in advance.

 

[vidru]

If the code already does what you need, then why not either turn it into a stored procedure, or create the report using 'Add Command' instead of tables directly? If you used a Command, you can just paste in you SQL, and you're done.

-dave

 

[vidru]

paste in you SQL

Sorry, that should be your.

-dave

 

[cmv131]

I guess I didn't do it that way because I never heard of that command

That seems to get me what I need, and it is good to know that if I can generate the SQL, I can generate a report.

Though I would still be curious if this could be done using CR syntaxes

Thanks for the help

 

[synapsevampire]

Elaborating on Dave's solution, consider using generic reusable objects, such as Views on the database.

Embedding SQL into the reports means that if something changes in the database, you'll be reworking every report, but if you use Views, you can generally tweak the View(s), and your client processes will function.

This also promotes reusability.

-k