Getting unwanted data that I can't get rid of 
Author Message
 Getting unwanted data that I can't get rid of

Hi,

I would greatly appreciate help on this problem which I think is dead
simple to solve but I just can't find a way of doing it.
I've included the sql statement with which I create a view, no
problems with that. The problem is that when I run the sql script at
the bottom of this message it rightly fetches every Sample with the
*where Provkod='10018004'
but it then includes every Sample which haven't got a Provkod attached
to it. I've tried everything I can think of, for example excluding all
Samples which have a Provkod value of NULL but to no avail. Should I
rework the script that creates the view or what ?

Please help me!

Regards,
Linus

--use NordLims
--go

--SETUSER 'Linus'
--go

drop view Rapport
go

Create view Rapport
        (
        Arbetsnr,
        Provnr,
        Provnamn,
        Elementnr,
        Elemtxt_sv,
        Elemtxt_fi,
        Elemtxt_uk,
        Attribut_sv,
        Attribut_fi,
        Attribut_uk,
        VardeTXT,
        VardeNUM,
        Enhet_sv,
        Enhet_fi,
        Enhet_uk,
        Standard,
        Metod,

        Analys,
        Sortering,

        Godkand,
        Godkand_datum,
        Godkand_av,

        MinInput,
        MaxInput,
        MinAlarm,
        MaxAlarm,
        Provkod
        )
as
SELECT
        S.Order_CF,
        S.SampleCode_C,
        S.DisplayText,
        R.Element_CF,
        Elem.Text_SV,
        Elem.Text_FI,
        Elem.Text_UK,
        Attr.Text_SV,
        Attr.Text_FI,
        Attr.Text_UK,
        R.TextValue,
        R.NumValue,
        Unit.Text_SV,
        Unit.Text_FI,
        Unit.Text_UK,
        R.Standard_F,
        R.Method_F,

        A.Analysis_P,
        AE.SortOrder,

        R.ApprovalStatus,
        R.ApprovalDate,
        R.ApprovedBy_F,

        MinInput =
                case    when R.MinInputUsage=1
                        then R.MinInput
                        else null
                end,

        MaxInput =
                case    when R.MaxInputUsage=1
                        then R.MaxInput
                        else null
                end,

        MinAlarm =
                case    when R.MinAlarmUsage=1
                        then R.MinAlarm
                        else null
                end,

        MaxAlarm =
                case    when R.MaxAlarmUsage=1
                        then R.MaxAlarm
                        else null
                end,

        SM.Mark_P

FROM
        LIMS.BT_SAMPLE S,
        LIMS.BT_RESULT R,
        LIMS.BT_ANALYSIS A,
        LIMS.BT_ANA_ELEM AE,
        LIMS.BT_ELEMENT Elem,
        LIMS.BT_UNIT Unit,
        LIMS.BT_ATTRIBUTE Attr,
        LIMS.BT_SAMPLEMARK SM

WHERE
        R.Attribute_F=Attr.Attribute_P
        and
        R.Unit_F=Unit.Unit_P
        and    
        R.Element_CF=Elem.Element_P
        and
        R.Sample_CF=S.Sample_P
        and
        R.Analysis_CF=A.Analysis_P
        and
        (R.Analysis_CF=AE.Analysis_PF and R.Element_CF=AE.Element_PF)
        and
        SM.Sample_PF=*S.Sample_P
                    ^^^
                   NOTICE!
go

grant select on NordLims.Linus.Rapport To LimsAdmin
go

select * from Rapport
where Provkod='10018004'
go



Sun, 21 Dec 2003 16:43:45 GMT
 Getting unwanted data that I can't get rid of

Take a look at the "outerjoin" entry in the SQL Server FAQ at
www.sqlserverfaq.com.

Microsoft has stated that they do not expect to continue indefinitely
supporting the old style outer join syntax, so it is worth your time and
effort to learn the ANSI compliant outer join syntax.

I strongly suspect that the problems are related to using the old style
outer join syntax rather than the ANSI compliant syntax (they are not
equivalent), and differences between the two are addressed in the FAQ entry
mentioned above.

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> Hi,

> I would greatly appreciate help on this problem which I think is dead
> simple to solve but I just can't find a way of doing it.
> I've included the sql statement with which I create a view, no
> problems with that. The problem is that when I run the sql script at
> the bottom of this message it rightly fetches every Sample with the
> *where Provkod='10018004'
> but it then includes every Sample which haven't got a Provkod attached
> to it. I've tried everything I can think of, for example excluding all
> Samples which have a Provkod value of NULL but to no avail. Should I
> rework the script that creates the view or what ?

