Is it possible to create a view in SSE like I use to do with Access:
SELECT Sum(IIf([Active],1,0)) AS Expr1
FROM Table1;
Active is a boolean field
Many thanks for any kind of help.
Hello,
I am wondering if someone could give some advice about how I could translate the following query in MS Access into T-SQL. Any help is greatly appraciated.
Thanks
------------------------------------------------------------------------------------
TRANSFORM Avg(A.Response) AS AvgOfResponse
SELECT A.QuestText, A.SubQuestText
FROM TableName A
WHERE A.Response Is Not Null
GROUP BY A.QuestText, A.SubQuestText
ORDER BY A.QuestText, A.SubQuestText
PIVOT A.[Ethnicity Group]
-------------------------------------------------------------------------------------------------------------------------------
Gary writes "I'm imported the query from Access (where it worked perfectly) to SQL View where the "Iif" statements caused an issue.
Here's a SMALL portion of the original:
SELECT tblTransactionBuyer.TransBuyerID,
tblDefaults.CompID,
([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA,
[TransBuyerPropSalePrice]*
(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],
IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, ......
Below, are my changes so far, but I'm stuck.
SELECT tblTransactionBuyer.TransBuyerID,
tblDefaults.CompID,
([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA,
[TransBuyerPropSalePrice]*
If([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],
If([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA]/100)) AS VAFunding, ........
It appears that I may not be able to put an IF into a SQL SELECT. If not, how can I get the same effect ?
Thanks. Gary
Listed Below is the Full Access Query:
SELECT tblTransactionBuyer.TransBuyerID, tblDefaults.CompID, tblTransactionBuyer.TransBuyerEntryDate, qryUserAlphaList.UserName, qryUserAlphaList.UserAreaCode, qryUserAlphaList.UserPhone, tblTransactionBuyer.TransBuyerName, tblTransactionBuyer.TransBuyerVaEligible, tblTransactionBuyer.TransBuyerPropAddress, tblTransactionBuyer.TransBuyerPropCity, tblTransactionBuyer.TransBuyerPropSalePrice, ([TransBuyerPropSalePrice]*([DefBasePercentFHA])/100) AS LoanBaseFHA, ([TransBuyerPropSalePrice]*([DefBasePercentCONV])/100) AS LoanBaseCONV, ([TransBuyerPropSalePrice]*([DefBasePercentVA])/100) AS LoanBaseVA, ([TransBuyerPropSalePrice]*([DefBasePercentJumbo])/100) AS LoanBaseJumbo, [LoanBaseFHA]*[DefMipFHA]/100 AS MipFHA, [LoanBaseCONV]*[DefMipCONV]/100 AS MipCONV, [LoanBaseJumbo]*[DefMipJumbo]/100 AS MipJumbo, [TransBuyerPropSalePrice]*(IIf([TransBuyerPropSalePrice]>=[DefLoanMinJumbo],[DefFundingJumboVA],IIf([TransBuyerVaUsed]="Yes",[DefFundingUsedVA],[DefFundingVA])))/100 AS VAFunding, [LoanBaseFHA]-[DownOverMinFHA]+[MipFHA] AS LoanTotalFHA, [LoanBaseCONV]-[DownOverMinCONV]+[MipCONV] AS LoanTotalCONV, [LoanBaseJumbo]+[MipJumbo]-[DownPaymentJumbo] AS LoanTotalJumbo, [LoanBaseVA]+[VAFunding]-[DownPaymentVA] AS LoanTotalVA, [TransBuyerPropSalePrice]*([DefMinDownPayFHA]/100) AS MinDownFHA, [TransBuyerPropSalePrice]*[DefMinDownPayCONV]/100 AS MinDownCONV, [TransBuyerPropSalePrice]*[DefMinDownPayVA]/100 AS MinDownVA, [TransBuyerPropSalePrice]*[DefMinDownPayJumbo]/100 AS MinDownJumbo, tblTransactionBuyer.TransBuyerIntRate, tblTransactionBuyer.TransBuyerLoanYears, tblTransactionBuyer.TransBuyerHazardIns, tblTransactionBuyer.TransBuyerFloodIns, tblTransactionBuyer.TransBuyerClosingDate, IIf(([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice]>[TransBuyerConvDownPayCash],([TransBuyerConvDownPayPercent]/100)*[TransBuyerPropSalePrice],[TransBuyerConvDownPayCash]) AS ConvDownPay, tblDefaults.DefBuyAppraisal, tblDefaults.DefBuyCreditReport, tblDefaults.DefBuyJumboUnderwriting, tblDefaults.DefBuyVAUnderwriting, tblDefaults.DefBuyFHAUnderwriting, tblDefaults.DefBuyCONVUnderwriting, tblDefaults.DefBuyJumboTaxService, tblDefaults.DefBuyVATaxService, tblDefaults.DefBuyFHATaxService, tblDefaults.DefBuyCONVTaxService, tblDefaults.DefBuyFloodCert, tblDefaults.DefBuyAbstractTitle, tblDefaults.DefBuyMiscFedex, tblDefaults.DefBuyRecording, tblDefaults.DefBuySurvey, tblDefaults.DefBuyAttorney, tblDefaults.DefBasePercentFHA, tblDefaults.DefBasePercentCONV, tblDefaults.DefBasePercentJumbo, tblDefaults.DefBasePercentVA, [TransBuyerPropSalePrice]*[DefPropTaxRate] AS PropTax, [LoanBaseFHA]*[DefBuyOrigFHA]/100 AS OrigFHA, [LoanBaseCONV]*[DefBuyOrigCONV]/100 AS OrigCONV, [LoanBaseVA]*[DefBuyOrigVA]/100 AS OrigVA, [LoanBaseJumbo]*[DefBuyOrigJumbo]/100 AS OrigJumbo, [DefTitleInsDollar]*([TransBuyerPropSalePrice]/[DefTitleInsPerX]) AS TitleInsurance, [OrigFHA]+[DefBuyAppraisal
Can someone tell me what this Where clause (from MS Access) should be T-SQL?
Basically, if the value in the [reason] field contains 'DIST' then the row should return as long as the value in [movement] is greater than or equal to 1.
Where IIf([reason] LIKE '%DIST%',Val([movement]),1)>=1
Thanks!
Good morning,
I have a project that consists of modifying a report in Crystal. The problem is that I know very little about SQL and I need to add some data items to Crystal using SQL. The guide that I was given refers to Enterprise Manager and SQL Query Analyzer but they have since upgraded to SQL Server Management Studio. Can anyone translate the following statement into SQL Server Management Studio so that I can complete this process?
Best regards,
We will now edit the View on the MS SQL server. Open Enterprise
Manager and find the cst_adEnroll_MyInitials _vw in the Views
section of the database. Right-click, select Copy, then go to the
menu bar and Select Tools and SQL Query Analyzer. Once Query
Analyzer opens: Change CREATE to ALTER before the view name.
Good morning,
I'm trying to create a view on SQL Server based on a query that i use on Access. The query returns detailed data from a table based on ID's selected and saved into another table.
the thing is, on access it returns back 5 records<which is a correct output> , while on SQL. its returning only one record. what could be the issue here ?
thank you,
SELECT DISTINCTROW "01C" AS dummy, Buildings.BuildingNumber,UCASE(Buildings.BuildingName) AS BuildingName,Buildings.MasterPlanCode, Buildings.UniformBuildingCode,Buildings.FunctionalCategoryCode, Buildings.OwnershipCode,Buildings.ConditionCode, Format$([BasicGrossArea],"0000000") ASdBasicGrossArea, Format$([CoveredUnenclosedGrossArea],"0000000") ASdCoveredUnenclosedGrossArea,IIf(Month([DateOccupancy])>9,Month([DateOccupancy]),"0" &Month([DateOccupancy])) & Year([DateOccupancy]) AS dDateOccupancy,Buildings.YearConstructed, Format$([NumberLevels],"00") ASdNumberLevels, Format$([UnrelatedGrossArea],"0000000") ASdUnrelatedGrossArea, Buildings.YearLatestImprovement,UCASE(Buildings.Address) AS Address, Buildings.CityCode,CityCodes.CountyCode, Format$([Circulation],"0000000") AS dCirculation,Format$([PublicToiletArea],"0000000") AS dPublicToiletArea,Format$([Mechanical],"0000000") AS dMechanical,Format$([Custodial],"0000000") AS dCustodialFROM CityCodes INNER JOIN Buildings ON CityCodes.CityCode =Buildings.CityCodeORDER BY "01C", Buildings.BuildingNumber, Buildings.BuildingName;Please if anyone can help me in Converting the above given Access Queryto Sql Server. I don't know which function to use for format$, IIF. Iwould really appreciate your suggestions.Thanks,
View 8 Replies View RelatedDear All,
i am making small web application using asp.net, C# ,sql2000.
i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help
thanks
I would like to be able to view the complete SQL command that is sent from a Microsoft Access query which has linked SQL Server tables through ODBC, and the query also has some VBA functions manipulating and filtering data.I tried using the SQL Profiler to view the sql command send to SQL Server but all I see is the simple query format and some of the fields. I don't see the fields that have a VBA function such as trim(fieldname) etc in the column. And If I use a VBA fucntion in the Criteria then nothing shows up in SQL Profiled under the TextData column.How can I see the complete SQL command sent to SQL Server?
View 2 Replies View RelatedI have a view that give me the data of all the batched. Now I am using a query on view to get a single batched data. when I am using direct query it was taking 0 sec but when I am using Through view "select * from myView where batched=2" then its taking 30 mnt.
View 3 Replies View RelatedMy question is on an appropriate approach to take.
We have an very large company database that supports all of current software in Sql Server. A current requirement is to take data from a Oracle Database that has "agreed upon" tables and to "Translate" that to SQL Server and place them in agreed upon "Temp" tables. Then our other software will know what to do with these temp tables and do their thing.
Upon launching this translator application, it will delete all of those temp tables and do (what I just specified above).
My initial thought was to dump each Oracle table into a DataTable. Then delete any temp tables in our database. Then create the temp tables and begin updating our Sql Server by iterating through the DataTable performing updates?
Is this a good idea?
Hi all
I have this snippet of data
hierId nodeId nodeName parentId childId
----------- ----------- -------------------------------------- ----------- -----------
437 1582275 Accountants 1582270 1582275
437 1582276 Asset financiers 1582270 1582276
437 1582270 Banking & financial institutions NULL NULL
437 1582286 Private banks 1582277 1582286
437 1582277 Banks 1582270 1582277
I want to loop through this data and put the results into a temp table so it looks like this:
hierId nodeName ids
------------------------------------------------------------------------------------------------------
437 Banking & financial institutions -> Accountants1582270 | 1582275
437Banking & financial institutions -> Asset financiers1582270 | 1582276
437Banking & financial institutions -> Banks1582270 | 1582277
437Banking & financial institutions -> Banks -> Private banks1582270 | 1582277 | 1582286
437Banking & financial institutions1582270
Can anyone help me with achieving this
Thanks in advance
I am having trouble getting my head round participation for some scenarios. Consider the following:
River MUST flow through 1 or more cities.
City MUST have at least 1 river.
This is represented the pk of River being put as a fk in city AND (to represent that city must have a river), the foreign key does NOT allow nulls.
So how about...
River MAY flow through 0,1 or more cities. City MAY have ONLY 1 river.
This time we allow the foreign key in city to have NULLs, but how about the otherside, the pk of river. How do I represent the MAY here, and a pk value cannot be null.
Thanks
Drew
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
View 10 Replies View RelatedMy OLDB data source in the Lookup Transformation is not converting
When I try to join the colunms it claims the data types do not match the only differnece is the percision.??
ORACLE
select CAST(group_id AS NUMBER(18)) group_id from blah blah table
SSIS data type = [DT_NUMERIC], Percision = 38
SQL Sever data source
SELECT
CAST(PRINCIPAL_ID as numeric) GROUP_ID,
SSIS data type = [DT_NUMERIC], Percision = 18
I have a view created in Database1 accessing information in Database2. Do users need access to Database2 in order to select from the view?
View 4 Replies View RelatedI'm converting a View from access to Sql and I'm stuck on this IF statement.
IIf([FG_Qtys_1].[CoreQty]=0 Or [FG_Qtys_1].[CoreQty] Is Null,[KitCoreOnHand],[FG_Qtys_1].[CoreQty]);
I know that I could use CASE statement but i keep on getting errors.
My Case Statement:
case when [dbo.FG_Qtys_view].[CoreQty]=0 then dbo.FG_Qtys_Kits.KitCoreOnHand
when [dbo.FG_Qtys_view].[CoreQty] ISNULL then [dbo.FG_Qtys_view].[CoreQty]
end as CoreQty
Hello all, i hope someone that understands SQL can help me.
I have a report that runs from a access query, which is simple.
My problem is that i get an overflow error becuase one of the queries formulas are to divide some numbers and in this one instance there is a "0" involved.
My formula is:
Col2%: [Col 2 Hrs]/([TotHrs]-[Col 6 Hrs])
The problem is that [Col 2 Hrs]=0 this month for a specific employee.
In excel i know i can do
=IF([Col 2 Hrs]=0,"0",([Col 2 Hrs]/([TotHrs]-[Col 6 Hrs])))
and that works fine.
Can someone please show me how to do this in SQL so instead of returning an error it will display 0 or N/A.
Thanks inadvance
Hope i explained my issed well enough for everyone.
I'm having trouble creating a read-only view. I've got 1 or more tables that I wish to remain updatable but I want to create a view that covers the table and/or spans all the tables. However, I want the view to be select only. I can't seem to get it to work.
DENY UPDATE ON [dbo].[MyView] TO [dbo] CASCADE
All that seems to execute my dbo user can still use...
Update MyView set SomeID = SomeID + 10
Plus, ideally I just want to say, DENY UPDATE ON VIEW TO ALL
Any thoughts?
Hello all, I need to prevent all users (other than sysadmin/dbo) privs to access the database code (SP/Views/UDF). What would be the best way to achieve that? TIA
View 5 Replies View RelatedI try to create a view in Access Project but in design view I only see "all columns" in a table. The individual columns are not listed. This happens only with a certain SQL Server database. Any suggestions ?
View 1 Replies View RelatedI migrated a DB from Access 2000 to SQLserver 2000.The tables are OK, but I see the old Access views... as TABLE inSQLserver!!Any suggestions?--
View 3 Replies View RelatedI am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").
How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....
Hi there,
Not sure if this is possible but anyways. Need to restrict access on systems for users
and at the moment they are limited to being server and process administrators only.
This gives them enough room to do what they need to do, however the taskpad view in Enterprise manager only shows them the Log space used, not the datafiles.
The only way to get the TaskPad view to show the datafiles is to add them to the database creators role, which already gives them more permissions than they need.
So, just a shot in the dark but is there someway to get Taskpad view to show all the info there about the database?
Wendy,
I do not know if you can see my reply after yours. Any way I try it here again.
In the DTS Export Wizard, after select the source (SQL Server)and destination (Excel),
I have 2 choices:Table copy or query.
When I use query, I can place my sp there, and the export works fine exactly as you recommended.
Thanks a lot.
But when I selected a table copy, I was given a "Select Source Tables" popup form, I can
see all the table names there, but can not find any views in the database. I do not know why.
Am I in the right place this time?
Thank you very much for your quick help.
Charlie
Hey everyone. I've linke an access database and I am able to query the tables like so:
SELECT *
FROM QFinity...tblEmployees
I can do that to all the tables, however, I'd like to create a view to this linked database. Is this possible? I have a more complex query I'd like to run:
SELECT dbo_evaluations.eval_id, dbo_evaluations.quality_date, dbo_eval_questions.status
FROM QFinity...dbo_evaluations INNER JOIN dbo_eval_questions ON dbo_evaluations.eval_id = dbo_eval_questions.eval_id;
I get the error "Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo_evaluations'."
I'm afraid I've reached the limit of my know how concerning sql server 2005... I think I read that I need to create a view? But I can't figure out how to do that.
Thanks for any help!
Dave
Windows XP, Office XP
Our business application is running SQL Server in a remote data center and we had been using MS Access from a local PC to occasionally fix data problems in the data base. As the tables continue to grow, it's painfully obvious MS Access just can't handle the volume of data. One of our tables we need to get into is about 200,000 rows, another is over 100,000 rows.
We need to be able to change individual rows, and perform search and replace commands across one or more columns in select tables.
What software can we install on local desktops to allow viewing and editing of SQL table data?
Hi,
Does anyone know the method to transfer result data of a view or stored procedure
in MS SQL 7.0 to MS Access or Excel , Manually or automatically? I did not find the
right tool in SQL 7 to do so.
I notice we can transfer table from SQL 7 to MS Access / Excel through DTS. But did not
find any tool/menu to transfer result of view / stored procedure.
I am new to SQL 7. Maybe this is a silly ?. Any help will be appreciated.
Charlie
We are running SQL Server 2012 on Windows 2008 Server. In one database, we would like to create a view which access objects in another database without giving the user permissions to the underlying base tables in the other database. The ownership chain is broken in this case. Can this be accomplished (considering the ownership chain is broken)? If so, what is the easiest method to accomplish this task? Or
Example 1 (Works):
In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).
In DB2:
--UserA exists with select permissions on the base tables accessed by Schema1.View1 (in DB1).
Example 2 (trying to accomplish):
In DB1:
--UserA selects from Schema1.View1 (which access tables in DB2).
In DB2:
--UserA exists (or may not exists) with NO permissions on the base tables accessed by Schema1.View1 (in DB1).
Hi,
We are using SQL Server 2005 Management Studio.
I created a Minimal User in an application DB. The user will access tables through stored procedures.
I do not want this user to view any other objects including objects in the Master DB.
I can prevent the minimal user from viewing objects from our application DB.
How do you prevent the minimal user from viewing objects in the Master DB?
Thanks.
Tim.
Hi,
I have an application that uses following code:
Code Snippet
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
namespace TimeTracking.DB
{
public class sql
{
OleDbConnection conn;
//
//the constructor for this class, set the connectionstring
//
public sql()
{
DBConnectionstring ConnectToDB = new DBConnectionstring();
conn = ConnectToDB.MyConnection();
}
//
//
//
public void UpdateEntry(int ID, string Week, string Year, string Date, string Project, string Action, string Time, string Comment)
{
int m_ID = ID;
int m_Week = (Convert.ToInt32(Week));
int m_Year = (Convert.ToInt32(Year));
string m_Date = Date;
string m_Project = Project;
int m_ProjectID = new int();
string m_Action = Action;
int m_ActionID = new int();
Single m_Time = (Convert.ToSingle(Time));
string m_Comment = Comment;
//
//get the project ID from the database and store it in m_ProjectID
//
OleDbCommand SelectProjectID = new OleDbCommand("SELECT tblProject.ProjectID FROM tblProject"
+ " WHERE (((tblProject.Project) LIKE @Project))", conn);
SelectProjectID.Parameters.AddWithValue("@Project", m_Project);
try
{
//open the connection
conn.Open();
OleDbDataReader Dataset = SelectProjectID.ExecuteReader();
while (Dataset.Read())
{
m_ProjectID = (int)Dataset["ProjectID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//get the action ID from the database and store it in m_ActionID
//
OleDbCommand SelectActionID = new OleDbCommand("SELECT tblAction.ActionID FROM tblAction"
+ " WHERE (((tblAction.Action) LIKE @Action))", conn);
SelectActionID.Parameters.AddWithValue("@Action", m_Action);
try
{
OleDbDataReader Dataset = SelectActionID.ExecuteReader();
while (Dataset.Read())
{
m_ActionID = (int)Dataset["ActionID"];
}
Dataset.Close();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
//
//
//
OleDbCommand Query = new OleDbCommand("UPDATE [tblEntry] SET [tblEntry].[Weeknumber] = @Week,"
+ " [tblEntry].[Year] = @Year, [tblEntry].[Date] = @Date, [tblEntry].[Project] = @ProjectID, [tblEntry].[Action] = @ActionID,"
+ " [tblEntry].[Hours Spent] = @Time, [tblEntry].[Comments] = @Comment WHERE (([tblEntry].[ID]) = @ID)", conn);
Query.Parameters.AddWithValue("@ID", m_ID);
Query.Parameters.AddWithValue("@Week", m_Week);
Query.Parameters.AddWithValue("@Year", m_Year);
Query.Parameters.AddWithValue("@Date", m_Date);
Query.Parameters.AddWithValue("@ProjectID", m_ProjectID);
Query.Parameters.AddWithValue("@ActionID", m_ActionID);
Query.Parameters.AddWithValue("@Time", m_Time);
Query.Parameters.AddWithValue("@Comment", m_Comment);
try
{
Query.ExecuteNonQuery();
}
//Some usual exception handling
catch (OleDbException e)
{
throw (e);
}
finally
{
//close the connection
if (conn != null)
{
conn.Close();
}
}
}
}
}
Code Snippet
The update statement is not working in my application, no error in C# and no error in ms-access. When I paste the update query into the ms-access query tool and replace the parameter values (@....) with real values, is will update the record.
What am I overseeing here?
--Pascal
I want to create a view to get records from multiple tables. I have a UserID in all the tables. When I pass UserID to view it should get records from multiple tables. I have a table
UserInfo with as data as
UserID=1, FName = John,
LName=Abraham and Industry = 2. I have a
Industry table with data as
ID=1 and Name= Sports,
ID =2 and Name= Film.
When I query view where UserID=1 it should return record as
FName =John,
LName = Abraham and
Industry= Film
How to write query?
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,