Solved

Trouble creating a view in SQL

Posted on 2011-02-10
14
464 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 51

Expert Comment

by:HainKurt
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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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 51

Expert Comment

by:HainKurt
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 51

Expert Comment

by:HainKurt
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 51

Expert Comment

by:HainKurt
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:
ewangoya 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

860 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