garbage data back

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:18 PM Brad White <b55white@gmail.com> wrote:

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'

I'm stumped. Any guesses?

Agreed, what you've shown doesn't make sense.

Try:
EXPLAIN ANALYZE
REINDEX (on any underlying table indexes)

Show the version you are running.

David J.

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"

Wait, then why are the Item IDs different?

Here are the results again, with the FileKey field expanded a bit.
[image: image.png]

On Mon, Mar 6, 2023 at 2:23 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On Mon, Mar 6, 2023 at 1:48 PM Brad White <b55white@gmail.com> wrote:

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"

Wait, then why are the Item IDs different?

Here are the results again, with the FileKey field expanded a bit.
[image: image.png]

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.

On 3/6/23 12:48 PM, Brad White wrote:

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"\

Because "Orders"."ID" is different then "Order Items"."ID"?

Wait, then why are the Item IDs different?

--
Adrian Klaver
adrian.klaver@aklaver.com

In that picture, it's clear what is happening.
Here, again, is the result that had me stumped.
[image: image.png]
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 3/6/23 1:27 PM, Brad White wrote:

In that picture, it's clear what is happening.
Here, again, is the result that had me stumped.
image.png
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.  ¯\_(?)_/¯

psql is your friend.

--
Adrian Klaver
adrian.klaver@aklaver.com

David,

Thanks!
'EXPLAIN ANALYZE' is very slick.
Promises to be very helpful.
As soon as I saw the output, it was obvious where my problem was.

Brad.

On Mon, Mar 6, 2023 at 2:30 PM David G. Johnston <david.g.johnston@gmail.com>
wrote: