Tag Info

New answers tagged

0

I have an idea that MAY cause it: The tables you are showing us are not SELECT * FROM ... are they? I think that in dtr_tbl, the logdate has a time part that is not 00:00:00 and this causes that in the WHERE condition, log_date BETWEEN CAST('03/13/2015' AS datetime) AND CAST('03/23/2015' AS datetime) leads to ie 2015-03-23 8:00:05 between ...


0

Try this: SELECT correct_picks, points, pick_user_id, bp, (bp + points) AS total, user_user_id, username, avatar_date, avatar_width, avatar_height, gravatar, gender FROM ( SELECT COUNT(game.game_id) AS correct_picks, SUM(game.points) points, pick.user_id AS 'pick_user_id', -- Alias to avoid same name with user.user_id (SELECT ...


2

The correct query is: UPDATE lp_plates_backup AS t INNER JOIN ( SELECT plate_uid, brand, model, date_validated FROM lp_pictures_backup as parent WHERE brand <> '' AND date_validated = ( SELECT MAX(date_validated) FROM lp_pictures_backup ...


0

Use aliases like that: Select a.Column, b.Column FROM TableA AS a JOIN TableB AS b on a.TableBId = b.Id This way the engine knows when you want to use Column from TableA and when from TableB. It is a good practice - you should always use aliases when writing SQL queries. The query you are looking for might be: SELECT c.catname ,( SELECT ...


1

Most likely ,(SELECT NVL (I.sap_xref_no, part_doc_nbr) FROM eissap.tbitem i WHERE i.item IN (SELECT comp_part FROM eissap.tbpart_config_comp_config WHERE item = comp_part)) AS sap_xref_no, returns more than one row (exactly as error says). When subquery is used in SELECT clause of ...


0

Looks like it can be radically simplified with plain joins to a subquery: SELECT u.name AS u_name, c.name AS c_name FROM user_table u JOIN (SELECT u.id, c.id AS content_id FROM ... UNION -- fold duplicates SELECT u.id, c.id AS content_id FROM ... ) uc USING (id) JOIN content_table c ON c.id = uc.content_id If there cannot be ...


1

There are several ways to rewrite the query, even with keeping the CTEs. But please consider that CTEs in Postgres are pre-evaluated, so they cause efficiency problems. first, the LEFT join is equivalent to an inner JOIN, as you have a column from the right-side table in the following WHERE clause. second, the FROM user_table LEFT OUTER JOIN content_table ...


1

In Postgres 9.4 you can simplify with the aggregate FILTER clause: CREATE VIEW fumbbl.matches AS SELECT m.fmid, m.time, d.name AS division , min(s.coachbracket) FILTER (WHERE NOT s.away) AS hbracket , min(t.name) FILTER (WHERE NOT s.away) AS hteam , min(c.name) FILTER (WHERE NOT s.away) AS hcoach , min(s.score) ...


0

After some processing this boiled down to: While your predicate d."SettlementPointName" = 'John' is filtering a single value for "SettlementPointName" anyway, simplify to: SELECT count( d."SettlementPointPrice" < 10.5 OR NULL) AS da_00_10 , count(d."SettlementPointPrice" >= 10.5 AND d."SettlementPointPrice" ...



Top 50 recent answers are included