Thursday, April 25, 2013

[T-SQL] how pick value from database on sql server2000?

[T-SQL] how pick value from database on sql server2000?

how pick value from database on sql server2000?

Posted: 24 Apr 2013 11:32 PM PDT

Hai friends, I m creating one sample application its has two dropdown boxes ddl1 and dd2if i choose dd1 in air to reflect value of economy ,business ll show on dd2how to create the code for that.

Recursive CTE

Posted: 25 Apr 2013 01:03 AM PDT

Hi,I have a query below I'm doing recursive CTE.The logic behind it is, Client(John) can be linked another Client(Mary). And Mary can also be linked to another Client(Smith). All this clients has Client Numbers. Now when a user input a Client's Number let's say (1-23) which is John's, the query supposed to return all Clients linked to John, and if those Clients are linked to other Clients, show them as well. I hope it makes sense.Now the problem I have is, when I input Mary's client Id, which is (1-33), I get all clients linked to her and other clients in the linking chain, except for one client linked to John(1-23), that client is Diana(1-14)I hope I've been able to explain this very well. Can one spot out my error and help me.Below is my code to attempt it:--create PR_LINK_INV_HST temp table--Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))--insert into PR_LINK_INV_HST temp table--Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-14')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-33')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana',null)Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-19')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith',null)Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-16')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-17')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo',null)Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-50','Josh','1-23')DECLARE @CLIENT_ID VARCHAR(15)SET @CLIENT_ID = '1-33' -- This CTE search for the linked client(child)--;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])AS(SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 1FROM #PR_LINK_INV_HST WHERE LINK_CLIENT_ID = @CLIENT_IDUNION ALLSELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1FROM #PR_LINK_INV_HST HST JOIN pr_linked LNKON LNK.CLIENT_ID = HST.LINK_CLIENT_ID)SELECT * INTO #RESULTSFROM pr_linked-- This CTE search upwards for the linked client(parent)--;WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])AS( SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0 FROM #PR_LINK_INV_HST WHERE CLIENT_ID = @CLIENT_ID UNION ALL SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] - 1 FROM #PR_LINK_INV_HST HST JOIN pr_linked LNK ON HST.CLIENT_ID = LNK.LINK_CLIENT_ID)INSERT INTO #RESULTSSELECT * FROM pr_linked-- display resultSELECT *FROM #RESULTSdrop table #RESULTSdrop table #PR_LINK_INV_HST

Need help on creating Left outer Join

Posted: 24 Apr 2013 06:30 PM PDT

Hi All,Need your help to create a Left Out join on 2 tables, which will be based on the Period Code.Below are the tables, not that challenge is that, we need to get the Max of the Period From table 2 which should be Less than or equal to the Table 1 Period - 6 Month.Example: Row ADS 01.2013 should have the row in table 2 with ADS and Period in (01.2013,12.2012,11.2012,10.2012,09.2012,08.2012)Table1:Name PeriodABD 01.2013BCD 02.2013ADS 03.2013AZD 04.2013Table 2:Name PeriodABD 02.2013ABD 08.2012BCD 02.2013ADS 04.2012Please need help on the same....!AZD 04.2013

Delete other than top 2 rows for each foreign key element in table.

Posted: 24 Apr 2013 10:04 PM PDT

I have a table like that MemberId(F.K) ! Event ! Event Date1 'Test Event1' "2012-03-20 05:39:51"1 'Test Event1' "2012-03-21 05:39:51"1 'Test Event1' "2012-03-22 05:39:51"3 'Test Event3' "2012-03-23 05:39:51"2 'Test Event2' "2012-03-24 05:39:51"2 'Test Event2' "2012-03-19 05:39:51"1 'Test Event1' "2012-03-23 05:49:51"3 'Test Event3' "2012-03-23 05:49:51"4 'Test Event4' "2012-03-27 05:39:51"3 'Test Event3' "2012-03-21 05:39:51". . . . .and what i require is to keep only two latest events for each members and to delete the rest ones.i.e.1 'Test Event1' "2012-03-23 05:49:51"1 'Test Event1' "2012-03-22 05:39:51"2 'Test Event2' "2012-03-24 05:39:51"2 'Test Event2' "2012-03-19 05:39:51"3 'Test Event3' "2012-03-23 05:39:51"3 'Test Event3' "2012-03-23 05:49:51"4 'Test Event4' "2012-03-27 05:39:51"I have an idea of doing like that by using CTE or by using RowNumbers and Partitions but i have to avoid the power of DBMS , Cursors and to write a pure SQL for that , any help will be appreciated.Thanks.

The maximum recursion 100 has been exhausted

Posted: 24 Apr 2013 09:57 PM PDT

