Обсуждение: garbage data back
I was doing a SELECT * FROM view WHERE field LIKE pattern
and getting garbage data back.
Turns out it is filtering on a different field.
As you can see here, when attempting to filter on the FileKey, it is actually filtering on the Order Item ID field.
select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
where "FileKey" LIKE '%1317'
where "FileKey" LIKE '%1317'
I'm stumped. Any guesses?
Вложения
LOL
Joke's on me.
Here's the relevant part of the view
SELECT ...
"Order Items"."ID" AS "OrderItemID",
...
(("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"
...
(("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"
Wait, then why are the Item IDs different?
Here are the results again, with the FileKey field expanded a bit.
On Mon, Mar 6, 2023 at 2:23 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 3/6/23 12:17, Brad White wrote:
> I was doing a SELECT * FROM view WHERE field LIKE pattern
> and getting garbage data back.
>
> Turns out it is filtering on a different field.
>
> As you can see here, when attempting to filter on the FileKey, it is
> actually filtering on the Order Item ID field.
>
> select "FileKey", "OrderItemID" from "vw_rptInvc_Permits"
> where "FileKey" LIKE '%1317'
>
> image.png
> I'm stumped. Any guesses?
What is the view definition?
Is there a table named vw_rptInvc_Permits?
--
Adrian Klaver
adrian.klaver@aklaver.com
Вложения
On Mon, Mar 6, 2023 at 1:48 PM Brad White <b55white@gmail.com> wrote:
LOLJoke's on me.Here's the relevant part of the viewSELECT ..."Order Items"."ID" AS "OrderItemID",
...
(("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"Wait, then why are the Item IDs different?Here are the results again, with the FileKey field expanded a bit.
I don't understand what you are questioning...FileKey is a hyphenated concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is just the later - and the suffix of the former matches the later.
David J.
Вложения
In that picture, it's clear what is happening.
Here, again, is the result that had me stumped.
What threw me was that the field I was looking for had the format of 6d-4d and this field appeared to have the same format.
But once you expand it, you can see that it has a 6d-7d format and isn't the field I'm looking for at all. I was trying to take a shortcut by finding the data in the table instead of digging in and looking up what field was used in the report. And it appeared at first that I had found it. ¯\_(?)_/¯
On Mon, Mar 6, 2023 at 2:54 PM David G. Johnston <david.g.johnston@gmail.com> wrote:
On Mon, Mar 6, 2023 at 1:48 PM Brad White <b55white@gmail.com> wrote:LOLJoke's on me.Here's the relevant part of the viewSELECT ..."Order Items"."ID" AS "OrderItemID",
...
(("Orders"."ID" || '-'::text) || "Order Items"."ID") AS "FileKey"Wait, then why are the Item IDs different?Here are the results again, with the FileKey field expanded a bit.I don't understand what you are questioning...FileKey is a hyphenated concatenation of the ORDER Id and the ORDER ITEM Id, while OrderItemID is just the later - and the suffix of the former matches the later.David J.
