Solved

Trouble creating a view in SQL

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

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
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
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now