Solved

Trouble creating a view in SQL

Posted on 2011-02-10
14
467 Views
Last Modified: 2012-05-11
I am trying to create a view, I seem to be getting an error when I am trying to convert a string to a numeric field.  I do not know what is missing.  when I execute the query I seem to be getting the same error.

CREATE VIEW SALES_TRANSFERS AS
SELECT p21_view_audit_trail_customer_1307.column_changed, p21_view_audit_trail_customer_1307.date_created, p21_view_audit_trail_customer_1307.key2_cd, p21_view_audit_trail_customer_1307.key2_value, CONVERT(decimal,p21_view_audit_trail_customer_1307.key1_value)
from p21_view_audit_trail_customer_1307
where p21_view_audit_trail_customer_1307.column_changed = 'class_5id'


Msg 4511, Level 16, State 1, Procedure SALES_TRANSFERS, Line 3
Create View or Function failed because no column name was specified for column 5.
THESELECT STATEMENT IS ALL ON THE SAME LINE
0
Comment
Question by:fbhunt
14 Comments
 
LVL 9

Expert Comment

by:AriMc
ID: 34865908
Try adding a name to the 5th column ie. CONVERT(decimal,p21_view_audit_trail_customer_1307.key1_value) AS ABC

Otherwise you won't be able to specify the column in the queries you make using the view.

0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 34865973
dont use table name everywhere

CREATE VIEW SALES_TRANSFERS AS
SELECT   column_changed,
         date_created,
         key2_cd,
         key2_value,
         CONVERT (decimal, key1_value) keyl_value
  FROM   p21_view_audit_trail_customer_1307 t
 WHERE   column_changed = 'class_5id'
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 34868130
Also this:
CONVERT(decimal, key1_value)

Is the same as:
CONVERT(integer, key1_value)

or:
CONVERT(bigint, key1_value)

I suspect you need:
CONVERT(decimal(p, s), key1_value)           -- Where p and s are numeric values for the Precision and Scale.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:fbhunt
ID: 34870555
When this view is created in SQL SERVER Management Studio where is it placed/located.  I was hoping since is done with the P21 schema that it would appear in the list of views in Crystal Reporting.
If not how do I place the view so I can link to it with Crystal?
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 34871649
use schema name in object name

CREATE VIEW P21.dbo.SALES_TRANSFERS AS ...
0
 

Author Comment

by:fbhunt
ID: 34871801
When I made the change above I got a

msg 155, level 15

CREATE/ALTER VIEW does not allow specifying the database name as a prefix to the object name.
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 34872068
it should be created under dbo

CREATE VIEW SALES_TRANSFERS AS ...

it goes under dbo  to test it

select * from dbo.SALES_TRANSFERS
0
 

Author Comment

by:fbhunt
ID: 34872346
Okay, the dbo.SALES_TRANSFERS is listed on the views on the left pane Object Explorer on the Microsoft SQL SERVER Management Studio.

This is my SQL steps to create the view :
CREATE VIEW SALES_TRANSFERS AS
SELECT   column_changed,
         date_created,
         key2_cd,
         key2_value,
         CONVERT (decimal, key1_value) as customer_ID
  FROM   p21_view_audit_trail_customer_1307 t
 WHERE   column_changed = 'class_5id'

When I do the  
 SELECT * from SALES_TRANSFERS

Msg 8114, Level 16, State 5, line 2
I get an error convertingt data type varchar to numeric.
(this is probably referring to the convert field and I do not get any data)
0
 
LVL 52

Expert Comment

by:Huseyin KAHRAMAN
ID: 34872602
use

declare @key1_value varchar(10) = '124.346';
select case when isnumeric(@key1_value)!=0 then CONVERT (decimal, @key1_value) else 0 end as customer_ID

and your code
CREATE VIEW SALES_TRANSFERS AS
SELECT   column_changed,
         date_created,
         key2_cd,
         key2_value,
         case when isnumeric(key1_value)!=0 then CONVERT (decimal, key1_value) else 0 end as customer_ID
  FROM   p21_view_audit_trail_customer_1307 t
 WHERE   column_changed = 'class_5id'

Open in new window

0
 

Author Comment

by:fbhunt
ID: 34926356
The examples given helped me create the table but I have 1 outstanding issue.  In the same table there is not a field that clearly states the salesman number which I need so I would like to force the salesman id to the table i am creating.  The salesman id will be based on the column labeled new_value from the p21_view_audit_trail_customer_1307.  The values in this column are as follows:
I.e.    "RL-WM account transfer "  this would translate to salesman ID=2730
         "RL-WK account transfer"     salesman ID = 2725
         "RL-BM4 account trasnfer'    salesman ID = 2729
         "House-AV Account transfer  salesman ID = 1788
Those are examples of the values in new_value field.  I need to assign the salesman ID and place on the view I am creating.  There might be 12 - 15 different acccount transfer to 12-15 salesman Id.
how would i write the statement to include in the view SALES_TRANSFERS (prior posts)

 
0
 

Author Comment

by:fbhunt
ID: 34932557
could someone please review above comment and supply a solution
0
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 500 total points
ID: 34954523

CREATE VIEW SALES_TRANSFERS AS
SELECT   column_changed,
         date_created,
         key2_cd,
         key2_value,
         case when isnumeric(key1_value)!=0 then CONVERT (decimal, key1_value) else 0 end as customer_ID,
         case
           when new_value = 'RL-WM account transfer' then
             730
               when new_value = 'RL-WK account transfer' then
                 2725
               when new_value = 'RL-BM4 account trasnfer' then
                 2729
               when new_value = 'House-AV Account transfer' then
                 1788
           end as SalesManID
  FROM   p21_view_audit_trail_customer_1307 t
 WHERE   column_changed = 'class_5id'
0
 
LVL 22

Expert Comment

by:8080_Diver
ID: 34954537
You have two choices, as I see it:

Choice 1:
In your View's SQL, you will need to have a CASE statement something like the following

CASE WHEN  p21_view_audit_trail_customer_1307.new_value = 'RL-WM account transfer' THEN 2730
            WHEN  p21_view_audit_trail_customer_1307.new_value = 'RL-WK account transfer'   THEN 2725
            WHEN  p21_view_audit_trail_customer_1307.new_value = 'RL-BM4 account trasnfer'   THEN 2729
          WHEN  p21_view_audit_trail_customer_1307.new_value = 'House-AV Account transfer'  THEN 1788
          ELSE 0
END SalesmanID

Open in new window


Choice 2:
Create another auxiliary table that contains the verbage (e.g. 'RL-WK account transfer' ) and the SalesmanID (e.g. 2730) and LEFT OUTER JOIN to that table based upon the p21_view_audit_trail_customer_1307.new_value.

Note, in either case, you will need to figure out how to JOIN from your new View to the p21_view_audit_trail_customer_1307 view.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

697 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question