Database Discussion Boards

Re: Database table creation error

Mycroft Holmes11-Mar-12 2:06

Mycroft Holmes

11-Mar-12 2:06

Is is if you don't have system.Text referenced!

Never underestimate the power of human stupidity
RAH

Re: Database table creation error

Richard MacCutchan11-Mar-12 2:43

Richard MacCutchan

11-Mar-12 2:43

True; my comment was rather flippant.

Unrequited desire is character building. OriginalGriff
I'm sitting here giving you a standing ovation - Len Goodman

Re: Database table creation error

PIEBALDconsult11-Mar-12 3:40

PIEBALDconsult

11-Mar-12 3:40

Off-hand I'd guess that it is the GO lines.

sql ROLLUP

byka9-Mar-12 10:59

byka

9-Mar-12 10:59

I am using ROLLUP build in function
SELECT Product,COUNT(Total),
FROM myTable
Group BY TOTAL with ROLLUP

Percent - is calculated %.
when I run it for product I am getting NULL in ROLLUP row.
How to I replace with word?
so it looks like this
TOTAL 23 100 instead of NULL 23 100

Re: sql ROLLUP

R. Giskard Reventlov9-Mar-12 11:41

R. Giskard Reventlov

9-Mar-12 11:41

SQL

SELECT
    case when (Grouping(Product) = 1) then  'Total'
    else Product
    end Product,
    COUNT(Product) as Counter
FROM myTable
Group BY Product with ROLLUP

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Re: sql ROLLUP

Mycroft Holmes9-Mar-12 12:27

Mycroft Holmes

9-Mar-12 12:27

Well I'll be buggered, I did not know the keyword rollup, thank you have 5.

Never underestimate the power of human stupidity
RAH

Re: sql ROLLUP

R. Giskard Reventlov9-Mar-12 12:29

R. Giskard Reventlov

9-Mar-12 12:29

I'll take the 5, thank you, but pass on the buggery, if you don't mind. Smile | :)

"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum

me, me, me

Transfer Database

Zeyad Jalil7-Mar-12 20:47

Zeyad Jalil

7-Mar-12 20:47

Hi All,

I try to transfer my database from sql server 2008 to sql azure but the all ways failed until this time.

Please help me.. How I can transfer a database (Schema script + Data) from a local sql server 2008 to sql azure.

Thanks all

Re: Transfer Database

foxyland7-Mar-12 21:58

foxyland

7-Mar-12 21:58

Doesn't the backup-database-on-MSSQL2008-and-restore-it-on-azure work? Honestly, I have not use Azure yet, but because it worked on 2000-2005-2008, and I guess, it would work on Azure as well.

hth,
foxyland

Re: Transfer Database

Zeyad Jalil7-Mar-12 22:32

Zeyad Jalil

7-Mar-12 22:32

when i try to restore it the following error shown:
Statement 'RESTORE DATABASE' is not supported in this version of SQL Server.

Re: Transfer Database

rana ray10-Mar-12 1:32

rana ray

10-Mar-12 1:32

Is it resolved now?

regards,
Vatsa
www.objectiveprogramming.com

Re: Transfer Database

sherabhs11-Mar-12 18:47

sherabhs

11-Mar-12 18:47

I think there is edition mismatch between both the database instances where you are trying to backup and restore. Please check the edition of your source instance and destination of target instance. you cannot restore from a higher edition to a lower edition. For Ex- Restoration from standard edition backup to workgroup edition is not possible .

sheela
DBA

Re: Transfer Database

Simon_Whale8-Mar-12 1:12

Simon_Whale

8-Mar-12 1:12

Have a read of this

Migrating database to SQL Azure[^]

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

SQL Query enhancement

wonder-FOOL1-Mar-12 7:57

wonder-FOOL

1-Mar-12 7:57

I wrote a query that gets the counts of which has different statuses.
The query goes like

SQL

SELECT COUNT(rec.rmaNum) as Created
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0 
UNION 
SELECT COUNT(rec.rmaNum) as Received
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT COUNT(rec.rmaNum) as Closed
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT COUNT(rec.rmaNum) as Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4

The output of this query is;

Created
-------
0
1
3
6

I think because of the union the result set comes like this. What I want as output is different

Created Received Closed Voided
--------/----------/-------/--------
--0-----/-----1----/--3--/----6---

How can I get as a result set like the upper result? Thanks in advance.

