|
SELECT MSGNUM, LISTAGG(DESCR, ',') WITHIN GROUP (ORDER BY LVL) AS MSGDESCR
FROM (SELECT DISTINCT MSGNUM, LEVEL LVL
,SUBSTR(NVL2(MSGNUM, MSGNUM || ',', NULL)
,(CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
,INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL)
- (CASE WHEN LEVEL > 1 THEN
INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL - 1) + 1
ELSE LEVEL END)
) MSGNUM_TO_ID
FROM
(SELECT SYSDATE - 1 STARTDATE, '1,2,3' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 2 STARTDATE, '4' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 3 STARTDATE, '5,6' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 4 STARTDATE, '' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 5 STARTDATE, '4,2,5' MSGNUM FROM DUAL UNION
SELECT SYSDATE - 6 STARTDATE, '' MSGNUM FROM DUAL)
CONNECT BY INSTR(NVL2(MSGNUM, MSGNUM || ',', NULL), ',', 1, LEVEL) != 0
) T1
,(SELECT 1 ID, 'ONE' DESCR FROM DUAL UNION
SELECT 2 ID, 'TWO' DESCR FROM DUAL UNION
SELECT 3 ID, 'THREE' DESCR FROM DUAL UNION
SELECT 4 ID, 'FOUR' DESCR FROM DUAL UNION
SELECT 5 ID, 'FIVE' DESCR FROM DUAL UNION
SELECT 6 ID, 'SIX' DESCR FROM DUAL) T2
WHERE T1.MSGNUM_TO_ID = T2.ID
GROUP BY MSGNUM
;
Thanks & Regards,
Niral Soni
|
|
|
|
|
We are just switching over to SQL Server 2012 from SQL Server 2008 and we are having some trouble with an XML Source (from a URL dontcha know).
It works fine on 2008, but always reports that it can't find the file on 2012 -- even though it is able to find the file to generate an XSD.
Even on the same server under the same user.
The file exists and the user has access to it.
Has anyone else seen this? Any ideas? A quick search didn't turn up any clues.
|
|
|
|
|
Hi all,
I'm relatively new to sql and need help to accomplish what should be a straight forward task.
I need to insert record templates in a database table. The records are identical except for IDfield which is a sequence from n to n+count. I can't use Autoincrement because I want to be able to enter different templates where I can define the base index n.
I'm currently using transactions to insert records in batches but need a faster method using a single SQL statement.
Example:
2000 Apple Fruit Granny Smith
2001 Apple Fruit Granny Smith
2002 Apple Fruit Granny Smith
..
..
2900 Apple Fruit Granny Smith
A different fruit may have a base index of 6000 and so on.
I would appreciate any help you can give me.
Thanks.
|
|
|
|
|
Not sure if MS Access supports while statement but you can try something like this... it works in SQL Server
declare @counter int
declare @max int
SET @counter = 0
SET @max = 12
while @counter < @max
begin
INSERT INTO tableName (FruitCodeName) VALUES(CONVERT(nvarchar(3), @counter) + 'Apple Fruit Granny Smith' )
SET @counter = @counter + 1
end
|
|
|
|
|
You Can't Do That With One Statement
I Do Wonder What Exactly Your Code Is. You Should Be Using A Parameterized Statement, In Which Case You Need Only Set The One Parameter Value And Execute The Statement Again.
|
|
|
|
|
Thanks for your input. I figured how to use AddWithValue but for such a trivial task I was hoping to avoid a loop in my application and let the database engine do the work for me.
Thanks
|
|
|
|
|
sobelhaj wrote: let the database engine do the work for me
That ain't gonna happen.
|
|
|
|
|
sobelhaj wrote: I can't use Autoincrement because I want to be able to enter different templates where I can define the base index n.
Well technically his design is wrong, the ID field should be stupid bejond identifying the record. What you are calling an ID field is actually your CODe field and may be edited by the users. What happens if they want to change the code for Granny Smith Apples to 7 series?
You could always write a scrip/code for the loop passing in the start series and the product. You cannot avoid a loop somewhere.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
sobelhaj wrote: I would appreciate any help you can give me.
Based on your description - a redesign would probably be the best thing.
Why doesn't your record just have the following for where the last value is the count?
<one id=""> Apple Fruit Granny Smith 2900
|
|
|
|
|
I have a table named(information)tha has 3 columns(id,fnam,lnam)
I know that I can select an special row by
select form information (id,fname,lname)WHERE id="1"
string lname="last name witch it's id is "1"";
plz help me!!!
|
|
|
|
|
You're probabyl going to be just fine with this
SELECT id,fname,lname
FROM information
WHERE id = 1
|
|
|
|
|
ok,but how can I access the fname as string ;
my code is like this:
<script>
protected void page_Load(object sender, EventArgs e)
{
Id.Value = (string)Session["field3"];
}
protected void salam(object sender,EventArgs e)
{
name.value="the row's name witch it's id is session["field3"]"
}
</script>
<html>
<input id="name"/>
<input id="Id"/>
</html>
do you undrestand me?
|
|
|
|
|
Not really  Can you give me some more details on what specifically are you trying to do?
|
|
|
|
|
ok,I put all of codes here!
<pre><%@ Page Language="C#" AutoEventWireup="true" CodeBehind="authenticate1.aspx.cs" Inherits="hokm.Admin.authenticate1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void salam(object sender, EventArgs e)
{
Session["field1"] = sh1.Text;
Response.Redirect("editinformation.aspx");
}
</script>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server" enctype="multipart/form-data" >
<div dir="rtl">
<table>
<tr class="style1">
<td width="3%" bgcolor="#FF6666">
Please enter your id number
<br />
<br />
<asp:TextBox name="shomare" ID="sh1" runat="server"/>
<br />
<br />
<asp:Button ID="Button1" Width="60" runat="server" Text="confirm" OnClick="salam" />
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
so I put the Id in session["field1"];
and in the editinformation.aspx
protected void page_Load(object sender, EventArgs e)
{
sh.Value = (string)Session["field3"];
}
<html>
<body>
<table>
<td colspan="5">
ID number:
<input name="shomare karmandy" disabled="disabled" id ="sh" runat="server" />
</td>
<tr>
<td>
name:
<input name="name" id="sh2" disabled="disabled" runat="server"/>
</table>
</body>
</html>
you see that I have filled the id textbox with a value,and I want to do this with name input,but I dont know how should I use the database to access the name with this Id?????
|
|
|
|
|
Hi all
I have a database named information and every row in this database has a unique id!
I want to update the row with the special id(that I have stored that id,for example it is in "ID" variable), but I dont know how should I use the select and update command to do this!
plz help me!
|
|
|
|
|
Start by providing the statements you are trying to use; others can only guess at what the table looks like.
|
|
|
|
|
Provide more details like table structure & sample data
|
|
|
|
|
The basic update statement is
update <yourtable>
set <yourfield>=<yourvalue>
where <conditional>
You probably want something like "where ID = 5"
Without more details, this is the best I can do to help.
|
|
|
|
|
ok,for example I have an information table with columns(id,fname,lnam) and I want to access the (fname,lnam) by Id,because I want to use them as string;
I know that I should the select command Like this:
select(id,fname,lname)WHERE id="" but I dont know how to access the fnam,lname with this Id as string!
|
|
|
|
|
BEGIN TRANSACTION
CREATE TABLE Person
(
ID INT IDENTITY(1,1) PRIMARY KEY,
FIRSTNAME VARCHAR(50),
LASTNAME VARCHAR(50)
)
INSERT INTO Person VALUES ('John', 'Doe'), ('Code', 'Project')
SELECT *
FROM Person
UPDATE Person
SET FIRSTNAME = 'Peter'
WHERE ID = 1
SELECT *
FROM Person
ROLLBACKExecute in SQL-Management Studio. You'll see two tables; one before, and one after the update. You will need a good book that introduces you to some basic SQL-constructions.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi all
I have a problem with database.I work on " add new user.aspx "file witch add a new user to the table(Authenticate)but it doesnt work! this is my code in add new user.aspx file:
<script runat="server">
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
string connectionstr=@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\EHSAN\Documents\Visual Studio 2010\Projects\The Club\The Club\App_Data\Database1.mdf;Integrated Security=True;User Instance=True";
con.ConnectionString=connectionstr;
string sqlstring;
sqlstring ="insert into Authenticate (username,password)VALUES ("+ CreateUserWizard1.UserName+","+CreateUserWizard1.Password+")";
System.Data.SqlClient.SqlCommand objcommand = new System.Data.SqlClient.SqlCommand(sqlstring, con);
Response.Redirect("~/Login.aspx");
}
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="cphMain" Runat="server">
<asp:CreateUserWizard ID="CreateUserWizard1" Runat="server"
oncreateduser="CreateUserWizard1_CreatedUser">
</asp:CreateUserWizard>
</asp:Content>
I execute that but it doesnt add any new row to my table!!!!
plz help me!
|
|
|
|
|
I suspect you need to put apostrophes around the values, but the better (much much better) solution is to use a parameterized command.
|
|
|
|
|
thank you so much
but I dont know how should I use parameter!for example I have the CreateUserWizard1.UserName variable but I dont know how should use it as parameter! can you say me how should I do it?
|
|
|
|
|
ur missing objcommand.ExecuteNonQuery(); and parenthesis in code
try this one
<script runat="server">
protected void CreateUserWizard1_CreatedUser(object sender, EventArgs e)
{
System.Data.SqlClient.SqlConnection con = new System.Data.SqlClient.SqlConnection();
string connectionstr=@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\EHSAN\Documents\Visual Studio 2010\Projects\The Club\The Club\App_Data\Database1.mdf;Integrated Security=True;User Instance=True";
con.ConnectionString=connectionstr;
string sqlstring;
sqlstring ="insert into Authenticate (username,password)VALUES ('"+ CreateUserWizard1.UserName+"','"+CreateUserWizard1.Password+"')";
System.Data.SqlClient.SqlCommand objcommand = new System.Data.SqlClient.SqlCommand(sqlstring, con);
Con.Open();
objcommand.ExecuteNonQuery();
Con.Close();
Response.Redirect("~/Login.aspx");
}
</script>
<asp:Content ID="Content1" ContentPlaceHolderID="cphMain" Runat="server">
<asp:CreateUserWizard ID="CreateUserWizard1" Runat="server"
oncreateduser="CreateUserWizard1_CreatedUser">
</asp:CreateUserWizard>
</asp:Content>
|
|
|
|
|
thank you so much,it works
I had 2 mistake:
1:in insert command
2:in execute command
thank you so much again
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
