Dynamic SQL Newbie needs urgent help

Have a report that is working fine. Customer wants to add another field to
it. I have a field name of navigationPK to get the field they want. Only
problem is is that the navigationPK can be a primary key from any number of
tables.
Scenario......
Have a usertask displayed on dashboard....need to do a report on all
overdue tasks. Each task has a particular navigationpk. This navpk can be
linked to three or four tables. ie....navpk 1189134 could be the pk in a
tpp table, rvc table or a tr table. Need to ascertain which one it is so
that I can get the relevant additional field from each table.
Kinda hard to explain. But if anybody wants anymore info then please ask.
I was told to do dynamic sql...only prob is I have no idea where to start
and how to fit into existing script. Below is my initial script....works
fine so far. Needs tidying up I know, but essentially it works.
select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId
from person p
--INNER JOIN personherdrole phr on p.id = phr.personid
--INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
The additional field required is the herdid, and this herdid could be from
the tpp table, rvc table, tr table .
Help please. Going round in circles and not sure where to start.Article about dynamic sql
http://www.sommarskog.se/dynamic_sql.html
You did not describe the rules as to how you know which table it will be in.
Can it be in multiple tables but you only want a specific one?
Or the HerdID will only be in one of the tables?
If so
select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
ut.DueDate As DueDate
--h.id as HerdId
from person p
INNER JOIN personherdrole phr on p.id = phr.personid
INNER JOIN herd h on phr.Herdid = h.id
INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
INNER JOIN usertask ut on ut.UserName = p.UserName
Inner JOIN (SELECT newfield, HerdID
FROM tpp
UNION
SELECT newfield, HerdID
FROM rvc
UNION
SELECT newfield, HerdID
FROM tr ) DerivedTable on DerivedTable.HerdID=HERD.id
where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
No Dynamic required
"Nat Johnson" wrote:
> Have a report that is working fine. Customer wants to add another field to
> it. I have a field name of navigationPK to get the field they want. Only
> problem is is that the navigationPK can be a primary key from any number of
> tables.
> Scenario......
> Have a usertask displayed on dashboard....need to do a report on all
> overdue tasks. Each task has a particular navigationpk. This navpk can be
> linked to three or four tables. ie....navpk 1189134 could be the pk in a
> tpp table, rvc table or a tr table. Need to ascertain which one it is so
> that I can get the relevant additional field from each table.
> Kinda hard to explain. But if anybody wants anymore info then please ask.
> I was told to do dynamic sql...only prob is I have no idea where to start
> and how to fit into existing script. Below is my initial script....works
> fine so far. Needs tidying up I know, but essentially it works.
> select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
> ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
> ut.DueDate As DueDate
> --h.id as HerdId
>
> from person p
> --INNER JOIN personherdrole phr on p.id = phr.personid
> --INNER JOIN herd h on phr.Herdid = h.id
> INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
> INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
> INNER JOIN usertask ut on ut.UserName = p.UserName
> where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
>
> The additional field required is the herdid, and this herdid could be from
> the tpp table, rvc table, tr table .
> Help please. Going round in circles and not sure where to start.
>|||1. create your select statement
2 run the select statement and generate fields
3. generate report
last but not least turn the sql into a string after you have everything
working. us a report parameter to pass the dynamic part to the querry.
like this
="select * from " + Parameters!company.value
its that easy.
rmartinez wrote:
>Article about dynamic sql
>http://www.sommarskog.se/dynamic_sql.html
>You did not describe the rules as to how you know which table it will be in.
>Can it be in multiple tables but you only want a specific one?
>Or the HerdID will only be in one of the tables?
>If so
>select fulllegalname, p.Id AS PersonId, dali.Name As DDCMAreaName,
>ut.UserName As UTUserName, ut.MessageBody AS Task, ut.NavigationPK as NavPK,
>ut.DueDate As DueDate
>--h.id as HerdId
>from person p
>INNER JOIN personherdrole phr on p.id = phr.personid
>INNER JOIN herd h on phr.Herdid = h.id
>INNER JOIN personddcmarealink pdal on pdal.personId = p.Id
>INNER JOIN ddcmarealookupitem dali on pdal.ddcmarealookupitemId = dali.Id
>INNER JOIN usertask ut on ut.UserName = p.UserName
>Inner JOIN (SELECT newfield, HerdID
> FROM tpp
> UNION
> SELECT newfield, HerdID
> FROM rvc
> UNION
> SELECT newfield, HerdID
> FROM tr ) DerivedTable on DerivedTable.HerdID=HERD.id
>where (ut.DeletedDate IS NULL) and (ut.DueDate < getdate())
>No Dynamic required
>> Have a report that is working fine. Customer wants to add another field to
>> it. I have a field name of navigationPK to get the field they want. Only
>[quoted text clipped - 31 lines]
>> Help please. Going round in circles and not sure where to start.
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200508/1