Re: SQL Query enhancement

Chris Meech1-Mar-12 9:05

Chris Meech

1-Mar-12 9:05

I'd do it this way.

select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (
SELECT COUNT(rec.rmaNum) created_count
      ,0 received_count
      ,0 closed_count
      ,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated
WHERE rec.rmaStatus = 0 
UNION 
SELECT 0 created_count
      ,COUNT(rec.rmaNum) received_count
      ,0 closed_count
      ,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ure on ure.id = rec.userCreated
WHERE (rec.rmaStatus = 1 OR rec.rmaStatus = 2)
UNION
SELECT 0 created_count
      ,0 received_count
      ,COUNT(rec.rmaNum) closed_count
      ,0 voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 3
UNION
SELECT 0 created_count
      ,0 received_count
      ,0 closed_count
      ,COUNT(rec.rmaNum) voided_count
FROM RMARecords rec
LEFT JOIN RMAUsers ucl on ucl.id = rec.userCreated
WHERE rec.rmaStatus = 4
);

You could also look into creating a view for those joins. Smile | :)

Chris Meech
I am Canadian. [heard in a local bar]

In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]

posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]

Re: SQL Query enhancement

wonder-FOOL1-Mar-12 9:19

wonder-FOOL

1-Mar-12 9:19

Hello Chris,

In your first line

SQL

select sum(created_count), sum(received_count), sum(closed_count), sum(voided_count) from (

I am having errors in this line for the column names which you placed inside the brackets. For; created_count, received_count, closed_count, voided_count.

The error is; invalid column name.

In my sql statement I am only looking to the column name rmaNum.

Re: SQL Query enhancement

Chris Meech1-Mar-12 9:29

Chris Meech

1-Mar-12 9:29

Your original post included the 'as' keyword for aliasing. Perhaps your DB requires that. Try the following

...
SELECT COUNT(rec.rmaNum) as created_count
      ,0 as received_count
      ,0 as closed_count
      ,0 as voided_count
FROM RMARecords rec
...

Re: SQL Query enhancement

Corporal Agarn1-Mar-12 9:39

Corporal Agarn

1-Mar-12 9:39

You might try:

SELECT 
    SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created
    SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received 
    SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed
    SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided 
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated

Re: SQL Query enhancement

MaulikDusara4-Mar-12 23:39

MaulikDusara

4-Mar-12 23:39

TRY THIS

djj55 wrote:
SELECT
SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created
SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received
SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed
SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated

comma missing..

SQL

SELECT
    SUM(CASE WHEN rec.rmaStatus = 0 THEN 1 ELSE 0 END) as Created,
    SUM(CASE WHEN (rec.rmaStatus = 1 OR rec.rmaStatus = 2) THEN 1 ELSE 0 END) AS Received ,
    SUM(CASE WHEN rec.rmaStatus = 3 THEN 1 ELSE 0 END) AS Closed,
    SUM(CASE WHEN rec.rmaStatus = 4 THEN 1 ELSE 0 END) AS Voided
FROM RMARecords rec
LEFT JOIN RMAUsers ucr on ucr.id = rec.userCreated

Maulik Dusara
Sr. Sofware Engineer
I love it when a plan comes together

Re: SQL Query enhancement

Corporal Agarn5-Mar-12 0:32

Corporal Agarn

5-Mar-12 0:32

Thanks! I just did a quick non-tested coding. Blush | :O

Re: SQL Query enhancement

datakeyword8-Apr-12 20:19

datakeyword

8-Apr-12 20:19

Hi,
It's clear that you have met some trouble in this problem, why not try esProc to solve it?
esProc is a tool for mass data computation, especially fit for the complex data computation like yours.
Check below for more details:
A Query Language Over-perform SQL[^]

MySql Connection Suddenly Broken

PDTUM28-Feb-12 7:11

PDTUM

28-Feb-12 7:11

Gentleman:

I was using a MySql connection in VS 2008 in a database that I made with .Net 3.5 and it worked perfectly (a year ago). I updated to .Net 4.0 about that time but I have not had to use MySql since. Yesterday, I tried to get a MySql connector for .Net 4.0 project and I have not been able to do it. I have tried an older version connector but it does not work at all (no intellisence). In fact, the VS 2008 app no longer works, and the only way I can justify this is to feel that the some update along the way has nullified the original reference. So....is there a dot net connector for MySql for .Net 4.0?...which one works?...and where do I get it from? The dev.mysql.com site seems to be down right now.

Thank You for your help...Pat

Well...OK...since I could not get an answer here, I tried the following and it seems to have resolved the issue; I downloaded the provider marked 6.4.4 from the site dev.mysql.com, but I UNINSTALLED all previous MySql providers first from my (.Net 4.0) application and it is now working perfectly. I hope this will help someone else.. Smile | :)

