|
From: Jack B. <Jac...@es...> - 2019-10-14 09:25:26
|
1) Union ALL renders same results … but good point on default UNIONl
2) I KNEW there was a record when I created the first statement, but returned NO RECORDS as results set. Through trial and error the second statement worked – so posted question with UNION and should have been UNION ALL
TBH I suspect ODBC driver connected with Progress database or (maybe?) Squirrel
Thanks for your help / comments on this
Jack Brosch
IT Report Developer
Jac...@es...
T:
M:704-622-6995
www.essentra.com
Essentra
,
United States of America
-----Original Message-----
From: John Hardin <jh...@im...>
Sent: Saturday, October 12, 2019 11:26 AM
To: Jack Brosch <Jac...@es...>
Subject: Re: [Squirrel-sql-users] Bad SQL results ??? Explanation
Cyber Awareness Month: External email – treat with caution
On Thu, 10 Oct 2019, Jack Brosch via Squirrel-sql-users wrote:
> SQL command is (ONLY difference is the % in front of RPT on 2nd select :
>
> SELECT * FROM "VISION"."PUB"."PM_Program"
> WHERE "PM_Program"."ProgCode" like 'RPT%' and "PM_Program"."ProgCode" like '%Label%'
> UNION
> SELECT * FROM "VISION"."PUB"."PM_Program"
> WHERE "PM_Program"."ProgCode" like '%RPT%' and "PM_Program"."ProgCode" like '%Label%'
>
> Results are 1 record??
UNION implies DISTINCT. If you want all rows returned by both (all) branches, even if they are exact duplicates, you need to use UNION ALL (which is also faster because it skips the DISTINCT step).
That seems to me an unnecessary UNION, as '%' also matches nothing, so '%RPT%' (the second branch) *will* match 'RPT.....' (the first branch) (as well as 'RPT' and '.....RPT'). Did you intend to see 'RPT.....' rows *twice*?
If there is a row where ProgCode is 'RPT.....' it will match both LIKE clauses and then be filtered back down to one row by the UNION's implied DISTINCT.
--
John Hardin KA7OHZ https://urldefense.proofpoint.com/v2/url?u=http-3A__www.impsec.org_-7Ejhardin_&d=DwIBAg&c=H1TZfAUq0s96UPNrDmKV-e0s_0PTvVM9pb4v1yW-_hY&r=f4DIE9GKTOpbN419sZmqY0dKfE8s9gOfXuvx8VBMEks&m=S0uWEPCiJNQxfGZlpeCDxsdX5616kla6M_qSCs5cYd4&s=IXi5xysieSW1TpBzIWfxFi6nHyNoWTM_BN33qvOJF5Y&e=
jh...@im... FALaholic #11174 pgpk -a jh...@im...
key: 0xB8732E79 -- 2D8C 34F4 6411 F507 136C AF76 D822 E6E6 B873 2E79
-----------------------------------------------------------------------
Are you a mildly tech-literate politico horrified by the level of
ignorance demonstrated by lawmakers gearing up to regulate online
technology they don't even begin to grasp? Cool. Now you have a
tiny glimpse into a day in the life of a gun owner. -- Sean Davis
-----------------------------------------------------------------------
926 days since the first commercial re-flight of an orbital booster (SpaceX)
|
