×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

SQL Expression Field wtih multiple FROMs

SQL Expression Field wtih multiple FROMs

SQL Expression Field wtih multiple FROMs

(OP)
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

RE: SQL Expression Field wtih multiple FROMs

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.

RE: SQL Expression Field wtih multiple FROMs

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:

CODE

(
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

RE: SQL Expression Field wtih multiple FROMs

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-wh... for more info about working with commands.

-Dell

Senior Manager, Data & Analytics
Protiviti
www.protiviti.com

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close