String Parameter for "ALL"

[shelby55]

Hi

I am using SQL code as a Union Query and I've created the parameter for Site:

SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and Data.Inst like {?Site}
Union ALL Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and Data.Inst like {?Site}

But in other software I can create the parameter of site but also have a selection for "ALL. In other words:
({?Site}="ALL" or Data.Inst={?Site}). Is something like this possible in SQL?

Thanks.

 

[gmmastros]

Yes. The only difference is, you are using a like comparison instead of equal comparison. This should work.

SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv 
FROM   Data 
WHERE  Data.DisDate >={?Begin_Date} 
       and Data.DisDate<={?End_Date} 
       and [!]({?Site} = 'ALL' Or Data.Inst like {?Site})[/!]

Union ALL 

Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv 
FROM   Data
WHERE  Data.DisDate >={?Begin_Date} 
       and Data.DisDate<={?End_Date} 
       and [!]({?Site} = 'ALL' Or Data.Inst like {?Site})[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Please read this very interesting blog on this common problem

http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/

PluralSight Learning Library

 

[shelby55]

Hi

When using the suggested syntax I get the error message "Not supported. Details: DAO Error Code Oxd88. Source: DAO database. Description: Data type mismatch in criteria expression".

Any suggestions? I did read the blog suggested (thank you) but as a newbie with SQL I didn't understand what I should do in this case.

Any and all assistance greatly appreciated.

 

[markros]

Are you using SQL Server or a different database? Your question reminds me of this one

http://social.msdn.microsoft.com/Fo...l/thread/3780e8f5-67d8-421f-9a13-36b6e28310e0

though it may be completely different problem.


PluralSight Learning Library

 

[shelby55]

Hi

I'm using crystal reports running from an Access 2002 database. However, I'm using the "add command" function which is written in SQL code.

Markos thanks for replying but the syntax that works correctly is:
SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and Data.Inst like {?Site}
Union ALL Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and Data.Inst like {?Site}

but the link you attached was for issues of dates which isn't my problem. The problem is when I add the ALL parameter to the code:

SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and ({?Site}='ALL' or Data.Inst like {?Site})
Union ALL Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and ({?Site}='ALL' or Data.Inst like {?Site})

Thanks.

 

[markros]

Can you set the parameter value to NULL and its description to 'All'. I'm not sure exactly how this can be done, but the error itself suggests that we need to use something compatible and 'All' we can not pass for a date parameter.

The query will then become

SELECT Data.AHour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and ({?Site} IS NULL or Data.Inst like {?Site})
Union ALL Select Data.Dhour as allhr, Data.AHour, Data.Dhour, Data.AcctNo, Data.ChartNo, Data.DisDate, Data.Inst, Data.DocSrv FROM Data
WHERE Data.DisDate >={?Begin_Date} and Data.DisDate<={?End_Date} and ({?Site} IS NULL or Data.Inst like {?Site})

PluralSight Learning Library

 

[shelby55]

Hi Markros

I did as you suggested and it accepted the code but when prompted it is defaulting to the initial inst number in the database though I don't have one included as a default in the parameter. It won't let me deselect the parameter so that all institutions are included. I can't type in all because that isn't noted anywhere.

Other thoughts?

 

[markros]

Is it possible to specify the default value for the parameter?

PluralSight Learning Library

 

[NastiaMurzin]

select * from tablename1, tablename2, tablename3;
go
That looks like union to me. U can also do
select * from tablename1, tablename2 where tablename1.columnname = tablename2.columnvalue;
go
or
select * from tablename1, tablename2 where tablename1.columnname = tablename2.columnname;
go
There.