Is this a valid insert?

Database
Enthusiast

Is this a valid insert?

Is this a valid insert?

CREATE MULTISET VOLATILE TABLE table_partial

(part_add1 VARCHAR(50), part_add2 VARCHAR(50), part_city VARCHAR(50), part_st VARCHAR (50), part_zip VARCHAR (50))

PRIMARY INDEX (part_add1)

ON COMMIT PRESERVE ROWS;

INSERT INTO table_partial VALUES ('%ADDRESS%','%','%','%','%');

Alright so here is what I am trying to do:

I want to be able to querey off of a patrial address, in this example I have the address (or part of address) but do not have apt#, city, state, zip

The error I'm running into is I don't think all those % act as valid placeholders. My data is populating with 0 rows when it shouldn't be.  My select statement logic is using:

WHERE c.adr1 LIKE a.part_add1

AND c.adr2 LIKE a.part_add2

AND b.citynm LIKE a.part_city

AND b.stcd LIKE a.part_st

AND b.pstlcd LIKE a.part_zip

If I -- all of the AND statements, because the above example is only working off add1, then it works. Is there a way around having to -- all of the AND statements?

What is also strange is: the script runs fine with some addresses and not others, even though through reverse testing all of them should have populated.

Any help would be appreciated.

Thanks!

9 REPLIES
Senior Supporter

Re: Is this a valid insert?

Hi,

I run SQLs below and it worked as I would expect. It's a nice way of handling this.

CREATE VOLATILE TABLE table_partial

(id integer, part_add1 VARCHAR(50), part_add2 VARCHAR(50), part_city \
VARCHAR(50), part_st VARCHAR (50), part_zip VARCHAR (50))

PRIMARY INDEX (part_add1)

ON COMMIT PRESERVE ROWS;

CREATE MULTISET VOLATILE TABLE table_all

(add1 VARCHAR(50), add2 VARCHAR(50), city VARCHAR(50), st VARCHAR \
(50), zip VARCHAR (50))

PRIMARY INDEX (add1)

ON COMMIT PRESERVE ROWS;

INSERT INTO table_all VALUES ('MyADDRESS is this','and this','new york','main street','12345');
INSERT INTO table_all VALUES ('My home is my castle','and this','atlanta','main road','22345');
INSERT INTO table_all VALUES ('My home ADDRESS is my castle','and this','San Diego','pacifc','32345');

INSERT INTO table_partial VALUES (1,'% ADDRESS %','%','%','%','%');
INSERT INTO table_partial VALUES (2,'% ADDRESS %','%','%','% main \
%','%');

select a.*
from table_all a
join
table_partial p
on a.add1 like p.part_add1
and a.add2 like p.part_add2
and a.city like p.part_city
and a.st like p.part_st
and a.zip like p.part_zip
where id = 2;

select a.*
from table_all a
join
table_partial p
on a.add1 like p.part_add1
and a.add2 like p.part_add2
and a.city like p.part_city
and a.st like p.part_st
and a.zip like p.part_zip
where id = 2;

Enthusiast

Re: Is this a valid insert?

Unless I am crazy - I just copied your exact scripting into my Teradata. The only way it works is if I -- the AND statements that are not being used, like city, state, zip. Maybe I'm doing something wrong??

Senior Supporter

Re: Is this a valid insert?

hm, this is my bteq output - looks fine to me.

BTEQ 13.10.00.01 Fri Feb 24 14:27:50 2012

+---------+---------+---------+---------+---------+---------+---------+----
.run file ./logon.txt;
+---------+---------+---------+---------+---------+---------+---------+----
.logon xxx,yyy

*** Logon successfully completed.
*** Teradata Database Release is 13.10.01.02
*** Teradata Database Version is 13.10.01.04
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
*** Warning: EOF on INPUT stream.
+---------+---------+---------+---------+---------+---------+---------+----

.set width 500;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

CREATE VOLATILE TABLE table_partial
(id integer, part_add1 VARCHAR(50), part_add2 VARCHAR(50), part_city VARCHAR(50), part_st VARCHAR (50), part_zip VARCHAR (50))
PRIMARY INDEX (part_add1)
ON COMMIT PRESERVE ROWS;

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

CREATE MULTISET VOLATILE TABLE table_all
(add1 VARCHAR(50), add2 VARCHAR(50), city VARCHAR(50), st VARCHAR (50), zip VARCHAR (50))
PRIMARY INDEX (add1)
ON COMMIT PRESERVE ROWS;

*** Table has been created.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

INSERT INTO table_all VALUES ('MyADDRESS is this','and this','new york','main street','12345');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
INSERT INTO table_all VALUES ('My home is my castle','and this','atlanta','main road','22345');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
INSERT INTO table_all VALUES ('My home ADDRESS is my castle','and this','San Diego','pacifc','32345');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

INSERT INTO table_partial VALUES (1,'%ADDRESS%','%','%','%','%');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
INSERT INTO table_partial VALUES (2,'%ADDRESS%','%','%','%main%','%');

*** Insert completed. One row added.
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

select a.*
from table_all a
join
table_partial p
on a.add1 like p.part_add1
and a.add2 like p.part_add2
and a.city like p.part_city
and a.st like p.part_st
and a.zip like p.part_zip
where id = 2;

*** Query completed. One row found. 5 columns returned.
*** Total elapsed time was 1 second.

add1 add2 city st zip
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
MyADDRESS is this and this new york main street 12345

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

select a.*
from table_all a
join
table_partial p
on a.add1 like p.part_add1
and a.add2 like p.part_add2
and a.city like p.part_city
and a.st like p.part_st
and a.zip like p.part_zip
where id = 2;

*** Query completed. One row found. 5 columns returned.
*** Total elapsed time was 1 second.

add1 add2 city st zip
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
MyADDRESS is this and this new york main street 12345

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

select a.*
from table_all a
join
table_partial p
on a.add1 like p.part_add1
and a.add2 like p.part_add2
and a.city like p.part_city
and a.st like p.part_st
and a.zip like p.part_zip
;

*** Query completed. 3 rows found. 5 columns returned.
*** Total elapsed time was 1 second.

add1 add2 city st zip
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
My home ADDRESS is my castle and this San Diego pacifc 32345
MyADDRESS is this and this new york main street 12345
MyADDRESS is this and this new york main street 12345

+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-

.exit 0;
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 0

Enthusiast

Re: Is this a valid insert?

Maybe it has something to do with our Teradata versions? When I use YOUR data and YOUR scripts exactly as written I still have to -- out the AND statements that are not included in the partial values.

Senior Supporter

Re: Is this a valid insert?

can you run my bteq script (with all conditions) and share the bteq output?

Enthusiast

Re: Is this a valid insert?

I do not have BTEQ :(

Senior Supporter

Re: Is this a valid insert?

Which tool do you use?

Which access (odbc or .net)?

Which DB version?

What is the exact error code?

Enthusiast

Re: Is this a valid insert?

Okay I got it figured out. I do not run UNIX so all those \ were messing up my data. I got it work when I finally got rid of the last pesky \. The issue I was running into on my end was null values. if ADD2 was null, the LIKE statement on a null value was returning 0 rows. If I use coalesce(a.add2, null) like b.add2, problem solved. Thanks for helpling!

Senior Supporter

Re: Is this a valid insert?

Sorry for the \ they are placed automaticly in my editor. Didn't saw them when I copied them.

Yes, null need to be handled correctly. I had the impression from the initiial post that you had an syntax error problem. Good that you got it working know. As stated before it can be an elegant way to make flexible searches.