SQL View with Text Field - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL View with Text Field

Author

Topic

ryancravey
Starting Member

2 Posts

Posted - 2008-04-01 : 16:12:25

When creating a View, I am able to link the tables togethor and pull the data that I am looking for without a problem. However, when I add a field that is a 'Text' field, I get the following error:

....Column 'dbo.commpay.fdescript' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I add it to the GROUP BY clause, then I get the following error:

....The text, ntext and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

This particular field is contains the description of the parts in our inventory table.

VIEW Statement:

SELECT dbo.armast.fcinvoice AS Invoice, dbo.commpay.fnCalcComm AS Commission, dbo.commpay.fnrate AS CommRate, dbo.armast.fbcompany AS Company, dbo.commpay.fdarpost AS InvDate, dbo.commpay.fpartno AS PartNum, dbo.commpay.fnamount AS Price, dbo.commpay.fsalespn AS RepID, dbo.commpay.fmdescript AS Description
FROM dbo.armast INNER JOIN dbo.commpay ON dbo.armast.fcinvoice = dbo.commpay.fcarinv INNER JOIN dbo.slspns ON dbo.commpay.fsalespn = dbo.slspns.fsalespn
GROUP BY dbo.armast.fcinvoice, dbo.commpay.fnCalcComm, dbo.commpay.fnrate, dbo.armast.fbcompany, dbo.commpay.fdarpost, dbo.commpay.fpartno, dbo.commpay.fnamount, dbo.commpay.fsalespn, dbo.commpay.fmdescript

Any assistance or guidance would be appreciated!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 16:23:40

We can't help unless you provide sample data to illustrate to us what it is supposed to do. Make sure to illustrate it with data from each of the tables involved plus the expected output.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-01 : 16:51:13

this may help you out:

http://weblogs.sqlteam.com/jeffs/archive/2005/12/14/8546.aspx

you should not be grouping on all those columns.

- Jeff
http://weblogs.sqlteam.com/JeffS

ryancravey
Starting Member

2 Posts

Posted - 2008-04-01 : 17:15:29

Here is an example of what I am looking for:

Table 1:

Field #1: Part Number (Type: char)
Field #2: Description (Type: Text)
Field #3: Price (Type: Money)
Field #4: Qty (Type: char)
Field #5: Revision (Type: Char)
Field #6: Purchase Date (Type: datetime)
Filed #7: Commission Amount (Type: Money)

Trying to create a view that only has the following:

Field #1: Part Number
Field #2: Description
Field #3: Price

I can add any field with no problem, until I try to add a 'Text' field. That is when it starts throwing the errors indicated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-01 : 17:16:43

Your description of what you want isn't clear as this would satisfy your request according to your last post:

SELECT PartNumber, Description, Price
FROM YourTable

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources