SQL query help required

i have the following query which has worked fine up until now.  What it does is look for a path in the
[Membership Plan] field and then it does the reverse substring thing to append it onto the domain therefore making it a URL link rather than a folder link e.g. c:\test\test.pdf.

As I say it works fine BUT I have been informed that people are getting the NoData.pdf displayed because multiple groups from the same organisation do not have a [Membership Plan] link in the table, what they have is an entry into the [group Doc.Holder No] which is the No_ of the parent record.  This is how it works in the internal system.  So what I need adding to the query is that if there is a [group Doc.Holder No] entry in the record use that value as the No_ for the main record and use that path!! PHEW!

So to recap a bit mkore simplistic you may have 'Company A' with a [Membership Plan] field with folders\companyA_info.pdf and then all related comanies might be called CompanyA_logistics, ComapnyA_Communications etc.. but will have no entry in [Membership Plan]  but will have an entry in [group Doc.Holder No] which is the primary key for the mother company e.g. 'Company A' so if that is the case, just use 'Company A' value for [Membership Plan]  

does this make sense????

SELECT No_  , Case When CHARINDEX('\', [Membership Plan]) > 0 Then REVERSE(SUBSTRING(REVERSE([Membership Plan]), 1, CHARINDEX('\', REVERSE([Membership Plan]))-1))              Else 'NoData.pdf'     End AS [Membership Plan] FROM dbo.[Healthshield$Customer] WHERE No_ = ?"
ckawebcreationAsked:
Who is Participating?
 
YveauConnect With a Mentor Commented:
It should always return a number (300630 or 300652 I suppose) ... let's take it from there.
Does it return the correct numbers ?

What is the next step ?
Include it in this function: REVERSE(SUBSTRING(REVERSE([MP]), 1, CHARINDEX('\', REVERSE([MP]))-1)) ?
(See code snippet)

Hope this helps ...
SELECT  No_
,       CASE
        WHEN    PATINDEX('%[A-Z]',[Membership Plan]) > 0
        THEN    REVERSE(SUBSTRING(REVERSE([Group Doc_Holder No_]), 1, CHARINDEX('\', REVERSE([Group Doc_Holder No_]))-1))
        ELSE    [Membership Plan]
        END     AS [Membership Plan]
FROM    dbo.[Healthshield$Customer]
 
 
--

Open in new window

0
 
YveauCommented:
I think you are looking for the IsNull() function:
    IsNull([Membership Plan],[group Doc.Holder No])

This will return the value of [Membership Plan], but if that value turns out to be missing (is NULL), than it will return the value of [group Doc.Holder No].

So you would end up with what's in the Code Snippet.

Hope this helps ...



SELECT No_  
,      Case 
           When CHARINDEX('\', IsNull([Membership Plan],[group Doc.Holder No])) > 0 
               Then REVERSE(SUBSTRING(REVERSE([Membership Plan]), 1, CHARINDEX('\', REVERSE([Membership Plan]))-1))
           Else 'NoData.pdf'
       End AS [Membership Plan] 
FROM dbo.[Healthshield$Customer] 
WHERE No_ = ?"

Open in new window

0
 
YveauCommented:
Oops, should have been:

SELECT No_  
,      Case 
           When CHARINDEX('\', IsNull([Membership Plan],[group Doc.Holder No])) > 0 
               Then REVERSE(SUBSTRING(REVERSE(IsNull([Membership Plan],[group Doc.Holder No])), 1, CHARINDEX('\', REVERSE(IsNull([Membership Plan],[group Doc.Holder No])))-1))              
               Else 'NoData.pdf'
       End AS IsNull([Membership Plan],[group Doc.Holder No]) 
FROM dbo.[Healthshield$Customer] 
WHERE No_ = ?"

Open in new window

0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ckawebcreationAuthor Commented:
hi Yveau:

i am getting the following error??


Microsoft VBScript compilation error '800a0409'

Unterminated string constant

/testarea/membership1.asp, line 137

pdf_cmd.CommandText = "SELECT No_ , Case
----------------------------------------^
0
 
YveauCommented:
Hi,

I used your code, only replaced "[Membership Plan]" with "IsNull([Membership Plan],[group Doc.Holder No])"
... or at least I tried to. Maybe a cut & paste error ?
0
 
YveauCommented:
... or should that have been [group doc].[Holder No] ??? from a different table ?
0
 
nmcdermaidCommented:
You are best off validating the SQL in query analyzer before you start trying to construct VBScript strings. That way you can validate that the SQL works OK beforehand.
0
 
nmcdermaidCommented:
.. in fatc its a 'Microsoft VBScript compilation error '800a0409'' which indicates that its your VBScript rather than the SQL.
0
 
ckawebcreationAuthor Commented:
i have put all the string on one line but it is still giving the followeing SQL server error??

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near 'Membership Plan'.

/testarea/membership1.asp, line 142
0
 
YveauCommented:
You didn't answer my last question:
... or should that have been [group doc].[Holder No] ???
Is it a field called [Holder No] from a different table named [group doc], or field [group doc Holder No] from the [Healthshield$Customer] table ?
0
 
ckawebcreationAuthor Commented:
sorry yveau,

it is a field called [group_doc Holder No_] in the  [Healthshield$Customer] table

cheers
0
 
YveauCommented:
:-) ... been too eager with search and replace.
note the change after the 'AS ...'

Tested the code, now it runs from within SQL Server Managment Studio.

Hope this helps ...

SELECT No_  
,      Case 
           When CHARINDEX('\', IsNull([Membership Plan],[group Doc.Holder No])) > 0 
               Then REVERSE(SUBSTRING(REVERSE(IsNull([Membership Plan],[group Doc.Holder No])), 1, CHARINDEX('\', REVERSE(IsNull([Membership Plan],[group Doc.Holder No])))-1))              
               Else 'NoData.pdf'
       End AS [Membership Plan]
FROM dbo.[Healthshield$Customer] 
WHERE No_ = ?"

Open in new window

0
 
ckawebcreationAuthor Commented:
hi yveau,

i have implemented it but still getting an error, i have changed the column name you had in the code to the correct name as it is in the sql database - Group Doc_Holder No_ i  changed all 4 instances in the code and i am 100% sure they are spelt correctly, so it cannot be the name can it??

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'Group Doc_Holder No_'.
/membership1.asp, line 143
0
 
nmcdermaidCommented:
Why don't you print the SQL or capture it in Profiler and verify that it is what you expect.

If it IS what you expect, copy it and run it in Query Analyzer.
0
 
ckawebcreationAuthor Commented:
hi nmcdermaid:

do I do that in sql server?
0
 
ckawebcreationAuthor Commented:
any ideas???
0
 
YveauCommented:
in SSMS, the client tool for SQL Server.
Open a connection to the server, right click on the server name and select 'new query'.
Than cut and paste the SQL code and run it (CTRL+E) ...

omit the 'where NO_ = ?' ... or at least replace the ? with a value that exists in the NO_ column ...
Is 'NO_' a column name in the Healthshield$Customer table ? ... that might be the problem ...

Hope this helps ...
0
 
ckawebcreationAuthor Commented:
hi Yveau:

the query worls fine now but it brings back the original record.  What I need it to do is see if the field [Group Doc_ Holder No_] has a number in it and use that as the original number in the query???  does that make sense?

What is happening with the test data is this, the No_ value is 300630A and the [Group Doc_ Holder No_] is 300630, so what I need it to do is not use the [memebership_plan] path from 300630a but use the [memebership_plan] path from record 300630.  This is because there could be 50 records which point back to 300630 through the  [Group Doc_ Holder No_]  as the pdf link.  if the page uses the original 300630a there is no pdf path because it is supposed to be looking at the  [Group Doc_ Holder No_] and go to that record for the path... hope this makes sense...  the sql server database comes from a huge internal system and this is one of many little problems I have to try and work around!!!
0
 
YveauCommented:
is there a pattern in the [Group Doc_Holder No_] ?
Like 111111A as you mentioned ? In that case we can work something out ... if there is not a pattern in it, it will be a real challenge !
0
 
ckawebcreationAuthor Commented:
Hi Yveau,

yes there is a pattern, the pattern is that the No_of someone with  a  [Group Doc_Holder No_] always has a letter on the end, here are some examples  

No_ 300630A has no pdf link in the [membership plan]
 but has a group doc holder no of 300630

No_  300652A has no pdf link in the [membership plan]
but has a group doc holder no of 300652

No_  300652B has no pdf link in the [membership plan]
but has a group doc holder no of 300652

No_  300652B has no pdf link in the [membership plan]
but has a group doc holder no of 300652

and then the records with the No_ 300630 and No_  300652 have the pdf links in the [membership plan]

does that make it any easier???
0
 
YveauCommented:
OK, will start on it in a minute ...

0
 
ckawebcreationAuthor Commented:
cheers yveau..
0
 
YveauCommented:
... so the complete CASE statement should be rewritten, right ?
0
 
YveauCommented:
So this will return either the number part of the [Group Doc_Holder No_] or the [Membership Plan].

Hope this helps ...
SELECT  No_
,       CASE
        WHEN    PATINDEX('%[A-Z]',[Membership Plan]) > 0
        THEN    SUBSTRING([Group Doc_Holder No_],1,PATINDEX('%[A-Z]',[Membership Plan]))
        ELSE    [Membership Plan]
        END     AS [Membership Plan]
FROM    dbo.[Healthshield$Customer]
 
--

Open in new window

0
 
ckawebcreationAuthor Commented:
cheers Yveau: i am out all day, will check this evening when I get back..
0
 
ckawebcreationAuthor Commented:
hi yveau,

it doesn't give any errors but it is not appending the [membership plan] filename on the url like before??

cheers
0
 
ckawebcreationAuthor Commented:
hi yveau,

do i need to add this to the exisitng query somewhere?

cheers
0
 
YveauCommented:
... shouldn't this be the substitute for the previous query ?
I'm a bit lost I guess ... Sorry !
0
 
ckawebcreationAuthor Commented:
hi yveau,

i have used part of your query and a redirect to solve the problem!!!  it works fine now cheers for your help..
0
 
YveauCommented:
You're welcome !
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.