Database

Re: Check Different Select query result [modified]

11-Aug-11 22:24

Thank you for you reply

Sorry, I didn't get what you mentioned. This are some of my many different conditions

SQL

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H20,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_H45,0)=0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

SQL

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 Or isNull(TDR_D40,0)>0 Or isNull(TDR_H40,0)>0 Or isNull(TDR_D45,0)>0 Or isNull(TDR_H45,0)>0) AND (isNull(FL_D20,0) =0 AND isNull(FL_D40,0)=0 AND isNull(FL_D45,0)=0 AND isNull(FL_H20,0)=0 AND  isNull(FL_H40,0)=0 AND isNull(FL_H45,0)=0) AND (isNull(CLL_D20,0)=0 AND isNull(CLL_D40,0)=0 AND isNull(CLL_D45,0)=0 AND isNull(CLL_H20,0)=0 AND isNull(CLL_H40,0)=0 AND isNull(CLL_H45,0)=0)

SQL

WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL AND (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 And isNull(TDR_D40,0)=0 And isNull(TDR_H40,0)=0 And isNull(TDR_D45,0)=0 And isNull(TDR_H45,0)=0)   AND ((isNull(FL_D20,0)>0 OR isNull(FL_D40,0) >0 OR isNull(FL_D45,0) >0 OR isNull(FL_H20,0) >0 OR isNull(FL_H40,0) >0 OR isNull(FL_H45,0) >0)  OR (isNull(CLL_D20,0) > 0 OR isNull(CLL_D40,0)  > 0 OR isNull(CLL_D45,0) > 0 OR isNull(CLL_H20,0) > 0 OR isNull(CLL_H40,0) > 0 OR isNull(CLL_H45,0)>0))

Wendelius11-Aug-11 23:04

Wendelius

11-Aug-11 23:04

Ok,

If I take an example. Perhaps the easiest way to combine different statements would be to use union. For example if you have following logic (pseudo):

SQL

select @result = count(*) from table where column1 < 100
if @result > 10 then color = blue
else
  select @result = count(*) from table where column1 > 100
  if @result = 20 then color = yellow

Now you could combine these to something like:

SQL

cursor = select count(*) from table where column1 < 100
         union all
         select count(*) from table where column1 > 100
if first_row_in_cursor > 10 then color = blue
else if second_row_in_cursor = 20 then  color = yellow

However, as you see, both select statements would get the count from different rows. In example 1 the second query isn't executed at all if count > 10. In the second example all the selects are executed even if the results in different portions aren't needed. This can be very time (and resource) consuming.

This was a trivial case and most likely isn't exactly the same as in your situation but the point I'm trying to make is that is it really beneficial to combine all the statements. Another point of view is that the statement may become very large and hard to maintain if it contains too much logic.

Another (perhaps an easy ) option is to use scalar queries. This might work more easily for your case. The previous example could be something like:

SQL

select
      @result1 = (select count(*) from table where column1 < 100)
      @ersult2 = (select count(*) from table where column1 > 100);

if @result1 > 10 then color = blue
else if @result2 = 20 then  color = yellow

However the same performance problem as described earlier may arise.

The need to optimize rises from a bad design.My articles[^]

modified on Friday, August 12, 2011 6:43 AM

Re: Check Different Select query result

Naunt12-Aug-11 0:56

Re: Check Different Select query result

12-Aug-11 0:56

Thanks again.

Like your last example, it's look clear and easy Smile | :)

But as you have told all selects will execute Frown | :(

Pascal Ganaye16-Aug-11 6:28

Pascal Ganaye

16-Aug-11 6:28

I don't know your data but you could try to get all your counts in one go using a CASE WHEN.
The COUNT aggregates only non null values.

SQL

SELECT 
	@result1 = COUNT(CASE WHEN (isNull(TDR_D20,0)= 0 And isNull(TDR_D40,0)=0 ...)	
		THEN 1 ELSE NULL END),
	@result2 = COUNT(CASE WHEN (isNull(TDR_D20,0)>0 Or isNull(TDR_H20,0)>0 ...)
		THEN 1 ELSE NULL END),
	@result3 = COUNT(CASE WHEN (isNull(TDR_D20,0)=0 AND isNull(TDR_H20,0)=0 ...))
		THEN 1 ELSE NULL END)
FROM table
WHERE Vessel_Code=@Vsl AND Voyage_No=@Voy AND POL=@POL

This would be efficient as you would only read your table once this way.

Then the logic you can probably remove quite a bit of the begin end and it becomes (sometime) clearer.