> Please help me!

> Regards,
> Linus

> --use NordLims
> --go

> --SETUSER 'Linus'
> --go

> drop view Rapport
> go

> Create view Rapport
> (
> Arbetsnr,
> Provnr,
> Provnamn,
> Elementnr,
> Elemtxt_sv,
> Elemtxt_fi,
> Elemtxt_uk,
> Attribut_sv,
> Attribut_fi,
> Attribut_uk,
> VardeTXT,
> VardeNUM,
> Enhet_sv,
> Enhet_fi,
> Enhet_uk,
> Standard,
> Metod,

> Analys,
> Sortering,

> Godkand,
> Godkand_datum,
> Godkand_av,

> MinInput,
> MaxInput,
> MinAlarm,
> MaxAlarm,
> Provkod
> )
> as
> SELECT
> S.Order_CF,
> S.SampleCode_C,
> S.DisplayText,
> R.Element_CF,
> Elem.Text_SV,
> Elem.Text_FI,
> Elem.Text_UK,
> Attr.Text_SV,
> Attr.Text_FI,
> Attr.Text_UK,
> R.TextValue,
> R.NumValue,
> Unit.Text_SV,
> Unit.Text_FI,
> Unit.Text_UK,
> R.Standard_F,
> R.Method_F,

> A.Analysis_P,
> AE.SortOrder,

> R.ApprovalStatus,
> R.ApprovalDate,
> R.ApprovedBy_F,

> MinInput =
> case when R.MinInputUsage=1
> then R.MinInput
> else null
> end,

> MaxInput =
> case when R.MaxInputUsage=1
> then R.MaxInput
> else null
> end,

> MinAlarm =
> case when R.MinAlarmUsage=1
> then R.MinAlarm
> else null
> end,

> MaxAlarm =
> case when R.MaxAlarmUsage=1
> then R.MaxAlarm
> else null
> end,

> SM.Mark_P

> FROM
> LIMS.BT_SAMPLE S,
> LIMS.BT_RESULT R,
> LIMS.BT_ANALYSIS A,
> LIMS.BT_ANA_ELEM AE,
> LIMS.BT_ELEMENT Elem,
> LIMS.BT_UNIT Unit,
> LIMS.BT_ATTRIBUTE Attr,
> LIMS.BT_SAMPLEMARK SM

> WHERE
> R.Attribute_F=Attr.Attribute_P
> and
> R.Unit_F=Unit.Unit_P
> and
> R.Element_CF=Elem.Element_P
> and
> R.Sample_CF=S.Sample_P
> and
> R.Analysis_CF=A.Analysis_P
> and
> (R.Analysis_CF=AE.Analysis_PF and R.Element_CF=AE.Element_PF)
> and
> SM.Sample_PF=*S.Sample_P
>                     ^^^
>                    NOTICE!
> go

> grant select on NordLims.Linus.Rapport To LimsAdmin
> go

> select * from Rapport
> where Provkod='10018004'
> go



Sun, 21 Dec 2003 22:04:05 GMT
 Getting unwanted data that I can't get rid of

Quote:

> Take a look at the "outerjoin" entry in the SQL Server FAQ at
> www.sqlserverfaq.com.

> Microsoft has stated that they do not expect to continue indefinitely
> supporting the old style outer join syntax, so it is worth your time and
> effort to learn the ANSI compliant outer join syntax.

> I strongly suspect that the problems are related to using the old style
> outer join syntax rather than the ANSI compliant syntax (they are not
> equivalent), and differences between the two are addressed in the FAQ entry
> mentioned above.

Thanks! It works perfectly now.

Regards,
Linus Silvander



Tue, 23 Dec 2003 21:54:51 GMT
 
 [ 3 post ] 

 Relevant Pages 

1. Getting rid of the IDENTITY property on a column withou getting rid of the column

2. Getting rid of 'FindFast' Commands

3. Getting rid of deleted dll's from project references list

4. ADO DataGrid and getting values of the current row of the Data G rid

5. Getting Rid of Duplicate Data

6. Getting data from 'TEXT' datatype field

7. Getting Unwanted Values From View

8. Getting rid of version control stored procs

9. Getting rid of the time part

10. Getting rid of version control stored procs

11. getting rid of the return status of a stored procedure


 
Powered by phpBB® Forum Software