can someone tell me why this sql statement doesnt work?
SQL = "SELECT (Count(department_id) as 'totals' FROM nonconformance WHERE department_id = '7'),(Count(department_id) as 'totals2' FROM nonconformance WHERE department_id = '1') FROM nonconformance"
How do I fix it?
Thanks
i am new at T-SQL and am trying to delete entries in a column that are not integers. i have the following code. why does it not work.
T-SQL
select invoicenumber from [control register] where convert(int, invoicenumber) like '%'
if @@error > 0
begin
delete from [control register] where invoicenumber = invoicenumber
end
go
gives me the error in Query analyser:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the nvarchar value '10-3-05' to a column of data type int.
@@error is supposed to pick up the error. why is it not.
please could you reply urgently at chris@captivedesign.co.za
thanks in advance
Chris Morton
Can someone tell me why this thing in the end return Null?
declare @uz_id uniqueidentifier
set @uz_id=null
set @uz_id=
case @Uz_ID
when null then '00000000-0000-0000-0000-000000000000'
else @Uz_ID
end
select @uz_id
...it should return '00000000-0000-0000-0000-000000000000' in my opintion
P.S. When I say return i mean the value that is shown after 'select @uz_id' is executed:)
This is an insert statement i'm trying to run. I want it to only insert this recird if the corresponding EMPLOY_REF exists in the EMPLOYEE table. Heres my statement:
INSERT INTO SALHISTY(EMPLOY_REF, SALARY, SAL_REASON, SAL_DATE)
VALUES ('8971','175000.0000','ANNRV','2007-04-01 00:00:00.000')
WHERE '8971' IN (SELECT EMPLOY_REF FROM EMPLOYEE)
This is the error message i'm getting.
Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'WHERE'.
Any help would be greatly appreciated. Thanks
select distinct
a.Patients,
b.Patients,
a.pct,
b.pct
from
(
select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] <= 18
group by [pct of res]
) as a right outer join
(select distinct[pct of res] from testing) as c on a.pct=c.[pct of res]and a.pct <> 'null' --is not null
(select count(*) as Patients, [pct of res] as pct
from testing
where [18 week wait] >18
group by [pct of res]
) as a left outer join as b on c.[pct of res]=b.pct
I have the following in my commandtext but it doesnt seem to replace the LanguageColumnName variable: Dim cmd As New SqlCommand("SELECT '+@LanguageColumnName+' FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _ "WHERE b.UserCode=@UserCode", MyConnection) cmd.Parameters.Add(New SqlParameter("@UserCode", UserCode)) cmd.Parameters.Add(New SqlParameter("@LanguageColumnName", LanguageColumnName))I have tried '+@LanguageColumnName+' and also just @LanguageColumnName but this variable isnt replaced for some reason.The value of LanguageColumnName is "de"...the funny thing is that when I just type my command like the following it DOES work..:SELECT de FROM tblSports a INNER JOIN tblUsersAndSports b ON a.SportID=b.SportID " & _ "WHERE b.UserCode=@UserCodeWhat am I doing wrong?
View 1 Replies View RelatedI have my db in a pocket pc wm5.0, I just want to make a simple query
select * from table
where pk = '1'
but this doesnt work, if you tried any other field else than the primary key
it works... WHY???
would it be a problem with the sdf file? help please!!!
Hi,
I have an issue. When I am performing a Merge Join using two fields of type
DT_WSTR on both sides of the Join, the Dataviewer after the Join only shows the left side 2 key
fields having values populated but both fields from the right hand side as being NULL. The Dataviewers I put in before the join show both sides of the join fields being populated ( 2 sides x 2 fields ). If I remove one of the fields from the join it shows both fields from the left side and the right side of the join being populated . I am trying to knock out duplicate values from the insert using 'genuine' Nulls from the right hand side so I need both parts of the 'Key'. I have checked the adnvanced tab that ignores Case etc but has had no effect.
I am using the technique from here : http://www.sqlis.com/311.aspx
Is this an SSIS bug ??? It is driving me up the wall.
Help would be much appreciated please.
Thanks
Jon
HI All,I have started sqlserver in single user mode and changed filename in sysaltfiles for tempdb too point to new location. While starting sqlserver in normal it points to the old path and doesnt get updated with the new path.1.)is there any systable still to be altered???2.) i have even tried alter database, doesnt work.3.)The master files have been taken from server1 (where tempdb points to d:data) to server2 ( where i need to point tempdb to point E:mssqldata). i can detach and attach msdb and model successfully, but in the case of tempdb, i cant either alter sysaltfiles or detach and attach tempdb to new path., too tired in trying all the possiblities... Is there any possibility to update tempdb to point to new path???
View 5 Replies View Relatedhi there,
i've made a report containing a subreport. the subreport is in a table. the table headers should repeat. but if the subreport expands more than one site, the table header doesnt repeat.
okay. i could place the table header in report header, but in report header i cant use fields from a dateset.
thanks for any idea, tobi
hi, i hope this is the correct forum?i have developed a small asp.net 2.0 web site with vwd using sqlExpress which appears to just bean data file and a log file in my app_data directory. there are two databases, the users one that is created by the login manager and my own that i created to store data. this all works fine on my laptop. i have an xp-pro machine setup with IIS and the frontpage web extentions, i have also installed the .net framework v2.0 on it and done all the windows updates. i have given app_data read/write access for the aspnet and network service users.i used the copy site feature of vwd to install on the xp box and although the site seems to workwhen it needs to bind a gridview to my sqldatasource it gives an error Cannot open user default database. login failed.Loginfailed for user 'rlxpaspnet'Any ideas? i have googled and tried all sorts, but to no avail, so i throw my self on your mercy!Cheers,russ
View 5 Replies View RelatedHello all!
I've a problem with my database. Till yesterday the option for Auto Grow of Database (10 %) was working very fine, but now it seems to be some problems with it. Finally I had to specify a restricted size for the database and then it again startd to give me some space in the database to write in. Ideally it should have worked automatically, isnt it ???
There is no problem with the space on the drive, I still have some 76 gb of free space there ...
Thanks in advance ...
Anjä
Hello, I created a new Rs server, but when I try to deploy from visual studio, it asks me for username and password multiple times and never deploys.
I am an administrator on the RS server, even on the report folders.
I can access via IE to the report manager.
Hi ,
In the below procedure in emp table name column is not null.
so when i try to update null value it should rollback previous transactions and should not commit.
but it is updating the previous queries and raising error on 3rd query. not rolliing back. I need to rollback previous queries.
the xactstate i get here is 1.
Please help me with this.
ALTER PROCEDURE [dbo].[tran]
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
BEGIN TRANSACTION
declare @name varchar(50)
set @name = null
update dept set dname='ddd' where id=10
update emp set name='eee' where id=354
update emp set name=@name where id=354
COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF (XACT_STATE())=-1 ROLLBACK TRANSACTION
END CATCH
END
venp---
Hi ,
Hello, I tried this and it doesnt work, I just started up the service with -T flag on default instance but I havent been able to start it with -T flag 1400 on named instances.
How can I Know that the directory MSSQL.4 is the MIRROR instance??
C:Archivos de programaMicrosoft SQL ServerMSSQL.4MSSQLBinn>sqlservr.exe -T
1400 MIRROR
I tried LUCHOMIRROR, MSSQLSERVER$MIRROR
and It doesnt work
Thanks
Another question I have, in the documentation it says that mirroring is only for evaluation purposes and not for production environments, what does it suppose to mean? MSFT didnt test the mirroring feature enough? If its not suppoerted why its included..?
Microsoft support policies do not apply to the database mirroring feature in SQL Server 2005. Database mirroring is currently disabled by default, but may be enabled for evaluation purposes only by using trace flag 1400 as a startup parameter. (For more information about trace flags, see Trace Flags (Transact-SQL).) Database mirroring should not be used in production environments, and Microsoft support services will not support databases or applications that use database mirroring. Database mirroring documentation is included in SQL Server 2005 for evaluation purposes only, and the Documentation Policy for SQL Server 2005 Support and Upgrade does not apply to the database mirroring documentation.
Hello everybody,
have following problem:
I need info from 2 Tables. from the Table 2 I just need 1 column. When i ask for this column the output I get is data repeating themselve many times.
Distinct, should give me unique data, but is doesnt....
the code:
SELECT DISTINCT FSenddate, FSupplyIDName, FSupplyerNumber,FBillNo,FSourceBillNo,FItemName,FItemModel,
FAuxQty,FAuxTaxPrice,FHeadSelfP0237
FROM vwICBill_26
WHERE FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
This code just works in Table1 (vwICBill_26)
but with table 2 (vwICBill_1)
SELECT DISTINCT vwICBill_26.FSenddate,vwICBill_26.FSupplyIDName,
vwICBill_26.FSupplyerNumber,vwICBill_26.FBillNo,
vwICBill_26.FSourceBillNo,vwICBill_26.FItemName,
vwICBill_26.FItemModel,vwICBill_26.FAuxQty,
vwICBill_26.FAuxTaxPrice,vwICBill_26.FHeadSelfP0237,
vwICBill_1.FDate,vwICBill_1.FContractBillNo
FROM vwICBill_26,vwICBill_1
WHERE vwICBill_26.FSenddate BETWEEN DATEADD(dd,-14,GETDATE()) AND GETDATE()
AND vwICBill_1.FContractBillNo=vwICBill_26.FSourceBillNo
The last sentence is the problem
I want that it shows me the data that is not equal.
As soon as I implement the not equal it shows me the massive repeating data.
I mean even without the last sentence I get this data output.
All together, I want a clear database output without data repeating.
Any ideas how it may work without DISTINCT?
I think this problem is a typical amateure problem, but I would apreciate help!
Hello, I am trying to make this.
CREATE PROCEDURE [dbo].[P_SEL_ALLPERSONAS]
@nmpersona int,
@sortorder varchar(20)
AS
BEGIN
select nmpersona, dsprimernombre, dssegundonombre,
dsprimerapellido, dssegundoapellido
from personas
order by @sortorder
END
But I got this error. Please help
Msg 1008, Level 16, State 1, Procedure P_SEL_ALLPERSONAS, Line 13
The SELECT item identified by the ORDER BY number 1 contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name.
Hi,
The distributed query seems to work on the management studio of the server where I have linked the other server to but not accross the network on other management studio with the same impersonated logins. The error I get is.
OLE DB provider "SQLNCLI" for linked server "usbo-sql01" returned message "Communication link failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
Msg 18452, Level 14, State 1, Line 0
Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection.
I am having the same problems as those in another post. SSIS package works fine when executed in BIDS and through execute package utility but it doesnt work when executed as a step in a job.
The other problem is that the logging also doesnt work when i try executing it as a job. So I have no clue about what to do without knowing what error it is. When I run the job it simply says the step has failed.
I have tried most of the solutions posted in other websites most of them to do with using proxies with credentials but havent hit a solution. I would love to get any input on what to do.
Thanks
Hello all,
I need help concerning a differential backup on a MSDB database.
I received this message when I tried to backup it
Log from Windows
Event Type: Error
Event Source: SQLVDI
Event Category: None
Event ID: 1
Date: 8/1/2007
Time: 2:03:37 PM
User: N/A
Computer: XXX
Description:
SQLVDI: Loc=SignalAbort. Desc=Client initiates abort. ErrorCode=(0). Process=9028. Thread=5928. Client. Instance=. VD=GlobalData Protector_(DEFAULT)_msdb_14_00_21.
Log from DataProtector
Normal] From: XXX "(DEFAULT)" Time: XXX
SQL statement:
BACKUP DATABASE [msdb] TO
VIRTUAL_DEVICE = "Data Protector_(DEFAULT)_msdb_06_00_14"
WITH NAME = 'Data Protector: 2007/08/01 0064', DIFFERENTIAL, BLOCKSIZE = 4096, MAXTRANSFERSIZE = 65536;
[Warning] From: XXX "(DEFAULT)" Time: XXX
Error has occurred while executing a SQL statement.
Error message: '<Microsoft SQL-DMO (ODBC SQLState: 42000):bdb>
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot perform a differential backup for database "msdb", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.'
Ive tried to do a full followed straight after by a diff but doent help.
Thank you for your help
in both vs2008 I added a ReportViewer control to my WinForm, intending to render an RS 2005 report remotely. In the design pane (Report Viewer Tasks Popup) I specify <server report>,
URL http://server name/reportserver$instance name
path /folder/report name
and everything is fine.
When I set these values in c# as
reportViewer1.ProcessingMode = ProcessingMode.Remote;
reportViewer1.ServerReport.ReportServerUrl = new Uri("http://server name/reportserver$instance name");
reportViewer1.ServerReport.ReportPath = "/folder/report name";
I started getting a msg saying "the source of the report definition has not been specified". If I hit refresh, the report renders. Before I started playing with the designer setting, and only had the c# stuff specified, I wouldnt even get the message, the control would appear with all RS buttons (eg page forward arrow etc) disabled.
Does anybody know why the message is appearing and how to get c#'s settings to work immediately like designer settings?
Hi,
I just upgraded to SQL server 2005 and some of my Openrowset to Oracle doesnt work. I found out that the issue occur with field with structure Numeric.
The issue is not solve even when I try to cast the field to varchar. sometimes it succeeded and sometimes I get error 7320 & 7321.
I'm using Oracle connector from SQL to Oracle.
I've 64 bit SQL Server.
Attach the code, the Numeric field is SCORE.
select *
FROM
OPENROWSET('ORAOLEDB.ORACLE',aaaa;bbbb;cccc,
'SELECT AUDIT_OPRID, AUDIT_STAMP, AUDIT_ACTN,EMPLID, ACCOMPLISHMENT, MAJOR_CODE,
CAST(SCORE AS Varchar(250))
FROM SYSADM.PS_RTA_ACCOMPLISHM WHERE 1=2')
Anyone can advice on resolution?
Thanks,
Assaf
While installing SQL Server 2005 Enterprise Edition on Windows Vista Ultimate
the setup alerts me "there are known compatibility problems, please
install SP2 after this". So ok, all went nice and installed without error.
IIS 7 installed fully, so all green in setup display.
(But after installation i do not see any service which begins with SQL***...)
At the end, the setup wanted to start the administration Tool.
Ok, but my name was not listed, so i could not add me to the permitted users.
I can not use any tool, because i have no rights.
Some Ideas?
Microsoft SQL Server Management Studio 9.00.3042.00
Microsoft Analysis Services-Clienttools 2005.090.3042.00
Microsoft Data Access Components (MDAC) 6.0.6000.16386 (vista_rtm.061101-2205)
Microsoft MSXML 3.0 4.0 5.0 6.0
Microsoft Internet Explorer 7.0.6000.16546
Microsoft .NET Framework 2.0.50727.312
Betriebssystem 6.0.6000
thanks Frank
I set up the "Send Email Task" succesfully with "SMTP Connection to myExchangeSERVER" using "Windows Authentication"
However, as we all know - you can't have html format for the Send Mail Task. BUT this piece of code straight from MSDN doesnt work for me - each time it pops up this "Mail Sent Succesfully" - but I receive NO freaking EMAILs!!! Am I missing something or is it another one of those Microsoft "gotchas" ?
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports System.Net.Mail
Public Class ScriptMain
Public Sub Main()
Dim htmlMessageTo As String = "me.here@mydomain.com"
Dim htmlMessageFrom As String = "SSIS@mydomain.com"
Dim htmlMessageSubject As String = "SSIS Success - My Package"
Dim htmlMessageBody As String = _
Dts.Variables("User::HTMLtemplateText").Value.ToString
Dim smtpServer As String = "myExchangeSERVER"
SendMailMessage( _
htmlMessageTo, htmlMessageFrom, _
htmlMessageSubject, htmlMessageBody, _
True, smtpServer)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub SendMailMessage( _
ByVal SendTo As String, ByVal From As String, _
ByVal Subject As String, ByVal Body As String, _
ByVal IsBodyHtml As Boolean, ByVal Server As String)
Dim htmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
htmlMessage = New MailMessage( SendTo, From, Subject, Body)
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient = New SmtpClient(Server)
Dim myCred As New System.Net.CredentialCache()
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
MsgBox("Mail sent")
End Sub
I am currently having this problem with gridview and detailview. When I drag either onto the page and set my select statement to pick from one table and then update that data through the gridview (lets say), the update works perfectly. My problem is that the table I am pulling data from is mainly foreign keys. So in order to hide the number values of the foreign keys, I select the string value columns from the tables that contain the primary keys. I then use INNER JOIN in my SELECT so that I only get the data that pertains to the user I am looking to list and edit. I run the "test query" and everything I need shows up as I want it. I then go back to the gridview and change the fields which are foreign keys to templates. When I edit the templates I bind the field that contains the string value of the given foreign key to the template. This works great, because now the user will see string representation instead of the ID numbers that coinside with the string value. So I run my webpage and everything show up as I want it to, all the data is correct and I get no errors. I then click edit (as I have checked the "enable editing" box) and the gridview changes to edit mode. I make my changes and then select "update." When the page refreshes, and the gridview returns, the data is not updated and the original data is shown. I am sorry for so much typing, but I want to be as clear as possible with what I am doing. The only thing I can see being the issue is that when I setup my SELECT and FROM to contain fields from multiple tables, the UPDATE then does not work. When I remove all of my JOIN's and go back to foreign keys and one table the update works again. Below is what I have for my SQL statements:------------------------------------------------------------------------------------------------------------------------------------- SELECT:SELECT People.FirstName, People.LastName, People.FullName, People.PropertyID, People.InviteTypeID, People.RSVP, People.Wheelchair, Property.[House/Day Hab], InviteType.InviteTypeName FROM (InviteType INNER JOIN (Property INNER JOIN People ON Property.PropertyID = People.PropertyID) ON InviteType.InviteTypeID = People.InviteTypeID) WHERE (People.PersonID = ?)UPDATE:UPDATE [People] SET [FirstName] = ?, [LastName] = ?, [FullName] = ?, [PropertyID] = ?, [InviteTypeID] = ?, [RSVP] = ?, [Wheelchair] = ? WHERE [PersonID] = ? ---------------------------------------------------------------------------------------------------------------------------------------The only fields I want to update are in [People]. My WHERE is based on a control that I use to select a person from a drop down list. If I run the test query for the update while setting up my data source the query will update the record in the database. It is when I try to make the update from the gridview that the data is not changed. If anything is not clear please let me know and I will clarify as much as I can. This is my first project using ASP and working with databases so I am completely learning as I go. I took some database courses in college but I have never interacted with them with a web based front end. Any help will be greatly appreciated.Thank you in advance for any time, help, and/or advice you can give.Brian
View 5 Replies View RelatedHello,
This query should return results, minus any rows that have a UserId in a Filter table (which is just two columns, one the userId and the other a filteredUserId that the user has chosen to block)
alter procedure sp_wm_GetAds
@userid int
as
select
a.*,
dbo.GetAge(a.bday, GETDATE()) age
from
wm_user a
where
hasimage=1 and
a.userid not in
(select userid from wm_filter where userid=@userid and filteredUserId=a.userid)
order by nickname
VB.NET hitting MySQL Express using a sqldataadapter
Here is my Sql statement
SELECT FormName, FieldSeq, FieldTitle, FieldType, FieldLength, DecimalPlaces, CodeList, CodeAdd, CodeMask, TableName, AlternateTableName, FieldName,
FieldRequired, CodeTableName, CanEdit, ToolTip, ImportantColor
FROM FormFieldInfo
WHERE (FormName = 'frmPolicy') AND (FieldSeq < 1000) AND (FieldName <> 'Split1') AND (FieldName <> 'Split2') AND (FieldName <> 'Split3') AND
(FieldName <> 'Split4')
ORDER BY FieldSeq
This works great it gets the rows that i want except for there are 4 rows in which the FieldName value is Null and these are also being excluded. Can someone tell me why?
I have tried multiple variations on this select statement and either it doesn't exclude these rows or it excludes these and the nulls.
I have the sql statement given below. I want to sort by agency name, except, I want the agency a person belongs to at the top. The text "DHS" is stuffed through code. AgAbbreviation is varchar(4). AgName is varchar(60). This never works. The row containing "DHS" is never at the top. Please help. Thanks
SELECT AgAbbreviation, AgName
FROM Agency
ORDER BY CASE WHEN RTRIM(AgAbbreviation) = ' DHS' THEN 0 ELSE 1 END, AgName
i am having problem running a simple delete statement against a table. it just hangs is there anything i should look at? the table has 4 primary keys and the index makes up of the 4 keys and ideas?
i viewed the delete statement with the execusion plan and this is what i saw.
delete -> index delete/delete -> sorting the input -> table delete/delete -> Top -> Index scan.
I’m using VB.NET to extract data from an Access database, simple enough. However, I type in the following line:
Sql = "SELECT * FROM [IPAddressList] WHERE [StartIPNo]>=" & IP_Address And "[EndIPNo]<=" & IP_Address
It doesn’t work.
If I type in:
Sql = "SELECT * FROM [IPAddressList] WHERE [StartIPNo]>=" & IP_Address
The above sql line works.
I now realize SQL commands is very sensitive.
FIELD NAMES:
quote:StartIP |EndIP|StartIPNo|EndIPNo|CountryCode
5.0.0.0 |5.163.66.79|83886080|94585423|ZA
5.163.66.80|5.163.66.95|94585424|94585439|SE
5.163.66.96|5.255.255.255|94585440|100663295|ZA
If the user types in a value, I want it to check the values between the StartIPNo and EndIPNo and return the CountryCode.
Any help would be appreciated
Could someone tell where I can find out if it's true that duringUPDFATE SQL Serve deletes data from table, and then inserts new one.Thanks-A
View 4 Replies View RelatedHi:
the following is my TSQL command of a job(after passing databaseName, I don't want multiple steps, just with 1 step)
--===================================
BACKUP DATABASE ABC To Backup_ABC_1 with init, name = 'Backup of 1(M, W, F ) Job_ABC_1Mon' go backup log ABC with truncate_only go print 'shrinkdatabase begin-- ' dbcc checkdb(ABC) go dbcc shrinkdatabase(ABC, 10)
Issue: with 3 'go' inside the whole line, execution failed. after replace 'go' with 'begin' and 'end' for each sql statemet, the execution works. Someone could explain why 'go' as a batch does not work in this situation?
also, is this a good practice to
1. truncate_only
2. dbcc checkdb
3. shrinkdatabase
after a daily backup?
thanks
David
If any of you can tell me how the following SQL SELECT statement works , I will be forever grateful! What I mean is, if anyone has actually seen an SQL statement like this and knows what it does, I would be eternally grateful, because I have been writing SQL for over 10 years, and not only do I have no clue how this monstrosity works (and I assure you that it does work), I have never seen anything quite like it. The primary purpose of the code is to sort the chapel messages (mp3s) for my institutions website, but it moonlights for NASA as any code they deem necessary to perform the most complicated computerized tasks known to mankind.
email me if you need clarification...
SELECT DA_Resource.Title, DA_Resource.ResourceID, DA_Resource.ShortBlurb,
(SELECT TOP 1
RIGHT('000' + CAST(DA_ScriptureReferenceBook.DisplaySeq AS varchar), 3) +
case
when PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 3) IS NOT NULL then
RIGHT('000' + PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 3),3)
when PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 2) IS NOT NULL then
RIGHT('000' + PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 2),3)
when PARSENAME(REPLACE(DA_ScriptureReference.location, '-', '.'),2) IS NOT NULL then
RIGHT('000' + PARSENAME(REPLACE(DA_ScriptureReference.location, '-', '.'), 2), 3)
when CHARINDEX('&', DA_ScriptureReference.location) > 0 then
RIGHT('000' + RTRIM(LTRIM(LEFT(DA_ScriptureReference.location, CHARINDEX('&', DA_ScriptureReference.location)-1))), 3)
else
RIGHT('000' + DA_ScriptureReference.location, 3)
end
+
case
when PARSENAME( REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 2), ' ', ''), '-', '.') , 2) IS NOT NULL then
RIGHT('000' + PARSENAME( REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 2), ' ', ''), '-', '.') , 2), 3)
when PARSENAME( REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 1), ' ', ''), '-', '.') , 2) IS NOT NULL AND
CHARINDEX(':', DA_ScriptureReference.location) > 0 then
RIGHT('000' + PARSENAME( REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 1), ' ', ''), '-', '.') , 2), 3)
when PARSENAME( REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 1), ' ', ''), '-', '.') , 1) IS NOT NULL AND
CHARINDEX(':', DA_ScriptureReference.location) > 0 then
RIGHT('000' + PARSENAME( REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 1), ' ', ''), '-', '.') , 1), 3)
else
'000'
end
+
case
when CHARINDEX(':', DA_ScriptureReference.location, CHARINDEX(':', DA_ScriptureReference.location)+1) > 0 then
RIGHT('000' + PARSENAME(REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 2), ' ', ''), '-', '.'), 1), 3)
when CHARINDEX(':', DA_ScriptureReference.location) = 0 AND CHARINDEX('-', DA_ScriptureReference.location) > 0 then
RIGHT('000' + PARSENAME(REPLACE(DA_ScriptureReference.location, '-', '.'), 1), 3)
else
'000'
end
+
case
when CHARINDEX(':', DA_ScriptureReference.location, CHARINDEX(':', DA_ScriptureReference.location)+1) > 0 then
RIGHT('000' + PARSENAME(REPLACE(REPLACE(PARSENAME(REPLACE(DA_ScriptureReference.location, ':', '.'), 1), ' ', ''), '-', '.'), 1), 3)
when CHARINDEX(':', DA_ScriptureReference.location) > 0 AND CHARINDEX('-', DA_ScriptureReference.location) > 0 then
RIGHT('000' + PARSENAME(REPLACE(DA_ScriptureReference.location, '-', '.'), 1), 3)
else
'000'
end
FROM DA_ScriptureReference INNER JOIN
DA_ScriptureReferenceBook ON DA_ScriptureReference.BookID = DA_ScriptureReferenceBook.BookID
WHERE DA_ScriptureReference.ResourceID = DA_Resource.ResourceID)
as ReferenceSort
FROM DA_CategoryResource INNER JOIN
DA_Resource ON DA_CategoryResource.ResourceID = DA_Resource.ResourceID
WHERE DA_CategoryResource.CategoryID = #URL.CategoryID#
ORDER BY ReferenceSort, DA_Resource.Title