|Query rows between dates and before|
Joining on the @users table variable and using
"row_number() where = 1" should be faster for
selecting the top record per UserId...
from MyTable t inner join @users u on t.UserId =
where t.StartUnixTime between @start and @end
|Left join expression and amount of rows returned by Oracle|
Your assumption that t2.id = t2.id is always true
is wrong. If the value were NULL that would be
treated as false. I don't believe that is
relevant for this particular example, but just to
The question is how is a left join processed. The
idea is simple. The on clause is processed. If
there are no matches, then the row from the first
table is kept. This is regardless of what is in
|Number of repeating customers in SQL Server|
numbers of the customers who used more than one
CountOfCust,PaymentType,CustNum from Customers
GRPUP BY PaymentType,CustNum
|Unable to retrieve the month from date parameter using vba function in mdx|
(try uppercase 'MM' rather than 'mmm' - even 'mm'
is wrong as it will look for minutes rather than
This question and answer looks at working with
Changing a date format to a shorter date
MSDN is a good reference for the available vba
functions in mdx that you can use to play around
with dates. Current link is here:
|SQL Query CASE WHEN Statement Probelm|
My guess is that the line:
WHEN tr_time > '75600' or tr_time < '14400' and
tr_loc = '6-LDT2' then '3'
is being interpreted as:
WHEN tr_time > '75600' or (tr_time < '14400'
and tr_loc = '6-LDT2') then '3'
Then would come to the first clause:
WHEN tr_time > '75600'
Which satisfies the OR statement, giving a value
of '3' .
|Display column name with max value between several columns|
You can do this with a big case statement:
(case when denver = greatest(denver,
seattle, new_york, dallas, sanfran) then 'denver'
when seattle = greatest(denver,
seattle, new_york, dallas, sanfran) then 'seattle'
when new_york = greatest(denver,
seattle, new_york, dallas, sanfran) then
when dallas = greatest(denver,
|Generic set returning function|
The answer is yes. But it's not trivial.
As long as you return anonymous records (returns
setof record) a column definition list is required
for the returned record to work with it in SQL.
Pretty much what the error message says.
There is a (limited) way around this with
CREATE OR REPLACE FUNCTION cs_get(_tbl_type
anyelement, _colname text)
RETURNS SETOF anyelement AS
|Buggy query for dropping databases|
This script will make sure you are connected to
that database, then change database mode to single
user, it means only one user can be connected to a
database, then you will connect to Master database
it will leave database to be dropped with no
connections, and finally you can drop the
Also use appropriate data types, your database
will never be 2000 characters then why use a
|SQL - returning two matching values|
Just use order by and limit or top or fetch first
1 row only. For example:
SELECT PARTNER_WORKORDER.statusNo as number,
count(statusNo) as total
FROM PARTNER_WORKORDER inner join
ON PARTNER_WORKORDER.salesOrderNo =
WHERE orderDt <= '2012-11-20 00:00:00.000' AND
country_code <> 'US'
group by PARTNER_WORKORDER.sta
|Conversion failed when converting the varchar value '.03' to data type int|
You have this expression:
You are multiplying a substring by integers. I
have no idea what NoOfHoursWorked is. It would
seem strange to me if this were stored as anything
other than a numeric. So, perhaps this will work:
If you do need the strange
|Query to Sum Numbers Based on Location (State)|
I went back and used a UNION statement to give me
the correct answer:
select sum(procperf) as 'Procedures Performed'
select sum(proceduresperformed) as procperf
from DriveProjectionAndCollectedTotals DPaCT
inner join rpt_DriveMaster DM on
left outer join rpt_Accounts Acct on
inner join rpt_AddressDetail AD on
|Change database ownership SQL Server 2012 Express|
For this to work, two things must be true, per
Server objects (such as databases) must be owned
by a server principal
This means the account you want to give ownership
must have a login on the server (not just a user
in the database -- it is possible to have users
without logins if you restore from a backup).
Requires TAKE OWNERSHIP permission on t
|How a query can verify itself that all template parameters are replaced before executing|
Can you replace the params with actual variables,
but not actually declare the variables? That way,
if they haven't replaced the variable with the
desired values, the parser will complain about the
variable not being initialized.
IF ( @Template_param + @Value =
RAISERROR (@ErrorMsg, 18, -1, N'');
|find column number in a SQL table in IBM netezza database|
You can get that information by joining the
_v_table and _v_relation_column views.
attname AS COL_NAME,
b.FORMAT_TYPE AS COL_TYPE,
attnum AS COL_NUM
FROM _v_table a
JOIN _v_relation_column b
ON a.objid = b.objid
WHERE tablename = 'TEST1'
ORDER BY attnum;
TABLENAME | COL_NAME | COL_NUM
TEST1 | COL1 |
|SQL Rounding and Selecting|
To do them all
UPDATE MyTable SET value1 = ROUND(Value1,0)
To just do the ones that need it
UPDATE MyTable SET value1 = ROUND(Value1,0) WHERE
Value1 - cast(Value1 as int) > 0
|Unexpected AST Node LEFT OUTER JOIN|
defm doesn't appear to be a column so change it to
* or the columns you want.
also =: isn't needed here as you're doing a left
join syntax. I belive the := or =: was old ANSI
style SQL for joins.
FROM DefermentAccount defm
LEFT JOIN defm.division division
|SQL working select with group by - how to get the id of found records?|
You can use your grouped query (slightly
modified), to get the lat and lng you're
interested in, and then join those results back to
the facilities table to get the other data - id in
SELECT a.lat, a.lng, a.locationCount, b.id
FROM (SELECT lat, lng, count(*) locationCount
GROUP BY lat, lng
HAVING count(*) > 1) a
INNER JOIN facili
|select rows from main table based on highest date in child table between a date range|
You will need to pull the MAX per ApplicantId with
a GROUP BY in a sub-query, then JOIN to that
result. This should work for you:
Select A.ApplicantId, A.[Applicant Name]
From ApplicantTableName A
Select D.ApplicantId, Max(D.Dt) DT
From DetailsTableName D
Group By D.ApplicantId
) B On A.ApplicantId = B.ApplicantId
Where B.DT Between '03/05/2014' And '04/20/2014'
|Pull field from master table where two child tables have matching data|
Questions is a bit unclear on which fields are in
which table but choose which version works for
FROM FileName fn
INNER JOIN DepartmentA da ON fn.ID = da.LastNameA
INNER JOIN DepartmentB db ON fn.ID = db.LastNameB
WHERE da.LastNameA = db.LastNameB
FROM FileName fn
INNER JOIN DepartmentA da ON fn.ID = da.ID
INNER JOIN DepartmentB db ON fn.ID = db.ID
|In SQL, split an amount among multiple rows|
The following uses CTE approach to run the
calculate the totals.
DECLARE @Travel TABLE (ID INT, State VARCHAR(10),
DECLARE @Travel_Sum TABLE (State VARCHAR(10),
INSERT @Travel VALUES (1, 'AL', 20), (2, 'AL', 2),
(3, 'AL', 14)
INSERT @Travel_Sum VALUES ('AL', 24)
;WITH TravelRows AS (
|"Standard" method for refreshing production cubes SSAS|
I prefer to use an SSIS package, mainly for the
logging. I send the logs to the msdb..sysssislog
table so you get detailed logging with minimal
effort. It's very useful when a build fails, as
SSAS typically puts out hundreds of irrelevant
"process cancelled" messages.
I do all the design work in Visual Studio, with an
Environment configuration pointing to the DEV
environment, then switch to a P
|How do I exclude multi-line set of data when ONE line of data has a specific value? SQL-ORACLE|
You need a correlated subquery for the NOT EXISTS:
NOT EXISTS (SELECT 1
FROM sqlmgr.PRICE P2
WHERE p2.ITEM = p.ITEM AND P2.PRICE =
Your version returns nothing because there are
records that have PRICE1 in them -- without the
correlation piece (p2.ITEM = p.ITEM), it would
check the entire table instead of just the item of
interest on a given row.
|Use value from qry using Count and update to a table in Access 2007|
If I understand correctly, you can do this with a
correlated subquery. The following does the
update for all records:
set NumRecords = (select count(*)
from tbl_Interpolation as i
where i.unit =
tbl_CalcReg.unit and i.car = tbl_CalcReg.car and
You can ad
|unexpected LEFT OUTER JOIN behaviour in SQLITE|
How about something like this?
LEFT OUTER JOIN table1
ON table1.ticker =
|How to select what button is being clicked on a multi-button click event, backed by sql|
This is how I determine which button was clicked,
and then use a Select Case to determine what to do
with said button click.
Dim bt As Button = o
Dim btName As String = bt.Text
In your case if the name of the button or the id
is tied directly to a value you can just pass that
into your parameters by accessing it from the bt
|SQL Server : query multivalued table recursive|
I think that the following recursive CTE will give
you what you want:
;WITH cte AS
SELECT m2.object_id AS groupID,
FROM @mms AS m1
INNER JOIN @mms_mv AS m2 ON m1.object_id =
INNER JOIN @mms m3 ON m2.object_id =
WHERE m1.attribute_type = 'user' AND
m3.attribute_type = 'group'
SELECT m.object_id AS groupID, m.attribut
|Joining two tables with PreparedStatement|
+ "JOIN ACCOMODATION a"
+ "ON p.ACCOMNO = a.ACCOMNO "
there is no space between "....ACCOMODATION a" and
replace them with something like this:
+ "JOIN ACCOMODATION a "
+ "ON p.ACCOMNO = a.ACCOMNO "
|Unicode to Non-unicode conversion|
No, Unicode is Unicode. The standard allows for
innumerable "special" characters beyond ASCII.
The best you can do is search for your desired
subset of characters before the conversion and
convert them to your favorite ASCII stand-ins.
|Finding maximum value of group of rows POSTGRESQL|
FROM President pr
INNER JOIN (SELECT Party, MAX(years_served) as
YS FROM president GROUP BY Party) as MaxYS on
Hope is what you are looking for.
|comparing the results in two tables|
I think you can use full outer join and some
conditions in the select clause:
select coalesce(a.id, b.id) as id,
(case when a.plan_hash_value is null then
'Plan not found in table t11'
when b.plan_hash_value is null then
'Plan not found in table t22'
else 'plans same'
end) as status,
a.cost as cost_t11, b.cost as cost_t22
from t11 a full outer join
|Insert missing rows (with zero values) into table, following a display pattern|
You can do this by generating all the rows first
(using a cross join) and then bringing in the
select y.val_year, g.val_name, g.Group_A,
coalesce(in_stock, 0) as in_stock
from (select distinct val_year from temp_data) y
(select distinct val_name, Group_A, Group_B,
Group_C from temp_data) g left join
on d.val_year = y.val_y
|PostgreSQL Trigger with duplicate values|
Finally I found a way to make a TRIGGER wich
solves my problem, the code is...
CREATE OR REPLACE FUNCTION mvp_player_equal()
RETURNS trigger AS $$
IF (TG_OP = 'INSERT') THEN
IF max_points = NEW.mvp_score THEN
UPDATE match SET mvp_player = (SELECT
player FROM MA
|Cannot assign a default value to a local variable in SQL|
Prior to SQL Server 2008, assigning a default
value (or initial value) to a local variable is
not allowed; otherwise this error message will be
Solution 1: (Use SET)
DECLARE @thresholdDate DATETIME
set @thresholdDate = '2014-11-30'
For more details about the error :
Solution 2: (Upgrade)
Another way of avoiding this e
|Is it possible to set no lock or TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at database level?|
The short answer is "no". The default isolation
level in SQL Server is READ COMMITTED, and there
is no way to change this to UNCOMMITTED, either
globally or per-database. And that's a very good
WITH (NOLOCK) is a recipe for trouble when it
comes to getting accurate results from your
database, and in bad cases it can even cause
timeouts from queries that run forever due to data
|SQL constraint check with date from table linked with foreign key|
This is not possible to do using check
constraints, since check constraints can only
refer to columns inside the same table.
Furthermore, foreign key constraints only support
If you must perform this check at the database
level instead of your application level, you could
do it using a trigger on INSERT/UPDATE on the
action-table. Each time a record is inserted or
updated, you check w
|Filter SQL table with max record within two specific dates range|
Are you over-thinking it? Why not something like:
SELECT EmployeeName, TargetTxDate,
sum(ActualTarget) as Actual, sum(RequiredTarget)
AS ReqTarget, sum(variance) AS Variance
FROM dbo.TargetsTx as TargetsTx
WHERE TargetTxDate >= @BgnDate
AND TargetTxDate < @EndDate
group by EmployeeName,TargetTxDate
ORDER BY TargetTxDate DESC
|Loop for stored procedure|
Try this using CURSOR.
DECLARE @sItemNumber AS VARCHAR(MAX)
DECLARE TestCursor CURSOR FOR
select ITEM_NUMBER from xTable
FETCH NEXT FROM TestCursor INTO @sItemNumber
WHILE @@FETCH_STATUS = 0
EXEC ProcedureName @sItemNumber
FETCH NEXT FROM TestCursor INTO @sItemNumber
|No rows returned when column has a null value - sql|
Edit: Completely rewrote my answer, as I think I
understand your question now.
Try something like this:
WITH UniqueItems AS (
SELECT DISTINCT cid, price
FROM (select * from UniqueItems i, Features f) t
LEFT JOIN Items i2 ON i2.cid = t.cid AND i2.fid =
SELECT cid, price, value, null, null
|is there anything like timestamp(9) in teradata|
Perhaps you can accomplish this by storing three
fields in your table:
Message_Queue_Timestamp TIMESTAMP(6) NOT NULL
Message_Queue_Date DATE NOT NULL
Message_Queue_Seconds_From_Midnight FLOAT NOT NULL
Using dnoeth's suggestion to convert the input
using to_timestamp() to retain a DBA-friendly
timestamp in the table. This is
Then split the incoming timestamp into two f