|
Hi
I am trying to convert an Oracle query to SQL. But i am totally confused thinking how to proceed. For some of the oracle packages , am not able to find an equivalent SQL function
Here is my Oracle query
cursor c_fields(c_xml XMLTYPE) is
select DBMS_XMLGEN.convert(extract(value(x), '//field/@name').getstringval(),1) FLD_NAME,
extract(value(x), '//field/@resultCol').getstringval() RESULT_COL,
extract(value(x), '//field/fieldConfiguration/@isAttachmentAllowed').getnumberval() AllOW_ATTACHMENT,
extract(value(x), '//field/@key').getnumberval() FLD_ID
from TABLE(XMLSEQUENCE(EXTRACT(c_xml, '//table/field'))) x;
I need to convert this to SQL query. In SQL we dont have parameterized cursor concepts. However to some extent we can achieve the functionality by using "WHERE" condition. But in this query, am not able to trace out how can i write the equivalent SQL functionality since "FROM" part itself is using the parameterized cursor.
Please help.
Thanks
Arudya
|
|
|
|
|
My first choice would be to refactor the database unless that code is touched very seldom. That sort of meta data solution is very inefficient.
But after that I would use dynamic sql. You create a varchar with equivalent sql in it, specifically the extracted table name. I would probably extract the rest as well.
Then you use 'execute' to execute the varchar.
|
|
|
|
|
Hi, I am wondering if there is a way of proofing a stored procedure in terms of the datetime localisation. For example, in the C# we may use the web.config or machine.config to set the application's localised date settings:
globalization uiCulture="en" culture="en-GB">
This helps when we do a date comparison in a static class of ours to return a certain rating factor.
Is there an equivalent way of us checking whether or not the date we pass into the stored procedure is in e.g. UK format and will be compared against our dates in the UK format too? We have 2 datetime columns, DateEffectiveFrom and DateEffectiveTo in our table and we use those dates to compare with the date passed in to return the relevant data.
Thanks
|
|
|
|
|
Just to clarify one thing: if your columns are datetime columns, why do you need to worry about date formats? You can just compare one datetime with another.
Is the parameter to the stored proc being passed as a string/varchar and you need to figure out what format it is in order to do the comparison?
|
|
|
|
|
Hi David, the parameter will be a datetime. I need to find which date range this falls in.
For example, I pass in the date 11/11/2010 and the columns are:
ID StartEffDate EndEffDate Rate
1 01/01/2000 31/12/2003 1.05
2 01/01/2004 31/03/2011 1.15
3 01/04/2011 01/01/2012 1.25
I need to return the appropriate Rate, e.g. in this case 1.15
|
|
|
|
|
date formatting is a human thing, it does not relate to databases at all, unless you make a mistake. What you should do is define datetime fields, and pass DateTime SQLParameters, that avoids all datetime formatting issues on the DB side; obviously you still have to deal with the datetime formatting in the GUI, however that is the only place it should matter (and there you should follow the system's regional settings).
|
|
|
|
|
Luc's answer is still valid as the correct way of doing things, but if you want to pursue the hard way you can start looking here[^] for SQL Server.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Hi,
Can anyone help me with small sample for :
How to compare same field in two tables in sql server 2005 using C#
thanks
|
|
|
|
|
As I getting, you need to get value from two table based on same value. if so then Join Will be better option for your query.
Joins allow you to view data from related table in a single result set.
you can use any one of Three way of join.
Inner Join,
Outer Join,
Left Join,
Move Google for More Information
|
|
|
|
|
cancerion wrote: compare same field in two tables
Your question confusing little bit. Based on my guess you may use Alias.(I think you mentioned Same field like same field name in two tables)
SELECT A.*, B.* FROM TableA AS A, TableB AS B WHERE A.Field1 > B.Field1
If not then let us know & mention clear details in your question.
thatraja |Chennai|India|
Brainbench certifications
Down-votes are like kid's kisses don't reject it
Do what you want quickly because the Doomsday on 2012
|
|
|
|
|
I'm new in the database world...
I want to know if its possible to add image ( simple bitmap or jpeg ) to the database ?
If this is possible => how can i get and set the image from C# code ?
Thanks for the help.
|
|
|
|
|
Hi,
There is an "image" data type in SQL Server which is basically a binary datatype. http://msdn.microsoft.com/en-us/library/ms187993.aspx[^]
Storing binary inside a database is generally strongly advised against though. Before you do that you might want to check if using the filesystem could be a better solution.
|
|
|
|
|
Yes you can insert image or retrieve it from your Database by using BLOB(binary Large object) approach in which an image file is convert into Binary data which is compatible to store data in Database.
Google has a lots of resource for this purpose. whatever i suggest you to move for this given link
Save An Image Into SQL Server 2000 Database[^]
|
|
|
|
|
Tony Pottier wrote: Storing binary inside a database is generally strongly advised against though
I disagree on the "generally strongly" advice.
Manageability, consistency, security, reliability, portability are all reasons for storing binaries in the database.
Storing binaries in the database means that you have db-enforced referential integrity, one point of entry for the backup, one place to set security...
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
you can also download the given sample code to learn how to store and retrieve image from SQL server.
Click on the given link to Download
Click me[^]
|
|
|
|
|
I do every day differential backup, transaction log and shrink file, weekly full backup.when i restore database, it show this message:
Processed 6536 pages for database 'project2', file 'project2' on file 1.
Processed 2 pages for database 'project2', file 'project2_log' on file 1.
RESTORE DATABASE successfully processed 6538 pages in 12.534 seconds (4.273 MB/sec).
Processed 0 pages for database 'project2', file 'project2' on file 1.
Processed 4 pages for database 'project2', file 'project2_log' on file 1.
RESTORE LOG successfully processed 4 pages in 0.048 seconds (0.544 MB/sec).
Msg 4312, Level 16, State 1, Line 3
This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
Msg 3013, Level 16, State 1, Line 3
RESTORE LOG is terminating abnormally.
Msg 4312, Level 16, State 1, Line 4
This log cannot be restored because a gap in the log chain was created. Use more recent data backups to bridge the gap.
Msg 3013, Level 16, State 1, Line 4
RESTORE LOG is terminating abnormally.
Msg 3136, Level 16, State 1, Line 5
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.
how can i solve this problem.
|
|
|
|
|
First do not shrink the file.
|
|
|
|
|
anyone knows where can I find the Lite Edition of EMS SQL for PostgreSQL? I am unable to fine the download link!
Thanks
|
|
|
|
|
http://www.sqlmanager.net/products/postgresql/manager
|
|
|
|
|
why this code returns all rows although I specified the condition? how to correct?
CREATE OR REPLACE FUNCTION fn_get_resume_details2(resume_id integer)
RETURNS SETOF record AS
$$select resume_id, resume_title, objective_title, objective_text, summary_title, summary_text from resume_details where resume_id = resume_id;
$$LANGUAGE sql;
</pre>
|
|
|
|
|
In your where clause, resume_id = resume_id, that is the same as saying where 1 = 1. It will be true for all rows. I'd recommend changing the name of your parameter to be different from the column name.
CREATE OR REPLACE FUNCTION fn_get_resume_details2(in_resume_id integer)
RETURNS SETOF record AS
$$select resume_id
,resume_title
,objective_title
,objective_text
,summary_title
,summary_text
from resume_details
where resume_id = in_resume_id;
$$LANGUAGE sql;
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
why I am getting the above error when trying to run this code with PostgreSQL on C# .NET application? how can I fix it?
CREATE OR REPLACE FUNCTION fn_get_resume_details2(resume_id integer)
RETURNS SETOF record AS
$$select resume_id, resume_title, objective_title, objective_text, summary_title, summary_text from resume_details where resume_id = resume_id;
$$LANGUAGE sql;
</pre>
<div class="signature">
<div class="modified">modified on Monday, November 1, 2010 9:55 AM</div></div>
|
|
|
|
|
|
Please do not post the same message in multiple forums; choose one and stick to it. You have been a CP member long enough to know the protocol.
Just say 'NO' to evaluated arguments for diadic functions! Ash
|
|
|
|
|
I have a clinic management software on C# WinForm and planning to have an online interface for eServices. I am planning to move from SQL Server because of the cost and the Express Edition is not enough capacity..
So for such project, if you are the decision make then which database you'll move to? PostgreSQL or MySQL? or maybe another FREE db?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
