SQL Expression Field wtih multiple FROMs

[crytalbria]

SQL Expression Field,

Hello, I am attempting to build a SQL Expression Field with multiple 'FROM' (subquery)

Below are my examples - Is this not supported by SQL Expression Fields? I can't see to find much information on them.

(
SELECT Top 1 Current_status
From RV_PF
Where RV_PF.Code = 'HAC'
AND
RV_P.ID = RV_PF.ID
(FROM RV_P,RV_PF
WHERE RV_P.ID = RV_PF.ID)
)


SELECT (Top 1 Current_status
From RV_PF
Where RV_PF.Code = 'HAC'
AND
RV_P.ID = RV_PF.ID)
FROM RV_P,RV_PF
WHERE RV_P.ID = RV_PF.ID
)


Thank you for your time

 

[kray4660]

You cannot really do that and I do not see the purpose since the queries look the same. If you the queries where different you could use a join.

 

[pmax9999]

Hi crytalbria

I have used SQL Expressions extensively but while I am a Crystal specialist, I am not a SQL expert.

SQL Expressions can be tricky to get working. The main thing to remember is that if the SQL code doesn't work in a SQL Query Tool, it definitely won't work in a SQL Expression.

I'm not sure what flavour of SQL you are using but there aren't any I have used where your query above is valid code.

My general approach is to develop the query in a query tool that works with your flavour of SQL. For the purpose of getting the query working, use actual data for the field that is being linked to in the report, and only after getting the code to work do I then relace real data with a reference to the report field.

Without knowing the database type, or understanding your data and exactly what you are trying to achieve, I would think something like the following might work:

(
Select	Top 1
	Current_Status
From	(
	Select	Current_Status
	From 	RV_PF
	Where	Code = 'HAC' and
		ID = 123
	)
)

(NB: I would think that if you are using "TOP", an Order By clause might be critical as well)

The 123 used is simply to get to a point where the the code is valid (and assumed PV_PF.ID is numeric). if the code is valid, it can be relaced in the SQL Expression with the relevant field to be linked to from the report by selecting it from the report field list at the top of the SQL Expression editor.

Hope this helps.

Regards
Pete

 

[hilfy]

One of the things to remember with SQL Expressions is that they can only return a single value.

In general, SQL Expressions are not meant for writing Select statements. They're more meant for pushing processing to the database when necessary. For example, I have used them for:

- Calling a stored function that creates a full address or a full name from fields that may or may not have data in them (such as ADDR2, Middle Name, etc.) Yes, I can do this in Crystal, but sometimes it's easier to get the format you're looking for in the query itself - especially if there's already a scalar stored function in the database that will handle it.

- To avoid using Crystal formulas in the Select Expert. When you select data based on a Crystal formula the processing can't be pushed to the database in the Where clause of the query. This means that all of the data gets pulled into memory and filtered there, which can cause significant slowness. If I can avoid using a Crystal formula by using a database function in a SQL Expression and then using the SQL Expression in the Select Expert, this is what I do.

If your needs are more complex than that, I highly recommend using a Command to gather ALL of the data for your report in a single query. See

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

for more info about working with commands.

-Dell

Senior Manager, Data & Analytics
Protiviti

http://www.protiviti.com