HELP WITH UPDATE QUERY AND INNER JOIN AND OPENQUERY

If some could show me the syntax for updating a table in my database
with information in a linked table. the table I want to update is
called WorkList and I want to create an inner join query to a linked
table called public.FISCAL. This is all I have so far to create the
join...
SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
Now how can I change it to make and Update query on field B.[I-Plan] =
A.[I_PLAN]?
update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join SCH.[public].FISCAL
on Account_ID = B.[ACCOUNT#]
"mike11d11" wrote:

> If some could show me the syntax for updating a table in my database
> with information in a linked table. the table I want to update is
> called WorkList and I want to create an inner join query to a linked
> table called public.FISCAL. This is all I have so far to create the
> join...
> SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
> AS A
> INNER JOIN WorkList AS B
> ON A.Account_ID = B.[ACCOUNT#]
> Now how can I change it to make and Update query on field B.[I-Plan] =
> A.[I_PLAN]?
>
|||So this is what I have...
Select * FROM OPENQUERY(SCH,'SELECT [Account_ID], [I_PLAN] FROM
[public.FISCAL]') AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join A
on Account_ID = B.[ACCOUNT#]
I'll give it a try!
|||Here is the error I get when I use it.
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'SCH.public.FISCAL'.
|||anybody have an idea?

HELP WITH UPDATE QUERY AND INNER JOIN AND OPENQUERY

If some could show me the syntax for updating a table in my database
with information in a linked table. the table I want to update is
called WorkList and I want to create an inner join query to a linked
table called public.FISCAL. This is all I have so far to create the
join...
SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
Now how can I change it to make and Update query on field B.[I-Plan] =
A.[I_PLAN]?update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join SCH.[public].FISCAL
on Account_ID = B.[ACCOUNT#]
"mike11d11" wrote:

> If some could show me the syntax for updating a table in my database
> with information in a linked table. the table I want to update is
> called WorkList and I want to create an inner join query to a linked
> table called public.FISCAL. This is all I have so far to create the
> join...
> SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]'
)
> AS A
> INNER JOIN WorkList AS B
> ON A.Account_ID = B.[ACCOUNT#]
> Now how can I change it to make and Update query on field B.[I-Plan] =
> A.[I_PLAN]?
>|||So this is what I have...
Select * FROM OPENQUERY(SCH,'SELECT [Account_ID], [I_PLAN] FROM
[public.FISCAL]') AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join A
on Account_ID = B.[ACCOUNT#]
I'll give it a try!|||Here is the error I get when I use it.
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'SCH.public.FISCAL'.|||anybody have an idea?

HELP WITH UPDATE QUERY AND INNER JOIN AND OPENQUERY

If some could show me the syntax for updating a table in my database
with information in a linked table. the table I want to update is
called WorkList and I want to create an inner join query to a linked
table called public.FISCAL. This is all I have so far to create the
join...
SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
Now how can I change it to make and Update query on field B.[I-Plan] = A.[I_PLAN]?update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join SCH.[public].FISCAL
on Account_ID = B.[ACCOUNT#]
"mike11d11" wrote:
> If some could show me the syntax for updating a table in my database
> with information in a linked table. the table I want to update is
> called WorkList and I want to create an inner join query to a linked
> table called public.FISCAL. This is all I have so far to create the
> join...
> SELECT DSK FROM OPENQUERY(SCH,'SELECT Account_ID FROM [public.FISCAL]')
> AS A
> INNER JOIN WorkList AS B
> ON A.Account_ID = B.[ACCOUNT#]
> Now how can I change it to make and Update query on field B.[I-Plan] => A.[I_PLAN]?
>|||So this is what I have...
Select * FROM OPENQUERY(SCH,'SELECT [Account_ID], [I_PLAN] FROM
[public.FISCAL]') AS A
INNER JOIN WorkList AS B
ON A.Account_ID = B.[ACCOUNT#]
update WorkList
set [I-Plan] = A.[I_PLAN]
from Worklist B
inner join A
on Account_ID = B.[ACCOUNT#]
I'll give it a try!|||Here is the error I get when I use it.
Server: Msg 208, Level 16, State 1, Line 4
Invalid object name 'SCH.public.FISCAL'.|||anybody have an idea?

Help with UDF Syntax

I am a somewhat newbie to UDFs so don't laugh too hard!
I have 2 tables:
Tbl_Processes (first table)
ID
Process_Name
Included_In_Test
Tbl_Sub_Processes (second table)
ID
Sub_Process_Name
Lookup_To_Tbl_Processes_For_Process (points to the ID field of the
first table)
Included_In_Test
Here is what I am trying to do as explained in plain (I hope) english:
If Included_In_Test in the Processes table is 0 then return 0
BUT, if Included_In_Test in the Processes table is 1 then we have to find
out if any of the Sub Processes are also included. If none are, then we are
testing at the Process level and we still return 1
BUT, if only some of the Sub Processes are included in the test, then we
return 1 only for those that are included, otherwise we return zero.
Here is the code I am trying, and when I do a syntax check, I get a message
saying "Incorrect syntax near the keyword 'to'." Since there is no
occurrence of the character string 'to' in the code, I am puzzled. I believ
e
it has to do with my "SELECT SUM(Case " construct. Can anyone help? Or, am
I missing something completely different and simpler?
CREATE FUNCTION [dbo]. [Is_Process_And_Or_Sub_Process_Included_
In_Test]
(
@.ProcessID int,
@.SubProcessID int
)
RETURNS int
AS
BEGIN
Declare @.Result int
if @.ProcessID is null or @.SubProcessID is null
set @.Result to 0
ELSE
if (SELECT Included_In_Test FROM dbo.Tbl_Processes WHERE ID = @.ProcessID)
= 0
SET @.Result = 0
ELSE
if (SELECT SUM(Case When dbo.Tbl_Sub_Processes.Include_In_Test = 1 then
1 else 0 end) AS NumberOfSubProcessesIncluded) = 0
SET @.Result = 1
else
if (SELECT Included_In_Test FROM dbo.Tbl_Sub_Processess WHERE ID =
@.SubProcessID) = 1
SET @.Result = 1
else
Set @.Result = 0
ENDHi Bill,
set @.Result = 0
HTH, Jens Suessmeyer.|||Hi Bill,
whenever you get an error you can double click on that error and it will (in
many cases) will take you to the line where the error is.
Or the second way to look for an error is to do a <ctrl>+f and to a search
on whatever error you are getting.
In this case it was a trival mistake.
set @.Result = 0 as suggested by Jen
"Bill Sturdevant" wrote:

> I am a somewhat newbie to UDFs so don't laugh too hard!
> I have 2 tables:
> Tbl_Processes (first table)
> ID
> Process_Name
> Included_In_Test
> Tbl_Sub_Processes (second table)
> ID
> Sub_Process_Name
> Lookup_To_Tbl_Processes_For_Process (points to the ID field of the
> first table)
> Included_In_Test
> Here is what I am trying to do as explained in plain (I hope) english:
> If Included_In_Test in the Processes table is 0 then return 0
> BUT, if Included_In_Test in the Processes table is 1 then we have to find
> out if any of the Sub Processes are also included. If none are, then we a
re
> testing at the Process level and we still return 1
> BUT, if only some of the Sub Processes are included in the test, then we
> return 1 only for those that are included, otherwise we return zero.
> Here is the code I am trying, and when I do a syntax check, I get a messag
e
> saying "Incorrect syntax near the keyword 'to'." Since there is no
> occurrence of the character string 'to' in the code, I am puzzled. I beli
eve
> it has to do with my "SELECT SUM(Case " construct. Can anyone help? Or,
am
> I missing something completely different and simpler?
> CREATE FUNCTION [dbo]. [Is_Process_And_Or_Sub_Process_Included_
In_Test]
> (
> @.ProcessID int,
> @.SubProcessID int
> )
> RETURNS int
> AS
> BEGIN
> Declare @.Result int
> if @.ProcessID is null or @.SubProcessID is null
> set @.Result to 0
> ELSE
> if (SELECT Included_In_Test FROM dbo.Tbl_Processes WHERE ID = @.ProcessID
)
> = 0
> SET @.Result = 0
> ELSE
> if (SELECT SUM(Case When dbo.Tbl_Sub_Processes.Include_In_Test = 1 the
n
> 1 else 0 end) AS NumberOfSubProcessesIncluded) = 0
> SET @.Result = 1
> else
> if (SELECT Included_In_Test FROM dbo.Tbl_Sub_Processess WHERE ID =
> @.SubProcessID) = 1
> SET @.Result = 1
> else
> Set @.Result = 0
> END

Help with TSQL where clause syntax

Hey everyone I want a where clause based off a variable input and I'm having some trouble with the syntax. My current code(incorrect) is below but it shows what I am aiming for. Does anyone have any suggestions?

Code Snippet

(CASE
WHEN @.inIndustry = 'ALL' THEN
WHERE EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1)
ELSE
WHERE (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE
WHERE OpportunityID = op.OpportunituID AND monthyear LIKE '%' + @.Year1) END)

Maybe this:

Code Snippet

(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE

CASEWHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END--inner case

END)-- outer case

|||Hey. Thanks for the help but I dont see how that would work. I'm trying to make the WHERE clause dynamic in a way. It's all based off whatever the var @.inIndustry is equal to. So if @.inIndustry is equal to 'ALL' then is uses a certain where clause. I dont want to base the equivalence off of the entire statement there.|||

Code Snippet

WHERE(CASE

WHEN @.inIndustry ='ALL'AND

EXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

WHEN(acIF.Industry <>'Defense'OR acIF.Industry ISNULL)

ANDEXISTS(SELECT 1 FROM sysdba.C_PROJECTREVENUE

WHERE OpportunityID = op.OpportunituID AND monthyear LIKE'%'+ @.Year1)

THEN 1

ELSE 0

END)

= 1

|||Ahh I see how it works now. I plugged it in and it works like a charm. Thanks for the help!
|||

No problem; my pleasure.

Much appreciated if you can mark the solution as the answer

|||Actually it didnt work exactly as it should... This is the code i have at the moment.

Code Snippet

WHERE
(CASE
--MASTER
WHEN @.inIndustry = 'ALL'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)

THEN 1

--ENERGY AND UTILITIES
WHEN @.inIndustry = 'EU' AND acIf.Industry = 'Energy and Utilities'
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

-- NONGOV - E&U + OTHER
WHEN (acIF.Industry <> 'Defense' OR acIF.Industry IS NULL)
AND EXISTS (SELECT 1 FROM sysdba.C_PROJECTREVENUE WHERE OpportunityID = op.OpportunityID AND monthyear LIKE '%' + @.Year1)
THEN 1

ELSE 0
END) = 1

Now comparing original code and the code i wrote aboveI know that it performs the the last case statement no matter what i input for @.inIndustry. Is there any way to fix this?
|||

Nest CASE statements

CASE WHEN @.inIndustry = 'ALL' ..

ELSE

CASE WHEN ...

ELSE

END

END

|||I ended up adding checks to the last statement to make sure @.inIndustry wasn't equal to EU, ALL etc.. and it works fine now.

Thanks for all the help!