Creating a VIEW

I'm trying to create a view in EM.
SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
ORDER BY dbo.wf_styles.code
The error message I get was "Incorrect syntax near '100'". I didn't put "TOP
100 PERCENT" there (not sure what it does...) but EM puts it back after I
delete it. How can I get this to work?
Thanks!!
But theWill,
The ORDER BY clause cannot be used in a view definition without the TOP
clause listed. Do you really want to include the ORDER BY in the view
definition? Could cause unexpected results when querying the view and using
a different ORDER BY clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||Will,
Also move
AND dbo.wf_bom.raw_type = 'F'
to the WHERE clause.
HTH
Jerry
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||(a) write your VIEW in Query Analyzer, not Enterprise Mangler
(b) update your database to not be in 6.5 compatibility mode, where TOP
wasn't supported.
"will" <will@.discussions.microsoft.com> wrote in message
news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
> I'm trying to create a view in EM.
> SELECT TOP 100 PERCENT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
> dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
> FROM dbo.wf_styles LEFT OUTER JOIN
> dbo.wf_bom ON dbo.wf_styles.code =
> dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
> WHERE (dbo.wf_styles.SA_Active = 'Y')
> ORDER BY dbo.wf_styles.code
> The error message I get was "Incorrect syntax near '100'". I didn't put
> "TOP
> 100 PERCENT" there (not sure what it does...) but EM puts it back after I
> delete it. How can I get this to work?
> Thanks!!
> But the|||> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
No - this is the unpreserved table in an outer join. Doing this changes the
semantics of the query.|||I need that to be part of the JOIN condition because it's a LEFT JOIN.
"Jerry Spivey" wrote:

> Will,
> Also move
> AND dbo.wf_bom.raw_type = 'F'
> to the WHERE clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok, I took out the ORDER BY, now I'm getting:
Error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_styles'.
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.wf_bom'.
Query:
SELECT dbo.wf_styles.code, dbo.wf_styles.SA_Active,
dbo.wf_bom.raw_type, dbo.wf_bom.raw_code, dbo.wf_bom.qty
FROM dbo.wf_styles LEFT OUTER JOIN
dbo.wf_bom ON dbo.wf_styles.code =
dbo.wf_bom.style_code AND dbo.wf_bom.raw_type = 'F'
WHERE (dbo.wf_styles.SA_Active = 'Y')
Thanks.
"Jerry Spivey" wrote:

> Will,
> The ORDER BY clause cannot be used in a view definition without the TOP
> clause listed. Do you really want to include the ORDER BY in the view
> definition? Could cause unexpected results when querying the view and usi
ng
> a different ORDER BY clause.
> HTH
> Jerry
> "will" <will@.discussions.microsoft.com> wrote in message
> news:6F72E883-F63A-4355-AC18-F9F7C0A99044@.microsoft.com...
>
>|||Ok...that I've never seen before. Would you mind elaborating on that for my
understanding?
Thanks Scott.
Jerry
"Scott Morris" <bogus@.bogus.com> wrote in message
news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
> No - this is the unpreserved table in an outer join. Doing this changes
> the semantics of the query.
>|||wf_styles and wf_bom has a 1-to-many relationship. One line in wf_styles and
many wf_bom lines for each item (bom = Bill of Materials, what each item
contains). I only want to join where the wf_bom.raw_type = 'F'. If it's not
F, return NULL. If you put it in the WHERE condition, the results returned
would only be items that have F, which is not really what I wanted.
Basically, I want a list of all the styles in my table. If I have the amount
of fabric yards that style uses, display that too. That's why I need the LEF
T
JOIN. If you were to put that clause in the WHERE, it would be "Display all
styles in the table and the fabric yards it uses". But that won't work for m
e
since I have some styles where I don't have the fabric yards. Does that
explain it?
"Jerry Spivey" wrote:

> Ok...that I've never seen before. Would you mind elaborating on that for
my
> understanding?
> Thanks Scott.
> Jerry
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:%23PbXC1Z2FHA.1576@.TK2MSFTNGP15.phx.gbl...
>
>