Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 491
  • Last Modified:

Trouble creating a view in SQL

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
fbhunt
Asked:
fbhunt
1 Solution
 
AriMcCommented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Anthony PerkinsCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
fbhuntAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
use schema name in object name

CREATE VIEW P21.dbo.SALES_TRANSFERS AS ...
0
 
fbhuntAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
it should be created under dbo

CREATE VIEW SALES_TRANSFERS AS ...

it goes under dbo  to test it

select * from dbo.SALES_TRANSFERS
0
 
fbhuntAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
fbhuntAuthor Commented:
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
 
fbhuntAuthor Commented:
could someone please review above comment and supply a solution
0
 
Ephraim WangoyaCommented:

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
 
8080_DiverCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now