Database

Re: tried to use a case when inside a WHERE clause but did not work. Need more efficient way to cover all IF ELSE scenarios.

10-Oct-12 9:26

What 'architect' came up with that beauty?

Can a MySql sproc call another stored procedure? If yes, then I'd suggest splitting the else-if's and the query's for readability, lest this will become a maintenance-horror very quick. I'd also recommend strongly to use the stored-procedure-name to identify the query that your architect needs to execute and not a friggin' varchar-parameter.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

SQL Ed10-Oct-12 13:15

SQL Ed

10-Oct-12 13:15

Please don't respond to my questions anymore.i didn't post a question for some punk kid to use profane language.Also if you have no idea what the requirements are why would you reply with such a stupid reply. Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.

Re: tried to use a case when inside a WHERE clause but did not work. Need more efficient way to cover all IF ELSE scenarios.

R. Giskard Reventlov10-Oct-12 13:28

R. Giskard Reventlov

10-Oct-12 13:28

It's not for you to decide who should and shouldn't answer your question: anything put here is fair game and your question is ridiculous: no one has the time to wade through all of that: You need to reduce the code to more manageable chunks.

SQL Ed wrote:
Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.

Change the rules.

BTW; I don't see any 'profane' language in his response. You should also recognize that this is an international forum and some cultures swear far more as part of everyday language. Don't down vote just because you don't like what he says. I up-voted him to counter you and because his response was reasonable.

"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: tried to use a case when inside a WHERE clause but did not work. Need more efficient way to cover all IF ELSE scenarios.

Jörgen Andersson10-Oct-12 22:26

Jörgen Andersson

10-Oct-12 22:26

It was a proper answer with good suggestions considering the info available. That we don't know the requirements should have been obvious to you as you haven't told us.

The suggestion that you need to modularize the code still stands though, but as you're limited to using only one sproc makes everything quite hard (what kind of requirement is that by the way?)
It seems though as you all the time use the same query with different filtering, so one way is to use a view for the main query, performance will suffer though.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Re: tried to use a case when inside a WHERE clause but did not work. Need more efficient way to cover all IF ELSE scenarios.

Shameel11-Oct-12 0:27

Shameel

11-Oct-12 0:27

SQL Ed wrote:
Please don't respond to my questions anymore

This is an International forum and anyone can reply to any posts. If you don't like it, just ignore it.

SQL Ed wrote:
punk kid

Nope, Eddy is a respected member here and his answers are always useful and helpful. Just see his reputation points.

SQL Ed wrote:
profane language

As far as I can see, there's none.

SQL Ed wrote:
if you have no idea what the requirements

Might be true, but you didn't tell us either.

SQL Ed wrote:
Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.

That's not a technical constraint. A stored procedure should be able to call another many levels deeper and your 3rd party application need not even be aware of it, it just needs to know the name of the "outermost" stored procedure.

Re: tried to use a case when inside a WHERE clause but did not work. Need more efficient way to cover all IF ELSE scenarios.

Eddy Vluggen11-Oct-12 0:35

How to retrieve client IP address from sql server database

11-Oct-12 0:35

SQL Ed wrote:
Please don't respond to my questions anymore

I could promise now, but would probably forget your name in a week.

SQL Ed wrote:
i didn't post a question for some punk kid to use profane language.

The "friggin" was superfluous. It was partly an emotional response, something that simply seems to happen whenever I meet a 'solution' without a problem. As is, the requirement does not add value, on the contrary, it makes things more complex and error-prone.

SQL Ed wrote:
Because we are using the code in a 3rd party application,the requirements call for keeping all the code in a single stored proc.