SQL

IF @result1 = 20    SET @Color='Red Color'
Else IF @result2=20 SET @Color='Blue Color'
Else IF @result3>0  SET @Color='Blue Color'
Else IF @result4>0  SET @Color='Blue Color'
Else IF @result5=20 SET @Color='Green Color'
Else IF @result6>0  SET @Color='Yellow Color'
Else                    @Color='Orange Color'

See this

sivakumat11-Aug-11 1:20

sivakumat

11-Aug-11 1:20

http://www.codeproject.com/Forums/1649/Csharp.aspx?select=3989498&tid=3989498

Compare 3columns from a row with data Null value

Naunt10-Aug-11 23:16

Re: Compare 3columns from a row with data Null value

10-Aug-11 23:16

Dear all,

Please kindly advise my case

How to Compare 3columns from a row with data are Null value

A1	A2	A3	B1	B2	B3	C1	C2	C3
25	Null	Null	25	Null	Null	25	Null	Null
Null	20	Null	Null	20	Null	Null	20	Null

SQL

Select * from Table1 Where (A1=B1 and B1=C1) And (A2=B2 and B2=C2) And (A3=B3 and B3=C3)

A1	A2	A3	B1	B2	B3	C1	C2	C3
25	0	0	25	0	0	25	0	0
0	20	0	0	20	0	0<big></big>	20	0

When data value are Null I didn't get result from my query, but after change Null value to 0 (zero) then I have got the result.
Could you please advise how to compare data with Null values ?

Thanks and best regards

Mycroft Holmes10-Aug-11 23:26

Mycroft Holmes

10-Aug-11 23:26

Read up about ISNULL

Select * from Table1 Where (ISNULL(A1,'')=ISNULL(B1,'') and ISNULL(B1,'')=ISNULL(C1,'')) And (ISNULL(A2,'')=ISNULL(B2,'') and ISNULL(B2,'')=ISNULL(C2,'')) And (ISNULL(A3,'')=ISNULL(B3,'') and ISNULL(B3,'')=ISNULL(C3,''))

I hope that copy/paste worked

Never underestimate the power of human stupidity
RAH

Re: Compare 3columns from a row with data Null value

sujit076110-Aug-11 23:55

sujit0761

10-Aug-11 23:55

Try the below query

Select * from Table1 Where (ISNULL(A1,0)=ISNULL(B1,0) and ISNULL(B1,0)=ISNULL(C1,0)) And (ISNULL(A2,0)=ISNULL(B2,0) and ISNULL(B2,0)=ISNULL(C2,0)) And (ISNULL(A3,0)=ISNULL(B3,0) and ISNULL(B3,0)=ISNULL(C3,0))

Use isnull with all the column fields. If the value is null it will take it as zero else it will take the coressponding values.

I hope this will help you out!!!!

Re: Compare 3columns from a row with data Null value

Naunt11-Aug-11 0:18

Loop in a Stored procedure

11-Aug-11 0:18

Many Thanks to Mycroft Holmes and Sujit0761

Both answer are work in order.

HELP !!

Josh7harington10-Aug-11 5:14

Josh7harington

10-Aug-11 5:14

Does anyone know where i may be able to find / download a English to Afrikaans OR Afrikaans to English Database ??? I need it for a project i am doing, i do not want one that connects online or a translator, i want an actual database with the words and translations.

Thanx alot,
Josh

Re: HELP !!

Eddy Vluggen10-Aug-11 11:04

Eddy Vluggen

10-Aug-11 11:04

First of all, don't start a topic with some generic topic like "help". I had to click on it and read it to determine it's contents - something I don't do when there's not much time. People could have an easy answer and skip your question, simply because the topic doesn't provide any clue. Most people posting questions here on the boards are looking for help in one way or the other.

On to your problem; there are some free dictionaries here[^], but I never seen them and can't say much about them.

Ergane[^] has it's dictionaries available as a separate download.

Last alternative, you can download the wiktionary[^].

Good luck Smile | :)

Bastard Programmer from Hell Suspicious | :suss:

lionelcyril9-Aug-11 20:50

lionelcyril

9-Aug-11 20:50

Hi,
I am using a SSRS report and the report is generated by executing a stored procedure.
The procedure gets 4 input values a,b,c,d where values c and d is a string containing multiple values.
I am executing a Complex SQL statement for all values of c and d.
For this reason i am using nested loop:

SQL

