Get all records from 1 table

I have a view with the following FROM clause. I want to be able to get ALL
matching records from the ProfitCenterCodeSubs table, even if there are none
in the RepairOrderTasks table. Thanks.
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
DavidDavid
See if this helps
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode LEFT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
OR dbo.RepairOrderTasks.SubCenterID is null
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||Nope. Same results.
David
*** Sent via Developersdex http://www.examnotes.net ***|||So, please post DDL+ sample data + expected result
"David" <daman@.lifetime.com> wrote in message
news:esFBCr7WFHA.3712@.TK2MSFTNGP09.phx.gbl...
> Nope. Same results.
> David
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Consider selecting from ProfitCenterCodeSubs and left joining to
RepairOrderTasks.
"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
> I have a view with the following FROM clause. I want to be able to get
ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>|||"David Developer" <dlchase@.lifetimeinc.com> wrote in message
news:uFCuOW7WFHA.2776@.TK2MSFTNGP12.phx.gbl...
>I have a view with the following FROM clause. I want to be able to get ALL
> matching records from the ProfitCenterCodeSubs table, even if there are
> none
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
Swap the LEFT OUTER JOIN for a RIGHT OUTER JOIN, or switch the ON clause
around - you want the table that you need to retrieve all records from on
the side of the ON that the outer join indicates.
Dan|||Just try this
FROM dbo.RepairOrderTasks INNER JOIN
dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
dbo.Employees.EmployeeCode RIGHT OUTER JOIN
dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
dbo.ProfitCenterCodeSubs.SubCenterID
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>|||Then use a RIGHT OUTER JOIN instead.
FROM
(
dbo.RepairOrderTasks
INNER JOIN
dbo.Employees
ON dbo.RepairOrderTasks.EmployeeCode = dbo.Employees.EmployeeCode
)
right OUTER JOIN
dbo.ProfitCenterCodeSubs
ON dbo.RepairOrderTasks.SubCenterID = dbo.ProfitCenterCodeSubs.SubCenterID
AMB
"David Developer" wrote:

> I have a view with the following FROM clause. I want to be able to get AL
L
> matching records from the ProfitCenterCodeSubs table, even if there are no
ne
> in the RepairOrderTasks table. Thanks.
> FROM dbo.RepairOrderTasks INNER JOIN
> dbo.Employees ON dbo.RepairOrderTasks.EmployeeCode =
> dbo.Employees.EmployeeCode LEFT OUTER JOIN
> dbo.ProfitCenterCodeSubs ON dbo.RepairOrderTasks.SubCenterID =
> dbo.ProfitCenterCodeSubs.SubCenterID
> David
>
>sql

generating sql string for execution

I've a need to generate a condition clause (if statement) from 3 variables: a value, an operator & another value.

To generate this: 'abcd' like 'a%'
& verify if the condition is satisfied I've done the following:

CREATE TABLE #Temp(Result varchar(10))
declare @.cond1 as varchar(50), @.op as varchar(4), @.cond2 as varchar(50),
@.expr as varchar(50),@.result as varchar(10)
set @.cond1 = '''abcd'''
set @.op = 'like'
set @.cond2 = '''a%'''
set @.expr = @.cond1 + ' ' + @.op + ' ' + @.cond2
insert into #temp exec ('select case when ' + @.expr + ' then ''true'' else ''false'' end')
if exists (select result from #temp where result = 'true')
select 'it is true'
else
select 'it is false'
drop table #temp

It works, but a bit clumsy. Is there a more elegant way to do this ?How about something mildly perverse like:DECLARE @.cond1 as varchar(50), @.op as varchar(4), @.cond2 as varchar(50)

SET @.cond1 = '''abcd'''
SET @.op = 'like'
SET @.cond2 = '''a%'''

EXECUTE ('SELECT ''it is '' + CASE WHEN (' + @.cond1 + ' ' + @.op
+ ' ' + @.cond2 + ') THEN ''true'' ELSE ''false'' END')The extra parentheses are just digital "seat belts" in case anything goes wrong cooking up your expression.

-PatP|||Thanks Pat, very creative.

An extension on that: how do I get the result of the execute into a variable ?

with a select one can do this:

declare @.temp as varchar(10)
select @.temp = (select 'abc')
print @.temp

but that doesn't work with an exec

Thanks
Colin|||Now we need to get "creative" to make that happen!DECLARE
@.cond1 AS VARCHAR(50)
, @.cond2 AS VARCHAR(50)
, @.cmd AS NVARCHAR(200)
, @.op AS VARCHAR(4)
, @.result AS NVARCHAR(50)

SET @.cond1 = '''abcd'''
SET @.op = 'like'
SET @.cond2 = '''a%'''

SET @.cmd = 'SELECT @.i = ''it is '' + CASE WHEN (' + @.cond1 + ' ' + @.op
+ ' ' + @.cond2 + ') THEN ''true'' ELSE ''false'' END'

EXECUTE sp_executesql @.cmd, N'@.i NVARCHAR(50) OUTPUT ', @.result OUTPUT

SELECT @.result -- Just to show it worked-PatP

Generating complicate WHERE clause via parameters

Hi,
I have a large number of parameters defined which can be null and I have to
generate WHERE clause based on valid parameters, this makes things complicated
becuse I have to add "AND" to the WHERE clause depending on availability of
the parameters, the number of validation I have to do increases gradually
because the 10th parametrs should check for availability of 10 previous
parameters
before it can add "AND" infront of it. Is there any slick ways to handle
this problem?
Thank you.COALESCE will do.
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Thank you for you reply, I still do not see how CAOESECE can solve my problem.
My query looks like this.
SELECT column1, column2 FROM table1
WHERE
column3 = val1
AND
column4 = val2
with out val1 parameter supplied it should be:
SELECT column1, column2 FROM table1
WHERE
column4 = val2
So printing "AND" is the problem and only way to do it for me right now is
to check
if val1 was supplied or not.
can COALESCE help in this situation?
Thank you.
"Bing Bing Yu" wrote:
> COALESCE will do.
>
> "JC" wrote:
> > Hi,
> >
> > I have a large number of parameters defined which can be null and I have to
> > generate WHERE clause based on valid parameters, this makes things complicated
> > becuse I have to add "AND" to the WHERE clause depending on availability of
> > the parameters, the number of validation I have to do increases gradually
> > because the 10th parametrs should check for availability of 10 previous
> > parameters
> > before it can add "AND" infront of it. Is there any slick ways to handle
> > this problem?
> >
> > Thank you.|||WHERE column3 = COALESCE(val1, column3)
AND column4 = COALESCE(val2, column4)
"JC" wrote:
> Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> My query looks like this.
> SELECT column1, column2 FROM table1
> WHERE
> column3 = val1
> AND
> column4 = val2
> with out val1 parameter supplied it should be:
> SELECT column1, column2 FROM table1
> WHERE
> column4 = val2
> So printing "AND" is the problem and only way to do it for me right now is
> to check
> if val1 was supplied or not.
> can COALESCE help in this situation?
> Thank you.
>
> "Bing Bing Yu" wrote:
> >
> > COALESCE will do.
> >
> >
> > "JC" wrote:
> >
> > > Hi,
> > >
> > > I have a large number of parameters defined which can be null and I have to
> > > generate WHERE clause based on valid parameters, this makes things complicated
> > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > the parameters, the number of validation I have to do increases gradually
> > > because the 10th parametrs should check for availability of 10 previous
> > > parameters
> > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > this problem?
> > >
> > > Thank you.|||I eneded up doing it like this (using AdventureWorks) can this be enhanced?
(I have credited you by the way, thank you)
="DECLARE @.ContactID INT, " &
"@.FirstName NVARCHAR(50) " &
Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
" SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
"WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName =COALESCE(@.FirstName, FirstName)"
"Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
>
> WHERE column3 = COALESCE(val1, column3)
> AND column4 = COALESCE(val2, column4)
>
> "JC" wrote:
> >
> > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> >
> > My query looks like this.
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column3 = val1
> > AND
> > column4 = val2
> >
> > with out val1 parameter supplied it should be:
> >
> > SELECT column1, column2 FROM table1
> > WHERE
> > column4 = val2
> > So printing "AND" is the problem and only way to do it for me right now is
> > to check
> > if val1 was supplied or not.
> >
> > can COALESCE help in this situation?
> >
> > Thank you.
> >
> >
> > "Bing Bing Yu" wrote:
> >
> > >
> > > COALESCE will do.
> > >
> > >
> > > "JC" wrote:
> > >
> > > > Hi,
> > > >
> > > > I have a large number of parameters defined which can be null and I have to
> > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > the parameters, the number of validation I have to do increases gradually
> > > > because the 10th parametrs should check for availability of 10 previous
> > > > parameters
> > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > this problem?
> > > >
> > > > Thank you.|||Hi,
I think you need to build the query something like this.
declare @.str as nvarchar(1000)
set @.str = ''
set @.str = @.str + "select * from "...
if @.val is null
set @.str = @.str + "val = .."
exec sp_executesql @.str
you can use if conditions where ever required.
Amarnath
"JC" wrote:
> I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> (I have credited you by the way, thank you)
> ="DECLARE @.ContactID INT, " &
> "@.FirstName NVARCHAR(50) " &
> Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => COALESCE(@.FirstName, FirstName)"
>
> "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> >
> > WHERE column3 = COALESCE(val1, column3)
> > AND column4 = COALESCE(val2, column4)
> >
> >
> > "JC" wrote:
> >
> > >
> > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > >
> > > My query looks like this.
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column3 = val1
> > > AND
> > > column4 = val2
> > >
> > > with out val1 parameter supplied it should be:
> > >
> > > SELECT column1, column2 FROM table1
> > > WHERE
> > > column4 = val2
> > > So printing "AND" is the problem and only way to do it for me right now is
> > > to check
> > > if val1 was supplied or not.
> > >
> > > can COALESCE help in this situation?
> > >
> > > Thank you.
> > >
> > >
> > > "Bing Bing Yu" wrote:
> > >
> > > >
> > > > COALESCE will do.
> > > >
> > > >
> > > > "JC" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I have a large number of parameters defined which can be null and I have to
> > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > the parameters, the number of validation I have to do increases gradually
> > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > parameters
> > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > this problem?
> > > > >
> > > > > Thank you.|||Well, I'm pretty sure the dynamic query works just fine. One concern would
be since it's dynamic, SQL server won't be able to optimize the query.
"Amarnath" wrote:
> Hi,
> I think you need to build the query something like this.
> declare @.str as nvarchar(1000)
> set @.str = ''
> set @.str = @.str + "select * from "...
> if @.val is null
> set @.str = @.str + "val = .."
> exec sp_executesql @.str
> you can use if conditions where ever required.
> Amarnath
>
> "JC" wrote:
> >
> > I eneded up doing it like this (using AdventureWorks) can this be enhanced?
> > (I have credited you by the way, thank you)
> >
> > ="DECLARE @.ContactID INT, " &
> > "@.FirstName NVARCHAR(50) " &
> > Iif(Len(Parameters!ContactID.Value) > 0, "SELECT @.ContactID = " &
> > Parameters!ContactID.Value, " SELECT @.ContactID = null ") &
> > Iif(Len(Parameters!FirstName.Value) > 0, "SELECT @.FirstName = '" &
> > Parameters!FirstName.Value & "' " , "SELECT @.FirstName = null ") &
> > " SELECT Title, FirstName, MiddleName, LastName from Person.Contact " &
> > "WHERE ContactID = COALESCE(@.ContactID, ContactID) AND FirstName => > COALESCE(@.FirstName, FirstName)"
> >
> >
> >
> > "Bing Bing Yu"ë'ì?´ ì'ì?±í' ë?´ì?©:
> >
> > >
> > >
> > > WHERE column3 = COALESCE(val1, column3)
> > > AND column4 = COALESCE(val2, column4)
> > >
> > >
> > > "JC" wrote:
> > >
> > > >
> > > > Thank you for you reply, I still do not see how CAOESECE can solve my problem.
> > > >
> > > > My query looks like this.
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column3 = val1
> > > > AND
> > > > column4 = val2
> > > >
> > > > with out val1 parameter supplied it should be:
> > > >
> > > > SELECT column1, column2 FROM table1
> > > > WHERE
> > > > column4 = val2
> > > > So printing "AND" is the problem and only way to do it for me right now is
> > > > to check
> > > > if val1 was supplied or not.
> > > >
> > > > can COALESCE help in this situation?
> > > >
> > > > Thank you.
> > > >
> > > >
> > > > "Bing Bing Yu" wrote:
> > > >
> > > > >
> > > > > COALESCE will do.
> > > > >
> > > > >
> > > > > "JC" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I have a large number of parameters defined which can be null and I have to
> > > > > > generate WHERE clause based on valid parameters, this makes things complicated
> > > > > > becuse I have to add "AND" to the WHERE clause depending on availability of
> > > > > > the parameters, the number of validation I have to do increases gradually
> > > > > > because the 10th parametrs should check for availability of 10 previous
> > > > > > parameters
> > > > > > before it can add "AND" infront of it. Is there any slick ways to handle
> > > > > > this problem?
> > > > > >
> > > > > > Thank you.|||One easy way would be to use 1=1 and
'Select col1 from table 1 where 1=1 and' + @.str_where
@. str_where can be populated from the parameters you accept in
"JC" wrote:
> Hi,
> I have a large number of parameters defined which can be null and I have to
> generate WHERE clause based on valid parameters, this makes things complicated
> becuse I have to add "AND" to the WHERE clause depending on availability of
> the parameters, the number of validation I have to do increases gradually
> because the 10th parametrs should check for availability of 10 previous
> parameters
> before it can add "AND" infront of it. Is there any slick ways to handle
> this problem?
> Thank you.|||Although you can do dynamic SQL I find this technique easier:
select somefields from sometable where (somefield = @.Param1 or @.Param1 ='All') and (anotherfield = @.Param2 or @.Param1 = 'All')
Note that if a number field you can check for a number that doesn't exist in
your database whether it is -1 or -99999 or whatever as long as you know it
doesn't exist in real life.
I prefer to have an All selection, however, you can use Null or Empty string
as well.
select somefields from sometable where (somefield = @.Param1 or
isnull(@.Param1)) and (anotherfield = @.Param2 or isnull(@.Param1))
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Sunil" <Sunil@.discussions.microsoft.com> wrote in message
news:2666D2EB-03EA-47CF-A958-52EC722567FC@.microsoft.com...
> One easy way would be to use 1=1 and
> 'Select col1 from table 1 where 1=1 and' + @.str_where
> @. str_where can be populated from the parameters you accept in
>
> "JC" wrote:
>> Hi,
>> I have a large number of parameters defined which can be null and I have
>> to
>> generate WHERE clause based on valid parameters, this makes things
>> complicated
>> becuse I have to add "AND" to the WHERE clause depending on availability
>> of
>> the parameters, the number of validation I have to do increases gradually
>> because the 10th parametrs should check for availability of 10 previous
>> parameters
>> before it can add "AND" infront of it. Is there any slick ways to handle
>> this problem?
>> Thank you.