|
Member 4487083 wrote: With SQL people often say you shouldn't do 'SELECT *'.
I also tell people not to run their stupid queries without starting a transaction that can be safely rolled back.
Again, you DON'T do a SELECT *. It's not that you save a lot by omitting a DateTime column - but it would prevent that blob-field of 2Gb each that was added last month to be pulled over the network with each and every friggin' request, killing the network and the database-server. Or a nice calculated field that cripples the DB-server.
It doesn't take much time, and makes the application a bit more robust. Makes it easier for me to debug when I get thrown in your team as a maintenance-programmer.
Yes, it takes extra time, but it has a good ROI. It's not a religious thing - I won't go medievel if you do a simple "SELECT * FROM". Still, if you do it in a query that contains several joins you'll get this lecture, as each extra table means another chance at pulling columns you don't need.
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Eddy Vluggen wrote: Again, you DON'T do a SELECT *. It's not that you save a lot by omitting a DateTime column - but it would prevent that blob-field of 2Gb each that was added last month to be pulled over the network with each and every friggin' request
That wouldn't be an issue in my case. I would never store that kind of stuff in this particular database as it's in Azure and would cost me a a lot of money. Something large would go in a blob or somewhere else.
|
|
|
|
|
Member 4487083 wrote: There will always be special cases eg. massive tables, but everything is fairly small in this particular application.
If there were in fact many columns and you only want a couple then you should do that regardless of any other consideration.
However I do it because there is no guarantee to ordering with '*' and that can matter in a variety of ways such as when using external APIs, dumping data, etc.
|
|
|
|
|
jschell wrote: However I do it because there is no guarantee to ordering with '*' and that can matter in a variety of ways such as when using external APIs, dumping data, etc.
I actually use an ORM so it doesn't use * but lists every column (same thing from a coding point of view).
|
|
|
|
|
Like you I use and ORM (I think all of us do), however I never use *, always explicitly list the columns, just because it is good discipline  . I also work with small datasets and almost never store blob/binary.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
To add to the other comments. If you use named columns the possibility of using a covering index increases.
Here[^] is some recommended reading on the subject.
|
|
|
|
|
Just remembered why else I don't like 'Select *'.
I had to debug a stored procedure that was failing on a union because the writer had a 'Select *' on a table from a database unioned with a 'Select *' on an archival version of the table from another database.
When the vendor the system was purchased from updated the production table to add a column, the union blew up because the tables didn't match, and, the added column was not necessary for the archival copy.
|
|
|
|
|
The comments have been useful, and pretty much match my thoughts. I was starting to think that maybe my ideas were a little out of date. NOSQL is moving more towards denormalization, which goes against a lot of what we do with relational databases.
At the time of writing the post, I hadn't put much thought into joins which will have the biggest problems. I'm looking at some other ways to simplify my architecture without sacrificing best practices and performance.
|
|
|
|
|
I've been looking at repository pattern implementations (using .Net and EF). Many of them aren't selective.
One of the most common methods is GetByID. This gets the whole record. Is this really bad? It will return one record, and in most systems it'll use the primary key.
One of the most shocking things I have seen is List<myentity> GetAll(). This returns ALL records in a table. This is clearly going to become an issue in large systems.
Another way some systems work is by using DDD and having aggregates return all related data. This tends to make the code simple, but I have doubts about scalability. Has anybody got experience with a system like this?
|
|
|
|
|
I'm back to the old foxpro databases using OLEDB.
I'm suppose to pull all the items in a Sales Orders, and go back into the history database file, and get the total QYT and AMOUNT purchased in the past as well.
I can do the math, but wow it's slow!, and I have an issue at the end with formatting the number with 2 decimal places for the cents. So I'm not sure if I need to fix it in the database call, or try and fix it when presenting the number in the PDF.
So $85.30 appears as $85.3
In the past I have had trouble, but not for years since.
The other one i.FPRICE works fine, so I'm scratching my head on this one.
Here's what I have
SELECT
i.FCUSTNO
, i.FORDQTY
, i.FITEMNO
, i.FDESCRIPT
, i.FPRICE
, (SELECT SUM(s.FSHIPQTY) FROM ARTRS01H.dbf s WHERE s.FCUSTNO = i.FCUSTNO AND s.FITEMNO = i.FITEMNO) AS FSHIPQTY
, (SELECT SUM(s.FAMOUNT) FROM ARTRS01H.dbf s WHERE s.FCUSTNO = i.FCUSTNO AND s.FITEMNO = i.FITEMNO) AS FAMOUNT
FROM SOTRS01.dbf i
WHERE i.FSONO=@FSONO
The reader
If Not (reader.IsDBNull(5)) Then sIRi(rC).FATD_SHIPQTY = reader.GetValue(5)
If Not (reader.IsDBNull(6)) Then sIRi(rC).FATD_TOTAL = reader.GetValue(6)
And in ASP.Net to create a PDF of the Order Confirmation
Dim lbl_item_FATD_TOTAL As Label
lbl_item_FATD_TOTAL = New Label("", 373, currentY, 45, 12, Font.Courier, 9, TextAlign.Right)
lbl_item_FATD_TOTAL.Text = String.Format("{0:c}", sSOI(idx).FATD_TOTAL)
currentPage.Elements.Add(lbl_item_FATD_TOTAL)
Any Suggestions would be cool.
|
|
|
|
|
jkirkerx wrote: Any Suggestions would be cool.
It's an unformatted decimal/double (as should be), until you convert it to a string. Below gives a predictable result:
Console.WriteLine(String.Format("{0:c}", 3.8));
Console.ReadLine();
There could be two things wrong here - either your formatting isn't applied, or it is formatting correctly. If your formatting is applied, then check out the regional settings in the configuration - if your end-user behaves like I do, then there'll be a non-default value.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
I just figured it out!
It was the PDF generator, in which the number was being formatted correctly, but the space I allocated for the data was not wide enough, so somehow it truncated the decimal values after the .
That was a head scratcher that I spent hours on this morning.
But thanks for the response and help, appreciate it. 
|
|
|
|
|
I have xml files with name space references. If I strip the namespace references from I file, I can read the elements and attributes using xQuery. However, with the namespace references in place, I get no results. I can't figure out how to add the references into the xQuery call.
XML
<clinicaldocument xmlns="urn:hl7-org:v3" xmlns:voc="urn:hl7-org:v3/voc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<recordtarget>
<patientrole>
<name>
<birthtime value="00000000">
SQL
SELECT
x.value('(patientRole/patient/birthTime/@value)[1]','varchar(50)') as birthDate
FROM @XML.nodes(
'declare namespace xlmns="urn:hl7-org:v3";
declare namespace xlmns:voc="urn:hl7-org:v3/voc";
declare namespace xsi="http://www.w3.org/2001/XMLSchema-instance";
/ClinicalDocument/recordTarget') as Addr (x)
|
|
|
|
|
|
Thanks for the reply/link but that code is for C# and I'm working in SQL unless I'm missing something.
I've never used this site before and haven't figured out the navigation.
|
|
|
|
|
kjfoley1 wrote: I'm working in SQL unless I'm missing something
No but it is Richards job/privilege as a protector to reduce cross posting, which this may not be.
Sorry to not be of any use - I hate xml.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
There has been an error processing your requestError in file: "D:\xampp\htdocs\magento\app\code\core\Mage\Customer\sql\customer_setup\install-1.6.0.0.php" - SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'customer_entity' already existsTrace:#0 D:\xampp\htdocs\magento\app\code\core\Mage\Core\Model\Resource\Setup.php(645): Mage::exception('Mage_Core', 'Error in file: ...')#1 D:\xampp\htdocs\magento\app\code\core\Mage\Core\Model\Resource\Setup.php(421): Mage_Core_Model_Resource_Setup->_modifyResourceDb('install', '', '1.6.2.0.1')#2 D:\xampp\htdocs\magento\app\code\core\Mage\Core\Model\Resource\Setup.php(327): Mage_Core_Model_Resource_Setup->_installResourceDb('1.6.2.0.1')#3 D:\xampp\htdocs\magento\app\code\core\Mage\Core\Model\Resource\Setup.php(235): Mage_Core_Model_Resource_Setup->applyUpdates()#4 D:\xampp\htdocs\magento\app\code\core\Mage\Core\Model\App.php(417): Mage_Core_Model_Resource_Setup::applyAllUpdates()#5 D:\xampp\htdocs\magento\app\code\core\Mage\Core\Model\App.php(343): Mage_Core_Model_App->_initModules()#6 D:\xampp\htdocs\magento\app\Mage.php(683): Mage_Core_Model_App->run(Array)#7 D:\xampp\htdocs\magento\index.php(87): Mage::run('', 'store')#8 {main}Error log record number: 1319383624
|
|
|
|
|
"D:\xampp\htdocs\magento\app\code\core\Mage\Customer\sql\customer_setup\install-1.6.0.0.php" - SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'customer_entity' already existsTrace:#0
That suggests that you already have the database created. Delete all the associated files and try again. Although it may be better to spend some time reading the documentation first.
|
|
|
|
|
thank you very much,i will try again!
|
|
|
|
|
This is my table
RowNumber TestDate
1 2014-01-01 00:00:00.000
2 2014-01-04 00:00:00.000
3 2014-01-05 00:00:00.000
4 2014-01-06 00:00:00.000
5 2014-01-10 00:00:00.000
I want to Make Out Put Like this
RowNumber StartDate EndDate
1 2014-01-01 00:00:00.000 2014-01-01 00:00:00.000
2 2014-01-04 00:00:00.000 2014-01-06 00:00:00.000
3 2014-01-10 00:00:00.000 2014-01-10 00:00:00.000
|
|
|
|
|
Then you really need to explain the criteria for selecting the various items. As displayed above it is not obvious how you choose which dates.
|
|
|
|
|
Try this, changing Modified and TableName to suit.
SELECT D.Dt, max(Modified)MaxDT,MIN(Modified)MinDT
FROM TableName N
INNER JOIN (SELECT DISTINCT CONVERT(DATE,Modified) Dt
FROM TableName ) D ON D.Dt = CONVERT(DATE,N.Modified)
GROUP BY Dt
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
This sql not getting the correct Out put, it shows like this
Dt MaxDT MinDT
2014-01-01 2014-01-01 00:00:00.000 2014-01-01 00:00:00.000
2014-01-04 2014-01-04 00:00:00.000 2014-01-04 00:00:00.000
2014-01-05 2014-01-05 00:00:00.000 2014-01-05 00:00:00.000
2014-01-06 2014-01-06 00:00:00.000 2014-01-06 00:00:00.000
2014-01-10 2014-01-10 00:00:00.000 2014-01-10 00:00:00.000
I want to get it like this way
RowNumber StartDate EndDate
1 2014-01-01 00:00:00.000 2014-01-01 00:00:00.000
2 2014-01-04 00:00:00.000 2014-01-06 00:00:00.000
3 2014-01-10 00:00:00.000 2014-01-10 00:00:00.000
|
|
|
|
|
Then you are screwed, there is no indication that the 5th is not the end of a job. You have not supplied enough information.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
If I understand the problem correctly, a start date means the preceeding date in not in the table; an end date is the last successive date from a start date in the table or the start date if there are no successive dates.
So...
2014-01-01 is a start date because 2013-12-31 doesn't exist; 2014-01-01 is the end date because 2014-01-02 doesn't exist.
2014-01-04 is a start date because 2014-01-03 doesn't exist, giving 2014-01-06 as the end date.
And so on...
Given that, provide the SQL statement you are using and someone may help; don't ask them to write everything for you.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
