TSQL: Conditional Union Statement
Is it possible to have a conditional union statement in a stored proc?
Here's an example on the northwind database. If says there's a syntax
error near the UNION statement. Looks like it doesn't like having the
BEGIN directly in front of it.
Is the only solution to create a dynamic sql string then call exec on
it?
Any help appreciated.
Tom.
CREATE PROCEDURE usp_test
(
@both int = 1
)
AS
SET NOCOUNT ON
SELECT * FROM territories WHERE regionid = 1
IF @both = 1
BEGIN
UNION
SELECT * FROM territories WHERE regionid = 2
END
GO
View Complete Forum Thread with Replies
Related Forum Messages:
Conditional Union Possible?
I'm trying to achieve something like the following: SELECT 'page' as type, page_filename as filename, page_title as title, page_metadescription as intro FROM tbl_pages WHERE CONTAINS(*, @searchterm) IF 1=1 BEGIN UNION all SELECT 'news' as type, 'thiswillbeafilename.asp' as filename, news_title as title, news_intro as intro FROM tbl_news WHERE CONTAINS(*, @searchterm) END IF 2=2 BEGIN UNION ALL SELECT 'resource' as type, resource_filename as filename, resource_longtitle as title, resource_summary FROM tbl_resources WHERE CONTAINS(*, @searchterm) END I'm pretty sure this isn't possible using the approach i have here, however is there an different method i can use to ahcieve the same effect other than using dynamic sql? Cheers,
View Replies !
TSQL UNION, But Get The Differance In Dollar Amount In The Result
GridView_1 Category Name SubCategory Name Amount Construction Construction 2,877.00 Design Design 0.00 Soft Cost Inspection 0.00 GridView_2 Category Name SubCategory Name Amount Construction Construction 2,800.00 Construction Contingency 300.00 Design Design 500.00 Soft Cost Inspection 980.00 Soft Cost Survey 145.00 Soft Cost Testing 720.00 Soft Cost Management 1000.00 Soft Cost Other Costs 10,000.00 QUESTION: How could I UNION the two result sets together and where the Category Name and SubCategory Name are equal for GridView1 and GridView2, show the difference in the Amount column. This is the result I am looking for. Category Name SubCategory Name DIFFERANCE Construction Construction 77.00 = (GridView1 - GridView2) Construction Contingency 300.00 Design Design 500.00 Soft Cost Inspection 980.00 Soft Cost Survey 145.00 Soft Cost Testing 720.00 Soft Cost Management 1000.00 Soft Cost Other Costs 10,000.00 I am trying to do it on the back end. Though, if you have a clever way to produce a resulting gridview in C# with the results I need, I am all game! This is what I have so far: (TSQL code) SELECT tblCategories.txtCategoryName, tblSubCategories.txtSubCategoryName, tblEstimatesLineItems.curEscAmount FROM tblEstimatesLineItems, tblCategories, tblSubCategories WHERE tblEstimatesLineItems.lngzEstimateId = 24 AND --@lngzEstimateId_Compare1 tblEstimatesLineItems.lngzCategoryId = tblCategories.idsCategoryId AND tblEstimatesLineitems.lngzSubCategoryId = tblSubCategories.idsSubCategoryId union all SELECT tblCategories.txtCategoryName, tblSubCategories.txtSubCategoryName, tblEstimatesLineItems.curEscAmount FROM tblEstimatesLineItems, tblCategories, tblSubCategories WHERE tblEstimatesLineItems.lngzEstimateId = 25 AND --@lngzEstimateId_Compare1 tblEstimatesLineItems.lngzCategoryId = tblCategories.idsCategoryId AND tblEstimatesLineitems.lngzSubCategoryId = tblSubCategories.idsSubCategoryId
View Replies !
Using Conditional Statement In Stored Prcodure To Build Select Statement
hiI need to write a stored procedure that takes input parameters,andaccording to these parameters the retrieved fields in a selectstatement are chosen.what i need to know is how to make the fields of the select statementconditional,taking in consideration that it is more than one fieldaddedfor exampleSQLStmt="select"if param1 thenSQLStmt=SQLStmt+ field1end ifif param2 thenSQLStmt=SQLStmt+ field2end if
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
UNION In A WHILE Statement??
Hello,Im wondering if someone can offer some advice, Im trying to get a union in the statement below to it will return all the data as rows in one table..... DECLARE @counter INT SET @counter = 0 WHILE @counter < 24 BEGIN SELECT "Hour"=(SELECT @counter), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = @counter) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) SET @counter = @counter + 1 ENDIf i write each statment out with the values as apose to @counter and union under each i get the result i am looking for... SELECT "Hour"=(SELECT 0), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 0) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) UNION SELECT "Hour"=(SELECT 1), "HourAverage"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND (datestamp < getdate())) / (@numberofdays), "HourToday"=(SELECT COUNT(*) FROM webstats WHERE (hourstamp = 1) AND datestamp > DATEADD(d,-1,GETDATE()) AND datestamp < DATEADD(d,1,GETDATE())) UNION etc. etc. Does doing a while loop have any performace advantages to my other example? Thanks Bart
View Replies !
SQL Conditional WHERE Statement
Hi, is it possible to do a conditional WHERE in T-SQL? I have a table with a column that consists of a reference that starts with either a single alpha character or two alpha characters followed by four numeric digits (the numeric portion is always unique but the alpha isn’t). E.g. A1234, AB1235, AB1236, C1237, HT1238. What I want to do is select a range of rows based on the numeric portion of this reference column. In other words I want to select say 50 rows starting from row 1000 (rows 1000 to 1050) regardless of whether there is one or two alpha characters preceding the numerics.The Stored procedure I have so far works (using COUNT for testing) for selecting a range of rows that has two alpha's at the start. However, if I simply add an OR to the WHERE to select rows where there is a single alpha in the reference column, when a single alpha reference is found it will fail the first logical check for two alpha's giving an error condition. Therefore, how can I incorporate a conditional WHERE using IF or some alternative method, so that it will also give me all the rows in the number sequence that start with either single or double alpha's within the same SELECT / WHERE statement? Thanks for any help.ALTER PROCEDURE [dbo].[sp_Test] ( @startRef int, @endRef int ) AS BEGIN SELECT Count(*) FROM myTable WHERE ((SUBSTRING(Ref,3,LEN(Ref)-2) BETWEEN @startRef AND (@startRef + @endRef))) END RETURN
View Replies !
Conditional Where Statement
I have a stored procedure that performs a search function with params:@username nvarchar(50)@country nvarchar(50)and like 10 more.A user may provide values for these params optionally.So when the @username var is left blank, there should be no filtering on the username field (every field should be selected regardless of the username)Currently my statement is:select username,country from myUsers whereusername=@username and country=@countryWith this statement when a user provides no value for username the username field selects on ''m which returns ofcourse nothing...What can I do to solve this?Thanks!
View Replies !
Conditional WHERE Statement?
Hi all,I have one for all the blackbelters out there: is there a way i canmake a stored procedure where i can control the where statement withvariables? I have to do some complex transformations to get compose afact table for MSAS and there a a lot of similarities between thequeries and a few differences because of different account methodsetc. (booking in starting date, booking stuff on order entry datesetc) I want to put a combination of different rules in differentmembers of dimensions.An example of what i mean:CREATE STORED PROCEDURE dbo.FILLFACT (@PAR1, @PAR2)ASINSERT INTO FactTable (blah blah)SELECTIF @PAR1 = 'OrderDate'SourceView.OrderdateELSESourceView.StartDate,etc etc...FROMSourceViewWHEREIF @PAR2 = 'WholeTable'1=1IF @PAR2 = 'Incomplete'EndDate IS NULL OR EXIST (SELECT * FROM Exceptions WHERE...., etc)This way i could fill my fact table withEXEC dbo.FillFact 'beginDate','Wholetable'EXEC dbo.FillFact 'begindate', 'Rulebook1'EXEC dbo.FillFact 'BeginDate', 'Exceptions'etcetera.This is not an actual SQL script i use, just an example of what i'mtalking about. Or maybe i could pass the where statement entirley as avariable? But i can't use SET @PAR1 = 'EndDate IS NULL' and then useWHERE @PAR1 can I?I hope i'm making sense. Does anyone know if this is possible? Rightnow i have a procedure that is composed of a dozen of sql scripts thatare mostly the same, but i have to copy it for every combination ofsituations and then, of course, new stuff has to be added on 12different places. Again and again.Any thoughts?TIA,Gert-Jan van der Kamp
View Replies !
Conditional Statement! HELP!
Hi, I've been looling around, but was unable to correctly use a conditional statement in a T-SQL Query. I have this SQL query and in it how I would do if it was C#. If someone could please help me get what I need I would appreciate it a lot. Here is the query: Code Block SELECT Ficha, Almoxarifado, [Código do Item], Descrição, Unidades.Unidade, [1ª Contagem], [2ª Contagem], Recontagem, Observações, Cancelar FROM Fichas INNER JOIN Itens ON [Código do Item] = Código INNER JOIN Unidades ON Itens.Unidade = Unidades.ID WHERE Ficha BETWEEN 01 AND 5000 AND Recontagem IS NULL AND /* What I would do in C#, but need in T-SQL*/ if ([1ª Contagem] > [2ª Contagem]) { if ([2ª Contagem] / [1ª Contagem] < 0.99) return true; else { if ([1ª Contagem] / [2ª Contagem] < 0.99) return true; } I really need a help in this. Does anyone know how to accomplish this? Thanks. Regars, Fábio
View Replies !
HELP With A Select/Union Statement
I have 3 tables One table is the order Table, Bill to table and ship to table I have to Views created as followed This query uses the Ship to table to pull the ship to information to the shipping system. SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO); This query uss the Bill to as the ship to inforamtion SELECT CUSTOMER.ID, CUSTOMER.SHIPTO_ID, CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID, customer_order.SHIP_TO_ADDR_NO FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID = customer_order.CUSTOMER_ID; I need this infroamtion in one table which I have done in the UNION statement as followed: SELECT Cust_address.NAME, Cust_address.ADDR_1, Cust_address.ADDR_2, Cust_address.ADDR_3, Cust_address.CITY, Cust_address.STATE, Cust_address.ZIPCODE, Cust_address.COUNTRY, Cust_address.SHIP_VIA, customer_order.ID FROM Cust_address INNER JOIN customer_order ON (Cust_address.CUSTOMER_ID = customer_order.CUSTOMER_ID) AND (Cust_address.ADDR_NO = customer_order.SHIP_TO_ADDR_NO) UNION ALL SELECT CUSTOMER.NAME, CUSTOMER.ADDR_1, CUSTOMER.ADDR_2, CUSTOMER.ADDR_3, CUSTOMER.CITY, CUSTOMER.STATE, CUSTOMER.ZIPCODE, CUSTOMER.COUNTRY, CUSTOMER.SHIP_VIA, customer_order.ID FROM CUSTOMER INNER JOIN customer_order ON CUSTOMER.ID=customer_order.CUSTOMER_ID; Here is the problem when I pull information out of the ship to table I get 2 results as followed My key field to pull this information is the Last field Custoemr ID this custoemr ID exist in both tables but contains different information I want to ONLY pull in the info that I need in this case it would be the first line that is the Correct shipping information. NAMEADDR_1ADDR_2ADDR_3CITYSTATEZIPCODECOUNTRYSHIP_VIAID DIEBOLD INC (4076A)ATTN: RANCE AARON343 MANOR DRPACIFICACA9404418932 DIEBOLD, INCOHUPS #88X08X18932 MY POINT: Is there a way to select a over all DISTINCT order ID. Thank you for any help hope this make sense!
View Replies !
Parameters With A Union Statement
I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data. What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not. But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?" Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once? Thanks
View Replies !
UNION ALL SELECT Statement Help
I am not completly sure if I have this posted in the right forum so if I don't just let me know and I will move it. Here is my problem. I need to be able to use the WHERE keyword more than once in one SELECT statement and have not been able to figure this out. I need to be able to first search for information under one column with the WHERE keyword like usual and then I need to be able to search the returned results with another WHERE keyword to narrow down the returned results. I tried writing two SELECT statements and joining them with a UNION ALL keyword like this: "SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _ "FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _ "ON LI.CNID = CD.ID WHERE LI.CNID = '1'" & _ "UNION ALL" & _ "SELECT LI.ID, LI.CNID, CD.ID, LI.FDName, LI.FDR " & _ "FROM FinalDrive AS LI INNER JOIN CarData AS CD " & _ "ON LI.CNID = CD.ID WHERE LI.FDName = 'Car1" This hasn't worked and I didn't expect it to. Everytime I run this code I get an Unhandled SqlExecption: Invalid column name 'Towing'. Can anyone help me with figuring out how to use the WHERE keyword more than once. I am using Visual Basic.Net with ADO.Net. Thanks!
View Replies !
Union And Order Statement
I have the following query and for some reason when I try to run it, it tells me there is an incorrect syntax near the 'union' statement. Can you tell me what is wrong with this? SELECT MDN, DATEPART(d,CallDate) as CallDate, DATEPART(hh,CallDate) as Hour, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM VoiceCallDetailRecord WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN UNION SELECT MDN, DATEPART(d,CallDate) as CallDate, DATEPART(hh,CallDate) as Hour, sum(ceiling((Cast(DurationSeconds as Decimal)/60))) as Minutes FROM ZeroChargeVCDRecord WHERE DurationSeconds > 0 and CallDate >= '02/19/2007' and calldate < '03/19/2007' and COS = 3 AND (((CONVERT(varchar, CallDate, 108) Between '21:00:00' AND '23:59:59') OR (CONVERT(varchar, CallDate, 108) Between '00:00:00' AND '07:00:00')) OR DATEPART(weekday, CallDate) in (1,7)) and NOT (Left(Endpoint,3) IN ('011') or (Left(Endpoint,4) IN ('1340','1876','1868','1809', '1246','1242','1780','1403', '1250','1604','1807','1519', '1204','1506','1709','1867', '1902','1705','1613','1416', '1905','1902','1514','1450', '1418','1819','1306','1867'))) order BY DATEPART(d,CallDate), DATEPART(hh,CallDate), MDN
View Replies !
SQL Help: Conditional Statement Using Inserted
I'm learning SQL and here I'm trying to use two things that I'm not familiar with - IF statements and the Inserted temporary table. Here's the background - skip this paragraph if you like. I'm working on a tasking system for the Help Desk - they get requests from the web site for various items and I break up the request into Software, Hardware, Accounts, etc tables and list the status of each item as "Requested". I'm also keeping a Tasks table to make work orders for each item requested. I've got triggers on the Accounts and Hardware tables that automatically make a new task for those items but the Software is more tricky because all software for a given request should be just one task. Software installs are all done by one person at the same time. So I'm trying to make a trigger that creates a new Task when a new Software record is inserted. But if a task already exists with the same RequestID (meaning they requested two peices of software and this is the second one), then I just want to update the task already created. Here's what I got: 1 CREATE TRIGGER [NewSoftwareTask] 2 ON [dbo].[Software] 3 AFTER INSERT 4 AS 5 BEGIN 6 7 -- If a software task already exists for this request 8 -- then update it. Otherwise create a new task. 9 10 if exists(select TasksID 11 from Tasks 12 where Tasks.RequestsID = inserted.RequestsID and 13 TasksType = 'Software') 14 BEGIN 15 UPDATE [BGHelpdesk].[dbo].[Tasks] 16 SET [TasksDescription] = [TasksDescription] + vbcrlf + "Install " + inserted.SoftwareType + ". " + inserted.SoftwareComments 17 WHERE Tasks.RequestsID = inserted.RequestsID and 18 TasksType = 'Software' 19 END 20 21 else 22 23 BEGIN 24 INSERT INTO [BGHelpdesk].[dbo].[Tasks] 25 ([RequestsID] 26 ,[TasksType] 27 ,[TasksSubType] 28 ,[TasksTitle] 29 ,[TasksDescription]) 30 SELECT 31 s.RequestsID 32 ,'Software' 33 ,s.SoftwareType 34 ,'New ' + s.SoftwareType + ' Account for Request ' + cast(s.RequestsID AS varchar) 35 ,s.SoftwareComments 36 FROM Software s join 37 inserted ON s.SoftwareID = inserted.SoftwareID 38 END 39 END 40 GO It keeps balking at lines 12 and 17 saying "The multi-part identifier "inserted.RequestsID" could not be bound." The ELSE statement is what I use on the other tables and it works fine so the inserted temp record seems pretty straightforward but I must be doing something wrong...
View Replies !
Conditional Select Statement
Hello dbForumers, Yet another puzzling question. I remember I saw somewhere a particular syntax to select a column based on a conditional predicate w/o using a user defined function. What I want to accomplish is this : SELECT (if column colA is empty then colB else colA) as colC from SomeTable. Possible ? Not possible? Have I hallucinated ? Thank You!
View Replies !
IIf ANY Value In Table Conditional Statement
This is probably quite simple, but it isn't sticking out at me. In a simple table with two groups, I want to conditionally set the color of the first group to red or green based upon whether ANY value for this field equals a particular string. I know how to conditionally set the color of the field. However, the below code appears to only compare the FIRST value in Fields!myField.Value to the string "bad". I want it to return "Red" if ANY of the values for Fields!myField.Value = "bad". Code Block=IIf(Fields!myField.Value = "bad", "Red", "Green") myField is ouput in the detail scope if that makes any difference. Is this possible?
View Replies !
Adding Conditional Statement To A Sum
currently I am creating a total if the date is between two entered dates: select sum(case when exigo_data_sync.orders.orderdate between @prevMonthStart and @prevMonthEnd then 1 else 0 end) as PrevMonthCount, I need to check an additional column for ($1.00) or $1.00. If it contains $1.00 then proceed as normal and add to the total. If it contains ($1.00) then subtract one from the total. Any advice?
View Replies !
UNION Statement Doesn't Work
Hi,I have a database stored in MS SQL 2000 and an application written inVB5, which connects the database via JET/ODBC.I have a problem with the UNION statement.When I run a simple query like:"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"I get the following error:"Runtime error 3078 - The Microsoft Jet database engine cannot find theinput table or query 'select field1 from table1'. Make sure it existsand that its name is spelled correctly."I can run the queries separately "SELECT field1 FROM table1" and "SELECTfield1 FROM table2", so that I'm sure table and field names are correctand I have permission to access them.Both field1 and field2 are the same type (int).If I run the query in MS SQL Query Analyzer, it works fine.It doesn't work only when I run it from VB/JET/ODBC.Has anyone already had this kind of problem?Any help will be highly appreciated!Thank you so much for the attention.--Posted via http://dbforums.com
View Replies !
Limit And Order In Union Statement
hi all, i have the following union statement, which works like a charm:SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel ORDER BY [SortDate] now i want to ad a limit to this statement, but i can't get it working. i have tried this:SELECT * FROM (SELECT [Name], [EventID] AS [ItemID], [TourID], [Date], NULL AS [StartDate], [Date] AS [SortDate], [Type] FROM [Events] WHERE [TourID] IS NULL AND AccessLevel <= @AuthenticationLevel UNION SELECT [Name], [TourID] AS [ItemID], [TourID], NULL AS [Date], [StartDate], [StartDate] AS [SortDate], '2' AS [Type] FROM [Tours]WHERE AccessLevel <= @AuthenticationLevel) ORDER BY [SortDate] LIMIT 2 but i keep getting an error message saying "Incorrect syntax near the keyword "ORDER". what am i doing wrong? -bm
View Replies !
Writing Union Statement With 2 Order By's
Hi, I'm trying to write a UNION statement with 2 selects and can't figure out for the life of me how to do it. select top 10 * from tblusers ORDER BY dateJoined UNION select top 10 * from tblusers ORDER BY lastLogin The union works fine without the order by's, but I really need the order by's in there and I don't understand why its so difficult to do. Surely there must be some other strategy? Any help is much appreciated.. thanks!! mike123 How can I reproduce this results ?
View Replies !
I Need Help With This Tsql Statement
Every time I try this statement I keep getting a syntext error near count I must be over looking something can some one help me with this. SELECT 'Quarter 1' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (1,2,3))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 2' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (4,5,6))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 3' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (7,8,9))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_COMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE UNION ALL SELECT 'Quarter 4' as 'qtr' count(jobid) as 'transcount', count(distinct job.patientid) as 'patientcount', sum(job.LANGUAGE_TCOST) as 'lcost', Sum(job.LANGUAGE_DISC_COST) as 'dlcost', avg(LANGUAGE_DISC) as 'avgLDisc', (sum(job.LANGUAGE_TCOST) + sum(job.LANGUAGE_DISC_COST)) as 'LGrossAmtBilled', (sum(LANGUAGE_TCOST) / count(distinct job.patientid)) as 'PatAvgL', (sum(LANGUAGE_TCOST) / count(jobid)) as 'RefAvgL', sum(LANGUAGE_DISC) as 'avgPercentDiscL', JOB.JURISDICTION, PAYER.PAY_COMPANY, PAYER.PAY_CITY, PAYER.PAY_STATE, PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE INVOICE_AR.INVOICE_DATE AS EXPR1, INVOICE_AR.AMOUNT_DUE FROM JOB INNER JOIN INVOICE_AR ON JOB.JOBID = INVOICE_AR.JOBID LEFT OUTER JOIN PAYER ON PAYER.PAYERID = JOB.PAYER.ID LEFT OUTER JOIN STATES ON JOB.JURISDICTION = STATES.INITIALS WHERE (INVOICE_AR.AMOUNT_DUE > 0)AND (INVOICE-AR.INVOICE_DATE BETWEEN @startdate and @enddate)AND (MONTH(INVOICE_AR.INVOICE_DATE) IN (10,11,12))AND (PAYER.PAYCOMPANY like '%' + @Company + '%') Group By JOB.JURISDICTION PAYER.PAY_cOMPANY PAYER.PAY_CITY PAYER.PAY_STATE PAYER.PAY_SALES_STAFF_ID, JOB.INVOICE_DATE, INVOICE_AR.INVOICE_DATE, INVOICE_AR.AMOUNT_DUE Order By 'QTR' asc
View Replies !
TSQL - WITH Statement
Hi guys, I need help with this one... Iam Trying to understand how to use the statement WITH I am running the code below, but getting error. note: I have SQL SERVER 2005 in my PC, but retrieving data from the SQL SERVER 2000 (in the server) Thanks in advance, Aldo. Code Snippet WITH MyCTE (FILTER, SORTGROUP) AS ( SELECT ACCOUNTS.FILTER, ACCOUNTS.SORTGROUP FROM ACCOUNTS ) SELECT * FROM MyCTE AS CTE_01; Error Messages: Msg 156, Level 15, State 1, Line 1 Incorrect syntax near the keyword 'WITH'.
View Replies !
Case Conditional In SQL Statement 2000
Hi,I'm trying to do calculations in a SQL statement, but depending on onevariable (a.type in example) I'll need to pull another variable fromseperate tables.Here is my code thus far:select a.DeptCode DeptCode,a.Type Type,(a.ExpenseUnit / (select volume from TargetData b where b.type =a.type)) ExpenseFromcalc1 aThe problem... a.Type can be FYTD, Budget, or Target... and dependingon which one it is, I need to make b either FYTDData, TargetData, orBudgetData. I'm thinking a case statement might do the trick, but Ican't find any syntax on how to use Case in an MS SQL statement. EvenIf statements will work (if that's possible), though case would beless messy.Any suggestions would be much appriciative. Thanks...Alex.
View Replies !
Summing Items From A Conditional Statement
What I need to do in seperate a group of numbers into two different categories based on a phase code. I have acheived this through two conditional statements, but when I try to total the numbers that were returned for each group I receive an #error. This is an example of the switch statement I used in order to return the correct values for the Implemenataion. =Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, "") I've tried several different methods for aggregating the numbers that are returned. =SUM(Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, "")) I've tried substituting a 0 in for the "" at the end of each statement. I've also tried to take the first statement and put it into its own table field named ImplementationLedger, and them summing it. ie. =SUM(Fields!ImplementationLedger.Value) Please Help!
View Replies !
Conditional Where Clause W/ Case Statement Possible?
Greetings, After many hours search many forums and many failed experiments, I figure it's time to turn to the experts. I need to execute a query that changes the returned data based upon a parameter's value. In my example below, the lob field contains both text values and nulls. SELECT uniqueID, lob, xdate FROM mytable WHERE CASE WHEN @myparam = 'ALL' THEN xdate >= '2007-09-01' ELSE xdate >= '2007-09-01' or lob = @myparm END I've experimented with various forms of the LIKE function, checking for null/not null and keep coming up blank. I thought about using an IF statement and creating different versions of the entire statement, however, in real-life I need to do this with four fields using four parameters (one for each field). The permutations are a little too much. Any ideas? Rob
View Replies !
Conditional Statement In View Design
Have a View where I need to enter a conditional IF_THEN_ELSE statement in a new field (field has an alias). If this were an Access query I would do following: IIf([dbo.AR1_CustomerMaster.EmailAddress] Is Null, "A", "B") How can I accomplish same in View design??
View Replies !
Count The Number Of Rows In A UNION ALL Statement
Hi,Should be quite simple but can someone please tell me the best way tocount the number of rows in an UNION ALL statement.I tried using @@ROWCOUNT but that doesn't seem to contain the correctnumber.Also, I assume that running the query again but just returning count(*)instead of the data is horribly inefficient (plus the code is thenbloated.)?Thanks,Mark
View Replies !
UNION Statement That Works In SQL Server But Not Access
Hi, Is there a way I can get this select Union statement to work in Access. SELECT '' AS Router UNION SELECT DISTINCT Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND Router <> 'IPVPN Lite' AND Router <> 'VPN Bridge' AND Router <> 'IPVPN Aggregated Bandwidth' ORDER By Router I get this message in Access: Query input must contain at least input of query Thanks for any help Chris
View Replies !
How Can I Use Variables In This TSQL Statement
Hi all,I would like to replace the default directory location (c: emp) and thefilename (emails.csv) with variables like @FileDir and @FileName in thestatement below.SELECT @cnt = COUNT(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft TextDriver (*.txt; *.csv)}; DefaultDir=c: emp;','select * from "emails.csv"')However, my attempts have not been successful.Any ideas appreciated, and TIA.Greg
View Replies !
TSQL From An Access SQL Statement
Good morning one and all, I have some queries that were written in access that I need to port into SQL 7, the whole process is boring and mundane. Does any1 know of a translator (i.e. access sql to t-sql) or a reference to the differences between access SQL and t-Sql. Any and all help appreciated, Thanx Gurmi
View Replies !
What Is Wrong With This TSQL Statement
hello, I have wriiten this query to select all records from those tables in a database that have "to_be_transffered" column where this "to_be_transferred_column" is not null --code EXEC sp_MSForEachTable 'IF EXISTS(SELECT c.table_name, c.column_name FROM information_schema.columns c INNER JOIN information_schema.tables t ON c.table_name = t.table_name WHERE c.table_name = SUBSTRING(''?'', (CHARINDEX(''['',''?'', 2) + 1), ((LEN(''?'') - (CHARINDEX(''['',''?'', 2) + 1)))) AND c.column_name = ''to_be_transferred'' AND t.table_type = ''BASE TABLE'' ) BEGIN SELECT * FROM ? WHERE to_be_transferred IS NOT NULL END' But I am getting the following error Msg 207, Level 16, State 3, Line 12 Invalid column name 'to_be_transferred' I thought the if statement filters all those tables with the specified column and do select statement fot those tables only what did I do wrong, any suggestionm? Thanks
View Replies !
Tsql Case Statement
Hi, Here is the scenario. I want to add last year sale dollars in accordance with current period in exsiting fact table. And below is the syntax. Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(a.net_sale_Dollars) as sale_TY , sum ( b.net_sale_dollars ) as sale_LY , a.division_name, a.department_number fromFact 1 as a ,Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- The current table from this query is showing like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 But, if we assume that in the current date, dept = 2 has a sale amount, and in parallel year if dept=2 does not have any sale then this information was excluded. The structure of table that I want to create must look like: store_key date_key sale_TY sales_LY div dept ------------------------------------------------------------------------------------------------ 1 1 30 20 ABC 1 2 1 20 20 ABC 3 2 1 15 0 ABC 2 >>>> want to put 0 value where only one side ( current or parrallel period) has sales info. So, I'm thinking the case statement like: Case statement logic like: ------------------------------------------------------------ if a. dept not exist in b.dept then Sale TY -> a.net_sale_dollars Sale LY -> 0 if b.dept not exist in a.dept then sale TY -> 0 sale LY -> b. net_sale_dollars ------------------------------------------------------------- below is the syntax which doesn't work (it's wrong): Syntax: ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- select a. store_key, a.fisc_date_key, sum(case when a.department_number = b.department_number then a.net_sale_dollars else case when a.department_number NOT IN (b.department_number)then a.net_sale_dollars else null end) as sale_TY , sum ( case when b.department_number =a.department_number then b.net_sale_dollars else case when a.department_number NOT IN (b.department_number) as sale_LY, a.division_name, a.department_number fromFact 1 as a Fact 1 as b Whereb.fisc_date_key = (a.fisc_date_key -364) and a.division_name=b.division_name and a.department_number =b.department_number and a.store_key = b.store_key group by a.division_name, a.department_number, a.fisc_date_key, a.store_key , ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Is it possible to create these kind of structure? Please give me some comments. Thanks.
View Replies !
Conditional Statement With A Cast From String To Date
My source file is showing column 10 as string. My destination table is datetime. I am using the derived transformation with a conditional statement. How do I convert the value from string to date. Everywhere I try the (DT_DATE) I get an error. [Column 10] == "01/01/0001" ? " 01/01/1801" : [Column 10] <= "12/31/1801" ? "12/31/1801" : [Column 10]
View Replies !
Conditional Suppression Of Table Detail Row And If/then Statement
Two questions: 1) I am unable to get a detail row of my table to suppress. I am using: =Iif(IsNothing(Fields!Address2.Value), True, False) 2) I am not able to get an If/Then statement to work. I am able to use the followingl; however, ideally I would want to have multiple if/else statements: =Iif(Fields!Taxschid.Value = "CUST", 0, Fields!Selling_Price.Value*.08) Tx Les
View Replies !
MS Access Vs. Transact-SQL – Conditional SQL Select Statement Problem
Hi All, I am trying to write a Transact-SQL statement and am having no success. I have a customers table that has some duplicate Customer Numbers in it, however the records do have a unique Reference Number. I am trying select all records that match a list of Customer Numbers but if there are more than 1 matching Customer Number I only want the one with the largest Reference Number. My BASIC Select Statement is: SELECT Cust_Ref_No, Customer_No, Customer_Name, Address1, Address2, Suburb, State, Postcode, Phone FROM Customers WHERE Customer_No IN (SELECT Customer_No FROM temp_Customers) Much to my HORROR I found that what I am trying to do is simple in MS Access using the “First” Function – Such as below: SELECT First(Cust_Ref_No) AS Cust_Ref_No, Customer_No FROM Customers GROUP BY Customer_No ORDER BY First(Cust_Ref_No), Customer_No; There appears to be no “First” Function in Transact-SQL. Is someone able to show me how to achieve the same results in Transact-SQL as I can get with MS Access (I’d HATE to think that MS Access has more functionality in querying data than SQL Server in any way at all)? P.S. I really need to run the select statement as one step rather than splitting it up into parts. Regards, Trog28
View Replies !
TSQL: I Want To Use A SELECT Statement With COUNT(*) AS 'name' And ORDER BY 'name'
I am very new to Transact-SQL programming and don't have a programmingbackground and was hoping that someone could point me in the rightdirection. I have a SELECT statement SELECT FIXID, COUNT(*) AS IOIsand want to ORDER BY 'IOI's'. I have been combing through the BOL, butI don't even know what topic/heading this would fall under.USE INDIISELECT FIXID, COUNT(*) AS IOIsFROM[dbo].[IOI_2005_03_03]GROUP BY FIXIDORDER BY FIXIDI know that it is a simple question, but perhaps someone could assistme.Thanks,
View Replies !
Help With TSQL Statement To Check For Table Size
I have to deal with an environment where the developers create and modify tables at will, and the tool they use does not check if the table created is over the 8060 limit for a table. I have a sql statement below which checks the size for me, but I can't figure out how to put a where clause on it to check for the size being over 8060. I i have over 1000 tables to check and was going to write a cursor routine to check it and print it, but I need te where clause to work.Any help appreciated select Name=left(c.name,20), c.prec, scale = ISNULL(c.scale,0), t.name from syscolumns c inner join systypes t on c.xtype = t.xtype where id in (select id from sysobjects where name = 'mh_demographic2_') order by c.colid COMPUTE sum(c.prec)
View Replies !
GridView Based On SQLServerDataSource Using A Select Union Statement, Impacts On Update And Insert?
I have a GridView dispalying from a SQLServerDataSource that is using a SQL Select Union statement (like the following): SELECT FirstName, LastNameFROM MasterUNION ALLSELECT FirstName, LastNameFROM CustomORDER BY LastName, FirstName I am wondering how to create Update and Insert statements for this SQLServerDataSource since the select is actually driving from two different tables (Master and Custom). Any ideas if or how this can be done? Specifically, I want the Custom table to be editable, but not the Master table. Any examples or ideas would be very much appreciated! Thanks, Randy
View Replies !
Conditional Subscription / Conditional Execution Of Report
Hello everyone, Is there a way in order to execute a subscribed report based on a certain criteria? For example, let's say send a report to users when data exist on the report else if no data is returned by the query executed by the report then it will not send the report to users. My current situation here is that users tend to say that this should not happen, since no pertinent information is contained in the report, why would they receive email with blank data in it. Any help or suggestions will be much appreciated. Thanks, Larry
View Replies !
Using The &"IF EXISTS&" Conditional Statement
I'm trying to gather some user statistics based on 3 conditions. First I want to check if the referring querystring is already in the database. If not insert it into the db. Second, if the querystring is already in the database, then check if the ip-address of the user is already in the database. If it is, then check if the ip address was inserted today. If not, update the "refCountIn" field with +1. The problem lies in the third condition where we check if the ip-address was inserted today and if false, update the "refCountIn" field with +1 and if true, do nothing.Below is the code I have until now: 1 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 2 3 ' *** Declare the variables 4 Dim getStatCmd As String 5 Dim updStatCmd As String 6 7 Dim myRef As String = Request.QueryString("ref") 8 Dim myQueryString As String = Request.ServerVariables("QUERY_STRING").Replace("ref=", "") 9 Dim myRemoteAddr As String = Request.ServerVariables("REMOTE_ADDR") 10 Dim myHttpReferer As String = Request.ServerVariables("HTTP_REFERER") 11 12 Dim dtNow As Date = DateTime.Now 13 Dim dtToday As Date = DateTime.Today 14 15 ' *** Conditional INSERT command 16 getStatCmd = _ 17 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime = '" & dtToday & "') " & _ 18 "BEGIN " & _ 19 "SELECT 'This clickin has already been recorded!'" & _ 20 "END ELSE BEGIN " & _ 21 "SELECT 'Clickin recorded' " & _ 22 "INSERT INTO tblReferers(robotName, refIpAddress, refReferer, refTime) " & _ 23 "VALUES(" & _ 24 "'" + myQueryString + "'," & _ 25 "'" + myRemoteAddr + "'," & _ 26 "'" + myHttpReferer + "'," & _ 27 "'" + dtToday + "')" & _ 28 "END " 29 30 31 ' *** Conditional UPDATE command 32 updStatCmd = _ 33 "IF EXISTS(SELECT 'True' FROM tblReferers WHERE robotName = '" & myQueryString & "' AND refIpAddress = '" & myRemoteAddr & "' AND refTime <> '" & dtToday & "') " & _ 34 "UPDATE tblReferers " & _ 35 "SET refCountIn = refCountIn + 1, refTime = '" & dtNow & "' " & _ 36 "WHERE refIpAddress = '" & myRemoteAddr & "' AND robotName = '" & myRef & "'" 37 38 Dim insConnCmd As New SqlCommand(getStatCmd, New SqlConnection(connSD)) 39 Dim updConnCmd As New SqlCommand(updStatCmd, New SqlConnection(connSD)) 40 41 insConnCmd.Connection.Open() 42 insConnCmd.ExecuteNonQuery() 43 insConnCmd.Connection.Close() 44 45 updConnCmd.Connection.Open() 46 updConnCmd.ExecuteNonQuery() 47 updConnCmd.Connection.Close() 48 49 End Sub Anyone with an idea on how to solve this one? I think I need to write a subquery for the third condition, but I don't have a clue on how to handle this.Thanks in advance for your help!
View Replies !
TSQL Statement Extracting Data From One Table Through Another Table
Hi, I have 2 tables, MembersTemp and Organisations I'm trying to extract the organisation Name from the organisations table but am unsure of the sql statement to do this. Initiallt I only have the ExecID for the MembersTemp table MembersType table: ExecID 3013 OrganisationID 4550 Organisation table: ID 4550 (PK) Name "Microboff" Any ideas??
View Replies !
How Does &&"union/union All&&" Work Inside SQL Server?
Why the sequence different? select * from ( select id=3,[name]='Z' union all select 1,'G' union all select 2,'R' union all select 4,'Z' ) as t order by [name] --result: --------- --1 G --2 R --4 Z --3 Z select * from ( select id=3,[name]='Z' union select 1,'G' union all select 2,'R' union all select 4,'Z' ) as t order by [name] --result: ---------- --1 G --2 R --3 Z--changed --4 Z
View Replies !
Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]
Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005 1 -- Full Table Structure select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id 2 -- PK and Index select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id This sql is extracting some sort of the information about the structure of the sql server database[2005] I need a sql whihc will return the same result for sql server 2000
View Replies !
|
TRACKER
