Author |
Topic |
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-20 : 06:36:59
|
I hope I can explain this in a way that makes sense. I have a query on a peson application that poduces a record. Within the application there is a question that allows the applicant to choose several answers: -Applic_no Question_ID Question Answer12345 40 Medical 2,5,12There is a lookup table that tells me what each answer is: -ID Desc1 Stairlift2 Wheelchair3 Walk-in shower5 Ramp12 WC DownstairsHowever, how do I query the lookup table if my answer is 2,5,12? I need to somehow split it or query the string array to pick out the values seperated by commas.Any help would be apprecited. |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 07:58:25
|
you need to use something like belowSELECT m.Applic_no,m.Question_ID,m.Question,l.Desc AS AnswerFROM MainTable mINNER JOIN LookupTable lON ',' + m.Answer + ',' LIKE '%,' + CAST(ID AS varchar(5)) + ',%' ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-20 : 08:35:02
|
Thanks.What is the query doing exacly? Just so I can undersand it. |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-20 : 08:41:20
|
The LIKE clause returns true if the pattern on the right side exists in the string on the left side. So in the query ',' + m.Answer + ',' LIKE '%,' + CAST(ID AS varchar(5)) + ',%', in your example, the left side would be ,2,5,12,. On the right side, for row 1 of the lookup table, it would be '%,1,%' - which does not match the string on the left side. For the second row, the right side of the like clause would be '%,2,%' which does match the left side. And so on. |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-20 : 10:25:59
|
Thanks James I really appreciated it. I have it working up until the last part. My descriptions above where quick desciptions but here is the real query. Many ThanksNOTE:cir.seq-no = question ID(Medical)cir."chc-list" = Answer (2,4,12)pub.cirdetchc = lookup table cirdetchc.num = IDcirdetchc.dsc = descSELECT cir."applic-apno",cir."seq-no",cir."cde",cirdetchc.dscFROM pub.cir,pub.cirdetchcINNER JOIN pub.cirdetchcON ',' + cir."chc-list" + ',' LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%' |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-11-20 : 10:40:26
|
Do you have those parentheses and the word "Answer" around the on chc-list? If so you need to remove those. For example like this:....INNER JOIN pub.cirdetchcON ',' + REPLACE(REPLACE(cir."chc-list",')',''),'Answer (','') + ',' LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%' |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-20 : 12:56:48
|
how are the tables pub.cir,pub.cirdetchc related?the way you've written it, it will cause a cross join between them.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-21 : 04:20:50
|
pub.cir: -cde,seq-no,applic-apnopub.cirdetchc: -cde,dsc.So the link would have to be cde? |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-21 : 04:52:14
|
and sorry no that was just me. the chc-list value is 2,4,12Without the answer and brackets. |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-21 : 05:00:29
|
Syntax error in SQL statement at or about "?chc-list? + ',' LIKE '%,' + CAST(cirdet" (10713)State: S1000 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-21 : 06:54:21
|
quote: Originally posted by jp_fondu Syntax error in SQL statement at or about "?chc-list? + ',' LIKE '%,' + CAST(cirdet" (10713)State: S1000
where did '?' etc come from? are you using sql server?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-21 : 07:20:28
|
Im using Business Objects butting running a free hand SQL query through it. I'm not sure where its getting the ? from. I ran this: -SELECT cir."applic-apno",cir."seq-no",cir."cde",cirdetchc.dscFROM pub.cir,pub.cirdetchcINNER JOIN pub.cirdetchcON ',' + cir.”chc-list” + ',' LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%'Sorry this is dragging out :/ |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-11-21 : 07:43:00
|
first try it in sql management studio and see what happens------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-11-21 : 08:04:42
|
OK mate I'll give this a try and let you know.Thanks. |
|
|
jp_fondu
Starting Member
10 Posts |
Posted - 2013-12-06 : 04:51:26
|
Thanks mate finally got it working. I really appreciate the help. This was the end query: -SELECT cir.[applic-apno],cir.[seq-no],cir.[cde],cirdetchc.dsc,cirdetchc.num,CAST(cirdetchc.num AS varchar(5))FROM cirINNER JOIN cirdetchcON (',' + cir. [chc-list] + ',' LIKE '%,' + CAST(cirdetchc.num AS varchar(5)) + ',%')AND cir.[seq-no] = cirdetchc.[seq-no] |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-12-06 : 05:10:07
|
coolglad that you got it sorted------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|