modified 28-Feb-12 16:06pm.

Re: MySql Connection Suddenly Broken

Mycroft Holmes28-Feb-12 12:02

Mycroft Holmes

28-Feb-12 12:02

MySQL resources are a little thin on the ground here, it is after all a MS centric site and your problem is a little esoteric.

Glad you posted your resolution as it may well help the next poor sod struggling with this.

Never underestimate the power of human stupidity
RAH

Re: MySql Connection Suddenly Broken

PDTUM28-Feb-12 12:07

PDTUM

28-Feb-12 12:07

Hello Mr Holmes,

Understood. You have personally helped me in the past and I am gratefull. It is my pleasure to be able to occasionally add a small contribution. Thank you for your reply..Pat

Oracle 11g: Dynamic query exception

USAFHokie8028-Feb-12 7:06

USAFHokie80

28-Feb-12 7:06

Hi, I'm trying to write a dynamic query in a procedure called by a service. From examples i've found in the oracle docs, I think this is the right method, but I keep getting an exception. It seems to be a problem with an operator, but I don't see anything wrong. Anyone care to take a look? Thanks,

Exception:

ORA-00920: invalid relational operator
ORA-06512: at "SINC.EQUIPMENT_MGMT_PKG", line 89
ORA-06512: at line 1

The second error is pointing at the whitespace after the line "v_where:= ltrim(v_where, ' and');"

Procedure:

procedure FetchItemTechData_PRC
( c_items out T_CURSOR,
p_Niin IN CHAR,
p_Fsc IN CHAR,
p_Description in VARCHAR2,
p_Idn IN CHAR,
p_Tam IN CHAR,
p_Cos IN CHAR,
p_Scos IN CHAR,
p_Ec IN VARCHAR2,
p_LocalTam in VARCHAR2,
p_Status in Number,
p_PageNum in Number
) AS
v_where varchar2(2000);
BEGIN
if p_Niin is not null then
v_where:= concat(' p_Niin like ''%', concat(p_Niin, '%'''));
end if;
if p_Fsc is not null then
v_where:= concat(concat(v_where, ' and itd.fsc like ''%'), concat(p_Fsc, '%'''));
end if;
if p_Description is not null then
v_where:= concat(concat(v_where, ' and lower(itd.description) like lower(''%'), concat(p_Description, '%'')'));
end if;
if p_Idn is not null then
v_where:= concat(concat(v_where, ' and lower(itd.idn) like lower(''%'), concat(p_Idn, '%'')'));
end if;
if p_Tam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.tam) like lower(''%'), concat(p_Tam, '%'')'));
end if;
if p_Cos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.cos) like lower('), concat(p_Cos, ')'));
end if;
if p_Scos is not null then
v_where:= concat(concat(v_where, ' and lower(itd.scos) like lower('), concat(p_Scos, ')'));
end if;
if p_Ec is not null then
v_where:= concat(concat(v_where, ' and lower(itd.ec) like lower(''%'), concat(p_Ec, '%'')'));
end if;
if p_LocalTam is not null then
v_where:= concat(concat(v_where, ' and lower(itd.local_tam) like lower(''%'), concat(p_LocalTam, '%'')'));
end if;
if p_PageNum > -1 then
v_where:= concat(concat(v_where, ' and rownum > '), p_PageNum * 100);
v_where:= concat(concat(v_where, ' and rownum < '), (p_PageNum+1) * 100);
end if;

v_where:= ltrim(v_where, ' and');

open c_items for
'select
itd.niin,
itd.fsc,
itd.description,
itd.idn,
itd.tam,
itd.cos,
itd.scos,
itd.ec,
itd.local_tam,
itd.local_ind,
itd.serialized_ind,
itd.ui,
itd.unit_price,
itd.status,
itd.created_id,
itd.created_dt,
itd.modified_id,
itd.modified_dt
from item_tech_data itd
where :whereClause' using v_where;

END FetchItemTechData_PRC;

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.