A as INPUT
B as INPUT
C as INPUT
D as input
Cursor Cur as OUTPUT

	Loop for c
		Loop for d
			execute the SQL Statement(select 
		END loop
	END Loop

problem:
Now the results of the SQL statement has to be saved temporarily somewhere I am not allowed use permanent tables and I m struglling using temporary tables.
If i save the value in the cursor each time the loop runs the previous results get overwritten.

Shameel9-Aug-11 22:20

Shameel

9-Aug-11 22:20

Use a Table Variable to store temporary data.

Syntax:

SQL

DECLARE @TableVar1 table(
    Col1 int NOT NULL,
    Col2 varchar(100),
    Col3 datetime
)

And then you can use it just like a physical table, e.g:

SQL

INSERT INTO @TableVar1 (Col1, Col2, Col3) VALUES (10, 'Hello', GETDATE())

lionelcyril10-Aug-11 0:40

lionelcyril

10-Aug-11 0:40

Just to let you know.I am using this procedure in a package.

Where should I write the temp table declaration?

Shameel10-Aug-11 8:42

Shameel

10-Aug-11 8:42

What does you mean by a package? Since you mentioned SSRS in your original post, I assumed the database to be SQL Server. AFAIK, SQL Server does not support packages, only Oracle does.

lionelcyril10-Aug-11 8:46

lionelcyril

10-Aug-11 8:46

Sorry I didnt mention earlier..
I am writing the stored procedure in oracle.
I wanted to know if I can use a array and the store the results in the reference cursor which can be pulled by SSRS. but i dont know how to pass the values form array to reference

S Douglas20-Aug-11 9:20

S Douglas

20-Aug-11 9:20

Shameel wrote:
AFAIK, SQL Server does not support packages, only Oracle does

FYI, SSIS (SQL Server Intergration Services) does use the term 'packages' for its code base.

Common sense is admitting there is cause and effect and that you can exert some control over what you understand.

jschell10-Aug-11 10:07

jschell

10-Aug-11 10:07

I doubt it is going to be efficient to try to incrementally retrieve values from Oracle and use them in sql server.

So you have two steps.
1. Retreive the data from Oracle, all of it, and put it into a suitable data structure.
2. Use it in SSRS.

First step would probably be generally achieved by
A. Create appropriate temp table(s)
B. Populate table(s) from Oracle.

The design of the temp tables depends specifically on business information based on the what you are doing in SSRS and how that relates to the data in Oracle. So if you cannot determine the structure of the tables yourself you are going to need to explain the problem in detail or provide a sample that has been reduced from those requirements.

Keep in mind that you do NOT attempt step 2 until you have completed step 1.

Inner Query in mysql

Rakesh Meel8-Aug-11 0:56

Rakesh Meel

8-Aug-11 0:56

Is there any posibility of Inner Query in my SQL as Like MS Sql?

Simon Bang Terkildsen8-Aug-11 1:53

Simon Bang Terkildsen

8-Aug-11 1:53

I'm actually not sure what Inner Query means, I'm guessing a subquery, if so then this should help
http://dev.mysql.com/doc/refman/5.1/en/subqueries.html[^]

Simon_Whale8-Aug-11 2:49

Simon_Whale

8-Aug-11 2:49

You need to expand on your question so that we can answer it correctly and hopefully save you time.

also have a read of this in case the other answer is not what you are looking for.

MySQL Inner joins[^]

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

DaveAuld8-Aug-11 12:44

DaveAuld

8-Aug-11 12:44

Do you mean nested Selects?

i.e.

SQL

Select something From tableThis Where ID In (Select somethingelse From tableThat Where condition );

if you do then;
http://dev.mysql.com/doc/refman/5.0/en/subqueries.html[^]

Dave
Find Me On: Web|Facebook|Twitter|LinkedIn

Folding Stats: Team CodeProject

Rakesh Meel8-Aug-11 19:38

Rakesh Meel

8-Aug-11 19:38

Thanks for good comment dear...
but this syntex not working proper now i m sending my sample query on message board.

"SELECT * From tbladussentry where SID in (select Id from tbladusentry where SID in ( select ID from tbladuset where CID in(108,367)))"

GuyThiebaut13-Aug-11 1:35

GuyThiebaut

13-Aug-11 1:35

I know you asked for subqueries however in this case I think joins are easier to read and more elegant:

select t1.* 
From tbladussentry t1
join tbladusentry t2
    on t1.SID = t2.Id
join tbladuset t3
    on t2.SID = t3.ID
    and t3.CID in(108,367)

Continuous effort - not strength or intelligence - is the key to unlocking our potential.(Winston Churchill)