cancel
Showing results for 
Search instead for 
Did you mean: 

How do I create a subquery in 'Record Selection' ?

sharon_smith3
Participant
0 Kudos

I would like to create a subquery in the record selection.  I'm trying the following:

is this correct? I receive the error    "The ) is missing "

table a.seq_number =

(select table a.seq_number

from a table , b table

where

a.id = b.id

Accepted Solutions (0)

Answers (2)

Answers (2)

DellSC
Active Contributor
0 Kudos

Unless it's a typo, it looks like your missing the final ")" at the end of the formula.  However, you can't do a subquery like that in the Select Expert.

There are a couple of ways you might be able to do this, though.  Here's what I would try first:

It looks like table a is already in your report.  So, in the Database Expert, add table b to the report and link from table a.id to table b.id.  The default link type is an inner join, so this will cause the report to only get the data from a where a corresponding record exists in b, which is what it looks like you're trying to do in the subquery.

-Dell

sharon_smith3
Participant
0 Kudos

Thanks  Dell.    I already have an inner join on tables a and b.... what I'm actually trying to do is ultimately retrieve the 'previous row' based on the id in my first select statement.... 

First retrieve the row I need based on my conditions, and then go back and select the previous row in table b using id - 1. (the latter step is where I thought the subquery  would come in handy...)

although this seems logical in my head, not sure if this is the correct way about doing so...

abhilash_kumar
Active Contributor
0 Kudos

Hi Sharon,

You cannot do this inside the selection formula.

You should report off of a SQL query (via the Add Command Option) that includes this sub query.

If you're reporting directly against tables, you can do this using a SQL Expression field too.

The code would looks like this

(

Select A."field"

From Table A

Where A."field" = Table."Field"

)

-Abhilash