SQL Query - Selecting From Nested Query

[Nick366]

I am using CR 8.

I am trying to select information from multiple tables and am getting snagged by an invalid character. My best guess is that I am getting hung up on the FROM protion of my SQL Query.

I am trying to select information from a nested query. My from statment looks similar to this:

---------------------------------------
FROM
"TEST"."JELLYS" JELLYS,
(SELECT JAMID, JAM FROM TEST.JAMS WHERE EXPIRED = 'N') JAMS
---------------------------------------

Is this valid code? If not, is there a way to select from a nested query?

PS. The nested query is much more complicated then EXPIRED = 'N'. I would put the criteria in the main section, but it will not return the results I want.

 

[synapsevampire]

Your code does not appear to be valid.

If you need SQL training, I would suggest that you use one of the myriad of free sites to learn more about it, or use the appropriate SQL forum.

When requesting SQL programming assistance, please understand that the type of database and version used is critical, so this sort of post is something better suited for a chat room or BLOG.

If you have a real requirement, and paste the real SQL I will gladly assist you.

In general, I try to do this sort of thing in Views or Stored Procedures.

You might also read up on SQL Expressions in Crystal, I and others have posted here on occasion demonstrating nested queries.

-k

 

[Nick366]

Thanks for your input.

There is nothing wrong with my SQL. I can run that statement in a different program, such as Toad or Golden, and it will return exactly what I want. The question was and still remains, how do I get this statement that works in other programs to work in Crystal Reports. There has to be something comparable.

If possible, I'd like to avoid using Stored Procedures.

 

[Turkbear]

Hi,
Actually the code snippet you posted looks invalid ( altho' I realize it is valid, just incomplete)..

In your report ( I think this works in v8 ) select the 'show sql query' option under the database menu and paste your sql code there in place of the code that Crystal built..

 

[Nick366]

Turk,
The snippit I posted was just the FROM section of the query, not the whole thing. The whole thing is much longer. If that section looks correct, then I might need to focus looking else where in the query.

I did paste my query into the window you suggested, but it is rejecting it saying I have an invalid character. I am quite new to CR, and this book I have is not very helpful. Is there anyway for CR to tell me what exactly it does not like about the query?

Thanks!

 

[cmmrfrds]

I am guessing that you are connecting to Oracle. What connection method are you using? The various drivers are different in what syntax they allow. Also, different versions of the drivers can affect what is valid. For example, if you are using OLEDB, have you tried ODBC instead?

 

[Turkbear]

Hi,
Another way to approach this is to create a View that pre-joins the various tables and use that as the basis of your report.

Crystal is probably having some problems with the parens and/or the single quotes.

 

[synapsevampire]

I meant that the SQL is invalid for Crystal, that being the topic.

I would have elaborated but you didn't even bother to state the database type when referencing a SQL question...

Turk: You could NEVER replace SQL using the Database->SHOW SQL QUERY, that will blow Crystal, however you might use part of it there, check twhat is allowed in the screen, there's a blurb there.

More simply, select an ADO data source and paste in the entire SQL.

Or use a SQL Expression and paste in the subquery.

-k

 

[Turkbear]

Hi,
Thanks, - k, I forgot about the limitations of the Pasting process in the Show Sql Window ( I guess the Command Object in later versions got me sloppy)...
The ADO solution or, if I had to choose, the View method is the way to go..