HiI have this query below, CTE code. and I get this error: The statement terminated. The maximum recursion 100 has been exhausted before statement completion.Can someone please look at the query and see what am I doing wrong and help.Create TABLE #PR_LINK_INV_HST (CLIENT_ID varchar(15), NAME varchar(15), LINK_CLIENT_ID varchar(30))Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-14')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-14','Diana','1-23')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-33')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-23')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-19')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-19','Thabo','1-17')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-16','Smith','1-33')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-16')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-33','Mary','1-17')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-17','Pope','1-33')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-50','Josh','1-23')Insert into #PR_LINK_INV_HST (CLIENT_ID,NAME,LINK_CLIENT_ID) values('1-23','John','1-50')DECLARE @CLIENT_ID VARCHAR(15)SET @CLIENT_ID = '1-23' -- This CTE search upwards for the linked client(child);WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])AS(SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 1FROM #PR_LINK_INV_HST WHERE LINK_CLIENT_ID = @CLIENT_IDUNION ALLSELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] + 1FROM #PR_LINK_INV_HST HST JOIN pr_linked LNKON LNK.CLIENT_ID = HST.LINK_CLIENT_ID)SELECT * INTO #RESULTSFROM pr_linked--select * from #RESULTS-- This CTE search upwards for the linked client(parent);WITH pr_linked(CLIENT_ID, NAME, LINK_CLIENT_ID, [LEVEL])AS( SELECT CLIENT_ID, Name, LINK_CLIENT_ID, 0 FROM #PR_LINK_INV_HST WHERE CLIENT_ID = @CLIENT_ID UNION ALL SELECT HST.CLIENT_ID, HST.Name, HST.LINK_CLIENT_ID, LNK.[LEVEL] - 1 FROM #PR_LINK_INV_HST HST JOIN pr_linked LNK ON HST.CLIENT_ID = LNK.LINK_CLIENT_ID)INSERT INTO #RESULTSSELECT * FROM pr_linked-- display resultSELECT *FROM #RESULTSdrop table #RESULTSdrop table #PR_LINK_INV_HST

What is "cost"?

Posted: 24 Apr 2013 04:31 AM PDT

When the Query Analizer estimates the cost of a query as 5.2837628, is that 5.2837628 of something? Or is it just a number to use for comparison to the cost of other versions of the query?

Max or Top 1 more efficient

Posted: 24 Apr 2013 04:12 AM PDT

I have a table with a month value and year value both ints.I need two things, the latest date and the date 24 months before.I came up with these to get the latest date and trying to find the most efficient.[code]SELECT MAX(CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' + CAST(YearValue AS varchar))) AS ValueDateFROM dbo.myTable[/code]This gets me a plan ofStream Aggregate (Aggregate) -Cost 7%Compute Scalar - Cost 1%Clustered Index Scan - Cost 91%The other option is:[code]SELECT TOP 1 CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' + CAST(YearValue AS varchar)) AS ValueDateFROM dbo.myTableORDER BY ValueDate DESC[/code]Sort (top N Sort) -Cost 92%Compute Scalar - Cost 0%Clustered Index Scan - Cost 8%Which one is the most efficient? The costs seem equivelant.The whole query (depending on which select I use) would be:[code]DECLARE @ValueDate smalldatetimeSELECT @ValueDate = (SELECT TOP 1 CONVERT(smalldatetime, CAST(MonthValue AS varchar) + '/01/' + CAST(YearValue AS varchar)) AS ValueDateFROM dbo.myTableORDER BY ValueDate DESC)SELECT DATEADD(month, -23, @ValueDate)[/code]Thanks,Tom

Dynamic query providing wrong output

Posted: 24 Apr 2013 01:52 AM PDT

I have a table data_profile_stats which contains all possible information about all the tables and columns in the DB. I have a cursor running over data_profile_stats which goes line by line, picks up a table name, column name and creates a dynamic query. This query is used to count number of blank values in that column. For some reason, instead of an INTEGER number, I am getting '*' as output which is wrong. Here is the query:SET @QUERY='SELECT @BLANK_COUNT= COUNT(*) FROM ['+@DB_NAME+'].['+@SCHEMA_NAME+'].['+@TABLE_NAME + '] WHERE ['+@DB_NAME+'].['+@SCHEMA_NAME+'].['+@TABLE_NAME+'].['+@COLUMN_NAME + ']='+''''+''''EXEC SP_EXECUTESQL @QUERY, N'@BLANK_COUNT INT OUTPUT', @BLANK_COUNT=@BLANK_COUNT OUTPUTSET @QUERY='UPDATE [dbo].[Data_Profile_Stats] SET BLANK_COUNT='+''''+CAST(@BLANK_COUNT AS VARCHAR(5))+''''+' WHERE [DB_NAME]='+''''+@DB_NAME+''''+' AND SCHEMA_NAME='+''''+@SCHEMA_NAME+''''+' AND TABLE_NAME='+''''+@TABLE_NAME+''''+' AND COLUMN_NAME='+''''+@COLUMN_NAME+''''exec sp_executesql @QUERY Example of queries are:SELECT @BLANK_COUNT= COUNT(*) FROM [TEST].[dbo].[TBL_1] WHERE [TEST].[dbo].[TBL_1].[TCode]=''UPDATE [dbo].[Data_Profile_Stats] [red][b]SET BLANK_COUNT='*'[/b][/red] WHERE [DB_NAME]='TEST' AND SCHEMA_NAME='dbo' AND TABLE_NAME='TBL_1' AND COLUMN_NAME='TCode'As you can see, Blank_Count is getting set to *..can someone please help me out here

Is it possible to run a query to return the members of a database User group?

Posted: 24 Apr 2013 01:22 AM PDT

Is it possible to run a query to return the members of a database User group?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog