Implementing NOT IN (subquery) HELP!!

[marabou]

I have a problem which I have no idea whatsoever about solving.

I have been given an SQL statement which I need to implement in CR8. The statement is pretty simple apart from the last where clause which I have absolutely no idea how to implement. I have thought of shared variables, sub reports and formulas, but I can't seem to get the answer.

Here is the statement -

SELECT Serial.id, Serial.SerNo, Stock.Code, Location.Name , Location.id AS LocationId
FROM Stock, Serial, Location
WHERE LocationId = 11
AND Stock.id = Serial.StkId
AND Location.Id = Serial.LocationId
AND NOT Serial.id IN
(SELECT SerId
FROM DocSer
WHERE docID = 95561)

The main problem is the fact that the field docID must be a parameter within the main report.

Please help, I have been staring at the screen for about three weeks now.

Thanks, in advance

 

[IdoMillet]

You can simply create this query in
Crystal SQL Designer and use that source
for your report.

Alternatively, create it as a View ("Query"
in MS Access) in your DBMS an access it from
Crystal as if it was a regular table.

Cheers,
- Ido ixm7@psu.edu

 

[marabou]

Thanks, IdoMillet. However, as the docID in the sub-select statement needs to be chosen at run-time by the user, then using a view won't work.

Also, as far as I understand it, it is impossible to overwrite any of the clauses in a SQL Designer report from VB, or if it is I haven't had any luck, and I also need to choose the docId at run-time, without putting it in the record selection so I don't get the CR parameter values selection box appearing at run-time.

Is there anything else you can think of?

Thanks in advance

 

[Ngolem]

I think what IdoMillet is refering to is running the query itself in Crystal SQL designer and then basing the Crystal Report on the Crystal SQL Designer query.

I think you want to run this strictly from the Crystal report designer using table linkages...it can be done that way too

If it were not for this part:

AND NOT Serial.id IN
(SELECT SerId
FROM DocSer
WHERE docID = 95561)

I don't think you would have had a problem with your report

What you can do is create the main report out of the first part of the query and this subselect can be a subreport linked to the main report by Serial.ID ... you can then suppress the detail line whenever Serial.ID is not null.

I think that would work.

 

[marabou]

Thanks Ngolem, I thought that was what IdoMillet meant, but I still have (as far as I know) the problem of putting extra where clauses (ie Docid = 95561) when I use the an SQL Designer based query and then make the report in CR8 and try and open the report from within VB. You see the trouble is, I do not want those 'Enter parameter values' boxes to appear. Obviously, if this would work the way I want it to, I have no problem.

I sort of understand your idea, but am abit hazy when it comes to the selecting the serid's that are not in the specified doc (docid). If i use a parameter for that in both the main and subreport, won't that select only those serid's which are on that note for both main/subreports and therefore cancel each other out? Would it be possible for you to explain what you mean a bit more? I would greatly appreciate it.

Thanks in advance

 

[IdoMillet]

You should be able to create a parameter that feeds the value into the Crystal SQL Designer query.
Then, in VB, set the value of the parameter
and run the report.

Cheers,
- Ido

ixm7@psu.edu

 

[Ngolem]

AND NOT Serial.id IN
(SELECT SerId

How many values of SerID would be brought back from this sub select for a given DOCID?? 10,100...more?

the easiest way to do this with a subreport is to create an unlinked subreport and store the values of SERID in a SHARED String array called SERID...this subreport would be placed in your report header so it is run immediately before anything else is done.

in the subreport you would have an initialization formula placed in Report header of the subreport.

@initialization

//create this array about 50% larger than you feel necessary
//for future expansion
shared string array SERID := ["","",""......,""];
numbervar counterflag := 1;

place this formula in the detail line of the subreport

@calc

shared string array SERID ;
numbervar counterflag;

SERID[counterflag] := Serid;
counterflag := counterflag +1;

suppress all lines of the subreport and place the subreport in the reportheader of the main report....DO NOT suppress the report header in the main report but format the subreport with no border lines and make it as thin as possible to hide it (I usually color code Subreports so I can find them later (in the conditional suppress of background color (if 1 = 1 then crNoColor else crRed)

NOW in the main report...in the section expert...in the conditional suppress of the sections that you want to suppress place this formula

shared string array SERID;
Serial.id IN SERID

this shoul suppress the sections where there is a match

hope this helps
Jim

PS. using SQL designer is ok and sometimes easier .... but it won't work if you want to impliment your report ultimately to the web.

 

[marabou]

Jim, thanks for that. I understand now what I need to do, apart from two quick questions.

I could have up to 100,000 values of serid for this sub select of docid. Is that going to be a problem?

The other question is about the syntax of the shared string array SERID.

How do I set a string variable to be the value of a field?

I have looked around for syntax, but cannot find any examples anywhere.

Would greatly appreciate your help.

Thanks, in advance

 

[Ngolem]

Hmmm...100,000 values is too many for this approach to work. An array can only have 1,000 values. You could set up 100 arrays but I don't think this is the best way of doing it.

You ONLY want to see the data if it is not one of these 100,000 serid's?

As far as assigning the data to a shared variable...I showed that to you in the @calc formula

@calc

shared string array SERID ;
numbervar counterflag;

SERID[counterflag] := Serid;
counterflag := counterflag +1;

perhaps I should have chosen another name for the array.

If you have 100,000 values for SerID in DOCSer then I would think the original plan of creating a query in SQL Designer would be the best way to go. You can use parameters in SQL Designer that flow through to the Report Designer

then use the query result to build your report .

 

[IdoMillet]

I suspect the whole thread got "lost" in complex options
because you may not realize that SQL queries in Crystal SQL Designer _CAN_ have parameters.

Just make sure you are viewing the result set within SQL Designer; In that mode, use the menu option of Edit,
Parameter Field...

Cheers,
- Ido
ixm7@psu.edu

 

[marabou]

Thanks, Ngolem and Ido.

My only problem with SQL Designed reports before was that we have never manged to overwrite a parameter within VB. However, my problem with this query is that because the value I wish the parameter to be based on (DocID) is in the subquery, the field does not come through to CR for me to put a parameter on it.

I have tried adding the field in the main query, but that means an extra join, which, in this database, means i do not get the right amount of records out.

Thanks for your help anyway, I'll keep plugging away and if I do get a solution I will post it up here.

 

[csm2]

I don't know if this will help you but when I have a SQL query with a sub-query, I run the main query in SQL Designer creating a recordset which is input to Report Designer where I execute the subquery.

 

[Ngolem]

I like that...will have to remember it....unfortunately most of my stuff is web enabled and SQL designer doesn't work in the web...

But for non-web applications...I do like that approach.