Duplicate xReference Fields in 1 query ?

HI

Its been a while since i have posted here, but this one has stumped me. maybe i am just super hungry right now and missing something obvious... but i can get this one.

i would search the forums for the answer, but not even sure what to search for... not even sure if this (left joins) is the way to accomplish what i want.

Using MS-SQL database as the back end.

I have 3 data tables.
Table 1 is a cross reference table.
Table 2 is the primary data (group data)
Table 3 is the secondary data (users data) (multiple users in a group)

table 1 provides the text that matches the respective status from both the 2nd and 3rd tables.

xRefTable
MemberStatus MemberStatusText
---- ------
0 Non Member
1 Referral
2 Resident
3 Non Resident

PrimaryUserTable
GroupID GroupStatus
-- ----
1 0
2 3
3 1
4 2

SecondaryDataTable
MemberID GroupID MemberStatus
--- --- ----
1 1 0
2 1 0
3 1 0
4 1 0
5 2 3
6 2 3
7 3 1
8 3 1
9 4 2
10 4 1
11 4 1
12 4 3

The ultimate results i am trying to get is something like :

The Results should be able to yield

GroupID GroupStatus GroupStatusText MemberID MemberStatus MemberStatusText
--- ---- ----- --- ---- ------
4 2 Resident 9 2 Resident
4 2 Resident 10 1 Referral
4 2 Resident 11 1 Referral
4 2 Resident 12 3 Non Resident

i am trying to do this with a single SQL query, i am sure it is possible, but cant finger out how to structure it. this is what i was thinking, but i am sure you can see its not going to work.

Lines 4 and 7 are the hangup i believe.
Since both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?

1 SELECT
2 PrimaryUserTable.GroupID,
3 PrimaryUserTable.GroupStatus,
4 xRefTable.MemberStatusText as GroupStatusText

5 SecondaryDataTable.MemberID,
6 SecondaryDataTable.MemberStatus,
7 xRefTable.MemberStatusText as MemberStatusText

8 LEFT JOIN SecondaryDataTable on (PrimaryUserTable.GroupID = SecondaryDataTable.GroupID)
9 LEFT JOIN xRefTable on (PrimaryUserTable.GroupStatus = xRefTable.MemberStatus)
10 LEFT JOIN xRefTable on (SecondaryDataTable.MemberStatus = xRefTable.MemberStatus)

11 WHERE GroupID = 4
12 ORDER BY GroupID, MemberIDSince both are referring to 'MemberStatusText', how do i specify that line 4 relates to the primary data and line 7 related to the secondary data ?using table aliasesSELECT PrimaryUserTable.GroupID
, PrimaryUserTable.GroupStatus
, GroupXref.MemberStatusText as GroupStatusText
, SecondaryDataTable.MemberID
, SecondaryDataTable.MemberStatus
, MemberXref.MemberStatusText as MemberStatusText
FROM PrimaryUserTable
INNER
JOIN SecondaryDataTable
on SecondaryDataTable.GroupID = PrimaryUserTable.GroupID
INNER
JOIN xRefTable as GroupXref
on GroupXref.MemberStatus = PrimaryUserTable.GroupStatus
INNER
JOIN xRefTable as MemberXref
on MemberXref.MemberStatus = SecondaryDataTable.MemberStatus
WHERE PrimaryUserTable.GroupID = 4
ORDER
BY PrimaryUserTable.GroupID
, SecondaryDataTable.MemberID:)|||EXCELLENT !

exactly what i needed. the table alisases were just skipping from my mind that night.

THANK YOUsql

Dundas help topic missing: 'Start from Zero' property

I searched the Dundas help for this property but found nothing. I've been
experimenting with a stacked bar chart trying to work around another issue
related to sorting the bars from left to right with a zero line and having
the same order in the legend. I don't see that the property affects the
outcome of the chart. Anyone able to shed some light on what this property
is intended for?
Thanks,
Steve MunLeeuwHi Steve,
The StartFromZero property is not applicable to any chart type that
originates at the Axis, like a bar or column chart. This would be used
for a chart type that is not attached like a line, stock, gantt (or any
of the other range charts), etc. When set to true, the axis will
originate from zero regardless of data value. When set to false, the
chart will find a 'best' origin to focus the view to your data.
For example, take a stock chart when the security typically trades from
$100-103 daily. Viewing this with an axis starting from zero will make
the variations not noticeable. Setting StartFromZero to false will
make the trade range be focused in the chart area by having the minimum
value closer to 100 -- you will now be able to see the changes over
time.
If you would like help with your sorting problem please send the
request to our support dept and ask them to forward it to me.
Andrew Bryan
Dundas Software
Steve MunLeeuw wrote:
> I searched the Dundas help for this property but found nothing. I've been
> experimenting with a stacked bar chart trying to work around another issue
> related to sorting the bars from left to right with a zero line and having
> the same order in the legend. I don't see that the property affects the
> outcome of the chart. Anyone able to shed some light on what this property
> is intended for?
> Thanks,
> Steve MunLeeuwsql