As hard as it may sound, but IMO that's where the error is. Unless there's a very good justification (besides the remark that it's a requirement), I'd still suggest fixing the problem at it's root.

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Tridip Bhattacharjee9-Oct-12 20:53

Tridip Bhattacharjee

9-Oct-12 20:53

i need a script by which i can get client IP address from sql server database when people do DML operation on table. so i was looking for solution and i found a script which does the things fine from sql server 2005. here is the script

SQL

CREATE FUNCTION [dbo].[GetCurrentIP] ()
RETURNS varchar(255)
AS
BEGIN
   DECLARE @IP_Address varchar(255);

   SELECT @IP_Address = client_net_address
   FROM sys.dm_exec_connections
   WHERE Session_id = @@SPID;

  Return @IP_Address;
  END

  SELECT dbo.GetCurrentIP()

but the above script do not work in sql server 2000. so i need a way to get client ip from sql server 2000. please guide. thanks

tbhattacharjee

Re: How to retrieve client IP address from sql server database

Herman<T>.Instance10-Oct-12 1:20

Herman<T>.Instance

10-Oct-12 1:20

Have you read this. Second answer in that thread tells you the options are very limited for IP retrieving in Sql Server 2000

In Word you can only store 2 bytes. That is why I use Writer.

MySQL code- Problems with cade when inside a WHERE clause

SQL Ed9-Oct-12 11:31

SQL Ed

9-Oct-12 11:31

The following code keeps erroring out in MySQL Workbench. I have not been able to locate the syntax problem. Can anyone see whats wrong with this code. Runnining the code a section at a time still dosn't work. It errors at the first ; where I placed the MAD face. Cannot completely remove the errors they just keep changing places. Also is there a good library of code snippets to view if needed?

/*
PATCH MANAGEMENT
*/

DELIMITER $$
#MachineType means Server or Workstation as determined by the word server in the os field
DROP PROCEDURE IF EXISTS usp_cbi_PatchManagement$$
CREATE PROCEDURE usp_cbi_PatchManagement(MasterGrp varchar(30),Criteria varchar(30),LocationID varchar(30),
ClientName varchar(30))

BEGIN

SELECT IFNULL(COUNT(DISTINCT A.computerid),0) AS 'Dynamic Machine Counts'
FROM (SELECT HF.computerid, HF.hotfixID
FROM hotfixdata HFD
INNER JOIN hotfix HF
ON HF.hotfixid = HFD.hotfixid
AND HFD.ignore <> 1
LEFT OUTER JOIN Computers AS C
ON C.ComputerID=HF.ComputerID
LEFT OUTER JOIN locations AS L
ON C.LocationID=L.LocationID
LEFT OUTER JOIN Clients AS CL
ON C.ClientID=CL.ClientID
JOIN agentcomputers AS AC
ON C.ComputerID=AC.computerid
JOIN mastergroups AS MG
ON AC.groupid=MG.groupId

#Enter Case When here seperating different where clauses.

WHERE

CASE
WHEN

Criteria = 'Workstations Missing Patches'

THEN

INSTR(C.os,"server")=0
AND INSTR(C.os,"microsoft")>0
AND HF.installed <> 1
AND HF.Approved = 1
AND LocationID = L.Name
AND ClientName = Cl.Name
Mad | :mad:

AND MasterGrp = MG.Name AND MG.ParentID =0;

WHEN
Criteria = 'Workstations with Empty Patch Tabs'

THEN

HF.hotfixid IS NULL
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")=0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = Cl.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;

WHEN
Criteria = 'Workstations Pending Reboots'

THEN

(C.Flags & 1024) = 1024
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")=0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = C.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;

WHEN
Criteria = 'Total Patch Workstations'

THEN

INSTR(C.os,"server")=0
AND INSTR(C.os,"microsoft")>0
AND LocationID = L.Name
AND ClientName = CL.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;

WHEN
Criteria = 'Servers Missing Patches'

THEN

INSTR(C.os,"server")>0
AND INSTR(C.os,"microsoft")>0
AND HF.installed <> 1
AND HF.Approved = 1
AND LocationID = L.Name
AND ClientName = Cl.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;

WHEN

Criteria = 'Servers Pending Reboots'

THEN

(C.Flags & 1024) = 1024
AND (C.lastcontact > DATE_ADD(NOW(),INTERVAL -7 DAY))
AND INSTR(C.OS,"server")>0
AND INSTR(C.OS,"microsoft")>0
AND LocationID = L.Name
AND ClientName = C.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;

WHEN
Criteria = 'Total Patch Servers'

THEN

INSTR(C.os,"server")>0
AND INSTR(C.os,"microsoft")>0

AND LocationID = L.Name
AND ClientName = CL.Name
AND MasterGrp = MG.Name AND MG.ParentID =0;

WHEN
Criteria = 'Total Patch Machines'

THEN

INSTR(C.os,"microsoft")>0
AND LocationID = 'all'
AND ClientName = 'all'
AND MasterGrp = 'all';

END CASE;

EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Parameter not found!!');

GROUP BY HF.hotfixID, HF.ComputerID) A;

END;

END$$

Re: MySQL code- Problems with cade when inside a WHERE clause

Jörgen Andersson9-Oct-12 11:47

Jörgen Andersson

9-Oct-12 11:47

A case when clause can only return values, not conditions.

Light moves faster than sound. That is why some people appear bright, until you hear them speak.
List of common misconceptions

Database connectivity failure when using ODBC

bharath_ben9-Oct-12 3:16

bharath_ben

9-Oct-12 3:16

Quote:
type Exception report

message

descriptionThe server encountered an internal error () that prevented it from fulfilling this request.

exception

javax.servlet.ServletException: java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

root cause

java.sql.SQLException: [Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application

Re: Database connectivity failure when using ODBC

Jochen Arndt9-Oct-12 3:43

Jochen Arndt

9-Oct-12 3:43

Searching for the error message and ODBC with Google founds this MSDN article[^] as first result.

When using a 64-bit Java you must use the 64-bit driver.

Re: Database connectivity failure when using ODBC

bharath_ben28-Nov-12 3:46

bharath_ben

28-Nov-12 3:46

Absolutely!

SQL counting

Qazzy648-Oct-12 8:27

8-Oct-12 8:27

I want to get a count of an ID where the ID is either = -999 or its not. So i have 2 counts in total. I used union all to get that but i think there would be better way to get the count from the same field. any help would be appreciated.

select count(ID) as Id_999 from table_A where ID='-999'
union all
select count(ID) from table_A where ID<>'-999'

there is no null values allowed so it either a number or -999 if null when table is loaded.

scottgp8-Oct-12 9:33

scottgp

8-Oct-12 9:33

You could turn the counts into separate columns:
select SUM (
CASE
WHEN ID = '-999' THEN 1
ELSE 0
END)
AS ID999_count,
SUM (
CASE
WHEN ID <> '-999' THEN 1
ELSE 0
END)
AS nonID999_count
from table_A

Scott

Qazzy648-Oct-12 10:07

8-Oct-12 10:07

Thanks Scott it works great. I was close to that but i was putting ID, then the sum(CASE statement). Thanks again.

Eddy Vluggen8-Oct-12 13:25

8-Oct-12 13:25

Qazzy64 wrote:
there is no null values allowed so it either a number or -999 if null when table is loaded.

Just curious; does that mean that you replace a null-test with a test for -999, and act according? Why was null disallowed anyway?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Qazzy648-Oct-12 17:35

8-Oct-12 17:35

When they load the table it has a number(ID) or if its null they load -999 so they know to reprocess it later on when other matching data shows up. Hope that helps.

Eddy Vluggen9-Oct-12 5:08

9-Oct-12 5:08

The reason I asked is because it does not make any sense to me; you'd still have to deal with the same type of checks. The only difference being that the new "strategy" will be alien to new developers, and will cause weirder and harder to debug-exceptions than a null-reference would.

I might be missing some obvious advantage of the approach. So, where is it? What's the 'added value' of using a marker-value above a null-value?

Bastard Programmer from Hell Suspicious | :suss:

if you can't read my code, try converting it here[^]

Qazzy649-Oct-12 7:16