Solved

SQL query help required

Posted on 2007-11-20
30
159 Views
Last Modified: 2010-08-05
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_ = ?"
0
Comment
Question by:ckawebcreation
  • 14
  • 13
  • 3
30 Comments
 
LVL 18

Expert Comment

by:Yveau
ID: 20323366
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20323388
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
 

Author Comment

by:ckawebcreation
ID: 20323502
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20323778
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20323791
... or should that have been [group doc].[Holder No] ??? from a different table ?
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20325441
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20325444
.. in fatc its a 'Microsoft VBScript compilation error '800a0409'' which indicates that its your VBScript rather than the SQL.
0
 

Author Comment

by:ckawebcreation
ID: 20345698
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20346305
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
 

Author Comment

by:ckawebcreation
ID: 20346579
sorry yveau,

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

cheers
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20346774
:-) ... 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
 

Author Comment

by:ckawebcreation
ID: 20346852
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
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 20347372
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
 

Author Comment

by:ckawebcreation
ID: 20348884
hi nmcdermaid:

do I do that in sql server?
0
 

Author Comment

by:ckawebcreation
ID: 20350980
any ideas???
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 18

Expert Comment

by:Yveau
ID: 20351640
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
 

Author Comment

by:ckawebcreation
ID: 20352264
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20352429
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
 

Author Comment

by:ckawebcreation
ID: 20353021
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20358405
OK, will start on it in a minute ...

0
 

Author Comment

by:ckawebcreation
ID: 20360034
cheers yveau..
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20360311
... so the complete CASE statement should be rewritten, right ?
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20360362
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
 

Author Comment

by:ckawebcreation
ID: 20364420
cheers Yveau: i am out all day, will check this evening when I get back..
0
 

Author Comment

by:ckawebcreation
ID: 20369009
hi yveau,

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

cheers
0
 
LVL 18

Accepted Solution

by:
Yveau earned 250 total points
ID: 20381236
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
 

Author Comment

by:ckawebcreation
ID: 20391300
hi yveau,

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

cheers
0
 
LVL 18

Expert Comment

by:Yveau
ID: 20405516
... shouldn't this be the substitute for the previous query ?
I'm a bit lost I guess ... Sorry !
0
 

Author Comment

by:ckawebcreation
ID: 20405890
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
 
LVL 18

Expert Comment

by:Yveau
ID: 20405895
You're welcome !
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now