dtswizard.exe Only one row returned importing .XLS

When importing data from an xls file, only first column of first row is returned? Am I missing something?Make sure that in the Source component you're selecting a worksheet name that ends in "$". Otherwise, what you're selecting is actually a range and it might only be defined as the first row.
|||Thanks a Million!|||

Hi JayH,

Can you tell me how to loop on one table. I have an input mapped to a column of this table. Is there a way to accomplish it within DFT ?

Will appreciate your help.

Thanks,

Lohan

|||Could you give a better description of your problem and what you want to accomplish? Typically, looping is not done inside a data flow, it is done with a For Loop container in the control flow.

DTS Work Flow issue

OK, this shouldn't be this hard, I'm sure I am missing something
simple.
I have a DTS package with 1 source connection and 1 destination
connection.
I want to transform 2 tables, each as a separate Transformation task.
Now to the isssue.
(this one I get..)
If both Succeed, execute another set of procedures..
(This one I can't seem to figure out)
If Either of them fail, Execute a different set of procedures.
How can you set an OR precedence on failure?
TIA!
DavidOn Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david.hay@.gmail.com>
wrote:
>OK, this shouldn't be this hard, I'm sure I am missing something
>simple.
>I have a DTS package with 1 source connection and 1 destination
>connection.
>I want to transform 2 tables, each as a separate Transformation task.
>Now to the isssue.
>(this one I get..)
>If both Succeed, execute another set of procedures..
>
>(This one I can't seem to figure out)
>If Either of them fail, Execute a different set of procedures.
>How can you set an OR precedence on failure?
On success of a step, go to another step that sets a variable.
Have a step downstream that reads both variables.
And check out the .dts newsgroup!
J.|||On Aug 10, 12:17 am, JXStern <JXSternChange...@.gte.net> wrote:
> On Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david...@.gmail.com>
> wrote:
>
> >OK, this shouldn't be this hard, I'm sure I am missing something
> >simple.
> >I have a DTS package with 1 source connection and 1 destination
> >connection.
> >I want to transform 2 tables, each as a separate Transformation task.
> >Now to the isssue.
> >(this one I get..)
> >If both Succeed, execute another set of procedures..
> >(This one I can't seem to figure out)
> >If Either of them fail, Execute a different set of procedures.
> >How can you set an OR precedence on failure?
> On success of a step, go to another step that sets a variable.
> Have a step downstream that reads both variables.
> And check out the .dts newsgroup!
> J.
Thanks!

DTS Work Flow issue

OK, this shouldn't be this hard, I'm sure I am missing something
simple.
I have a DTS package with 1 source connection and 1 destination
connection.
I want to transform 2 tables, each as a separate Transformation task.
Now to the isssue.
(this one I get..)
If both Succeed, execute another set of procedures..
(This one I can't seem to figure out)
If Either of them fail, Execute a different set of procedures.
How can you set an OR precedence on failure?
TIA!
DavidOn Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david.hay@.gmail.com>
wrote:
>OK, this shouldn't be this hard, I'm sure I am missing something
>simple.
>I have a DTS package with 1 source connection and 1 destination
>connection.
>I want to transform 2 tables, each as a separate Transformation task.
>Now to the isssue.
>(this one I get..)
>If both Succeed, execute another set of procedures..
>
>(This one I can't seem to figure out)
>If Either of them fail, Execute a different set of procedures.
>How can you set an OR precedence on failure?
On success of a step, go to another step that sets a variable.
Have a step downstream that reads both variables.
And check out the .dts newsgroup!
J.|||On Aug 10, 12:17 am, JXStern <JXSternChange...@.gte.net> wrote:
> On Thu, 09 Aug 2007 15:02:03 -0000, David Hay <david...@.gmail.com>
> wrote:
>
>
>
>
>
>
>
>
> On success of a step, go to another step that sets a variable.
> Have a step downstream that reads both variables.
> And check out the .dts newsgroup!
> J.
Thanks!