|
I tried it but it doesn't seem to work. Also, I just read that in oracle stored procedures you shouldn't create the temp table in the procedure it should exist and then you may use it in the procedure.
but how can it exist in advance.
|
|
|
|
|
Oracle (PL-SQL) doesn't have temporary tables in the same way as SQL Server (T-SQL). Read up about Oracle Global Temporary Tables. These work in a different way from SQL Server #temp tables, but give you the same end result.
|
|
|
|
|
Hi, i have writeen below stored procedure. I will give input as one zipcode and querystr. If there is no record with given zipcode, it has to go for another query. But it is not going in second if condition. When i try to print the query always showing the first one.
--Exec Test '48019','t1.engl,t1.germ,t1.iris,t1.jewi,t1.scot,t1.wels'
ALTER PROCEDURE [dbo].[test]
(
@Zipcode Varchar(50),
@Querystr Varchar(200)
)
AS
Begin
SET NOCOUNT ON
DECLARE @Result VARCHAR(MAX)
Set @Result = 'SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_Origin WHERE Zip_Final =''' + @Zipcode + ''') AS t1 UNPIVOT (theValue FOR theCol IN (' + @Querystr + ')) AS u1 ORDER BY theValue DESC'
IF @Result IS NULL OR @Result = ''
Begin
Set @Result = 'Select Top 1 theCol from Tbl_Origin'
End
--Exec(@Result)
Print @Result
End
G. Satish
|
|
|
|
|
Good day Satish
Becacause of time i have, i will not write you a solution , but i will point to a problem
First you use one variable to do all things. Why do you create one variable that will hold the SQl statement and one to store the results
and evauate the result. i think you know what you are doing but you get confused by your code. declare another variable to hold the results after that you must test for only nulls and remove the quotes and try it like this
DECLARE @Result VARCHAR(MAX)
DECLARE @Final_Res VARCHAR(MAX)
Set @Result = (SELECT TOP 1 WITH TIES theCol FROM (SELECT * FROM Tbl_Origin WHERE Zip_Final =@Zipcode ) AS t1 UNPIVOT (theValue FOR theCol IN (@Querystr)) AS u1 ORDER BY theValue DESC)
IF @Result IS NULL
Set @Result = 'Select Top 1 theCol from Tbl_Origin'
else
select @Result
Hope this Helps
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
Hi! I need to do below note sure how can i design database as web application performance is a key to this
development scenario:
1. organisation adds up available fields (label and input field) that comes from synonym table
2. ability to add extra fields(label and input field)
synonym table as i think might have below cols not sure..
SID, SNAME, ORGID, ORGWGID, datatype
--------------------------------------
1, firstname, null, null, string - generic
2, lastname, null, null,string - generic
3, initialname, 1,1,string - orgid 1 specific
4, givenname,1,1,string
5,fname,2,2,string
6,surname,2,2,string
1. change synonym for existing default fields that will appear in the webform
2. add additional fields as per need for individual organisation (orgid=organisationId, orgwgid=organisation workgroup id)
please comment if my requirement is not clear. I am confused how should i design table as user might want to add extra form field i.e a label and textbox/checkbox/listbox/combo/moneyfield..all 27 datatypes available in sql. now I dont know how to do this as there will be about 20 organisation that might use this web application. thanks in advance.
|
|
|
|
|
I have a cunning plan!
Use two (or possibly more) tables, I haven't completely thought this out. 
First table for Generic data.
Second table:
OrganizationID
NameForAdditionalField
TypeForAdditionalField
Length...
etc
I hope that you get the idea. One table contains the 'required' fields and the other table(s) allow you to organise the requirements for each client.
Henry Minute
Do not read medical books! You could die of a misprint. - Mark Twain
Girl: (staring) "Why do you need an icy cucumber?"
“I want to report a fraud. The government is lying to us all.”
|
|
|
|
|
Trying to provide a backup "button" inside the C# Windows Forms application on a client PC that connects to a SQL Server database, I want to grab the resulting backup file of a call to the BACKUP DATABASE [^] T-SQL function.
Since the BACKUP DATABASE function takes a file path that is relative to the SQL Server itself, I see no reliable way to get the resulting backup file from the client.
My ideal way would be:
1.) Call the BACKUP DATABASE function from my client application.
2.) Specify to backup to a local file (or even better: a C#/.NET stream[^]).
3.) Capture the resulting BAK file from my client application.
Question: Is this possible to achieve?
(I will cross-post this message to the C# forum, since I am unsure whether this is more an ADO.NET topic)
Thanks
Uwe
|
|
|
|
|
On the link you included for the BACKUP command documentation, you'll see a section named "Backup History Tables". You can try reading those system tables - it looks like physical_name in the backupfile system table might have what you want.
Scott
|
|
|
|
|
Thank you, Scott!
I will try to investigate. The idea was to have the user do ZERO configuration, except specifying the connection string. Hopefully I will manage to do.
Cheers
Uwe
|
|
|
|
|
Good Day Uwe
Someone asked a very same questions weeks ago and i have given an answer. please search anything by me here on CP.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
|
I'm a bit confuse in following comparision
select * from table1 t1 join table2 t2 on t1.ID=t2.ID where t1.ID>5
vs
select * from (select * from table1 where ID>5) t1 join table2 t2 on t1.ID=t2.ID
the second query make sense that it will be faster as its filtering rows before join but I wanna know that does 'where' executes before 'join' (in first query condition) ?
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87֦ʻ6ϣN8ȤBcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKQUFK[M`UKs*$GwU#QDXBER@CBN%
R0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-iTV.C\y<pjxsg-b$f4ia> -----------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Try turning on the execution plan and examine the details. I doubt it makes any different at all as SQL interprets the query anyway.
Don't forget you can also have
Select *
From Table1, Table2
where Table1.something = 'value'
and table1.key = table2.key
I'm pretty sure the execution plan is the same but using the join style makes it more readable and therefore easier to support.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Good Morning Xmen W.K.
1)Select only the Column you want to display
2)The Second Query is a bad Query
3)Do you have indexes on the ID Field ?
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
why select only one column if I need all of 'em...I know in that case ID column will appear twice as it will from both tables but its just an example...
and why second is bad ?
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87֦ʻ6ϣN8ȤBcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKQUFK[M`UKs*$GwU#QDXBER@CBN%
R0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-iTV.C\y<pjxsg-b$f4ia> -----------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Xmen
i was wrong i misjudged your statements. The Queries are the same and the execution plan is the same in all parts.
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.somee.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/
|
|
|
|
|
no problem 
TVMU^P[[IGIOQHG^JSH`A#@`RFJ\c^JPL>;"[,*/|+&WLEZGc`AFXc!L
%^]*IRXD#@GKCQ`R\^SF_WcHbORY87֦ʻ6ϣN8ȤBcRAV\Z^&SU~%CSWQ@#2
W_AD`EPABIKRDFVS)EVLQK)JKQUFK[M`UKs*$GwU#QDXBER@CBN%
R0~53%eYrd8mt^7Z6]iTF+(EWfJ9zaK-iTV.C\y<pjxsg-b$f4ia> -----------------------------------------------
128 bit encrypted signature, crack if you can
|
|
|
|
|
Hi,
I have 4 tables in a cascading mode like this : 1)Countries --> 2)Chapionships --> 3) Teams --> 4) Coupons
Coupons table has 2 IDs (HomeTeamID and HostTeamID) that points to the Teams table.
It has also IDs for the tables (Countries and Chapionships)
All I want is to select the appropriate records from the Coupons Table, but instead of the foreing keys IDs I want the correspondence descriptions that these keys shows from their tables (ie Countries, Chapionships and Teams).
Is that possible ?
All I did until now is the query
SELECT * FROM Coupons LEFT JOIN (Countries, Chapionships, Teams)<br />
ON (Countries.CountryID=Coupons.CountryID AND Chapionships.ChapionshipID=Coupons.ChapionshipID AND ( Teams.TeamID=Coupons.HomeTeamID OR Teams.TeamID=Coupons.HostTeamID)) Where Date='2009-07-31' order by Time Asc;
but I get double rows for each record since there is an OR clause inside the ON. Any better solution ?
Regards
sdancer75
|
|
|
|
|
Use distinct or try to group.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
|
I am getting an exception thrown from my web app's call to a SQL SP.
The expecption says "incorrect syntax near '-'". The dash I have narrowed down to an argument passed to the stored procedure contains dashes, such as 992-123-123. The SP works fine if the argument passed is 992123123.
What is the best way of fixing this? In all likelyhood it is more important to keep the dashes in the entries in the columns. I think it is just my SP syntax, so here it is: @PACKAGE is the value that is giving issues, which is 999-999-999 style.
CODE
CREATE PROCEDURE dbo.sp_UpdateItemColumn
(
@PACKAGE nvarchar(255),
@COLUMN nvarchar(30),
@VALUE nvarchar(100),
@LASTCHANGEBY nvarchar(100)
)
AS
declare @sql nvarchar(1024)
set @sql=''
set @sql=@sql+'update tbl_swbom_checklist_items set '+@COLUMN+'='''+@VALUE+''' where package='''+@PACKAGE+''' '
--print @sql
exec sp_executesql @sql
GO
|
|
|
|
|
hi there,
please try to do below query. It is working perfect with the '-' value.
declare @sql nvarchar(1024)
set @sql=''
set @sql=@sql+'update tbl_swbom_checklist_items set ' + @COLUMN + '=' + '''' +@VALUE + ''' where @PACKAGE='''+ @PACKAGE +''''
exec (@sql)
Regards
|
|
|
|
|
I have a Tab control with 15 tabs. I am using the TabCtl.Value in Select statements to control the ListBoxes, Reports, etc. for each tab. It occurred to me that I could simplify this greatly by defining a Variant Array of the ListBox names and report names. Then I could simply use array(TabCtl.Value) instead of the Select Statement details.
The problem is that I cannot see how to make the array(TabCtl.Value) into a Control name that accepts property or method references. It seems like this should be simple, but I have tried a few things with no success.
I am using Access 2007 on Vista Bus 32. Here is a sample of my code: (I have used the same names for the Queries, ListBoxes, Reports, etc. as a way of maximizing the usefulness of the one array and as an aid to reducing misspellings during coding. I find this less confusing than a lot of similar names.)
'An Array of Query Names to use as the RowSource for A Tab ListBox.
Dim lstFindRptNames As Variant
lstFindRptNames = Array("lstByCusName", _
"lstByContact1", _
"lstByContact2", _
"lstByContact3", _
...
"lstByProspect", _
"lstByCustomer")
'Populate the ListBox by referencing a Query of the same name as the RowSource
lstFindRptNames(TabCtl0.Value).RowSource = lstFindRptNames(TabCtl0)
This fails because Access does not seem to substitute the String, in context, with the Control of the same name. So I tried to help it out with making a Control with a DIM first, I also tried a Generic Object. Neither of these lead to a solution.
In several other languages, other than VBA, this is a non-brainer. What am I missing?
"Coding for fun and profit ... mostly fun"
|
|
|
|
|
Assuming you want a solution for VB.NET, I would hold the references to the ListBoxes in a generic List (or even a Dictionary; not really an array), and most certainly not the names.
You then can use indexing, and even for each
In VBA, which I haven'tused for years, I would hope and use an array of ListBoxes.
BTW: you're in the wrong forum, this isn't a database question.
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
|
Thanks for the hint. Not sure which Forum is correct.
I tried to use the References Idea. It does not seem to apply or I am not clear on its use in this case. I see it is a way to get at Tools-> References. Tools-> References includes a reference to my open project. But I can get to these objects directly any way.
Can you offer a snippet?
I am also trying:
TabCtl0.Pages(TabCtl0.Value).Controls but it only has count and item as properties and no methods.
Hmmm...
"Coding for fun and profit ... mostly fun"
|
|
|
|
|
Hi,
I still don't know which language you want: VB, VB.NET, VBA.
Anyway they all belong in the "Visual Basic"forum.
I don't have any VBx snippets handy right now (and almost none most of the time, I'm more of a C# kind of guy).
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get.
Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
