|
Thanks, JSchell. I later found that although my apps were generally working OK, a couple of functions were failing. Digging deeper I found the issue was that on one StoredProc there was a "missing" parameter, and another was just missing altogether. Everything else was fine. I deduce from this that once alerted to the issue, they probably realised they'd forgotten to migrate the SPs and did a quick manual copy/paste. It's not a huge hosting company and although they have a few hundred databases, I suspect only a handful - or fewer - are using SPs so they may not even have been aware until I pointed out the issue.
They previously (last October) migrated to a new server op.system plus MySql upgrade. That also broke most of my sites since many call webservices on other domains, and the new server didn't support some of the older security protocols the sites were using. It was a one-line fix for each but took quite a while to identify. Also I couldn't send any emails via the SMTP server; I reported it and they claimed nothing had changed, but magically it started working about 10 minutes after I raised the ticket. Seriously p***ed off with them. They've given me a year's free hosting as compensation but when they continually break sites by screwing up upgrades (with no advance warning) I'd rather pay someone to do a proper job.... 
|
|
|
|
|
You might want to take a longer look at where you think your company/product is going.
Like I said I suspect MariaDb is better but, far as I can tell, there is more broad support for MySQL.
So if you think that you might want to move to a different host in three years then you should take care to continue to test and develop on MySQL. That would make your upgrade path easier then although harder now since you probably need to test on both. Although perhaps you could use that as a marketing claim ("runs on both!").
|
|
|
|
|
Fortunately it's a bespoke application for a specific client, so we'll host it wherever they want - and the choice of d/b will be part of that decision making process. In fact the whole data access layer is pretty much "pluggable" and switching DBMS is a non-event as far as the application itself is concerned - the only impact would be on stored procedures. So I'm not too fussed which d/b the host uses - what concerns me far more is that they think they can just "upgrade" one to another, and with no advance warning, no support, no awareness that the two are not 100% compatible, and not even knowing that they're different products, rather than just a later version. It's a real shame, because 15 years ago when I first started using them, they were small but outstanding. Expert support staff who really knew their product, and would go out of their way to assist and adapt as needed. They appreciated the importance of reliable and predictable service levels and the reputational damage outages could do for their clients. That ethos seems to have been abandoned...
|
|
|
|
|
DerekTP123 wrote: rather than just a later version. It's a real shame, because 15 years ago when I first started using them
I suspect that 15 years ago you also were different. So could be that they have gotten worse but could also be that you have become more discerning. Or a combination.
|
|
|
|
|
|
|
It lets you replace [MyDB].[dbo].[MyTable] with a shorter name, such as a:
SELECT a.*
FROM [MyDB].[dbo].[MyTable] AS a You can also use the aliasiing feature on column names, like so:
SELECT [MyReallyLongColumnName] AS ShorterName
FROM [MyDB].[dbo].[MyTable] A simple google search will give you all the nuances regarding the use of aliasing in SQL Server (for columns, sometimes you should, sometimes you must, and sometimes you don't have to use aliasing.
BTW, you generally alias table names when using join or merge.
".45 ACP - because shooting twice is just silly" - JSOP, 2010
-----
You can never have too much ammo - unless you're swimming, or on fire. - JSOP, 2010
-----
When you pry the gun from my cold dead hands, be careful - the barrel will be very hot. - JSOP, 2013
|
|
|
|
|
How can you get the alternate records from the table in the SQL?
|
|
|
|
|
The same way you get any records: you create a query that has the relevant parameters to extract the records that you are interested in.
|
|
|
|
|
|
1.If your looking random records from the table every time then use "order by NEWID()" with top clause.
EX:- SELECT TOP(10) * FROM TABLE ORDER BY NEWID();
2.Alternate records is identified with Identity column AND ROW_NUMBER() Rank function in
advance Rank function has choice to sorting based on columns under "CTE" and derived table.
|
|
|
|
|
I find your solution confusing.
Your first query
SELECT TOP(10) * FROM TABLE ORDER BY NEWID(); Just randomises the output and has nothing to do with selecting alternate records. Incidentally, you should avoid using reserved words (e.g. TABLE) as tablenames, but if you insist, then get into the habit of surrounding the reserved word with square brackets (i.e. [TABLE])
In your section 2 you have put Identity column in bold suggesting that it is someway relevant to identifying alternate records - it is not, as I stated earlier.
You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
Your final comment I think, is saying that the Rank function has an option to order columns - all Window functions have the potential for ORDER BY and/or PARTITION BY, that's why they are sometimes referred to as OVER functions. You can use Window functions on any table, not just derived tables or CTEs.
Here is an example of why Identity Column is not appropriate: Consider this sample data
create table test (d varchar(10))
insert into test (d) values
('Test 1'), ('Test 2'), ('Test 3'), ('Test 4'),
('Test 5'), ('Test 6'), ('Test 7'), ('Test 8')
DELETE from test WHERE Id = 3The contents of the table are
Id d
1 Test 1
2 Test 2
4 Test 4
5 Test 5
6 Test 6
7 Test 7
8 Test 8 Note the missing Id 3.
So I would expect to return rows where Id = 1, 4, 6 and 8. But if I just use the Identity Column
SELECT * FROM test where id % 2 = 1 I only get rows where id = 1, 5 and 7. Incorrect.
An example where RANK is inappropriate. Consider the following test data
create table test2 (Id int, d varchar(10))
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4'),
(2,'Test 5'), (2,'Test 6'), (3,'Test 7'), (3,'Test 8') The table contains the data
Id d
1 Test 1
1 Test 2
1 Test 3
2 Test 4
2 Test 5
2 Test 6
3 Test 7
3 Test 8 So I would expect to return the rows where d is Test... 1, 3, 5, 7.
If I try to use Rank like this
;with CTE AS
(
select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
FROM Test2
)
SELECT * FROM CTE WHERE r % 2 = 1 I get the correct answer. But I could just have easily used
SELECT * FROM CTE WHERE rn % 2 = 1 as both RANK and ROW_NUMBER return the same value in this instance. I contend that using ROW_NUMBER is clearer and less prone to risk - what if someone changes it to use a PARTITION … RANK() OVER (PARTITION BY id ORDER BY d) as r … you're going to get the rows where Test is … 1, 3, 4, 6, 7. Incorrect again.
Even if partition is not used, RANK can fail depending on the data being returned. Try adding some more data to test2 e.g.
insert into test2 (id,d) values
(1,'Test 1'), (1,'Test 2'), (1,'Test 3'), (2,'Test 4') Note that test2 now contains duplicate rows so the query that forms the CTE
select *, ROW_NUMBER() OVER (ORDER BY d) as rn, RANK() OVER (ORDER BY d) as r
FROM Test2 returns the following values
Id d rn r
1 Test 1 1 1
1 Test 1 2 1
1 Test 2 3 3
1 Test 2 4 3
1 Test 3 5 5
1 Test 3 6 5
2 Test 4 7 7
2 Test 4 8 7
2 Test 5 9 9
2 Test 6 10 10
3 Test 7 11 11
3 Test 8 12 12 Expected results would be Test 1, Test 2, Test 3, Test 4, Test 5, Test 7 but if I re-run the CTE I actually get Test 1, 1, 2, 2, 3, 3, 4, 4, 5, and 7. Incorrect again.
However using ROW_NUMBER will always work, regardless of the data contents
;with CTE AS
(
select *, ROW_NUMBER() OVER (ORDER BY d) as rn
FROM Test2
)
SELECT * FROM CTE WHERE rn % 2 = 1
|
|
|
|
|
CHill60 wrote: You then go on to mention "ROW_NUMBER() Rank function" - which one do you mean?
ROW_NUMBER is a ranking function; I suspect that's what Santosh meant. 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
I was trying to make him aware that his post was as clear as mud.
I'd just had a session with a user group from whom I'm trying to get some requirements. They (the group and the requirements!) are woollier than a woolly mammoth.
I wasn't in the best of moods.
|
|
|
|
|
You mean there can be requirements that aren't that woolly? 
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
We cannot share same solution..this site for sharing our knowledge and experience to find best and alternate solution to reach OP's expectations.I always try to understand others i hope u too 
|
|
|
|
|
I used SQLSERVER 2012,today when I query data,I found a very strange thing. As when I used the sql:
select count(1) as num from(select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx') )t
it returns 22 items
but when I use statement query in parentheses:
select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx')
it returns 20 items
how can this possible?I'm confused.
|
|
|
|
|
Quote:
select count(1) as num from(select * from View_paymentApproval v1 where exists(select 1 from payment_Approval where contractNo=v1.contractNo and payNo=v1.payNo and SendSAPStatus='0' and v1.approvalManCode='zhouyx') )t
it returns 22 items
As to the semantic used in your statement: it does NOT return 22 items. It returns a number which should be equal to the count of records returned by the view.
As to the strange issue... I can't reproduce your issue, but i can recommend to test this statement:
SELECT COUNT(*)
FROM View_paymentApproval v1
WHERE EXISTS (
SELECT 1
FROM payment_Approval
WHERE contractNo=v1.contractNo AND payNo=v1.payNo AND SendSAPStatus='0' AND v1.approvalManCode='zhouyx'
)
|
|
|
|
|
When you have a query that involves multiple tables, it's a good idea to prefix every column with the table name / alias:
SELECT *
FROM View_paymentApproval v1
WHERE Exists
(
SELECT 1
FROM payment_Approval a1
WHERE a1.contractNo = v1.contractNo
And a1.payNo = v1.payNo
And a1.SendSAPStatus = '0'
And v1.approvalManCode = 'zhouyx'
)
Not only does it make it easier to work out which table the column comes from, it can also prevent bugs.
For example, if the column contractNo existed in View_paymentApproval, but not in payment_Approval, then:
a1.contractNo = v1.contractNo would produce an error, whereas:
contractNo = v1.contractNo would produce incorrect results, since it would be equivalent to:
v1.contractNo = v1.contractNo which would always be true.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
it's right.Thank you,dude.The column 'contractNo' has null value.
|
|
|
|
|
Could someone who knows MySql well take a look at my question here?
Much appreciated.
[Editor: Moved]
Ger
modified 17-Apr-19 14:03pm.
|
|
|
|
|
Please don't do this. All questions get looked at in turn. Just think what the Lounge would be like if everyone followed your example.
|
|
|
|
|
This is now an older question and that phase has passed without much of value so this is the post of last resort.
Ger
|
|
|
|
|
You still have options:
- edit the question and add further information.
- reply to the person(s) who have already responded with some feedback or requests for more assistance.
|
|
|
|
|
I am trying retrieve record count for each column of a table with blank, not null, null and distinct count in oracle sql with query below.. however I am encountering below.. unable to find the missing paranethesis
select owner, table_name, column_name,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(distinct "' || column_name || '") as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as distinct_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when (' || column_name || ' = ' ' ) then 0 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as null_count,
to_number(xmlquery('/ROWSET/ROW/C/text()'
passing xmltype(dbms_xmlgen.getxml(
'select count(case when "' || column_name || '" is not null then 1 end) as c '
|| 'from "' || owner || '"."' || table_name || '"'))
returning content)) as notnull_count
from all_tab_columns
where owner = 'JAMES'
and table_name = 'TEST'
and data_type in ('NUMBER', 'DATE', 'TIMESTAMP', 'CHAR', 'VARCHAR2',
'NCHAR', 'NVARCHAR2');
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:
Error at Line: 9 Column: 58
Please can u help
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
