Conceptual ideas - 2 tables one changes other complete Cursors?

[rcamarda]

I think cursors might help me, but I'm not sure. I'm looking for ideas
on how to solve a problem I have.

Consider two tables, one table contains student information (very wide
100 fields) , the other historical changes of the student information,
(narrow, just fields that record changes).

As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
RECORD_DT and has one student in it.

Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
records, since the student changed their major 2 times.

I want to end up with a table the contains 3 rows, the 2 changes to the
Major and the current student record. I want each row to be complete.
Everything that I have tried (joins, outer joins, union) I end up with
some field being null (in my example, the STUDENT_NAME would on be in
the original row, and null for the two changes)
I know this is pretty vague, but I am wondering if this is a place to
use CURSORS?
(Some of you may recognize this as a type 2 dimension or slowly
changing dimension as used in a data warehouse, which it is. I need to
build up my historical changes to I can feed it to my warehouse. I have
the current student record, and all the descreet changes made to the
student.)
TIA
Rob

[Stu]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

How about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJO R, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu

[Stu]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

How about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJO R, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu

[AntiTrust]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?


"rcamarda" <rcamarda@cable speed.com> wrote in message
news:1118684160 .349709.100810@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>I think cursors might help me, but I'm not sure. I'm looking for ideas
> on how to solve a problem I have.
>
> Consider two tables, one table contains student information (very wide
> 100 fields) , the other historical changes of the student information,
> (narrow, just fields that record changes).
>
> As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
> RECORD_DT and has one student in it.
>
> Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
> records, since the student changed their major 2 times.
>
> I want to end up with a table the contains 3 rows, the 2 changes to the
> Major and the current student record. I want each row to be complete.
> Everything that I have tried (joins, outer joins, union) I end up with
> some field being null (in my example, the STUDENT_NAME would on be in
> the original row, and null for the two changes)
> I know this is pretty vague, but I am wondering if this is a place to
> use CURSORS?
> (Some of you may recognize this as a type 2 dimension or slowly
> changing dimension as used in a data warehouse, which it is. I need to
> build up my historical changes to I can feed it to my warehouse. I have
> the current student record, and all the descreet changes made to the
> student.)
> TIA
> Rob
>[/color]

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.

[AntiTrust]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?


"rcamarda" <rcamarda@cable speed.com> wrote in message
news:1118684160 .349709.100810@ z14g2000cwz.goo glegroups.com.. .[color=blue]
>I think cursors might help me, but I'm not sure. I'm looking for ideas
> on how to solve a problem I have.
>
> Consider two tables, one table contains student information (very wide
> 100 fields) , the other historical changes of the student information,
> (narrow, just fields that record changes).
>
> As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
> RECORD_DT and has one student in it.
>
> Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
> records, since the student changed their major 2 times.
>
> I want to end up with a table the contains 3 rows, the 2 changes to the
> Major and the current student record. I want each row to be complete.
> Everything that I have tried (joins, outer joins, union) I end up with
> some field being null (in my example, the STUDENT_NAME would on be in
> the original row, and null for the two changes)
> I know this is pretty vague, but I am wondering if this is a place to
> use CURSORS?
> (Some of you may recognize this as a type 2 dimension or slowly
> changing dimension as used in a data warehouse, which it is. I need to
> build up my historical changes to I can feed it to my warehouse. I have
> the current student record, and all the descreet changes made to the
> student.)
> TIA
> Rob
>[/color]

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

