[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1880
  • Last Modified:

Use query results from one dataset in another dataset

I have a dataset in my report which returns one field and one record.

What I would like to do is use that record's value in another dataset to return results based on the first dataset but I keep getting errors relating to the syntax of my query.

I have attached a code snippet.  The part that says 'First(Fields!SUMUSER.Value, "DataSet2")' is the part of the sql I am not sure is correct.

Many thanks in advance
SELECT CLA_NO, CLA_NAME 
FROM CLA_ACC 
WHERE CON(CLA_NO, 'First(Fields!SUMUSER.Value, "DataSet2")')='Y' AND cla_co='01' and cla_status <> '3' and CLA_NO LIKE 'C%' ORDER BY CLA_NO DESC

Open in new window

0
cjohnson300
Asked:
cjohnson300
  • 6
  • 5
1 Solution
 
Haris DjulicCommented:
SELECT CLA_NO, CLA_NAME
FROM CLA_ACC
WHERE CON(CLA_NO, (select value from dataset2))='Y' AND cla_co='01' and cla_status <> '3' and CLA_NO LIKE 'C%' ORDER BY CLA_NO DESC
0
 
cjohnson300Author Commented:
Sorry I should have mentioned that I am writing a report in SQL Reporting Services, therefore "dataset2" isn't a table as such but a dataset in reporting services.
0
 
shorakCommented:
Hi

What you will need to do is setup a hidden paramater which passes the value from dataset2 into dataset1.

replace the 'First(Fields!SUMUSER.Value, "DataSet2" with the name of your parameter you created (with a @ infront ).

I assume you know how to create a parameter in reporting services.. if not then here are some quick instructions..

go to Report (in the menu), then Report Parameters..

In the next screen enter a name for the paremeter in the Name property, select the data type in the next box. Select the 'Hidden' tickbox.

From the available values section, select the select from query option. In the first drop down, select your dataset2, in the value field, select the value field you want to pass into dataset1, for the label field drop down, select the same field.

from the default values section, select from query and again select dataset2 and for the value field, select the column you want to pass to dataset1... and thats it

see the code snippet to see how to use the parameter in the sql in dataset1

Hope this works for you

SELECT CLA_NO, CLA_NAME 
FROM CLA_ACC 
WHERE CON(CLA_NO, @paramater)')='Y' AND cla_co='01' and cla_status <> '3' and CLA_NO LIKE 'C%' ORDER BY CLA_NO DESC

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
cjohnson300Author Commented:
Thanks for the reponse.

I have followed your instructions, now when I go to preview the results of DataSet1, the Define Query Parameters dialog box comes up and when I click OK, I get the following error ORA-00936: missing expression

Also, when I went to put the Report parameter in, I recevied a message saying "The data extension ODBC does not support named parameters.  Use unnamed parameters instead".  What does this mean?!
0
 
shorakCommented:
what is the output of dataset 2? when you run dataset1, enter a valid value in the parameter dialog and see if it runs dataset 1. Are you using MSSQL or Oracle SQL?

The first error suggest that there is something wrong with the syntax of the query. Try this in the where clause.

WHERE CON(CLA_NO, @paramater)='Y' AND cla_co='01' and cla_status <> '3' and CLA_NO LIKE 'C%' ORDER BY CLA_NO DESC

Also.. do you have any other parameters in the report or just the one you created? the @parameter needs to be replaced with the name of the parameter you created.. ie if you called you paramater 'sumuser' then you will need to refer to it as @sumuser in the query
hence your where clause would change to
WHERE CON(CLA_NO, @sumuser)='Y' AND cla_co='01' and cla_status <> '3' and CLA_NO LIKE 'C%' ORDER BY CLA_NO DESC

Let me know what happens
0
 
shorakCommented:
incase your using an oracle datasource.. use : instead of @

Rob
0
 
cjohnson300Author Commented:
If I replace the parameter with a valid value, it returns the correct data to the report, however, it must be incased with single quotes otherwise it doesn't work.

Attached is what I have at the moment and this is the current error message: ORA-00920: invalid relational operator

And yes I am using an Oracle data source

Thanks for your help!
SELECT CLA_NO, CLA_NAME 
FROM CLA_ACC 
WHERE CON(CLA_NO, :parameter))='Y' AND cla_co='01' and cla_status <> '3' and CLA_NO LIKE 'C%' ORDER BY CLA_NO DESC

Open in new window

0
 
shorakCommented:
Check the data type of your parameter.. set it to integer (if dataset2 is returning numbers) as it might be set to string at the moment.

I'm not a expert in oracle SQL to be honest but I have had a quick look around relating to the error message and it saying that an invalid or missing relational              operator can cause this error.  valid relational operator such as =, !=, ^=, <>, >, <, >=, <=,              ALL, ANY, [NOT] BETWEEN, EXISTS, [NOT] IN, IS [NOT] NULL, or [NOT] LIKE should be used. I've had a look at your where clause and cant see anything wrong with it but I noticed you have 2 closing brakets which might be causing the problem.

update the parameter data type to interger (if set to string) and removed the extra closing bracket and see if it now works.

Rob
0
 
cjohnson300Author Commented:
The first dataset is returning a string.

Good spot on the extra closing bracket, I now have a different error:
ORA-01008: not all variables bound

Could this be a problem from the order the datasets are calcuated?
0
 
shorakCommented:
One final thing to try...

go to the dataset tab (where you create your datasets) and select dataset1 from the dropdown.

click on the 3 dots next to the dropdown and a popup dialog box should appear with a few tabs. select the parameters tab.

Under the name column, enter  ':parameter'
under the value column, enter '=Parameters!parameter.Value'
(both without the quotes and assuming you have named your parameter 'parameter')
click on okay

.. and then see if it works. Also it might be useful to rename your parameter to something different incase reporting services is getting confused.. you can try calling the parameter parameter1 etc.
you can give the report one name and the parameter in the where clause another name but make sure this is reflected in the dialog box mentioned about.
The Name column represents the name of the parameter in your SQL query and the Value column represent the name of the parameter you created in the report so if you called your report parameter 'sumuser' then the expression in value column would be '=Parameters!sumuser.Value

This links gives a brief overview of linking up the parameters http://www.timbus.com/article8.html

fingers crossed it works this time.

Rob
0
 
cjohnson300Author Commented:
Just when I was starting to lose the will to live, it's sorted.

Couldn't have done it without your help Rob but I had to use the Expression builder (the three dots button) to put this expression in:
="SELECT CLA_NO, CLA_NAME FROM CLA_ACC WHERE CON(CLA_NO, '" & Parameters!parameter.Value & "')='Y' AND cla_co='01' and cla_status <> '3' and CLA_NO LIKE 'C%' ORDER BY CLA_NO DESC"

The "parameter" was the report parameter so I didn;t need the parameter in the Parameters tab in the Expression builder dialog box.

Thanks again, much appreciated
0
 
shorakCommented:
well.. atleast we got there in the end. thanks for being patient

Rob
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now