concatenate filters | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

concatenate filters

Hi guys! this is my first post !!. sorry if i make a mistake or i am in the wrong place. Here is my situation:
i have a table where i store info about products. Each product has contents, so i am trying to compare contents between similar products.
I would like to compare product A against product A.01.
can i do something like this? at the moment i have to write the product code manually as per below:
select ….
from tproduct a, tproduct b
where
a.product_code=’A’ and
b.product_code="A.01′ thx a lot, max.
Yes. The codes looks fine so far. Do you have any issue / problem ?
KH
quote:Originally posted by khtan Yes. The codes looks fine so far. Do you have any issue / problem ?
KH

yes, the code is fine. but i would like to comapre all the products automatically. EXAMPLE:
if the table has: product_code content
———— ——-
A 1
A01 2
B 3
B01 3
C 8
C01 10 i would like to compare A against A01, B against B01, and so on. thx, max.
you can use like
select *
from tproduct a, tproduct b
where a.product_code like ‘%’ + b.product_code + ‘%’ KH
quote:Originally posted by khtan you can use like
select *
from tproduct a, tproduct b
where a.product_code like ‘%’ + b.product_code + ‘%’ KH

kthan, thx 4 your hlp.
it did not work 4 me. i have problems with the + ‘%’.
anyway. let me give you the real query instead of an example.
SELECT A.MODULE_CODE AS MODULE_BEFORE,B.MODULE_CODE AS MODULE_AFTER,A.STOWAGE_NO AS STOWAGE_BEFORE,B.STOWAGE_NO AS STOWAGE_NO_AFTER,
A.COMPONENT_CODE AS COMPONENT_BEFORE,B.COMPONENT_CODE AS COMPONENT_AFTER
FROM QF_SPACEP.MODULE_CONTENTS A, QF_SPACEP.MODULE_CONTENTS B
WHERE
A.AIRCRAFT_TYPE=B.AIRCRAFT_TYPE AND
A.STOWAGE_NO=B.STOWAGE_NO AND
A.COMPONENT_CODE<>B.COMPONENT_CODE AND
A.AIRCRAFT_TYPE=’734′ AND
A.MODULE_CODE = ‘+GDBH01.18.01’ AND
B.MODULE_CODE = ‘+GDBH01.19.01’
ORDER BY 1,3 the code above, compare different modules(same module but diff. versions) for the same aircraft and same stowage. As a result i get what the diff is per stowage position. what i try to do is to not to write manually the module_code and compare automatically for the same aircraft every module version 18 against version 19. i tryed your suggestion but toad doesnt like the "+ ‘%’" expression. any other suggestion? thx again, max.
How is the structure of MODULE_CODE like ? is it the frist 6 or 7 chars the same ? try
left(A.MODULE_CODE, 7) = left(B.MODULE_CODE, 7)
KH
quote:Originally posted by khtan How is the structure of MODULE_CODE like ? is it the frist 6 or 7 chars the same ? try
left(A.MODULE_CODE, 7) = left(B.MODULE_CODE, 7)
KH

kthan, i tryed left function, but toad doesnt like it.
thx again, max
Please provide the table DDL and some sample data with the required result
KH
quote:Originally posted by khtan Please provide the table DDL and some sample data with the required result
KH

kthan,
this is the why i solved, temporarelly my problem. is not 100 accurate, but is very good.
SELECT A.MODULE_CODE AS MODULE_BEFORE,B.MODULE_CODE AS MODULE_AFTER,A.STOWAGE_NO AS STOWAGE_BEFORE,B.STOWAGE_NO AS STOWAGE_NO_AFTER,
A.COMPONENT_CODE AS COMPONENT_BEFORE,B.COMPONENT_CODE AS COMPONENT_AFTER
FROM QF_SPACEP.MODULE_CONTENTS A, QF_SPACEP.MODULE_CONTENTS B
WHERE
A.AIRCRAFT_TYPE=B.AIRCRAFT_TYPE AND
A.STOWAGE_NO=B.STOWAGE_NO AND
A.COMPONENT_CODE<>B.COMPONENT_CODE AND
A.AIRCRAFT_TYPE=’733′ AND
a.module_code like b.module_code || ‘%’
thx 4 your time and hlp, max.
I’ll bite. Are you using SQL Server or some other RDBMS?. I haven’t seen the || operator in T-SQL before. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
|| is oracle concatenation operator, probably postgres too.
I was fairly certain about Oracle, good you confirmed that, but I never played with Postgres yet. —
Frank Kalis
Moderator
Microsoft SQL Server MVP
Webmaster:http://www.insidesql.de
Me too, but I know they more or less copied oracle pl/sql syntax.
]]>