CREATE TABLE "dbo"."F_Studen t_Sample"
(
"STUDENT_ID " VARCHAR(20) NOT NULL,
"STUDENT_LEAD_I D" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNA ME" VARCHAR(40) NULL,
"STUDENT_FIRSTN AME" VARCHAR(40) NULL,
"STUDENT_CAMPUS _ID" VARCHAR(10) NULL,
"STUDENT_ADMREP _ID" VARCHAR(10) NULL,
"STUDENT_MARKET CODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sampl e] VALUES
('100','900','2 005-05-01','CAMARDA',' ROBERT','HOST*0 01','TLS*123',' I20')


CREATE TABLE "dbo"."Student_ Changes_Sample"
(
"STUDENT_ID " VARCHAR(20) NOT NULL,
"CHANGE_COD E" NUMERIC(19) NULL,
"CHANGE" VARCHAR(100) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_CAMPUS _ID" VARCHAR(10) NULL,
"STUDENT_ADMREP _ID" VARCHAR(10) NULL
)
;
-- The addtion of the two columns my be redundant, (STUDENT_CAMPUS _ID
and STUDENT_ADMREP_ ID)
-- CHANGE_CODE = 7, CHANGE will contain the new value for
STUDENT_CAMPUS_ ID
-- CHANGE_CODE = 10, CHANGE will contain the new value for
STUDENT_ADMREP_ ID
-- STUDENT_ID is my "primary key" but it is not unique in this case,
since I need all the rows.

INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 002','2001-01-03','HOST*002', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 003','2002-04-03','HOST*003', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 004','2003-02-13','HOST*004', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'DMI10 ','2003-02-13',NULL,'DMI10 ')

I need to end up with 5 rows of information, the current record found
in F_STUDENT_SAMPL E, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

CREATE TABLE "dbo"."F_Studen t_Sample"
(
"STUDENT_ID " VARCHAR(20) NOT NULL,
"STUDENT_LEAD_I D" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNA ME" VARCHAR(40) NULL,
"STUDENT_FIRSTN AME" VARCHAR(40) NULL,
"STUDENT_CAMPUS _ID" VARCHAR(10) NULL,
"STUDENT_ADMREP _ID" VARCHAR(10) NULL,
"STUDENT_MARKET CODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sampl e] VALUES
('100','900','2 005-05-01','CAMARDA',' ROBERT','HOST*0 01','TLS*123',' I20')


CREATE TABLE "dbo"."Student_ Changes_Sample"
(
"STUDENT_ID " VARCHAR(20) NOT NULL,
"CHANGE_COD E" NUMERIC(19) NULL,
"CHANGE" VARCHAR(100) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_CAMPUS _ID" VARCHAR(10) NULL,
"STUDENT_ADMREP _ID" VARCHAR(10) NULL
)
;
-- The addtion of the two columns my be redundant, (STUDENT_CAMPUS _ID
and STUDENT_ADMREP_ ID)
-- CHANGE_CODE = 7, CHANGE will contain the new value for
STUDENT_CAMPUS_ ID
-- CHANGE_CODE = 10, CHANGE will contain the new value for
STUDENT_ADMREP_ ID
-- STUDENT_ID is my "primary key" but it is not unique in this case,
since I need all the rows.

INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 002','2001-01-03','HOST*002', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 003','2002-04-03','HOST*003', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 004','2003-02-13','HOST*004', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'DMI10 ','2003-02-13',NULL,'DMI10 ')

I need to end up with 5 rows of information, the current record found
in F_STUDENT_SAMPL E, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_ id,
'' as student_lastnam e
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_camp us_id,
t2.student_last name
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'


I get:
1000139200 NULL
1000139200 003
1000139200 006
1000139200 016
1000139200 HOST*006 Iverson Iii

I need the last name (Iverson Iii) to be on all rows

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_ id,
'' as student_lastnam e
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_camp us_id,
t2.student_last name
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'


I get:
1000139200 NULL
1000139200 003
1000139200 006
1000139200 016
1000139200 HOST*006 Iverson Iii

I need the last name (Iverson Iii) to be on all rows

[David Portas]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

Try this:

SELECT S.student_id, S.student_lead_ id, C.record_dt,
S.student_lastn ame, S.student_first name,
COALESCE(C.stud ent_campus_id,S .student_campus _id) AS student_campus_ id,
COALESCE(C.stud ent_admrep_id,S .student_admrep _id) AS student_admrep_ id,
S.student_marke tcode_id
FROM f_student_sampl e AS S,
student_changes _sample AS C

--
David Portas
SQL Server MVP
--

[David Portas]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

Try this:

SELECT S.student_id, S.student_lead_ id, C.record_dt,
S.student_lastn ame, S.student_first name,
COALESCE(C.stud ent_campus_id,S .student_campus _id) AS student_campus_ id,
COALESCE(C.stud ent_admrep_id,S .student_admrep _id) AS student_admrep_ id,
S.student_marke tcode_id
FROM f_student_sampl e AS S,
student_changes _sample AS C

--
David Portas
SQL Server MVP
--

[David Portas]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:O-2dnduoiZPNdjDfR Vn-oA@giganews.com ...[color=blue]
> Try this:
>
> SELECT S.student_id, S.student_lead_ id, C.record_dt,
> S.student_lastn ame, S.student_first name,
> COALESCE(C.stud ent_campus_id,S .student_campus _id) AS student_campus_ id,
> COALESCE(C.stud ent_admrep_id,S .student_admrep _id) AS student_admrep_ id,
> S.student_marke tcode_id
> FROM f_student_sampl e AS S,
> student_changes _sample AS C
>
> --
> David Portas
> SQL Server MVP
> --
>
>[/color]

[David Portas]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:O-2dnduoiZPNdjDfR Vn-oA@giganews.com ...[color=blue]
> Try this:
>
> SELECT S.student_id, S.student_lead_ id, C.record_dt,
> S.student_lastn ame, S.student_first name,
> COALESCE(C.stud ent_campus_id,S .student_campus _id) AS student_campus_ id,
> COALESCE(C.stud ent_admrep_id,S .student_admrep _id) AS student_admrep_ id,
> S.student_marke tcode_id
> FROM f_student_sampl e AS S,
> student_changes _sample AS C
>
> --
> David Portas
> SQL Server MVP
> --
>
>[/color]

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.stud ent_campus_id,S .student_campu s_id) AS
student_campus_ id,
COALESCE(C.stud ent_admrep_id,S .student_admre p_id) AS
student_admrep_ id,
COALESCE(C.stud ent_market_id,s .student_market _id) as student_market_ id,
COALESCE(c.chan geN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks

[rcamarda]

Re: Conceptual ideas - 2 tables one changes other complete Cursors?

David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.stud ent_campus_id,S .student_campu s_id) AS
student_campus_ id,
COALESCE(C.stud ent_admrep_id,S .student_admre p_id) AS
student_admrep_ id,
COALESCE(C.stud ent_market_id,s .student_market _id) as student_market_ id,
COALESCE(c.chan geN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks