|
I ended up wrapping the select in a select statement, and adding the union to get the history and the current records. I think I went through like 5 or 6 versions of this before I got it to work. The numbers they produce are correct, so perhaps I got it right, and it's fast.
"SELECT " & _
" SUM(FSHIPQTY * FCOST) " & _
", SUM(FSHIPQTY * FPRICE) " & _
", SUM(FAMOUNT) " & _
", SUM(FSHIPQTY * FPRICE - FSHIPQTY * FCOST) " & _
" FROM " & _
"( " & _
" SELECT " & _
" h.FSHIPQTY " & _
" , h.FCOST " & _
" , h.FPRICE " & _
" , h.FAMOUNT " & _
" FROM ARTRS01H.dbf h " & _
" WHERE " & _
" h.FSHIPDATE >= @startDate AND h.FSHIPDATE <= @stopDate " & _
" AND " & _
" h.FITEMNO = @FITEMNO " & _
" UNION ALL " & _
" SELECT " & _
" v.FSHIPQTY " & _
" , v.FCOST " & _
" , v.FPRICE " & _
" , v.FAMOUNT " & _
" FROM ARTRS01.dbf v " & _
" WHERE " & _
" v.FSHIPDATE >= @startDate AND v.FSHIPDATE <= @stopDate " & _
" AND " & _
" v.FITEMNO = @FITEMNO " & _
") "
|
|
|
|
|
What you've done there is open to SQL Injection attackes, which are a serious security issue. For a really long but excellent article about dynamic SQL and SQL injection, see http://www.sommarskog.se/dynamic_sql.html[^].
A nested query like this will work for a small dataset, but the inner queries will be run for every row in the outer query. It won't take long for this to be a very slow query overall.
|
|
|
|
|
It's an old DOS accounting program "Account Mate" using DBF files; Fox Pro; in which a Windows 7 and 8 application that I wrote for the customer provides extra features used all day long for electronic invoicing, electronic order confirmation, electronic past due statements via emails and PDF attachments.
It's not exposed to the internet.
|
|
|
|
|
string qryInterviews = "SELECT ID, UpdatedByAdmin, UpdatedBy,Deleted " +
"CASE UpdatedByAdmin WHEN 1 THEN Date1 ELSE TimesForDate1 END AS D1, " +
"CASE UpdatedByAdmin WHEN 1 THEN Date2 ELSE TimesForDate2 END AS D2, " +
"CASE UpdatedByAdmin WHEN 1 THEN Date3 ELSE TimesForDate3 END AS D3, " +
"FROM myTable where (Deleted is null OR Deleted = 0)";
In the above query, UpdatedByAdmin,Deleted are of tinyInt datatype. Please help.
Dhyanga
|
|
|
|
|
That doesn't even look like valid SQL.
|
|
|
|
|
this is the currently running query in my project. I don't know how columnname with tinyInt datatype be compared to any given value in the case statement in linq.
Dhyanga
|
|
|
|
|
var query = (from sub in db.myTable
where (sub.Deleted == null || sub.Deleted == 0)
select new
{
sub.ID,
sub.UpdatedByAdmin,
sub.UpdatedBy,
sub.Deleted,
D1 = sub.UpdatedByAdmin.Equals(true ? sub.Date1 : sub.TimesForDate1),
D2 = sub.UpdatedByAdmin.Equals(true ? sub.Date2 : sub.TimesForDate2),
D3 = sub.UpdatedByAdmin.Equals(true ? sub.Date3 : sub.TimesForDate3)
}).ToList();
But this is giving error message as:
System.ArgumentException: DbComparisonExpression requires arguments with comparable types.
I think its because my UpdatedByAdmin, Deleted columns are tinyInt datatype in the database. But When i used these fields in model class, i used byte datatype. I thought byte is equivalent to tinyInt.
Please help.
Dhyanga
|
|
|
|
|
Dhyanga wrote: I thought byte is equivalent to tinyInt.
It is, but SQL tends to be more forgiving than C#.
|
|
|
|
|
In this case, the query is more readable than the LINQ version. Secondly, this query executes on the server.
What use does changing it to LINQ have?
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
I am trying to implement entity framework to that project..
Dhyanga
|
|
|
|
|
hello,
I'm looking for a tool to migrate a database in informix server to a local server mysql or postgree.
what you proposed?
Thank you.
|
|
|
|
|
Ibrahim.elh wrote: what you proposed?
Create a small application, connect to the source-database, connect to the target-database, read a record from source, write a record to target.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
i have Join that i expect to give me 3 Records but it gives me to Records . There is a Record in Table A and in table "C" there are two records , i used a left outer Join and i only get two records instead of 3. i dont want to use a Union to do this. Below is an Example
CREATE TABLE #TABLEA
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL
)
INSERT INTO #TABLEA
VALUES('047600055/R',NULL)
CREATE TABLE #TABLEC
(
LIS_KEY VARCHAR(MAX) NULL,
FUNC_KEY VARCHAR(MAX) NULL
)
INSERT INTO #TABLEC
VALUES('047600055/R','GEOSS001')
INSERT INTO #TABLEC
VALUES('047600055/R','GEOSS002')
SELECT * FROM #TABLEA A
left outer JOIN #TABLEC C
ON A.LIS_KEY = C.LIS_KEY
WHERE A.LIS_KEY = '047600055/R'
Thanks
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Got the Solution
SELECT *
FROM
(
SELECT * FROM #TABLEA A
UNION ALL
SELECT * FROM #TABLEC C
)t
WHERE LIS_KEY = '047600055/R'
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
What happened to I don't want to use UNION 
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i take back my words
Vuyiswa Maseko,
Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa[at]dotnetfunda.com
http://www.Dotnetfunda.com
|
|
|
|
|
Two rows would be the expected result for a LEFT JOIN with that data.
Each row from the table on the left will be returned once for each matching row in the table on the right, or once if there are no matching rows. Since the table on the right only has two rows, and the table on the left only has one row, the result will be two rows.
Jeff Atwood has a good visual explanation of SQL joins:
http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/[^]
Another way to explain joins:
- Product the Cartesian Product of the two tables - each row in the left-hand table is matched with each row in the right-hand table.
- Remove the rows where the
JOIN condition is not met. - Depending on the
JOIN type:
INNER JOIN: Nothing to do.LEFT (OUTER) JOIN: Any rows in the left-hand table but not in the results are added back, matched with a row of NULL values for the right-hand table.RIGHT (OUTER) JOIN:: Any rows in the right-hand table but not in the results are added back, matched with a row of NULL values for the left-hand table.FULL (OUTER) JOIN: Apply the rules for both LEFT and RIGHT joins.
- Filter the result based on the conditions in the
WHERE clause (if any).
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
|
Too spammy. Please consider the Product Showcase.
|
|
|
|
|
Hi everybody
I was looking information about this tip, a found a lot of information, but still no clear to me.
I would like to know what setup I have to make to view my reports across Internet.
I have read that is possible, from this link, but my topology is more simple http://technet.microsoft.com/es-es/library/ms159272%28v=sql.105%29.aspx[^]
But still I have somo doubt about the settings.
The topology is PC with SQL Server and SSRS ---> ROUTER ----> PC Client
What I did until now
Setup SSRS for network Service
Setup firewall port http://PC:8080/Report
Setup router for the same port
Try to access from my IPPublic to http://188.234.xxx.xxx:8080/Report
But didn´t work...
Any suggestion, or tip, will be gratefull 
|
|
|
|
|
First question - have you got SSRS up and running for those on the local network, can users see reports when on the local network?
Second question - what does 'didn't work' mean, what error messages did you get?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
modified 3-Nov-14 10:07am.
|
|
|
|
|
Hi, thank you very much for your interest
First question - have you got SSRS up and running for those on the local network, can users see reports when on the local network?
Yes, inside the network everything is working fine
Second question - what does 'didn't work' mean, what error messages did you get?
The problem is the browser, Internet explorer can't find the web page.
I don´t know what is wrong with my configuration.
Thank you
|
|
|
|
|
JM76 wrote: Internet explorer can't find the web page
That's still not telling me the error message you receive.
Are you getting a 404, what is the exact wording of the error message?
“That which can be asserted without evidence, can be dismissed without evidence.”
― Christopher Hitchens
|
|
|
|
|
JM76 wrote: The problem is the browser, Internet explorer can't find the web page.
If your browser cannot open Google, is that the browsers fault? I'd first check connectivity, in particular NAT.
Download a portable Apache-server and put it on USB. Start that on the machine that you are trying to reach. That way you can verify whether the computer can be found and the TCP settings/firwall/router is correct. I'm going to guess that the translation of the external IP to an internal IP is incorrect.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
GuyThiebaut
That's still not telling me the error message you receive.
Are you getting a 404, what is the exact wording of the error message?
Eddy Vluggen
If your browser cannot open Google, is that the browsers fault? I'd first check connectivity, in particular NAT.
Download a portable Apache-server and put it on USB. Start that on the machine that you are trying to reach. That way you can verify whether the computer can be found and the TCP settings/firwall/router is correct. I'm going to guess that the translation of the external IP to an internal IP is incorrect.
Hi Mr.GuyThiebaut and Mr.Eddy Vluggen
Thank you very much for your reply and sorry for my delay. Right now I´m out until the next monday. I can´t prove anything more. As soon as I come back I´m going to prove it and then I´ll reply
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
