New answers tagged

0

Recreating linked server without @provstr=N'ApplicationIntent=ReadOnly' parameter solved no login-mapping existserror. So select permission is enough.


2

The message you are getting is telling you that there is no mapping between the credentials you have supplied and credentials on the target machine. You need to ensure that the credentials you use to setup your linked server also has a login or can be mapped to a login on the linked server. I suspect that when you set it up you specified the option Be made ...


1

I ended up doing it this way: SELECT temp1.hday, temp1.user_id, temp1.total_buy, temp1.total_sell, coalesce(temp2.total_profit,0) as total_profit, coalesce(temp3.margin,0) as margin from ( select calendar.days as hday, users.iduser as user_id, coalesce(sum(CASE WHEN transaction.transaction_type = 'Buy' THEN transaction.price_total END),0) as ...


0

There's no join to the profit table? You need conditional aggregates, i.e. join to transaction only once and all t1.column are replaced by CASE WHEN transaction_type = 'Buy' THEN column END Similar for t2.column: CASE WHEN transaction_type = 'Sell' THEN column END


1

Common Table Expressions are treated as an “optimization fence” by PostgreSQL: It will never push down predicates from the main query into the CTE nor collapse any joins across the CTE boundary. Instead, it will generally evaluate the whole CTE as it is, materializing the result; the main query will then access the temporary table generated from the CTE. So ...


3

You update column state inside the view. It refferences s.ClaimStatusName AS [Status] inside the view. From the code of the view we see that you update dimClaimStatus table (dimClaimStatus AS s). Seeing that you have 2 columns idClaimStatus and ClaimStatusName you have ID \ Name structure of the table. Inside the view you show ClaimStatusName. So you ...


7

General view updatability The key part of the CREATE VIEW (Transact-SQL) documentation is: Generally, the Database Engine must be able to unambiguously trace modifications from the view definition to one base table. Note that even if the view is technically updatable, it may not be actually updatable in practice, due to limitations of the query ...


3

Imagine your UPDATE is updating s with the FROM clause of your view. Then read this blog I wrote recently to see how it could be affected. http://sqlblog.com/blogs/rob_farley/archive/2016/01/12/join-effects-with-update.aspx Assuming you're not breaking the rules for updateable views, then you should be okay. Just also be wary of the things in my post.


2

As for the view being valid after dropping a column: The view is defined as SELECT * so the view is still valid. Regarding the validity of the view after dropping the table just use field VALID from SYSCAT.VIEWS, see below. db2 => DROP TABLE DB2INST1.TEMP_T DB20000I The SQL command completed successfully. db2 => SELECT SUBSTR(VIEWNAME,1,32) AS ...


11

Note: This answer addresses a couple of basic problems, but it's not the final solution. The question was still inconsistent after several requests for clarification, so I stopped processing. General difficulty The Problem is: predicates on some columns, ORDER BY on a different column. In your fast query, without ORDER BY, the first (arbitrary) 10 rows ...


10

Here is what I do in such cases, usually some of this helps: Look at the whole query and try to remove unneeded tables from it. Rethink outer JOINs (that is, LEFT/RIGHT JOIN) and if possible, eliminate them from view definition, replacing by inner JOINS. Try to increase planner constants so the server can put more effort into planning phase. You can do ...


-1

Decimal(n,0) still treated as integer in mysql 5.0.3 and above. Decimal (n,0) is a fixed point that did not has fractional part. So it is safe. The only different int(11) with decimal(11,0) is the maximum range of number. where as the decimal has bigger range than integer See MySQL numeric type overview


-4

You can use MATERIALIZED VIEW to get faster response then the simple view in oracle. here is the syntax for the same. try this in your query. CREATE MATERIALIZED VIEW hr.employees AS SELECT * FROM hr.employees;


1

You can query from sql cache for long time: SELECT cp.objtype AS ObjectType, OBJECT_NAME(st.objectid,st.dbid) AS ObjectName, cp.usecounts AS ExecutionCount, st.TEXT AS QueryText, qp.query_plan AS QueryPlan FROM sys.dm_exec_cached_plans AS cp CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle) AS qp CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle) AS st You ...


1

You might want to try using a window function for that: create view vw_current_value as select id1, id2, value from ( select id1, id2, value, row_number() over (partition by id1, id2 order by value_at desc) rn from your_values_table ) t where t.rn = 1; sqlfiddle EDIT: The above has an advantage of being standard SQL. However (thanks to ...



Top 50 recent answers are included