I created a table and inserted 3 rows. Then I added a new column using alter. How can I add values to the column without using any null values?
mardi 4 août 2015
In SQL, How to add values after add a new column in the existing table?
Verification of Result in ESQL
Here's the situation: I run a query that gives me one or many rows as a result in an array.
Like:
SET db = PASSTHRU('SELECT GUID,CONTROLNBR FROM TRANSACTION WHERE GUID > ? AND CONTROLNBR > ?' values(maxGuid,maxControlNbr);
That works fine but I want to verify the following:
Any result that has duplicate CONTROLNBR's must have the same GUID
So if my result set has something like this:
CONTROLNBR | GUID
--------------------
5 | 123abc
5 | 123abc
this is entirely valid, however this I need to return an error on:
CONTROLNBR | GUID
--------------------
5 | 123abc
5 | abc123
I'm not sure the best way to test the result like this in ESQL/SQL.
can't make a trigger update only the relevant rows
I'm working on a school project and my trigger gives me a hard time.
Its' purpose is to update the Rating field of an updated Product, but it updates all rows in Products instead.
CREATE TRIGGER Update_Rating
ON dbo.Reviews
FOR Insert
as
Update dbo.Products
set Rating=(Select [avarage_rating]=avg(r.Rating)
From dbo.Reviews as r join inserted on r.ItemNumber = inserted.ItemNumber
where r.ItemNumber = Inserted.ItemNumber)
Your help is much appreciated
ROW_NUMBER query
I have a table:
Trip Stop Time
-----------------
1 A 1:10
1 B 1:16
1 B 1:20
1 B 1:25
1 C 1:31
1 B 1:40
2 A 2:10
2 B 2:17
2 C 2:20
2 B 2:25
I want to add one more column to my query output:
Trip Stop Time Sequence
-------------------------
1 A 1:10 1
1 B 1:16 2
1 B 1:20 2
1 B 1:25 2
1 C 1:31 3
1 B 1:40 4
2 A 2:10 1
2 B 2:17 2
2 C 2:20 3
2 B 2:25 4
The hard part is B, if B is next to each other I want it to be the same sequence, if not then count as a new row.
I know
row_number over (partition by trip order by time)
row_number over (partition by trip, stop order by time)
None of them will meet the condition I want. Is there a way to query this?
Sql Server Pivoting
Trying to convert the row value to column (PIVOT) of the given table.
S.No ID Column2 Column3
1 1 1 Firstname
2 1 2 MiddleName
3 1 14 ContactNumber
4 2 1 Firstname
5 2 14 ContactNumber
6 3 14 ContactNumber
7 3 2 MiddleName
I want like below for ID 1
Clumn2 1 2 14
Column3 FirstName MiddleName LastName
please help me to solve it. Thanks
how to change this sql statement so it returns one row per widget
I have the following sql query:
select O.name, O.asset_no, A.name as attr_name, AV.string_value, D.dict_value INTO OUTFILE '/tmp/networkchassis_1503_detailed.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ',' ESCAPED BY '"' LINES TERMINATED BY '\r\n'
FROM Object as O
left join AttributeMap as AM on O.objtype_id = AM.objtype_id
left join Attribute as A on AM.attr_id = A.id
left join AttributeValue as AV on AV.attr_id = AM.attr_id and AV.object_id = O.id
left join Dictionary as D on D.dict_key = AV.uint_value and AM.chapter_id = D.chapter_id
left join Chapter as C on AM.chapter_id = C.id WHERE O.id IN ('5261', '5262', '5263', '5461', '5268', '5271', '22469', '5284', '14418', '5288', '5291', '5292', '5294', '5295', '20629', '20630', '5296', '5297', '5307', '5238', '22425', '22426', '5315', '5316', '22429', '22430', '5317', '22431', '22427', '22428', '5320', '5321', '5325', '5326', '13373', '5329', '14671', '14672', '22432', '22433', '8999', '648', '393', '394', '471', '395', '396', '1688', '1689', '268', '269', '5582', '5583', '5584', '5585', '5586', '5587') AND A.name in ("FQDN", "HW Type") ORDER BY O.name;
This returns two rows, per widet, one with FQDN, and the other for HW Type, like so:
+-----------+----------+-----------+--------------------------------+--------------------------------+
| name | asset_no | attr_name | string_value | dict_value |
+-----------+----------+-----------+--------------------------------+----------------------------------------+
| widget1 | 1026857 | HW Type | NULL | HP |
| widget1 | 1026857 | FQDN | widget1.domain.net | NULL |
I'd like to change the query so that I get one row, with both attributes specified. Something like this...
+-----------+----------+-------------------+----------------+--------------------------------+
| name | asset_no | fqdn | hw_type | string_value | dict_value |
+-----------+----------+-----------+--------------------------------+----------------------------------------+
| widget1 | 1026857 | widget2.domain.net| HP |
Syntax error (from clause) in query expression in statement
string sqlStatement = "SELECT Orders.[ID], Orders.[Checkintime], Orders.[RoomPrice], Orders.[OrderNo], Particulars.FirstName, Particulars.LastName FROM Orders, where Checkintime between '" + dateOnly + "' and '" + endDateOnly + "', Particulars;";
I tried using this statement to select information from my database but this statement has syntax errors from the FROM clause
Oracle forms where clause with a variable from bloc item
I'm trying to populate a block using a FROM clause in oracle forms like this:
select h.no_ordre, h.marge,h.date_modification, h.utilisateur_modification from hist_marge_0alb h, simul s, simtauxvar_0alb sv, affaire a where s.id_affaire = a.id_affaire and s.id_simul_element(+) = h.id_simul_element and sv.id_simul_element = s.id_simul_element and s.id_affaire = :SIMUL.id_affaire
But i got a FRM 40505 Error. I think the problem is in :SIMUL.id_affaire but I don't find a way to do it. Is there a way I can do it? Thanks.
concatenate query result in a string
I am getting a db result as 3 rows (Please see image link below).
The sql statement used is
select tevmkt.ev_mkt_id, tevmkt.name, tevmkt.ev_id, tevoc.ev_oc_id,
tevoc.desc, tevoc.fb_result, tevoc.lp_num, tevoc.lp_den,
tev.start_time
from tevmkt, tev,tevoc
where tevmkt.name = '|Match Result|' and tev.ev_id=tevmkt.ev_id and
tevoc.ev_mkt_id=tevmkt.ev_mkt_id and tev.start_time>=Today;
I will like to use php to concatenate each of the 3 rows into string or maybe use SQL statement.
So, the first 3 rows will display as ;
632274|Match Result||Draw||Aldershot Town||Arsenal FC|
And the next 3 rows
637799|Match Result||Draw||Southend United||Oxford United|
is it possible to store ResultSet from a query into an array and use the array as search parameters for an SQL query
i have two tables WorkSkillsPlanning(WSP) and TrainingAchieved(TA). WSP hold a list of planed training and targeted number of people to be trained e.g. ISOO:90001 10 people while TA holds the actual number of people trained as well as the actual course done. Since WSP and TA are dynamic in the sense that the data they hold is not static neither is known as training plans can change is it possible to run an intersect query on these table to find similarities i.e a course in WSP which has actually be done and recorded in TA. Store the results of the intersect query in an array e.g. MyArrayList{ISO,COMMUNICATION) these being values present in both table and use MyArrayList values to run count queries on TA to establish the number of people who would have done the course i.e ISO and COMMUNICATION and use the resultant to subtract from WSP (ISO,COMMUNICATION).
here is an example, first part
"Select QUALIFICATIONGROUP from APP.WSP intersect select COURSEBOOKED from APP.BOOKCOURSE"
which results in ISO and COMMUNICATION which i want to store in an ARRAY or variable.
second part
select count(COURSEBOOKED) from APP.BOOKCOURSE where COURSEBOOKED = Variable1
rs.getString(Count(COURSEBOOKED))
value returned == 5
re do the process again for COMMUNICATION and any other course in the array, of which after use the values returned from the count query to subtract to subtract WSP total minus TA total.
I hope this makes sense
Wondering about APIViews in Django
Hi I'm working on a Django site and am trying to figure out what the previously implemented code does. I'm new to Django so just figuring this stuff out on the fly. I'm looking at resources.py, and there's a snippet of code that looks like this:
class BaseQueryView(APIView):
def _parse_filter_values(self, data):
include = [] exclude = [] for d in data:and so on
I guess what I'm wondering is where is the APIView getting the "data" from? How can I find out how "data" is structured?
SQL - new column with the same cell value in every row for the same id without subquery
Sorry for bad title, I don't know how do describe better if you have a better one please tell me ;)
Please look at these small sql fiddle: http://ift.tt/1M93WZ6
I need the "Value" from a specific "Title" in a new column ("Age"). But I need the "Value" in any row for the same "SN_Main". The first query was my first try. It is fast and ok but I get the "Value" only for the row with the same "Title".
The second query is wat I want but the subquery is to slow, so I wanna solve this without subquery. The productive tables are bigger and I need this like 10 times and with subquery it become incredible slow.
So is there any way to get this output with other sql statements???
I hope you understand me, I'm so sorry about bad explanation :)
Regards Martin
Reload table after searching
I'm trying to edit a column of a table after I search it using a drop down list; however, when I click the "Edit" button nothing shows up. Please advise and thank you!
More info: I have a Home page where the drop down list is located and as I click search it references the Results.aspx page where the table is shown and, as stated above, when I click edit nothings shows up.
Home.aspx.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace Inventory
{
public partial class Home : System.Web.UI.Page
{
SqlConnection cn = new SqlConnection("Data Source=10.10.101.188;Initial Catalog=ActioNetITInventory;User ID=rails.sa;Password=ActioNet1234");
protected void Page_Load(object sender, EventArgs e)
{
//methods
Populate1();
}//end page_load
//puts data on the first dropdown list
public void Populate1()
{
SqlCommand cmd = new SqlCommand("SELECT * FROM [Inventory]", new SqlConnection(ConfigurationManager.AppSettings["ConnString"]));
cmd.Connection.Open();
SqlDataReader ddlValues;
ddlValues = cmd.ExecuteReader();
DropDownList1.DataSource = ddlValues;
DropDownList1.DataValueField = "Assigned";
DropDownList1.DataTextField = "Assigned";
DropDownList1.DataBind();
//starts the dropdown list with empty so you can search with serial or the drop down
DropDownList1.Items.Insert(0, new ListItem(String.Empty, "--Select--"));
DropDownList1.SelectedIndex = 0;
cmd.Connection.Close();
cmd.Connection.Dispose();
}//end populate one
}//end class
}//end namespace Inventory
Results.aspx.cs:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;
namespace Inventory
{
public partial class Results : System.Web.UI.Page
{
SqlConnection conn = new SqlConnection("Data Source=10.10.101.188;Initial Catalog=ActioNetITInventory;User ID=rails.sa;Password=ActioNet1234");
protected void Page_Load(object sender, EventArgs e)
{
}
}//end class
}//end name space
Results.html:
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Results.aspx.cs" Inherits="Inventory.Results" %>
<!DOCTYPE html>
<html xmlns="http://ift.tt/lH0Osb">
<head id="Head1" runat="server">
<title>Results</title>
<link href="StyleSheet1.css" rel="stylesheet" />
<link href="Background.css" rel="stylesheet" type="text/css" />
<link href="Default.css" rel="stylesheet" />
<link href="Component.css" rel="stylesheet" />
<link rel="shortcut icon" href="~/logo.ico" type="image/x-icon" />
<style type="text/css">
.auto-style1 {
height: 80px;
width: 335px;
}
</style>
</head>
<body>
<form id="form1" runat="server">
<div style="margin-left: 400px">
<br />
<br />
<br />
<br />
<br />
<br />
<!--logo-->
<img alt="" class="auto-style1" src="logo.jpg" />
<br />
<br />
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False"
CellPadding="4"
DataKeyNames="Serial"
DataSourceID="SqlDataSource1"
ForeColor="#333333"
ShowFooter="True"
Width="1393px" >
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<Columns>
<asp:CommandField ShowEditButton="True" ButtonType="Button" />
<asp:BoundField DataField="Type" HeaderText="Type" SortExpression="Type" />
<asp:BoundField DataField="Make" HeaderText="Make" SortExpression="Make" />
<asp:BoundField DataField="Model" HeaderText="Model" SortExpression="Model" />
<asp:BoundField DataField="Serial" HeaderText="Serial" ReadOnly="True" SortExpression="Serial" />
<asp:TemplateField HeaderText="Assigned" SortExpression="Assigned">
<EditItemTemplate>
<asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("Assigned") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Bind("Assigned") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Location" SortExpression="Location">
<EditItemTemplate>
<asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Location") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label2" runat="server" Text='<%# Bind("Location") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Notes" SortExpression="Notes">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Notes") %>'></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Bind("Notes") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ActioNetITInventoryConnectionString %>"
SelectCommand="SELECT * FROM [Inventory] WHERE ([Assigned] = @Assigned)"
DeleteCommand="DELETE FROM [Inventory] WHERE [Serial] = @Serial"
InsertCommand="INSERT INTO [Inventory] ([Type], [Make], [Model], [Serial], [Assigned], [Location], [Notes]) VALUES (@Type, @Make, @Model, @Serial, @Assigned, @Location, @Notes)"
UpdateCommand="UPDATE [Inventory] SET [Type] = @Type, [Make] = @Make, [Model] = @Model, [Assigned] = @Assigned, [Location] = @Location, [Notes] = @Notes WHERE [Serial] = @Serial">
<DeleteParameters>
<asp:Parameter Name="Serial" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="Type" Type="String" />
<asp:Parameter Name="Make" Type="String" />
<asp:Parameter Name="Model" Type="String" />
<asp:Parameter Name="Serial" Type="String" />
<asp:Parameter Name="Assigned" Type="String" />
<asp:Parameter Name="Location" Type="String" />
<asp:Parameter Name="Notes" Type="String" />
</InsertParameters>
<SelectParameters>
<asp:FormParameter FormField="DropDownList1" Name="Assigned" Type="String" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="Type" Type="String" />
<asp:Parameter Name="Make" Type="String" />
<asp:Parameter Name="Model" Type="String" />
<asp:Parameter Name="Assigned" Type="String" />
<asp:Parameter Name="Location" Type="String" />
<asp:Parameter Name="Notes" Type="String" />
<asp:Parameter Name="Serial" Type="String" />
</UpdateParameters>
</asp:SqlDataSource>
<br />
<br />
<asp:Button ID="Button1" runat="server" PostBackUrl="~/Home.aspx" Text="Back" Width="88px" />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
<br />
</div>
</form>
</body>
</html>
Running out of disk space on MySQL partition azure
I created replication in Microsoft Azure Virtual Machine .
I'm using MySQL and working with sql workbench (windows).
yesterday I discovered that my 250 GB storage are full and replication stopped.
this log wrote,
Timestamp, Thread, Type, Details
2015-07-29 23:26:44, 1672, Warning, Disk is full writing '.\database123-relay-bin.000164' (Errcode: 28 - No space left on device). Waiting for someone to free space...
and I created another 250 GB external storage.
I have 2 Q :
-
how can I create queries and use data within two difference storage ?
-
is it the right thing to do? to create another storage or there is a way to create flexible storage
?
that i found is this : http://ift.tt/1HnO64W
but it not help , need help and Guidance
SQL - Knocking off Accordingly
I have an issue in SQL where all transaction just come into one giant messy tables.
Example:
1 | Invoice | $300
2 | Invoice | $250
3 | Payment | $100
4 | Invoice | $200
5 | Payment | $300
So i will have 3 invoices and 2 paymentsbut
the Payment at line 3 can only be knocking off the Invoice on line 1 and Payment on line 5 is for the Invoice in line 2.
I want to Net Off the Payment and Find rather it is an Overpayment or Underpayment or it is knocking off entire invoice.
How can i do this?
How to PIVOT and filter with two parameter?
I have such SQ
SELECT * FROM (
SELECT dt.route_id,dc.card_type_id,dt.amount, dt.currency_id
FROM ddrc_trans dt, ddrc_card dc, ddrc_card_type drc
WHERE ( dt.card_id = dc.id AND dt.route_id IN ( 1,2) )
)
Which returns such result
1 1 2 50 3
2 1 2 50 3
3 1 2 50 3
4 2 1 50 1
5 2 1 50 1
OK that looks nice!
I write pivot, in order to convert rows to columns.
SELECT * FROM (
SELECT dt.route_id,dc.card_type_id,dt.amount, dt.currency_id
FROM ddrc_trans dt, ddrc_card dc, ddrc_card_type drc
WHERE ( dt.card_id = dc.id AND dt.route_id IN ( 1,2) )
)
pivot
(
SUM(amount) as AMOUNT_DATA
FOR card_type_id IN (1,2)
)
It looks perfect!
1 2 1 900 NULL
2 2 3 NULL 1050
3 1 2 1050 NULL
4 1 3 NULL 900
5 1 1 1050 NULL
6 2 2 1050 NULL
But 1. what should I do in order to filter with curency id? for instance I need to show only currency id. 2. How can I group with currency id? I do not want to be duplicated CURRENTY_ID.
I have do my solution like this, is there another way without concat?
SELECT *
FROM (
SELECT dt.route_id, dt.amount, concat(concat( dt.currency_id , ' '), dc.card_type_id) as conval
FROM ddrc_trans dt,
ddrc_card dc,
ddrc_card_type drc
WHERE ( dt.card_id = dc.id
AND dt.route_id IN ( 1,2)
AND drc.id IN ( 1,2,3 )
)
) s
PIVOT
(
SUM(amount) AM
FOR conval IN ('5 1', '5 2', '5 3')
) pivot1
Postgres - get count of a chacter in a column?
In Postgres/SQL, how can I get the count of a character in a column?
For example, I want to run a query which will return the number of times "X" appears in a column that has the value "XXX" - and it should return 3.
3-level Hierarchical SQL Query
I'm very well aware of basic SQL queries, however I've never worked with a query that deals with hierarchical data.
I'm using the Telerik TreeView control to display data related to a school board with the following structure:
--School "ABC"
----Class "Grade 1"
----Class "Grade 2"
------Student #1
------Student #2
--School "DEF"
----Class "Grade 1"
------Student #3
----Class "Grade 3"
The TreeView is in a tri-state checkbox mode, so each element can be checked off. So if a single child element is checked then the parent element will be checked as well, and when checking a parent element all the child elements will be checked.
After checking off some schools, classes, and students, the following screen displays information about the students in a graph which currently uses the school IDs (if multiple are checked) to select all students of those schools.
Here's where it gets tricky. Using the above structure as an example, I select the class "Grade 1" from the school "ABC" and class "Grade 3" from the school "DEF" which in turn will select students # 1 & #2.
As mentioned before, my current SQL query is based solely on the school ID and I know that I can't simply add two other conditions in the where clause that look like this:
AND ClassID IN ('Grade 1', 'Grade 3') --Note there is no primary key for classes, and I can't change that in my position..
AND StudentID IN (1,2)
as this will also select student #3 from the other class title "Grade 1"
So my question is, how to I combine the School ID(s), Class name(s), and student ID(s) into one condition that will solve this issue.
Any help would be greatly appreciated as I'm really stuck on this question with a very strict deadline, thanks!!
Reverse Concat - Split function
I have a table and it has a value column that lists data as: Row 1: '00','01','02','03' Row 2: '03','02','09','08'
I have a couple of split functions
FUNCTION [dbo].[udf_Split](@String varchar(MAX), @Delimiter char(1))
returns @temptable TABLE (Item varchar(MAX))
as
begin
declare @idx int
declare @slice varchar(8000)
select @idx = 1
if len(@String)<1 or @String is null return
while @idx<>0
begin
set @idx = charindex(@Delimiter,@String)
if @idx!=0
set @slice = left(@String,@idx - 1)
else
set @slice = @String
if(len(@slice)>0)
insert into @temptable(Item) values(@slice)
set @String = right(@String,len(@String) - @idx)
if len(@String) = 0 break
end return end;
I'm trying to create a view of the table, with that column and then I'd like my view results to be a list of rows that have each value broken to its own row (and distinct) So would look like: (the tics can stay or go, don't care about them right now) Row 1: 00 Row 2: 01 Row 3: 02 Row 4: 03
My view is pretty much a:
SELECT DISTINCT VALUE FROM TABLE
cross apply dbo.split(Value, ',') as Item
But it's not working. Can someone lend me some direction on how I should work this?
Inserting data to Formatted Excel after First row (Column header)
I am trying to add the grid view rows data to formatted excel, my formatted excel contains 6 column named col1, col2, col3, total1, total2 and total3, I made some calculation on total1,total2 and total3. My grid view contains 8 rows but I am trying to add first three fetched data to Excel, data are inserting perfectly except the data are inserted after some range i.e., non used area, but the calculation are available from row 2,
Please find my code below.
protected void Export_Click(object sender, EventArgs e)
{
int gridi,j,value1, value2, value3;
int rowcount = GridView1.Rows.Count;
int Colcount = 3;
String[] cellText;
string path = @"D:\ExcelTemplate\template.xlsx";
constr = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
constr = string.Format(constr, path);
using (OleDbConnection excel_con = new OleDbConnection(constr))
{
excel_con.Open();
string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
DataTable dtExcelData = new DataTable();
dtExcelData = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
String[] excelSheets = new String[dtExcelData.Rows.Count];
int i = 0;
foreach (DataRow row in dtExcelData.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
sheetinfo = excelSheets[2];
try
{
foreach (GridViewRow row in GridView1.Rows)
{
int count = 2;
value1 = Convert.ToInt32(row.Cells[0].Text.Trim());
value2 = Convert.ToInt32(row.Cells[1].Text.Trim());
value3 = Convert.ToInt32(row.Cells[2].Text.Trim());
//string excelquery = "Insert into ["+sheetinfo+"A2:C20000] (Column1,Column2,Column3) values(" + value1 + "," + value2 + "," + value3 + ")";
string excelquery = "Insert into [sheet1$](A" + count + ",B" + count + ",C" + count + ") values(" + value1 + "," + value2 + "," + value3 + ")";
using (OleDbDataAdapter oda = new OleDbDataAdapter(excelquery, excel_con))
{
oda.Fill(dtExcelData);
}
excel_con.Close();
Label1.Visible = true;
Label1.Text = "Insert to the Excel [[[Success]]]";
count += 1;
}
}
catch (Exception ex)
{
//Label1.Visible = true;
//Label1.Text = ex.ToString();
}
}
kindly suggest the solution for my issue.
Detecting rising and falling edge via SQL (loading cycles)
i need to detect rising and falling edges from a loading state in my logs and need to list all loading cycles.
Lets say i have a table LOG
UTS | VALUE | STATE
1438392102 | 1000 | 0
1438392104 | 1001 | 1
1438392106 | 1002 | 1
1438392107 | 1003 | 0
1438392201 | 1007 | 1
1438392220 | 1045 | 1
1438392289 | 1073 | 0
1438392305 | 1085 | 1
1438392310 | 1090 | 1
1438392315 | 1095 | 1
And need all cycles where STATE = 1 I need to know when they started how long they lasted and how much VALUE changed in each cycle.
I also might have a situation where the last cycle isn't finished yet.
Do you have an idea how i can do this in SQL in a good performing way ? Cuz i might run into situations where my logs return several hundred of thausends of rows.
Thanks for help
How can I filter intems by `count(*)` without `having`?
I have some query:
select disconnect_reason as disconnectReason, disconnect_cause, count(*) as callsCount
from calls group by disconnect_reason, disconnect_cause
How I need to get items with callsCount = 1 only.
How can I get it without having?
Scalar variable when using class
Do I need to add all the parameter I intend to use throughout the code in the class that have the SqlCommand, or can I pass the parameters when I call for the class?
In my Class file (Called DataAccess):
public static int SqlGetInt(string queryString)
{
int retVal = 0;
try
{
using (SqlConnection cn = GetConnection())
{
cn.Open();
using (SqlTransaction tr = cn.BeginTransaction(IsolationLevel.ReadUncommitted))
{
using (SqlCommand cmd = new SqlCommand(queryString, cn))
{
cmd.Transaction = tr;
using (SqlDataReader rdr = cmd.ExecuteReader())
{
if (rdr.Read())
retVal = GetRdrInt(rdr, 0);
}
}
tr.Commit();
}
}
}
in my Form1 code:
string sql = "SELECT ID FROM tbl_employees WHERE username = @username";
_responsible_id = DataAccess.SqlGetInt(sql);
Since I havent added the parameters, this ofcourse fails. So back to my question: Can I add these parameters in my Form1 file when I call for SqlGetInt() or do I need to add it in the SqlGetInt() in the Class file?
Select only if no date are under 60 days from now
I want to make an SQL request to display a list of users but only the one who haven't accepted a mission since at least 60 days. A user have multiple user missions attached to him, so I need to look at all of them and display the user only if no missions have been accepted since 60 days.
Here is what I have so far, but it is wrong, the user is in the list even if he have accepted a mission less than 60 days ago, but the mission doesn't show up though. So this request just display every missions that have been accepted more than 60 days ago. That is not what I want.
SELECT
u.username, u.id, u.email, date_part('days', now() - um.date_accepted) as "days since last mission"
FROM
users_user u
INNER JOIN
users_usermission um
ON
u.id=um.user_id
WHERE
date_part('days', now() - um.date_accepted) > 60
Anyone know how could I fix this request?
can't make a trigger update only the relevant rows
I'm working on a school project and my trigger gives me a hard time.
Its' purpose is to update the Rating field of an updated Product, but it updates all rows in Products instead.
CREATE TRIGGER Update_Rating
ON dbo.Reviews
FOR Insert
as
Update dbo.Products
set Rating=(Select [avarage_rating]=avg(r.Rating)
From dbo.Reviews as r join inserted on r.ItemNumber = inserted.ItemNumber
where r.ItemNumber = Inserted.ItemNumber)
Your help is much appreciated
Verification of Result in ESQL
Here's the situation: I run a query that gives me one or many rows as a result in an array.
Like:
SET db = PASSTHRU('SELECT GUID,CONTROLNBR FROM TRANSACTION WHERE GUID > ? AND CONTROLNBR > ?' values(maxGuid,maxControlNbr);
That works fine but I want to verify the following:
Any result that has duplicate CONTROLNBR's must have the same GUID
So if my result set has something like this:
1: CONTROLNBR: 5, GUID: 123abc
- CONTROLNBR: 5, GUID: 123abc
this is entirely valid, however this I need to return an error on:
-
CONTROLNBR: 5, GUID: 123abc
-
CONTROLNBR: 5, GUID: abc123
I'm not sure the best way to test the result like this in ESQL/SQL.
Thanks
How to use Excel ListBox for SQL Query parameters
I have to execute a SQL query on a database and analyse data into an Excel file.
The point is to put multiple parameters in a IN construct from a listbox. I managed to create the listbox in Excel but I'm having problem extracting data from it and putting it in the query.
Do you know how to or do you have any reference to a guide?
Recap:
- I have a listbox populated with data
- I need to put multiple entries of the listbox as parameter of a SQL query (IN construct)
- I don't mind use VBS or similar
Add parameters in query on c#
In my code behind of aspx page I have problem to passed values on Parameters in sql query.
Step 1:
I add in List the output of an query:
while (reader.Read())
{
idcolor = reader["idcolor"].ToString();
colorList.Add(idcolor.ToString());
}
ns = string.Join("','", colorList.ToArray());
In debug the output is:
ns = red','green
Step 2:
I need use the values of string ns on a sql query.
And pass the values of string ns in parameters:
str = null;
str = ns == null ? "" : ns.ToString();
sql = @" SELECT * FROM Experience WHERE Colors IN (?); ";
DataSet dsColors = new DataSet();
using (OdbcConnection cn =
new OdbcConnection(ConfigurationManager.ConnectionStrings["ConnMySQL"].ConnectionString))
{
cn.Open();
using (OdbcCommand cmd = new OdbcCommand(sql, cn))
{
cmd.Parameters.AddWithValue("param1", Server.UrlDecode(str.ToString()));
OdbcDataAdapter adapter = new OdbcDataAdapter(cmd);
adapter.Fill(dsColors);
}
}
return dsColors;
Step 3:
If used in query :
sql = @" SELECT * FROM Experience WHERE Colors IN (?); ";
The output in dataset is empty.
If used in query :
sql = @" SELECT * FROM Experience WHERE Colors IN ( '" + Server.UrlDecode(str.ToString()) + "' ); ";
The output in dataset is right.
Anybody know how can I resolve do this?
Can you suggest?
Can you help me?
Thank you in advance.
unable to group by category and unixtime desc
I have created: http://ift.tt/1SHCd5C
CREATE TABLE if not exists tblA
(
id int(11) NOT NULL auto_increment ,
userid int(255),
category int(255),
unixtime int(255),
PRIMARY KEY (id)
);
INSERT INTO tblA (userid,category,unixtime) VALUES
('1', '1','1438689946'),
('1', '2','1438690005'),
('1', '3','1438690007'),
('5', '1','1438690009'),
('2', '1','1438690005'),
('2', '1','1438690398'),
('1', '2','1438691020'),
('1', '3','1438691028'),
('4', '2','1438690005'),
('2', '3','1438691025'),
('2', '2','1438691020'),
('3', '3','1438691022');
and
Select * from tblA group by category order by unixtime desc;
But I am getting wrong values.The values do not contain right unixtime desc.How can I make it work ? I really appreciate any help.
SQL Server - Derived Table Data Storage
In SQL Server while writing a Query , I noticed that the data in Inner Query which is a derived table when joined with another table is taking long. The keys joined to the outer table is on Primary Key. So I was surprised since the data was about 10,000 records and 15 Columns. But if we store the data from derived table in a temp table and then JOIN the Performance was less than 2 Seconds. It made me wonder what the reason would be ?
SQL Number - Row_Number() - Allow Repeating Row Number
I'm using SQL Server 2008. I have this data returned in a query that looks pretty much like this ordered by Day and ManualOrder...
ID Day ManualOrder Lat Lon
1 Mon 0 36.55 36.55
5 Mon 1 55.55 54.44
3 Mon 2 44.33 44.30
10 Mon 3 36.55 36.55
11 Mon 4 36.55 36.55
6 Mon 5 20.22 22.11
9 Mon 6 55.55 54.44
10 Mon 7 88.99 11.22
77 Sun 0 23.33 11.11
77 Sun 1 23.33 11.11
What I'm trying to do is get this data ordered by Day, then ManualOrder...but I'd like a row counter (let's call it MapPinNumber). The catch is that I'd like this row counter to be repeated once it encounters the same Lat/Lon for the same day again. Then it can continue on with the next row counter for the next row if it's a different lat/lon. We MUST maintain Day, ManualOrder ordering in the final result.
I'll be plotting these on a map, and this number should represent the pin number I'll be plotting in ManualOrder order. This data represents a driver's route and he may go to the same lat/lon multiple times during the day in his schedule. For example he drives to Walmart, then CVS, then back to Walmart again, then to Walgreens. The MapPinNumber column I need should be 1, 2, 1, 3. Since he goes to Walmart multiple times on Monday but it was the first place he drives too, it's always Pin #1 on the map.
Here's what I need my result to be for the MapPinNumber column I need to calculate. I've tried everything I can think of with ROW_NUMBER and RANK, and going insane! I'm trying to avoid using an ugly CURSOR.
ID Day ManualOrder Lat Lon MapPinNumber
1 Mon 0 36.55 36.55 1
5 Mon 1 55.55 54.44 2
3 Mon 2 44.33 44.30 3
10 Mon 3 36.55 36.55 1
11 Mon 4 36.55 36.55 1
6 Mon 5 20.22 22.11 4
9 Mon 6 55.55 54.44 2
10 Mon 7 88.99 11.22 5
77 Sun 0 23.33 11.11 1
77 Sun 1 23.33 11.11 1
how do I change the thickness of a doughnut chart in SSRS?
I basically want to make the hole of the doughnut bigger so that the chart itself is thinner.
The green needs to be thinner:
Unable to Modify User-Defined Table Type
I have a SQL User-Defined Table Type. It used in more than fifty stored procedures.Now i need to change a column in that table type. I tried to drop and recreate the User-Defined Table Type.But SQL Server doesn't Allow that. It shows up following error.
Msg 3732, Level 16, State 1, Line 3
Cannot drop type 'dbo.UserDefinedTableType' because it is being referenced by object 'SP_DoSomething'. There may be other objects that reference this type.
Msg 219, Level 16, State 1, Line 3
The type 'dbo.UserDefinedTableType' already exists, or you do not have permission to create it.
How to alter the User-Defined Table Type without modifying all the Stored procedure that uses User-Defined Table Type ?
Sql queries not executed on H2 database
Recently i changed from HSQLDB to H2, changed a bit of code and my queries stopped executing.
I test my SQL code with RazorSQL where i try to access my DB from , bud to my suprise there is no table created,no errors thrown no null pointers , valid sql, db file created - everything seems to be running alright bud no content in database whatsoever.
Here are a crucial parts of my database access/creation.
I use connector class to create connect to database
public class H2DatabaseConnector {
private Connection connection;
private Statement statement;
private ResultSet resultSet;
public static final String[] PREP_STATEMENTS = new String[]{};
public static final String DB_FILE_NAME = File.separator + "dboc";
public static final String DB_DIR = Info.OC_DIR + "oc_database\\";
static {
try {
Class.forName("org.h2.Driver");
} catch (ClassNotFoundException ex) {
ex.printStackTrace();
}
}
public H2DatabaseConnector(String username, String password) {
try {
openConnection(username, password);
} catch (SQLException ex) {
ex.printStackTrace();
}
}
.....
//data credentials are correct
private void openConnection(String username, String password) throws SQLException {
connection = DriverManager.getConnection("jdbc:h2:file:" + DB_DIR + DB_FILE_NAME+";DATABASE_TO_UPPER=false", username, password);
statement = connection.createStatement();
}
public void execute() {
}
}
And utility class where i execute my sql commands
public class DbUtil {
public static final void createUsersTable(){
new H2DatabaseConnector(Info.Db.DB_MAIN_USERNAME,Info.Db.DB_MAIN_PASSWORD) {
@Override
public void execute() {
try {
getStatement().execute("CREATE TABLE users(name VARCHAR(255) NOT NULL,password VARCHAR(255) NOT NULL,email VARCHAR(255));");
System.out.println("Table Created users");
getStatement().executeUpdate("INSERT INTO users VALUES ('sa','sa',NULL);");
closeConnection();
} catch (SQLException ex) {
ex.printStackTrace();
}
}
}.execute();
}
}
Execution is going thru as expected bud no table is created. Why is this happening? There are no errors shown,and sql syntax is correct since when i open database in **RazorSQL and create/insert table there everything works.**
Any ideas? Im literally stuck on this for a whole day.
make sum out of 5 columns using a select statement
I have the following columns with the afferent values:
p1 | p2 | p3 | p4 | p5
______________________
1 1 2 2 1
2 2 3 1 1
I would want to make a statement that let me sum up the columns depending on the number inside. example..
row1=sum of(p1,p2,p5) and sum of(p3,p4)
row=sum of(p1,p2) and sum of(p3) and sum of (p4,p5)
SSRS 2012 - uninitialized server
I have been experiencing random connection/handshake problems w/ a hyper server VM running SQL and SSRS
So the network guys suggested building a new VM and trying it there. (Have you tried rebooting? )
I asked that they rename the old server (--> SQLBKUP) and name the new server to the current name (--> SQL) so all my connection strings will continue to work.
Regardless the wisdom of that approach, that is all now done.
All of our applications work. (and the weird handshake issue is gone,joy) I have reinstalled SSRS and I thought I was home free.
We backed up and restored the ReportServer and ReportServerTemp databases to the new server.
If i try to point to these databases , I keep getting this error
The report server installation is not initialized. (rsReportServerNotActivated) Get Online Help
Any all information I can find about this for 2012 says that the initialization happens automatically when you configure a database.
I tried creating a new database, and presto, everything works fine. I reconfigured SSRS to point at the old database and I again get the rsReportServerNotActivated error.
I also 'powered down' SQLBKUP in case it was causing some confusion, I cant imagine what that might be, but why not... This did NOT correct the problem.
Any ideas on why the databases that were working on 1 server wont work on the new one?
Searching the interweb for this issue I find two results for 2012 SSRS (many hits for 2005 issues/resolutions )
this article details how the RSExec role should be configured, I have verified that is all correct. http://ift.tt/1MJLPZY
this article details the mechanics of various ways to move a database. The back up and restore operations went off w/o a hitch. http://ift.tt/1Ih5agW
neither article mentions cleaning up any server names, ip addresses, etc. that might be in a config table. Inspecting the tables in SSMS, I dont see any tables that look like they might need such attention.
I can always recreate the environment, I am aout to that point, at least I will know what I have in front of me. If anyone has any suggestions, i would appreciate it, Im sure I will be up for a while... :-)
tyia greg
SQL Database design for statistical analysis of many-to-many relationship
It's my first time working with databases so I spent a bunch of hours reading and watching videos. The data I am analyzing is a limited set of marathon data, and the goal is to produce statistics on each runner.
I am looking for advice and suggestions on my database design as well as how I might go about producing statistics. Please see this image for my proposed design:
Basically, I'm thinking there's a many-to-many relationship between Races and Runners: there are multiple runners in a race, and a runner can have run multiple races. Therefore, I have the bridge table called Race_Results to store the time and age for a given runner in a given race.
The Statistics table is what I'm looking to get to in the end. In the image are just some random things I may want to calculate.
So my questions are:
-
Does this design make sense? What improvements might you make?
-
What kinds of SQL queries would be used to calculate these statistics? Would I have to make some other tables in between - for example, to find the percentage of the time a runner finished within 10 minutes of first place, would I have to first make a table of all runner data for that race and then do some queries, or is there a better way? Any links I should check out for more on calculating these sorts of statistics?
-
Should I possibly be using python or another language to get these statistics instead of SQL? My understanding was that SQL has the potential to cut down a few hundred lines of python code to one line, so I thought I'd try to give it a shot with SQL.
Thanks!
SQL Argument data type datetime is invalid for argument 1 of substring function
I'm trying to run an sql query to move data from one table into another but I'm having trouble. Here is my query:
TRUNCATE TABLE LIQ_ACCT_EOD_SIF
DECLARE @filedt varchar(32), @repdate int;
SET NOCOUNT ON
SET @filedt = CAST(getdate() as varchar);
select @repdate=CAST(right(S_ReportingDate, 4) + SUBSTRING(S_ReportingDate,4,2) + LEFT(S_ReportingDate, 2)
AS int)
FROM AllocationJunLLv2;
DELETE FROM dbo.LIQ_ACCT_EOD_SIF
WHERE REPORT_DATE=@repdate;
INSERT INTO dbo.LIQ_ACCT_EOD_SIF
(
FILE_NUM
,FILE_DATE_TIME
,ROW_NUM
,FILE_TYPE
,MIGRATION_DATE
,INSTITUTION_NAME
,RESULT_TYPE_NAME
,SCENARIO_NAME
,REPORT_DATE
,ASSET_CLASS_NAME
,INSTRUMENT_TYPE_NAME
,INSTRUMENT_SUBTYPE_NAME
,INSTRUMENT_NAME
,COUNTERPARTY_NAME
,CORPORATE_RETAIL_FLG
,CLIENT_NAME
,DEPOSIT_FLG
,POSITION_FLG
,LIQUID_FLG
,LOCATION_NAME
,PORTFOLIO_NAME
,ACCOUNT_TYPE_NAME
,ACCOUNT_NUMBER
,ACCOUNT_OPENING_DATE
,INSTRUMENT_ISO_CURRENCY
,ISO_CURRENCY_CODE
,CURRENT_VALUE
,MATURITY_DATE
,MATURITY_VALUE
,DRAWN_AMOUNT
,UNDRAWN_AMOUNT
,LIMIT_AMOUNT
,EXPOSURE_CD
,EXPECTED_CASH_FLOW
,EXPECTED_CASH_FLOW_DATE
,PASSED_DUE_IND
,INT_TYPE
,INT_FIXED_RATE
,INT_VAR_BASENAME
,INT_REL_VAR_RATE
,RATING_NAME
,RATING_AGENCY
,BASEL_OPTION
,RATING_TYPE
,REPORT_LINE_NUMBER
,REPORT_LINE_NAME
,DELTA
,GAMMA
,NETTING_NUMBER
,COLLATERAL_ACCOUNT_NUMBER
,BUFFER_ELIGIBLE
,ALTERNATIVE_YIELD_RATE
,FTP_PORTFOLIO
,REPO_FLAG
,LIMIT_EXPIRY_DATE
,AL_DEFINITION
,REPO_AMOUNT
,TRANSACTION_NUMBER
,UNENCUMBERED_AMOUNT
,MARGIN_ACCOUNT_NUMBER
,CASH_FLOW_TYPE
,value_of_house
,security_value
,security_type
,int_rate_floor
,int_rate_ceiling
,lcr_line_number
,AMM_line_number
,BASEL_APPROACH
,CCR_portfolio
,CCR_sector
,CCR_region
)
SELECT
1 AS FILE_NUM
,@filedt AS FILE_DATE_TIME
,0 AS ROW_NUM
,'EOD' AS FILE_TYPE
,getdate() AS MIGRATION_DATE
,'UBNL' AS INSTITUTION_NAME
,LEFT(b.STBITEM, 32) AS RESULT_TYPE_NAME
,'Unknown' AS SCENARIO_NAME
,@repdate AS REPORT_DATE
,ISNULL(rl.ASSET_CLASS_NAME, 'Unknown') AS ASSET_CLASS_NAME
,'Unknown' AS INSTRUMENT_TYPE_NAME
,'Unknown' AS INSTRUMENT_SUBTYPE_NAME
,'Unknown' AS INSTRUMENT_NAME
,LEFT(b.S_InstitutionCode, 32) AS COUNTERPARTY_NAME
,ISNULL(rl.CORPORATE_RETAIL_FLG, 'U')
,b.S_CustomerName AS CLIENT_NAME
,'U' AS DEPOSIT_FLG
,'U' AS POSITION_FLG
,'U' LIQUID_FLG
,'Unknown' AS LOCATION_NAME
,b.S_Ref1 AS PORTFOLIO_NAME
,'Unknown' AS ACCOUNT_TYPE_NAME
,b.Unique_ID AS ACCOUNT_NUMBER
,CAST( right(CAST(S_StartDate AS varchar(8)),4) + SUBSTRING(CAST(S_StartDate AS varchar(8)),4,2) + LEFT(CAST(S_StartDate AS varchar(8)), 2)AS int)
AS ACCOUNT_OPENING_DATE
,'USD' AS INSTRUMENT_ISO_CURRENCY
,'USD' AS ISO_CURRENCY_CODE
,CASE
WHEN rl.CURRENT_VALUE_FLG='Y' THEN
CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
ELSE 0
END AS CURRENT_VALUE
,ISNULL(CAST( right(S_MaturityDate,4) + SUBSTRING(S_MaturityDate,4,2) + LEFT(S_MaturityDate, 2)AS int), 29991231) AS MATURITY_DATE
,CASE
WHEN rl.MATURITY_VALUE_FLG='Y' THEN
CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
ELSE 0
END AS MATURITY_VALUE
,0 AS DRAWN_AMOUNT
,CASE
WHEN rl.UNDRAWN_AMOUNT_FLG='Y' THEN
CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
ELSE 0
END AS UNDRAWN_AMOUNT
,CASE
WHEN rl.LIMIT_AMOUNT_FLG='Y' THEN
CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
ELSE 0
END AS LIMIT_AMOUNT
,ISNULL(rl.EXPOSURE_CD, 'Unknown') AS EXPOSURE_CD
,CASE
WHEN rl.EXPECTED_CASH_FLOW_FLG='Y' THEN
CAST(CAST(CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END AS float) AS numeric(22,6)) * fx.EXCHANGE_RATE
ELSE 0
END AS EXPECTED_CASH_FLOW
,CASE
WHEN rl.EXPECTED_CASH_FLOW_FLG='Y' THEN ISNULL(CAST( right(S_MaturityDate,4) + SUBSTRING(S_MaturityDate,4,2) + LEFT(S_MaturityDate, 2)AS int), 29991231)
ELSE 0
END AS EXPECTED_CASH_FLOW_DATE
,'U' AS PASSED_DUE_IND
,'U' AS INT_TYPE
,0 AS INT_FIXED_RATE
,'Unknown' AS INT_VAR_BASENAME
,0 AS INT_REL_VAR_RATE
,'Unknown' AS RATING_NAME
,'Unknown' AS RATING_AGENCY
,'B2L' AS BASEL_OPTION
,'LT' AS RATING_TYPE
,ISNULL(CAST(SUBSTRING(b.STBITEM, 5, 2) AS int), 0) AS REPORT_LINE_NUMBER
,ISNULL(rl.REPORT_LINE_NAME, 'Unknown') AS REPORT_LINE_NAME
,0 AS DELTA
,0 AS GAMMA
,'Unknown' AS NETTING_NUMBER
,'Unknown' AS COLLATERAL_ACCOUNT_NUMBER
,'U' AS BUFFER_ELIGIBLE
,0 AS ALTERNATIVE_YIELD_RATE
,'Unknown' AS FTP_PORTFOLIO
,'U' AS REPO_FLAG
,0 AS LIMIT_EXPIRY_DATE
,ISNULL(rl.AL_DEFINITION, 'U') AS AL_DEFINITION
,0 AS REPO_AMOUNT
,'Unknown'AS TRANSACTION_NUMBER
,0 AS UNENCUMBERED_AMOUNT
,'Unknown' AS MARGIN_ACCOUNT_NUMBER
,CASE
WHEN rl.REPORT_LINE_NUMBER BETWEEN 6 AND 17 THEN 7
WHEN rl.REPORT_LINE_NUMBER=23 THEN 6
ELSE 1
END AS CASH_FLOW_TYPE
,0 as value_of_house
,0 as security_value
,'Unknown' as security_type
,0 AS INT_RATE_FLOOR
,0 AS INT_RATE_CEILING
,'Unk' as LCR_LINE_NUMBER
,'Unk' as AMM_LINE_NUMBER
,'Unknown' as basel_approach
,'Unknown' as CCR_portfolio
,'Unknown' as CCR_sector
,'Unknown' as CCR_region
FROM AllocationJunLLv2 b
INNER JOIN LIQ_EXCH_RATE_SIF fx ON 'USD'=fx.SOURCE_CURRENCY_CD
INNER JOIN DM_REPORT_LINE rl
ON CAST(SUBSTRING(b.STBITEM, 5, 2) AS int)=rl.REPORT_LINE_NUMBER AND rl.REPORT_NUMBER=48
WHERE ISNULL(CAST(CAST(
CASE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') WHEN '-' THEN '0' ELSE dbo.udf_ExtractChars(S_FormValue, '^0-9.E-') END
AS float)
AS numeric(22,6))
, 0) <> 0
AND NOT (
LEFT(b.STBITEM, 3) = 'F48' AND
RIGHT(b.STBITEM, 2) = 'C1' AND
CAST(SUBSTRING(b.STBITEM, 5, 2) AS int) BETWEEN 6 AND 17
)
UPDATE LIQ_ACCT_EOD_SIF
SET
CURRENT_VALUE=-CURRENT_VALUE,
MATURITY_VALUE=-MATURITY_VALUE
WHERE
REPORT_DATE=@repdate
GO
The errors I'm getting are:
Msg 8116, Level 16, State 1, Line 9
Argument data type datetime is invalid for argument 1 of substring function.
Msg 8116, Level 16, State 1, Line 17
Argument data type datetime is invalid for argument 1 of substring function.
Now I've tried
CAST(S_ReportingDate as nvchar(8))
but that didn't fix the problem, does anyone have any ideas?
Dynamic Job Schedule in SQL 2014
I have a SQL job that I have been trying to configure to run between a certain time (4pm - 7pm). I created the job I want to run between this time (lets call it DynJob). I set it a default schedule with the days I want it to run, and a default time value of 4pm.
But I don't want it to run at 4pm every single time it runs. I want it to run between 4pm - 7pm.
So I created another job (SJob). This job runs at 2pm (2 hours earlier than the scheduled start time for DynJob). All SJob does is run a query against the dbo.sysjobschedules, and does some math to come up with a value somewhere between 160000 and 190000, and inserts that value into the next_run_time field where the schedule_id = DynJob's schedule ID.
And it works (not really). It will adjust the time in the table, and I can see it gets a value (lets say 175691). But, it's all for not. Because when 4pm comes around, DynJob still gets run. Then SQL sets DynJobs next_run_time back to 4pm (160000) after it has run.
Am I going at this all wrong? Is there an easier way to set a job schedule for a window of time instead of one static time?
Here is the TSQL I am using that I talked about above.
USE [msdb]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
DECLARE @CallTime int = 1
DECLARE @MaxTime int = 30000
SET @CallTime = @MaxTime*RAND() + 160000
UPDATE dbo.sysjobschedules
SET next_run_time = @CallTime
WHERE schedule_id = 1
As always, thanks for taking the time!
P.S. Yes, I know having 2 jobs and 2 schedules just to get this to work is silly, but I am a newb and know not the ways of SQL Server Agent :) So any answers, or helpful documentation is always appreciated! If this is dumb, tell me! But explain it as well
what is the best data model to represent mathematical range (in database,xml,json...)?
mathematical range,for example:
greater or equal to 50 and smaller than 100 (>=50 && < 100)
smaller than 10 or greater than 40 (<10 || >40)
I have been thinking about how to represent mathematical range in a file and database, the range may be input by non programmer and I need to keep the input simple,but at another side, it also need to keep the input easy to convert to data and easy to check error input e.g.:"<10 || >100" seems the most simple but it is harder for me to parse the string to get the data,also need to consider input format error
I have been considering some input methods,using >=50 && < 100 as example,which is in key value form:
1.using 1 string to represent whole range:
<rangeInString>=50 && < 100</rangeInString>
2.separate 2 strings,one represent lower bound and another one represent upper bound,then parse each string in program:
<lowerBound> >=50 </lowerBound>
<upperBound> <100 </upperBound>
3.separate lower and upper bound,also separate the sign from number:
<lowerBound>
<sign> >= </sign>
<data>50</data>
</lowerBound>
<upperBound>
<sign> < </sign>
<data>100</data>
</upperBound>
4.separate lower bound and upper bound,also separate sign, and also separate the case that if includes the equal condition:
<lowerBound>
<sign> > </sign>
<isIncludeEqual>true</isIncludeEqual>
<data>50</data>
</lowerBound>
<upperBound>
<sign> < </sign>
<isIncludeEqual>false</isIncludeEqual>
<data>100</data>
</upperBound>
5.auto detect using "&&" or "||",e.g.:>= A with < B,if A < B,must be "&&" e.g.(>= 50 && <100),otherwise it is "||" e.g.(>= 100 || <50):
<A>
<sign> > </sign>
<isIncludeEqual>true</isIncludeEqual>
<data>50</data>
</A>
<B>
<sign> < </sign>
<isIncludeEqual>false</isIncludeEqual>
<data>100</data>
</B>
6.use a field "isAnd" to separate >=50 && < 100 (true) and <=50 || > 100 (false)instead of using field sign "<" and ">" :
<lowerBound>
<isIncludeEqual>true</isIncludeEqual>
<data>50</data>
</lowerBound>
<upperBound>
<isIncludeEqual>false</isIncludeEqual>
<data>100</data>
</upperBound>
<isAnd>true</isAnd>
7.other data model...
I need to consider somethings:
1.easy for non programmer to input
2.easy to convert or parse to data into program
3.easy to check error ,for example,parse string increase the complexity of converting data and checking incorrect format,also there may have other incorrect format,e.g.:<=50 && >100 should not be valid, I may allow auto detect using "&&" or "||" by the sign of input,but it may increase the complexity of the code
can anyone have idea?
Hive - How to remove column header from result set
I am trying to fix one issue which has created after hive query execution.
A new table named 'stock_data' which is holding stock price , stock symbol and all other details.
I had a tsv file ( tab separated file ) and used 'load data local inpath' command to load database table.
tsv file having column header like 'stock_name','stock_symbol' ....
Now, when I wanted to display stock symbol and with number of counts using below query :-
select stock_symbol,count(*) from stocks group by stock_symbol;
Output is loading column header
XOM 500
XRX 500
XTO 496
YPF 500
YUM 500
YZC 478
ZAP 494
ZF 494
stock_symbol 1
Time taken: 20.576 seconds, Fetched: 1735 row(s)
My question is :-
How to hide or remove 'stock_symbol 1' ( Last line ) from my result set ?
I tried to use below command before run :-
set hive.cli.print.header=false;
Did not work for me ...
Could anyone help me for the same ..
Thanks ..
Comparing two tables for equality
I have two tables, table1 and table2. Each with the same columns:
key, c1, c2, c3
I want to check to see if these tables are equal to eachother (they have the same rows). So far I have these two queries (<> = not equal in HIVE):
select count(*) from table1 t1
left outer join table2 t2
on t1.key=t2.key
where t2.key is null or t1.c1<>t2.c1 or t1.c2<>t2.c2 or t1.c3<>t2.c3
And
select count(*) from table1 t1
left outer join table2 t2
on t1.key=t2.key and t1.c1=t2.c1 and t1.c2=t2.c2 and t1.c3=t2.c3
where t2.key is null
So my idea is that, if a zero count is returned, the tables are the same. However, I'm getting a zero count for the first query, and a non-zero count for the second query. How exactly do they differ? If there is a better way to check this certainly let me know.
oracle sql cast a collection to another data type in sql statement
I have a collection of datasets that I need to convert into a string
SELECT COLLECT (col1) FROM test_table;
does anyone know how to cast/convert the collection into a string in an sql statement?
Cannot use Alias name in WHERE clause but can in ORDER BY
Why does this SQL not work?
The:
6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935')
- RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) )
Clause just calculates the order from a search point.
Which I am aliasing (because it so longwinded) to Distance.
SELECT [Hotel Id],latitude,longitude,establishmentname,6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) AS Distance FROM [dbo].[RPT_hotels] WHERE distance < '30' ORDER BY Distance
Here I replace the "Distance < 30" with the longwinded phrase and it works fine.
I can even ORDER BY the column alias and that works!!?
SELECT [Hotel Id],latitude,longitude,establishmentname,6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) AS Distance FROM [dbo].[RPT_hotels] WHERE 6371 * ACos( Cos(RADIANS(Latitude)) * Cos(RADIANS('50.017466977673905')) * Cos(RADIANS('24.69924272460935') - RADIANS(Longitude)) + Sin(RADIANS(Latitude)) * Sin(RADIANS('50.017466977673905')) ) < '30' ORDER BY Distance
What am I doing wrong?
Removing domain from sql query results
Say I use the command:
Select Username, UserType from USERS_DATABASE;
And i get the next results:
Username: UserType:
someDomain/user1 User
someDomain/user2 User
diffDomain/user3 User
Is there any way i can remove the domain through an SQL query or I need to parse it and then remove?
The domains can be different (Length, Characters and so on)
Thanks.
Mysql Conditional sum
Here is my sql
SELECT A.item_code,A.description,A.uom, A.open_stock, B.recd_total, C.issue_qty
FROM chem_master as A
LEFT JOIN (
SELECT item_code, SUM(recd_qty) as recd_total
FROM chem_receipts
GROUP BY item_code) AS B
ON A.item_code=B.item_code
LEFT JOIN(
SELECT item_code, sum(iss_qty) as issue_qty
FROM chem_issue
GROUP BY item_code) as C
ON A.item_code=C.item_code
HAVING (open_stock+recd_total-issue_qty) IS NULL OR (open_stock+recd_total-issue_qty)=0
Here, table chem_receipts & chem_issue do not have all the matching records in chem_master. Also, there may be multiple matching records.
I want to show items where stock is 0. There are 300 items in chem_master. But only 15 records in chem_receipts since we had additional purchase only in these 15 items. There are 100 records in chem_issue.
Now in the returned query against items where no additional purchase is made is shown as NULL & the same is the case where an item is never issued.
I understand that adding records of all the items with '0' in quantity in issue and receipt table will solve the issue. But that will not be a better method.
How can I improve this query?
Ruby on Rails join, where without losing associated objects
i am using ruby 2.1.5 on rails 4.2 and got a little problem I got Users and with a has many relation logins.
@users = User.joins(:logins).where("country LIKE ?", "%#{params[:searchc]}%").uniq
I want to find Users depending on the logins but i cant figure out a good solution to show the associated objects in my view too. With the join i lose the associated logins and only the found ones are left but i need all. Start a new load login ( @users.first.logins) in the view for each user is very slow...
How to get all records without duplicate
Hello everyone I'm trying to get all records without duplicate.
Here my sample database records.
(`id`, `productName`, `description`)
-----------------------------------------------
(1, 'OpenIDM', 'Platform for building enterprise provisioning solutions'),
(2, 'OpenAM', 'Full-featured access management'),
(3, 'OpenDJ', 'Robust LDAP server for Java'),
(4, 'OpenDJ', 'Robust LDAP server for Java') !!This line should not appear after SQL result
What I want to get with SQL
(`id`, `productName`, `description`)
-----------------------------------------------
(1, 'OpenIDM', 'Platform for building enterprise provisioning solutions'),
(2, 'OpenAM', 'Full-featured access management'),
(3, 'OpenDJ', 'Robust LDAP server for Java'),
Here is my SQL snippet for this subject.
select productName
from ForgeRock
WHERE productName in (select productName from ForgeRock group by productName having count(*)>1)
How can I get all elements without duplicates ? Thanks for helpings.
Get users where something where.not something
I'm trying to get group's users with specific ids that are not admin.
For the moment I have:
group.users
.joins(:roles)
.where(id: user_ids)
.where.not(roles: { role_type: Role::Type::ADMIN })
.pluck(:id)
In my log I have:
SQL to load the group:
(0.3ms) SELECT "users"."id" FROM "users" INNER JOIN "groups_users"
ON "users"."id" = "groups_users"."user_id"
WHERE "groups_users"."group_id" = $1 [["group_id", 137375]]
SQL for the query above:
(0.6ms) SELECT "users"."id" FROM "users" INNER JOIN "roles"
ON "roles"."user_id" = "users"."id" AND "roles"."is_destroyed" = $1
INNER JOIN "groups_users" ON "users"."id" = "groups_users"."user_id"
WHERE "groups_users"."group_id" = $2 AND "users"."id" IN (82884, 82885)
AND "roles"."role_type" != $3 [["is_destroyed", "f"],
["group_id", 137375], ["role_type", 1]]
The problem is I always get all the users of the group with matching user_ids. The where.not is not effective.
I had to do something like
users_in_group = group.users.where(id: user_ids).pluck(:id)
users_in_group -= group.users.joins(:roles).where
(roles: { role_type: Role::Type::ADMIN}).pluck(:id)
I don't understand why.
Insert Data To Another Table Using SQL Server Trigger
Hi, I have created two tables call enquiry_master and enquiry_master_hist..I'm updating column refference_no on table enquiry_master using SQL server Trigger..Now I want to insert enquiry_master_hist with same data what was inserted to enquiry_master. All fine data are inserting but that updated column only inserting with old data... their is any way to get updated data in SQL Server trigger??? This is code
UPDATE enquiry_master
SET enquiry_master.reference_no = pass.RandomPassword
FROM #Users tmp
CROSS APPLY dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@RefferenceNOLength)) pass
WHERE enquiry_master.enquiry_id = @EnquiryId;
DECLARE @InsertedID AS INT;
select @EnquiryId = i.enquiry_id from inserted i;
select @UpdatedRefference = i.reference_no from inserted i;
SET IDENTITY_INSERT enquiry_master_hist ON
INSERT INTO enquiry_master_hist([enquiry_id],
[enquiry_source_id],
[reference_no],
[assigned_staff_no],
[emp_id],
[status_id],
[remarks],
[enquiry_date_time],
[departing_from],
[travelling_to],
[departing_date],
[returning_date],
[hotel_name],
[airline],
[flight_class],
[no_of_adult_passangers],
[no_of_child_passangers],
[no_of_infant_passangers],
[taxes],
[from_price],
[total_price],
[name],
[home_office_no],
[mobile_no],
[prefered_callback_time],
[email],
[additional_comments],
[from_website],
[from_campaign],
[enquiry_type_id],
[type_of_group],
[budget],
[supplier],
[trip_code],
[trip_name],
[no_of_countries],
[no_of_days],
[countries],
[deal_reference_no],
[how_did_know],
[no_of_rooms],
[bedding],
[room_views],
[board_basis],
[system_date],
[system_time]
)
UPDATE enquiry_master
SET enquiry_master.reference_no = pass.RandomPassword
FROM #Users tmp
CROSS APPLY dbo.GeneratePassword_Real(CRYPT_GEN_RANDOM(@RefferenceNOLength)) pass
WHERE enquiry_master.enquiry_id = @EnquiryId;
DECLARE @InsertedID AS INT;
select @EnquiryId = i.enquiry_id from inserted i;
select @UpdatedRefference = i.reference_no from inserted i;
SET IDENTITY_INSERT enquiry_master_hist ON
INSERT INTO enquiry_master_hist([enquiry_id],
[enquiry_source_id],
[reference_no],
[assigned_staff_no],
[emp_id],
[status_id],
[remarks],
[enquiry_date_time],
[departing_from],
[travelling_to],
[departing_date],
[returning_date],
[hotel_name],
[airline],
[flight_class],
[no_of_adult_passangers],
[no_of_child_passangers],
[no_of_infant_passangers],
[taxes],
[from_price],
[total_price],
[name],
[home_office_no],
[mobile_no],
[prefered_callback_time],
[email],
[additional_comments],
[from_website],
[from_campaign],
[enquiry_type_id],
[type_of_group],
[budget],
[supplier],
[trip_code],
[trip_name],
[no_of_countries],
[no_of_days],
[countries],
[deal_reference_no],
[how_did_know],
[no_of_rooms],
[bedding],
[room_views],
[board_basis],
[system_date],
[system_time]
)
VALUES(
@EnquiryId,
@EnquirySourceId,
@UpdatedRefference,
@AssignedStaffNo,
@EmpId,@StatusId,
@Remarks,
@EnquiryDatetime,
@DepartingFrom,
@TravellingTo,
@DepartingDate,
@ReturningDate,
@HotelName,
@Airline,
@FlightClass,
@NoOfAdults,
@NoOfChild,
@NoOfInfant,
@Taxes,
@FromPrice,
@TotalPrice,
@Name,
@HomeofficeNo,
@MobileNo,
@PreferedCallbackTime,
@Email,
@AdditionalComments,
@FromWebsite,
@FromCompaign,
@EnquiryTypeId,
@TypeOfGroup,
@Budget,
@Supplier,
@TripCode,
@TripName,
@NoOfCountries,
@NoOfDays,
@Countries,
@DealRefferenceNo,
@HowDidknow,
@NoOfRooms,
@Bedding,
@RoomViews,
@BoardBasis,
@SystemDate,
@SystemTime)
SET IDENTITY_INSERT enquiry_master_hist OFF
END
strong text
Inscription à :
Commentaires (Atom)