conditional WHERE sections.

Hi I have a query where I am reading in a bunch of veriables and only want to
run parts of the where clause if the variables are valid, (will be input in a
stored procedure)
I tried
WHERE
if @.var1 IS NULL
BEGIN
{
tablename.fieldname >= @.var1
}
END
if @.var2 IS NULL
BEGIN
{
AND tablename.fieldname >= @.var2
}
END
[Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
thanks.
Paul G
Software engineer.
Paul
Create a dynamic SQL statement and build the where clause. Then execute the
SQL statement:
declare @.sql varchar(8000)
set @.sql = 'select * from table where '
If @.var1 is null
set @.sql = @.sql + 'condition 1'
else
set @.sql = @.sql + 'condition 2'
exec @.sql
"Paul" wrote:

> Hi I have a query where I am reading in a bunch of veriables and only want to
> run parts of the where clause if the variables are valid, (will be input in a
> stored procedure)
> I tried
> WHERE
> if @.var1 IS NULL
> BEGIN
> {
> tablename.fieldname >= @.var1
> }
> END
> if @.var2 IS NULL
> BEGIN
> {
> AND tablename.fieldname >= @.var2
> }
> END
> [Microsoft][ODBC SQL Server Driver]Syntax error or access violation occures
> thanks.
>
> --
> Paul G
> Software engineer.
|||There are several approaches for handling such requirements. Some of the
popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
Anith
|||Hi thanks for the information. Figured there may be several ways to the
solution.
"Anith Sen" wrote:

> There are several approaches for handling such requirements. Some of the
> popular ones are detailed at: http://www.sommarskog.se/dyn-search.html
> --
> Anith
>
>
|||Ok looks like the dynamic SQL statement should work for what I am trying to
do. Thanks.
"Bruce" wrote:
[vbcol=seagreen]
> Paul
> Create a dynamic SQL statement and build the where clause. Then execute the
> SQL statement:
> declare @.sql varchar(8000)
> set @.sql = 'select * from table where '
> If @.var1 is null
> set @.sql = @.sql + 'condition 1'
> else
> set @.sql = @.sql + 'condition 2'
> exec @.sql
>
> "Paul" wrote:

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

conditional display

To simplify, I have a report with sections A,B, and C.
Section A is always displayed.
Section B is only displayed when the subject is Male.
Section C is only displayed when the subject is Female.
I want section B or C to directly follow section A.
I have been using the hidden property to do this, but it is hard to maintain the report as I have placed these two sections on top of each other on the Layout tab.
Is there a way to make either section B or C directly follow section A without having them overlap in the Layout tab?

What is in the sections? Three different reports? I haven't had much use for sections myself.

If these were three different reports, you could have them flow nicely in the layout tab without overlap.

|||The sections contain data pertinent only to Males or Females. I have considered making separate reports, but the gender sections only make up about 10% of the report. Because of this I would rather keep it as one report. GregSQL mentioned not having much use for 'sections'. I am new to RS so forgive me if I have miused a term. I was using 'section' just as a loose description of my report. Also, my report is 'odd' in the sense that I am displaying data from just one record. Most of the reports I see in the tutorials are based on multiple rows. I am trying to display information on just one person. That information differs depending on gender. Its a 10 page report!

Hope this helps...|||

I believe I understand what you are trying to do.

You always want to display table A first.
If table B has data, table B will show directly under table A.
If table B does not have data and table C has data, table C will show directly under table A.
If table B has data and C has data, table B will show directly under table A and table C will show directly under table B.

I was looking around and I don't see another way to do this. I was thinking that you could dynamically change the location properties of the tables (B & C), but you can't dynamically change these.

Jarret

|||Jarret,

You are correct. That is exactly what I am saying. Except I am using Rectangles and Text boxes instead of tables. No need for tables as I am only displaying a single record at a time. Crystal Reports used a 'supress drill-down' feature that would conditionally display a section of the report. Surely RS has something comparable?
|||

I haven't tried this, but you might be able to put your rectangles/textboxes into a table (each as their own row), then set the visibility of the row to display which section (B or C) next.

Hope this helps.

Jarret

|||Forgive my ignorance but it appears to be working now.
If you put your textboxes in rectangles and butt the rectangles together, they will follow right after one another even if there is an 'invisible' rectangle in the middle.
I think my earlier attempts involved rectangles with space between them so the space was preserved? Maybe? My earlier attempt is at the top of the report. My latest victory is on page 5. So I think I will leave my earlier blemish alone...

conditional detail sections

i have 3 detail sections (assume detail prints 5 lines)

detail a - prints every line
detail b - has to print n blank lines if detail a prints < 10 lines otherwise nothing
detail c - has to print at 11th line always

i cant have this at footer because another detail e and detail f

Thanksi have 3 detail sections (assume detail prints 5 lines)

detail a - prints every line
detail b - has to print n blank lines if detail a prints < 10 lines otherwise nothing
detail c - has to print at 11th line always

i cant have this at footer because another detail e and detail f

Thanks

What I am getting from this is :
You want 'detail a' section to be 10 lines in height even if it contains less than 10 records.

If you want this only once on each page then do the following:
Remove your 'details b' section.
Make Page Header 10 lines in height approx.
Go to Page Header's properties in 'Format' and Check 'Underlay following sections'.

If you want this effect more than once on each page then you can group your records and do the same settings as mentioned above to that group header.

Just keep in mind you have to remove 'detail b' section.