Link to home
Avatar of fbhunt
fbhunt

asked on

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
Avatar of AriMc
AriMc
Flag of Finland image

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.

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'
Avatar of Anthony Perkins
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.
Avatar of fbhunt
fbhunt

ASKER

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?
use schema name in object name

CREATE VIEW P21.dbo.SALES_TRANSFERS AS ...
Avatar of fbhunt

ASKER

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.
it should be created under dbo

CREATE VIEW SALES_TRANSFERS AS ...

it goes under dbo  to test it

select * from dbo.SALES_TRANSFERS
Avatar of fbhunt

ASKER

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)
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

Avatar of fbhunt

ASKER

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)

 
Avatar of fbhunt

ASKER

could someone please review above comment and supply a solution
ASKER CERTIFIED SOLUTION
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
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.