Help with a hard query

[mas_oyama]

ok, i have to work with 2 tables, called "StockComment" and "StockDetailComment"StockComment: StockCommentID CommentType (i dont need that right now) CommentStockDetailComment: StockDetailCommentID StockDetailID (i dont need that right now) StockCommentID (foreing key) Value1 Value2So, what my query has to do: in the table StockComment, the Comment field contains a varchar(60). these are strings that either contains 0, 1 or 2 "|" (pipes). So my query has to replace those pipes with the Value1 and Value2 fields. so if theres no pipe, the Comment is returned as is, if there i one pipe, it replace by Value1, and if there is 2 pipes, the first is replaced with Value1 and the second is replaced by Value2.the 2 tables are linked by the stockCommentID. so the query has to check if there is some pipes in the string, and if there is some, take the values in the other table, and replace the pipe(s) with them.also, the query has to do this job for all rows of the table StockComment, because the results will be used to fill a comboBox.I'm really lost on this one! Plz help me!

[packrat]

is this for any particular database platform?are the pipe chars mixed in with the comment or do the fields with pipes only contain pipe chars?do you have control over these delimiters, can you decide to change delimiters or is that beyond your control?my only other comment at this time is that the problem as posed would likely be easier to deal with on the code side, the easy db side fix would probably require a stored procedure or function to cope with the string parsing.

[mas_oyama]

<i>is this for any particular database platform?</i>Access... but i'm just trying to make the select work on Query analyser<i>are the pipe chars mixed in with the comment or do the fields with pipes only contain pipe chars?</i>yeah. it's mxed. for example:Minimum | boxes of | uniti have to replace those two pipes with Value1 and Value2<i>do you have control over these delimiters, can you decide to change delimiters or is that beyond your control?</i>boyond my control. i dont have insert, update an drop permissions on that DB. otherwise, i would have changed one of the pipes for a !, and i would have used replace(replace(blah, '|', Value1), '!', Value2)my only other comment at this time is that the problem as posed would likely be easier to deal with on the code side, the easy db side fix would probably require a stored procedure or function to cope with the string parsing.

[packrat]

Query analyzer? is this Access connected to an SQL server (ADP file)? if so do you have permissions to write functions?if not pls elaborate on query analyzer

[packrat]

It ain't pretty but here's your access solution, if you're using jet/access not sql server.

SELECT 	IIF (InStr(1, SC.Comment, '|')>0		, IIF (InStr(InStr(1, SC.Comment, '|')+1,  SC.Comment, '|') > 0			, LEFT(SC.Comment, InStr(1, SC.Comment, '|')-1)	& SD.Value1 	& MID(SC.Comment, InStr(1, SC.Comment, '|')+1, InStrRev(SC.Comment, '|')-(InStr(1, SC.Comment, '|')+1)) 	& SD.Value2 	& Right(SC.Comment, Len(SC.Comment) - InStrRev( SC.Comment, '|'))	        ,    Replace(SC.Comment, '|', SD.Value1)	        )       , SC.Comment    ) AS CommentFROM StockComment SC	INNER JOIN StockDetailComment SD ON SC.StockCommentID = SD.StockCommentID

someone tell me how to keep code indented when posting....

[mas_oyama]

oh crap! thx! i really need to do this in SQL though. but i've got some help from my boss.i'll explain the situation. i'm doint my stage right now, and although we did a lot of SQL at school, we didnt do something like that at all. but i went to see my supervisor(my boss), and he gave me a hand. i had it almost completely done by myself, but something wasnt working. since i was on this thing since 7:30AM (its currently 2:15PM), i wasnt able to see my mistake, wich was really stupid : a damn coma that i misplaced. lol. anyway, so now its almost working. heres the code i had to do:

select Result = Part1 + Value1 + Part2 + Value2 + Part3from tbStockDetailComment dtlCmtright JOIN (SELECT    StockCommentID,    --DelPos1 = CharIndex('|', Comment),    --DelPos2 = CharIndex('|', Comment, CharIndex('|', Comment) + 1),    Part1   = CASE WHEN CharIndex('|', Comment) = 0 THEN Comment ELSE Left(Comment, CharIndex('|', Comment) - 1) END,    Part2   = CASE WHEN CharIndex('|', Comment, CharIndex('|', Comment) + 1) > 0 THEN Substring(Comment, CharIndex('|', Comment) + 1, CharIndex('|', Comment, CharIndex('|', Comment) + 1) - CharIndex('|', Comment) - 1) ELSE '' END            + CASE WHEN (CharIndex('|', Comment) > 0) AND (CharIndex('|', Comment, CharIndex('|', Comment) + 1) = 0) THEN Right(Comment, Len(Comment) - CharIndex('|', Comment)) ELSE '' END,    Part3   = CASE WHEN CharIndex('|', Comment, CharIndex('|', Comment) + 1) > 0 THEN Right(Comment, Len(Comment) - CharIndex('|', Comment, CharIndex('|', Comment) + 1)) ELSE '' ENDFROM dbo.tbStockComment) cmt on dtlCmt.StockCommentID = cmt.StockCommentID

anyway, the only thing that doesnt work is that if there is no Value1 or Value2, they are worth Null. and unlike in Access, if you do 'Hi' + Null, it doesnt result in 'Hi'. it results in Null(and thats really really stupid. anyway. i'll find a solution to that... at worst i'll find it tomorrow since i dont seem to be able to see my smallest mistakes... loloh and... i know its a really small amount of code for a whole day of labor... lol but my SQL notions are pretty far behind me, so i searched for a long time instead of actually coding.

[mas_oyama]

oh and Query analyser is a tool made by micro$oft. basically, it is used to test you queries before you actually implent them. it can also be used to actually make your stored proc. by adding alter function at the beginning of your code.and no, i cant write stored procedures in that specific Database. i can in another db, but not on that one. but when my query will be finished, i'll give the code to my suppervisor, and he'll create it from his login

[packrat]

ISNULL(Value, '')unlike access where ISNULL return boolean, MsSql ISNULL substitutes the second param for null values, that should take care of the null problem.select Result = Part1 + ISNULL(Value1, '') + Part2 + ISNULL(Value2, '') + Part3Since you're on SQL, if you can write a function to do this your final select would be a lot cleaneri.e. udf_SubstitutePipes(Comment, Replacement1, Replacement2)

[mas_oyama]

ISNULL(Value, '')unlike access where ISNULL return boolean, MsSql ISNULL substitutes the second param for null values, that should take care of the null problem.select Result = Part1 + ISNULL(Value1, '') + Part2 + ISNULL(Value2, '') + Part3Since you're on SQL, if you can write a function to do this your final select would be a lot cleaneri.e. udf_SubstitutePipes(Comment, Replacement1, Replacement2)

i dont think i could, since i need to scan the whole table... doing udf_SubstitutePipes(Comment, Replacement1, Replacement2) would do one row. but anyway, once the query will be created, no one will have to go back in it.oh and thx for the isnull() function... i did something else that wasnt really pretty :

select Result = part1 + case when Value1 is not null then Value1 else '' end + part2 + case when Value2 is not null then Value2 else '' end + part3...

lolanyway, thx for the info, it has been really useful

[packrat]

glad to be of help.as far as the function goes, just for the sake of discussion, the function would be called in the select and would be evaluated for each row in the result set.

SELECT  udf_SubstitutePipes(SC.Comment, SD.Value1, SD.Value1) AS CommentFROM StockComment SC    INNER JOIN StockDetailComment SD ON SC.StockCommentID = SD.StockCommentID

if you're happy with it, or just want to get away from it, then by all means call it done.

[mas_oyama]

if you're happy with it, or just want to get away from it, then by all means call it done.

haha yeah... honestly i just want to get away from it... lol i passed a whole day of labor for a mere 12 lines of code... lol i feel pretty stupid right now. lol. i did the 2 projects that were supposed to take me about 10 weeks in only 3 weeks, and that impressed my boss a lot, but then i have to put a whole day in 12 stupid lines of code, which arent difficult after all! anyway. now it works. so it will stay like it is. loloh and again, thx a lot for the help.