While 'CASE WHEN' update, other columns become null
sql-server,stored-procedures,case-when
You are assigning NULL because you have no ELSE in your CASE. I assume that you instead want to use the old value: UPDATE SET abcdTable.InpostPrice = CASE WHEN tempTable.VendorName = 'Inpost' THEN tempTable.Price ELSE abcdTable.InpostPrice END, ..... From documentation: ELSE else_result_expression: Is the expression returned if no comparison operation...
calculating ratio of aggregates for subsample satisfying a condition
sql,case,aggregate-functions,case-when,monetdb
First of all, when you create the view, you need to use 0.0 in your case statement. This will make sure that the column in the view is created using a correct data type (double in your case): CREATE VIEW sys.over26_2007 (personlopnr,peorglopnr,loneink,below26_loneink) AS ( SELECT personlopnr,peorglopnr,loneink, CASE WHEN fodelsear <...
i do a case when section with group by, after i want a count
Simply put it in a subquery: select count(*) as anum, voucher, date from ( select case when anum like '0%' then 0 else 1 end anum , ,voucher , date from ledger group by voucher, date, case when accountnum like '0%' then 0 else 1 end order by voucher )...
NULL values appear before empty strings when doing an ORDER BY in SQL
sql,sql-server-2008,tsql,dml,case-when
The CASE statements must be specified first. Having said that, you can achieve desired results by rewriting the ORDER BY clause: ORDER BY CASE WHEN dir_email <> '' AND dir_tele <> '' THEN 1 -- Both dir_email and dir_tele are present WHEN dir_email <> '' THEN 2 -- At least...
Multiple Calculations in case statement not running
you can't use the alias name given to a column in the same select clause you need to write it as a subquery or cte. with cte as ( select DATEPART(dw,t1.Date) as dw1, DATENAME(dw,t1.Date) as dw2, AVG (CASE WHEN (rraw.Mode='Passive') THEN T1.LengthSec + T1.Sec ELSE NULL END) AS 'Passive', AVG...
How to make a CASE statement dynamic in a MySQL Statement/Query
php,mysql,group-by,case,case-when
The problem is that you are going to land up with a varying number of columns, which is messy to process (and is probably easier to bring each back as a different row anyway) It would be possible using php to dynamically build up a query, although I wouldn't want...
How to create T - SQL statement that return different result set base on criteria (IF statement)
sql,sql-server,if-statement,case-when
You don't want different results set, you simply want a different column... SELECT id, lookup_code,item_name, CASE WHEN @param = 'Regular' THEN regular_price WHEN @param = 'Promo' THEN promo_price WHEN @param = 'Employee' THEN employee_price WHEN @param = 'Customer' THEN custom_price END as thePrice FROM dbo.vwMenuAndItemLookup ORDER By item_name ASC ...
SQL Case When - delete 'empty' rows from output
sql,row,sql-server-2014,case-when
Use the original query as a derived table, then MAX the columns: select MAX('2010'), ... from ( SELECT case when Year(LOAN_START_DATE) = 2010 then max(LOAN_RES_BANK_CODE) else 0 end as '2010', ... ) ...
Overwrite field values based on membership in a set
python,if-statement,pandas,sqlite3,case-when
Okay, I'm assuming that your frames actually look like this (also, here's how to make dataframes): a = pd.DataFrame({'Invoice Number':[1341262, 10327970, 1037941, 1805305, 3302259, 1037388], 'Invoice Type':[None]*6}) b = pd.DataFrame({'Sold On Invoice Number':[1341250, 3302261, 1341271, 1037388, 134127]}) To get the indices where there's overlap, do a boolean slice using .isin,...
Multiple When case inside a join in MySQL
Below is an attempt to format your query in a somewhat readable way: SELECT * FROM items i1 join param on (case when (ITEM_ID=param_item_id and i_status=1 and item_page=164) then param_item_id=ITEM_ID when (i_micro_site>=1 and i_status=7 and (EXISTS(select * from multiple where multiple_id=ITEM_ID and multiple_cat=21 and multiple_enum="item" ) || item_page=169 ) )...
case when based on group dense_rank() value and row_number() value
sql,sql-server-2008,row-number,case-when,dense-rank
select * from ( select col1,col2,col3,col4,qty ,dense_rank() over (order by col1,col2,col3) as outer_index_group ,row_number() over (partition by col1,col2,col3 order by col1,col2,col3) as inner_index_group ,RANK() over (partition by col1,col2,col3 order by col1,col2,col3,col4 desc) as rnk from myTable ) T where rnk = 1 important part here is Rank() over (..., col4...
ORA-00913: too many values while using case when
sql,oracle,oracle11g,case-when
When you use a case, you must return only a single record - not more than 1. To achieve your desired result, I would use a left outer join (assuming you have a way to join table1 to table2), but add your check on table1.c1 into the join condition so...
SQL statement to count each individual NPANXX series
Without knowing your expected results, I imagine you need to use group by with your query. Perhaps something like this: select case when length(Dialled_Number) = 11 then Substr(Dialled_Number, 2, 7) else Substr(Dialled_Number, 1, 6) end, count(*) from Error_Event group by case when length(Dialled_Number) = 11 then Substr(Dialled_Number, 2, 7) else...
How to add Multiple Variables in THEN clause When using WHEN THEN in Select statement
mysql,sql,jasper-reports,ireport,case-when
Change your logic to simple boolean comparisons: WHERE ( $P{status} is not null AND STATUS IN ($P{status}, ' ') ) OR ( $P{status} is null AND STATUS IN ('V', 'R') ) ...