I am struggling rewriting my query from MS Access' IIF, Then to SQL
Servers TSQL language. I am hoping some one can give me some
guidance. I believe I have the first portion of the query correct but
do believe this requires a "NESTED" argument. This is where I am
lost.
IIf([CARRIER]="VENDOR4",[DOMESTIC LD RATES]![INTER_VENDOR4],
IIf([CARRIER]="VENDOR3",[DOMESTIC LD RATES]![INTER_VENDOR3],
IIf([CARRIER]="VENDOR2",[DOMESTIC LD RATES]![INTER_VENDOR2],
IIf([Carrier]="VENDOR1",[DOMESTIC LD
RATES]![INTER_VENDOR1]))))) AS CPM,
[CPM]*[MOU] AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC;
I have tried to re-write this in SQL SERVER as --
SELET DISTINCT REGION TRAFIC.*,
CASE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTRA_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTRA_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTRA_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTRA_VENDOR1
ELSE
WHEN [CARRIER]="VENDOR4" THEN [DOMESTIC LD
RATES].INTER_VENDOR4
WHEN [CARRIER]="VENDOR3" THEN [DOMESTIC LD
RATES].INTER_VENDOR3
WHEN [CARRIER]="VENDOR2" THEN [DOMESTIC LD
RATES].INTER_VENDOR2
WHEN [CARRIER]="VENDOR1" THEN [DOMESTIC LD
RATES].INTER_VENDOR1
END
AS CPM
CPM*MOU AS COST
INTO INTRALATA_LD
FROM REGION_TRAFIC LEFT JOIN [DOMESTIC LD RATES] ON
REGION_TRAFIC.RATEKEY = [DOMESTIC LD RATES].RATEKEY
WHERE (((REGION_TRAFIC.[TERM LATA])=[REGION_TRAFIC]![LATA]))
ORDER BY REGION_TRAFIC.[TERM LATA] DESC
My challenge is the Case portion of the query and the nesting! I am
not sure if I have the correct syntax or even chose the correct
argument for my purpose.
Hello, I have the following query in Access 2000 that I need to convertto SQL 2000:UPDATE tblShoes, tblBoxesSET tblShoes.Laces1 = NullWHERE (((tblShoes.ShoesID)=Int([tblBoxes].[ShoesID])) AND((tblBoxes.Code8)="A" Or (tblBoxes.Code8)="B"))WITH OWNERACCESS OPTION;The ShoesID in the tblShoes table is an autonumber, however the recordsin the tblBoxes have the ShoesID converted to text.This query runs ok in Access, but when I try to run it in the SQLServer 2000 Query Analizer I get errors because of the comma in the"UPDATE tblShoes, tblBoxes" part. I only need to update the tblShoesfield named Laces1 to NULL for every record matching the ones in thetblBoxes that are marked with an "A" or an "B" in the tblBoxes.Code8field.Any help would be greatly appreciated.JR
Hi, I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
Hi.. I want to convert .dbf files to sql server 2000 tables.. without using any tools. I need to create a different structure for sql server tables other than contains in the .dbf files. May be the dbf files contain only 3 columns. but i need 5 columns and some calculations to determine the values of some fields to insert into sql server table... i need to code this using c# in asp.net.. can u help me? thanks in advance.. Fraijo
OK, I have some SQL Server 2000 databases that I want to convert to 2005. My question: how do I do that? I tried importing the data, but it only picked up 3 tables out of 7 user tables in one of my SS2K databases. Plus, it did not pick up any stored procedures or any other kind of objects. So I tried restoring from a backup, but predictably it could not do that because the original backup was on a different server. I also tried the upgrade advisor utility that comes with SS2K5, but it could not seem to connect to my SS2K server -- it said it could not find any SQL Server objects on the server, even though I am connected using Enterprise Manager and have been using this server for a long time. Is it looking for 2005 databases? That wouldn't make a lot of sense. Anyway, I am stumped... can anyone provide help on this? Thanks!
I have a large (huge) database that I want to copy onto my new slq 2005 test server. I'm leaning toward detaching the data on my 2000 box, duplicating it, copying it to my new 2005 machine, and attaching it. Is it possible that it could be that simple? If not, how is it done? Thanks a bunch for any help or pointers to the articles I was totally unable to find on the subject.
I am trying to upsize a database to SQL server (on which I am a novice). InAccess as part of a much more complex query I had the following (from sqlview)SELECTIIf(InStr([ItemName],"*")>0,Left([ItemName],InStr([ItemName],"*")-1),[ItemName]) AS ShortName FROM corp_infoWhich gives a return value for the whole of ItemName if there is no star init, or the portion up to the star if there is a starI am having a nightmare trying to get an equivalent in SQL server. I'veworked out that Instr is charindex in sql and can adjust for that, but can'twork out how to get a conditional select statement working.It may well be obvious, but any help much appreciated. Thanks.Robin Hammondwww.enhanceddatasystems.com
I have an inline query that I am trying to convert it into JOIN, results are not coming out the same:
Original query:
SELECT distinct (select count (distinct LoanID) FROM Q_C_Main_Sub1 WHERE DAY(LastWorked) = DAY(GETDATE()) and MONTH(LastWorked) = MONTH(GETDATE()) and YEAR(LastWorked) = YEAR(GETDATE()) and PrimStat = '1' and Collector = '3') As DcountMy query:
I am working on a code that will convert the query output into a html query output. That is the output of the query will be along with html tags so that we can save it as a html file.
The stored procedure that i have used is downloaded from symantec website and is working fine. Below is the code of that stored procedure.
/****** Object: StoredProcedure [dbo].[sp_Table2HTML] Script Date: 12/21/2011 09:04:30 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[sp_Table2HTML] ( @TABLENAME NVARCHAR(500), @OUTPUT NVARCHAR(MAX) OUTPUT,
[Code] ....
The code works fine and i am able to get the output with html tags. The problem occurs when i insert stylesheet in the code. I tried to enforce styles using a stylesheet for the table returned in my sql code, so that it will look good. below is the stylesheet code that i inserted between <head> and </head> tags.
If I run the procedure without the style sheet code, it works fine. but when i run the procedure with style sheet code i am getting the below errors.
Msg 105, Level 15, State 1, Line 98 Unclosed quotation mark after the character string '</table></body><'. Msg 102, Level 15, State 1, Line 98 Incorrect syntax near '</table></body><'.
[Code] .....
I checked the code and i am not able to find what is the mistake. I tried changing the quotation mark but it didn't worked.
Hi all--I'm trying to convert a function which I inherited from a SQL Server 2000 DTS package to something usable in an SSIS package in SQL Server 2005. Given the original code here: Function Main() on error resume next dim cn, i, rs, sSQL Set cn = CreateObject("ADODB.Connection") cn.Open "Provider=sqloledb;Server=<server_name>;Database=<db_name>;User ID=<sysadmin_user>;Password=<password>" set rs = CreateObject("ADODB.Recordset") set rs = DTSGlobalVariables("SQLstring").value
for i = 1 to rs.RecordCount sSQL = rs.Fields(0).value cn.Execute sSQL, , 128 'adExecuteNoRecords option for faster execution rs.MoveNext Next
Main = DTSTaskExecResult_Success
End Function
This code was originally programmed in the SQL Server ActiveX Task type in a DTS package designed to take an open-ended number of SQL statements generated by another task as input, then execute each SQL statement sequentially. Upon this code's success, move on to the next step. (Of course, there was no additional documentation with this code. :-)
Based on other postings, I attempted to push this code into a Visual Studio BI 2005 Script Task with the following change:
public Sub Main()
...
Dts.TaskResult = Dts.Results.Success
End Class
I get the following error when I attempt to compile this:
Error 30209: Option Strict On requires all variable declarations to have an 'As' clause.
I am new to Visual Basic, so I'm on a learning curve here. From what I know of this script: - The variables here violate the new Option Strict On requirement in VS 2005 to declare what type of object your variable is supposed to use.
- I need to explicitly declare each object, unless I turn off the Option Strict On (which didn't seem recommended, based on what I read).
Given this statement:
dim cn, i, rs, sSQL
I'm looking at "i" as type Integer; rs and sSQL are open-ended arrays, but can't quite figure out how to read the code here:
This code seems to create an instance of a COM component, then pass provider information and create the recordset being passed in by the previous task, but am not sure whether this syntax is correct for VS 2005 or what data type declaration to make here. Any ideas/help on how to rewrite this code would be greatly appreciated!
hi All, I tried following piece of code in SQL 2005 , is working fine. Select * from Table FOR XML RAW('RECORDS')ELEMENTS,ROOT('MyTable'). But when i tried the same thing in SQL 2000 , it was not working . Plz suggest a way in 2000 to get the XML output with custom RootNode Name in 2000 also like in 2005 ? Thanks in advance. Mohit
@startRowIndex int, @maximumRows int, @SortOrder int AS
DECLARE @TempItems TABLE ( ID int IDENTITY, OrderID int ) DECLARE @maxRow int SET @maxRow = (@startRowIndex + @maximumRows) - 1 SET ROWCOUNT @maxRow INSERT INTO @TempItems (OrderID) SELECT OrderID FROM Orders o INNER JOIN Customers c ON c.CustomerID = o.CustomerID INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID ORDER BY CASE @SortOrder WHEN 0 THEN OrderID WHEN 1 THEN c.CompanyName WHEN 2 THEN e.FirstName WHEN 3 THEN o.OrderDate WHEN 4 THEN o.RequiredDate WHEN 5 THEN o.ShippedDate END
SET ROWCOUNT @maximumRows SELECT o.OrderID, CompanyName, e.FirstName + ' ' + e.LastName EmployeeName, o.OrderDate, o.RequiredDate, o.ShippedDate FROM @TempItems t INNER JOIN Orders o ON o.OrderID = t.OrderID INNER JOIN Customers c ON c.CustomerID = o.CustomerID INNER JOIN Employees e ON e.EmployeeID = o.EmployeeID WHERE t.[ID] >= @startRowIndex AND (o.OrderID LIKE @intOrderID OR @intOrderID = '') AND (o.CustomerID = @strCustomerID OR @strCustomerID='') AND (o.EmployeeID = @intEmployeeID OR @intEmployeeID = 0) AND (o.OrderDate = @dteOrderDate OR @dteOrderDate IS NULL) SET ROWCOUNT 0 GO
when i execute the sp with @sortOrder = 0, it works fine exec CustomPaging '','',0,null, 1, 10, 0
but if i try anything else (@SortOrder=1) i get the error message exec CustomPaging '','',0,null, 1, 10, 1 Syntax error converting datetime from character string.
but im unsure what is causing this problem? and how to sort it?
i have a query which goes like this:-select a.col1,b.col2,c.col2from tab1 a,tab2 b,tab3 cwherea.col1 *= b.col1 anda.col2 *= c.col2 andb.col3 = c.col3how do I write this query in SQL 92?also can we replace *= with =* by interchanging the participating tables as in"a.col1 *= b.col1" is this same as "b.col1 =* a.col1"
if it is possible to run a distributed query against 2000 from 2005, what would the OPENDATASOURCE parameters look like? I'd like to be able to pivot without copying my older 2000 db to 2005 or using linked servers..
For reference, here's an example of a distrib query that reads excel...
ie SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source=C: estxltest.xls;Extended Properties=Excel 8.0')...[Customers$]
SQL 2000: 8.00.2187 x86, 8 way 700mhz, 6GB Ram SQL 2005: 9.00.3042 IA64 2 Way Dual-Core 1.66Mhz 16 GB ram
Symptoms
Querys to the SQL 2005 box from SQL 2000 work but when the query is parameterised with non-literals (@variables) then the query run on the SQL 2005 box excludes any where clause causing the entire table to be returned to the SQL 200 box. When the query is parameterised using literal values the query is executed on SQL 2005 including the where clause.
At first I thought that the "Collation Compatible" setting was the culprit but setting this to 1 made no difference. Other SQL 2000 boxes work as expected and any queries from these using literal and non-literal parameters.
Please, any ideas?
Working
SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = 'value'
Not Working (correctly anyway!)
DECALRE @Value tinyint SET @Value = 22 SELECT A.Column FROM linkedServer.IA.dbo.Table Where A.Column = @value
Hi, I am using SQL SERVER 2000. I need a procedure/ method where i need to display the output value of a particular query. As I wrote the following procedure CREATE proc proc1as beginDeclare @str nvarchar(100)set @str = 'select top 5 * from CT_TM_EmployeeMaster'print @strend the output is giving "select top 5 * from CT_TM_EmployeeMaster" I need the output of the above query. What should i do?
Dear all,I have a database with 3 tables, one is named Enrollment, and the other Attendance. What I am trying to accomplish here is to get all the Student ID from the Enrollment table which matches a given class ID and insert all of them into the Attendance table for a given day.I did this with the SQL query below :- INSERT INTO Attendance (ClassID, Date, StudentID, Status) VALUES (1, '10-4-2005', (Select t.StudentID FROM Student t WHERE t.ClassID=1), 1)Trouble here is I get the error : Subqueries are not allowed in this context. Only scalar expressions are allowed.I understand waht is going on here, that it returns more than 1 result. So my question is, are there any other ways to accomplish what I am trying to do? Hope you all can help me with this.Thanks all in advance,-Victor-
This query executes (instantly) in SQL Server 2000 without SP4. With SP 4, it hangs - goes over 5 mins and does nothing. If we make the following change to the query: (replace the variables with actual values) ... where Links.Matter_ID = 5872
AND Links.Entity_ID = 1
Order By Contacts.ContactID ... It executes almost instantly in SP4 as well. Looks like a SP4 issue, any ideas on how we could fix this? t-sql follows:
Declare @MatterID bigint
Declare @EntityID int
Declare @ReturnDetails int
Declare @RETVAL VarChar(8000)
Declare @EntityDetails VarChar(1000)
Set @MatterID = 5875
Set @EntityID = 1
Set @ReturnDetails = 0
DECLARE Entity_Cursor CURSOR FOR
Select CASE WHEN Contacts.Job_Title IS Null Then '' Else Contacts.Job_Title + ' ' End + CASE WHEN Contacts.First_Name IS Null Then '' Else Contacts.First_Name + ' ' End + CASE WHEN Contacts.Middle_Name IS Null Then '' Else Contacts.Middle_Name + ' ' End
+ CASE WHEN Contacts.Last_Name IS Null Then '' Else Contacts.Last_Name End EntityDetails
From Links
INNER JOIN Contacts
On Links.Contact_ID = Contacts.ContactID
where Links.Matter_ID = @MatterID
AND Links.Entity_ID = @EntityID
Order By Contacts.ContactID
OPEN Entity_Cursor
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails
Select @RETVAL = ''
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'test'
/* If @ReturnDetails = 1
Select @RETVAL = @RETVAL + @EntityDetails + ' '
Else
Select @RETVAL = @RETVAL + @EntityDetails + ', '
FETCH NEXT FROM Entity_Cursor INTO @EntityDetails */
I have two tables in an inner join. I'm detailing the tables with some of their fields below. These tables are in a database I'm creating to manage backup tapes. Most importantly, this database will inform me when backup tapes which have already been used can be recycled (e.g. after all the jobs on the tape are over 28 days old). I want to write something which will look at each tape in turn and, if all related backup jobs on that tape are aged, the tape status will be changed from Active to Spare.
Tapes --TapeNo --Status (Spare / Assigned)
Jobs --JobNo --Name --Description --TapeNo --AgedJob (BIT field indicating whether or not the job has aged)
Each tape can have 0, 1 or many jobs and each job can be on more than one tape.
Anyway, I have the tables and relationsips set up and they're ok. Again, what I'm struggling with is how I take each tape and look at all its jobs and, if all have aged, change the Status for the tape to Spare. I'm using SQL Server 2000 (Access 2003 as front end) and am pretty new to SQL. I was thinking this could be done by using some kind of update query and subquery, but I'm stumped. Could someone please help ?
Hello.After searching Microsoft's website for hours and confusing myself morethan I was before I started...Can anyone explain to me what the current state of affairs is for SQL2000 licensing?In particular with a server that has multiple processors and multipleinstances of SQL Server 2000 running - for both Standard and EnterpriseEditions of SQL Server 2000.Please help!Regards,Taz
When you are inserting/altering a table and you expect values to be added, should you see the number of rows affected at the bottom of the query screen as the time goes by or not?
The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second so how i can incease the performance for SQL server
SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND (PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND (PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND (PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND (PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND ACC_KEY1 > '0' ORDER BY ACC_KEY1 Note 1: value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count Note 2: the number of rows in each table around 15000000 Note 3: I used the same index structure for both DB2 and SQL server 2000 Note 4: I used some other feature in DB2 that increase the performance but I did not found the alternative for it in SQL server 2000 : a- cardinality varies at run time feature b- include column in index instead of use compound index for ( ACC_KEY1 ,ACC_STATUS_LAST ) columns Note 5 : Enable reverse scan for index
I'm having trouble coming up with a query in SQL Server 2000. Trying my best to sum it up in one sentence; I need a result set that for every EventID/StartDate that exists in tblEvents, I need all StartDates within x days to have a minimum sequential, unique number starting at 1. Now I'll go into some more detail and possibly confuse everyone.
What I have is a table of events that to simplify this question include only an EventID and an StartDate field. What I need to do is for each event, find the other events that occur within x days after the event in question. I then need to number the events starting at 1 so that each eventB that occurs within x days of EventA has the minimum number different from any events from the past x days. I know I just sucked at putting it in words so I'll give an example.
Here is what my idea of the end result table should like if x=2. The source table is basically the same thing minus the Num column.
Here's a quick diagram I made (I know it's ugly and not to scale) which hopefully helps you understand what I need better. Basically, I will be drawing a timeline using vml and each item is going to have text next to it so I need items to be staggered if they are on the same day or close to each other so that things don't overlap. I also need to minimize the vertical space it takes up which is why num needs to get back to 1 as soon as it can instead of just putting each event on its own line. The "num" column will tell me what row to put the item in.
Hi,I would like some help converting an access query to a SQL Server query.The access query is made up of the following and then repeated for each field:SELECT Sum(IIf([gender]='Female',1,0)) AS Female, Sum(IIf([gender]='Male',1,0)) AS Male...FROM dbo.applicants I have tried using the following to test out an alternative, but it brings back the incorrect figure:SELECT COUNT(CASE WHEN gender = 'Female' THEN 1 ELSE 0 END) AS FemaleFROM dbo.applicants I've looked at the table and should get back 350, but only get back 193.But using the following query I get the correct figure:SELECT COUNT(gender) AS FemaleFROM applicantsGROUP BY genderHAVING (gender = 'Female') Although I can't use the above query because I want to also count how many 'Male' applicants there are.How can I do this?Thanks
I getting an error when trying to convert the following into a view. I can't take the credit for the code as I copied it from with a program.
Create view vwMAW_KnowledgeDB AS Select * from don0001 left join (select donclass.donor from donclass where classification = 9 and code in ('KB')) inc0 on inc0.donor = don0001.donor where inc0.donor is not null
quote:Error; Msg 4506, Level 16, State 1, Procedure vwMAW_KnowledgeDB, Line 2
Column names in each view or function must be unique. Column name 'donor' in view or function 'vwMAW_KnowledgeDB' is specified more than once.
SELECT distinct whse_zone_id, bay_id From prod_geneology WHERE whse_zone_id in ('SH','CU','SG') START WITH unit_id = 'BL7B230811' CONNECT BY PRIOR parent_id = unit_id
And in this case, 'BL7B230811' could be any 10 character string
I have a query slower in SQL Server 2005 than in SQL Server 2000. I have a database in SQL 2000, I put it on the same server, but with SQL 2005 and the query take 5 seconds instead of 0 seconds. The DB compatibility is SQL Server 2000 (I tried with 2005 and result is the same). Execution plan seems right and I tried to change some DB options without results. It is weird, when I remove left join on MandatsEx, it take 2 seconds. The view currentEmployeeLevelHistoric returns 45 000 rows and mandatsex has 0 rows.
Here is the sample:
Select ls.EmployeNiveau.pk_EmployeNiveauID as NoNiveau, IsNull(nullif(ls.Traduction.Description, ''), ls.TraductionDefaut.Description) + ' (' + ls.currentLevelHIstoric.NoNiveau + ')' As Nom, ls.currentEmployeeLevelHistoric.DebAssign As DateAssignationDebut, ls.EmployeNiveau.assignmentReason As AssignmentReason, ls.currentEmployeeLevelHistoric.FinAssign As DateAssignationFin, ls.MandatsEx.noDossier, ls.MandatsEx.pk_MandatID, '' As Period, ls.currentEmployeeLevelHistoric.NiveauPrincipal, ls.currentEmployeeLevelHistoric.pk_emplNiveauHisto_Id, ls.EmployeNiveau.fk_NiveauID_Niveaux, ls.EmployeNiveau.No_Ent_leg, IsNull(nullif(TradPere.Description, ''), TradDefautPere.Description) + ' (' + NiveauPere.NoNiveau + ')' As NomSup, ls.EmployeNiveau.No_Ent_leg + ls.EmployeNiveau.no_divisio + ls.EmployeNiveau.no_sec_eco + ls.EmployeNiveau.no_etabli + ls.EmployeNiveau.no_mat as employeeScope, case when ls.mandatExternalisation.fk_mandatID_MandatsEx is null then 2 else 1 end as ContractCategory From ls.currentEmployeeLevelHistoric Inner Join ls.EmployeNiveau on ls.currentEmployeeLevelHistoric.pk_EmployeNiveauID = ls.EmployeNiveau.pk_EmployeNiveauID Inner join ls.currentLevelHistoric On ls.EmployeNiveau.fk_NiveauID_Niveaux = ls.currentLevelHistoric.fk_niveauID_niveaux Left Outer Join ls.TraductionDefaut On ls.currentLevelHIstoric.fk_TraductionID_TraductionDefaut = ls.TraductionDefaut.pk_NoTraduction Left Outer Join ls.Traduction On ls.TraductionDefaut.pk_NoTraduction = ls.Traduction.No_Traduction and ls.Traduction.Langue = 1 Left Join ls.MandatsEx on ls.EmployeNiveau.fk_MandatID_MandatsEx = ls.MandatsEx.pk_MandatID Left Join ls.mandatExternalisation on ls.MandatsEx.pk_MandatID = ls.mandatExternalisation.fk_mandatID_MandatsEx left Join ls.Niveaux as NiveauPere on NiveauPere.niveauID = ls.currentLevelHIstoric.supId Left Outer Join ls.TraductionDefaut As TradDefautPere On NiveauPere.fk_TraductionID_TraductionDefaut = TradDefautPere.pk_NoTraduction Left Outer Join ls.Traduction As TradPere On TradDefautPere.pk_NoTraduction = TradPere.No_Traduction and TradPere.Langue = 1 Where ls.EmployeNiveau.fk_EmploID_Emplos = 345158 and (convert(varchar, ls.currentEmployeeLevelHistoric.DebAssign, 112) <= '20060802' and (ls.currentEmployeeLevelHistoric.FinAssign is null or convert(varchar, ls.currentEmployeeLevelHistoric.FinAssign, 112) >= '20060802'))
The SQL statement below works without the WHERE statement but as soon as i try to use the where it stops working, I think it has something to do with the HAVING statement in the vwMultiAE2 view but I am not sure.It did the same thing when I had it in one view. Anyone know what is going on here?SELECT Ch, Reg, FMM, AE, Acc, Totals FROM vwMultiAE2 WHERE (g1ID = @g1 or @g1 = 'All') AND (g2ID = @g2 or @g2 = 'All') AND (g3ID = @g3 or @g3 = 'All') AND (AEID = @g4 or @g4 = 'All')
vwMultiAE2 > SELECT TOP 100 PERCENT Ch, Reg, FMM, AE, Acc, Totals, g1ID, g2ID, g3ID, AEID FROM vwMultAE WHERE Account_ID IN (SELECT Account_ID FROM vwMultAE GROUP BY g2ID, Account_ID HAVING ( COUNT(Account_ID) > 1 )) ORDER BY g2ID, Account_ID
