Views are created in SQL Server.

Pennywise2

Junior Member
Oct 20, 2022
1
0
6
In SQL Server, I'm constructing the view shown below.
Code:
create view View_CAM
as
SELECT
'5' FUNCTION,
getdate() UPLOAD_DATE,
'U' STATUS,
NULL INITIATED_DATE,
NULL PROCESSED_DATE,
NULL FINACLE_CUST_ID,
NULL PROCESSING_USER_ID,
**select currency from table_name** CURRENCIES
For the CURRENCIES column, I must create a select query in order for the data to be shown. The above one is incorrect. how it can be accomplished
Thank you in advance.
 

mooncancook

Platinum Member
May 28, 2003
2,872
45
91
You probably want to join the tables like this:
select table1.myField, table2.currency from table1 join table2 on table2.someId=table1.someId
provided your two tables have proper relationship established.
If it's one to many relationship, you probably need to use aggregate function like sum() and group by clause.

Depend on your need and the relationship, you can also do a inline aggregate select statement like this:
select table1.myField,
(select sum(currency) from table2 where table2.someField=table1.someField) as currency
from table1
 

Lious

Junior Member
Nov 17, 2022
2
0
11
You can accomplish this by including the select query for the CURRENCIES column within the main select statement of the view.
Code:
create view View_CAM
as
SELECT
'5' FUNCTION,
getdate() UPLOAD_DATE,
'U' STATUS,
NULL INITIATED_DATE,
NULL PROCESSED_DATE,
NULL FINACLE_CUST_ID,
NULL PROCESSING_USER_ID,
(select currency from table_name) CURRENCIES
You can also use the JOIN clause to join the view with the table containing the CURRENCIES column, like this:
Code:
create view View_CAM
as
SELECT
'5' FUNCTION,
getdate() UPLOAD_DATE,
'U' STATUS,
NULL INITIATED_DATE,
NULL PROCESSED_DATE,
NULL FINACLE_CUST_ID,
NULL PROCESSING_USER_ID,
table_name.currency CURRENCIES
FROM table_name
It is important to notice that you must replace 'table_name' with the real name of the table. You can also use subquery or CTE (common table expression) to accomplish this.

I hope this helps! Let me know if you have any other questions.
 

ASK THE COMMUNITY