Database

Re: Using the output of a case statement as selection criteria in an IN clause

21-Oct-09 21:51

Try

ELSE SELECT 'W' UNION SELECT 'R'

Member 265586122-Oct-09 4:16

Member 2655861

22-Oct-09 4:16

I have tried that but get a syntax error
line 14
Incorrect syntax near the keyword 'SELECT'
line 15
Incorrect syntax near ')'

line 14 ELSE SELECT 'W' UNION SELECT 'R'
line 15 END)

I have tried just the SELECT CASE part of the Query with no group key and line 14 as
ELSE 'W'+','+'R' and this returns W,R.
Even tried doubling up the quotes viz: ELSE '''W'''+','+'''R''' which returns 'W','R'
but this also fails to bring back rows

Re: Using the output of a case statement as selection criteria in an IN clause

J4amieC22-Oct-09 4:39

Re: Using the output of a case statement as selection criteria in an IN clause

22-Oct-09 4:39

Ive tried this a few different ways, and hit upon the same error as you've gotten.

I think you're going to be reduced to splitting it into IF..THEN logic

IF EXISTS(SELECT 1
   FROM group gr1
   WHERE gr1.group_key = 'RA'
   AND gr1.gra_valid = 1
   AND 0 = (gr1.gra_flags & 7)^3) 
      SELECT * FROM callsigns WHERE mantype = 'R'
ELSE IF EXISTS(SELECT 1
   FROM group gr2
   WHERE gr2.group_key = 'RA'
   AND gr2.gra_valid = 1
   AND 0 = (gr2.gra_flags & 7)^1) 
      SELECT * FROM callsigns WHERE mantype = 'W'
ELSE
      SELECT * FROM callsigns WHERE mantype IN ('W','R')

Edit: I actually think thats also miles more readable than your original too. Big Grin | :-D

Member 265586122-Oct-09 5:04

Member 2655861

22-Oct-09 5:04

Thanks for that.
I had come to the same conclusion myself. Seems a pity that CASE could not be used a quirk of SQL Server!!

New to db4o? Check this funny intro

netquake21-Oct-09 2:44

netquake

21-Oct-09 2:44

Check this series of funny (comic style) db4o blog posts which make a great way to get you started with db4o (why not having some fun while you're learning?). The blog posts also deal with key issues that you must have in mind if you're new to db4o (activation, object identity, etc).

Includes:

db4o, The Basics
db4o: Activation- & Update-Depth
db4o: Object-Identity and High-Level-Caching
db4o: Transactions
db4o: Persistent Classes Tips
db4o: Single Object-Container Concurrency

http://developer.db4o.com/blogs/community/archive/2009/10/21/new-to-db4o-learn-have-fun.aspx[^]

Re: New to db4o? Check this funny intro

Mycroft Holmes21-Oct-09 13:20

Mycroft Holmes

21-Oct-09 13:20

The only reason this has not been voted off as abuse of the forum is because it is open source (note the down votes), if it was commercial it would no longer be here.

If you want to promote something like this why not write an article, there is a product article category specifically set up for this and I'd be surprised if there is a charge for publishing and article on open source stuff.

Never underestimate the power of human stupidity
RAH

[Message Deleted]

khan201021-Oct-09 2:17

21-Oct-09 2:17

[Message Deleted]

J4amieC21-Oct-09 2:22

21-Oct-09 2:22

Gosh im getting a bit sick of writing sql queries for people, maybe im just getting grouchy but this is seriously about the easiest query you could ask for.

Why dont you give it a try, post what you get and what problems you encounter, and i'll be happy to help.

If you're incapable, I suggest you go back to your boss, hand him your keycard and tell him you are not qualified to be a developer.

dan!sh 21-Oct-09 2:33

dan!sh

21-Oct-09 2:33

J4amieC wrote:
Why dont you give it a try,

I hope it does not turns out as one of the recent threads in the visual basic forum.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

khan201021-Oct-09 2:51

21-Oct-09 2:51

'''I tried this following Query but it giving me wrong total what i am missing in following query

SELECT Prd.Item_Name, sum(P.Piece_No) AS PStock, sum(D.Itm_Qty) AS SoldStock, sum(R.Itm_Qty) as RefStock, sum(P.Piece_No) - sum(D.Itm_Qty) - sum(R.Itm_Qty) AS Avl_Stock
FROM Product AS Prd, Purchase_Detail AS P, Detail_Description AS D, Refund_Detail as R
WHERE Prd.Item_Name=P.Item
GROUP BY Prd.Item_Name;

i.j.russell21-Oct-09 3:03

i.j.russell

21-Oct-09 3:03

You need to add links in the WHERE clause for D and R to Prd. At the moment you have cross joins to these two tables so you will get incorrect results.

You may want to look at using LEFT OUTER JOINS as it is possible that products may not have been sold or refunded and in that case, the results will not appear for that product if you use INNER JOINS.

khan201021-Oct-09 3:24

21-Oct-09 3:24

Thanks for reply i.j.russell

if i am using this
WHERE Prd.Item_Name = P.Item and Prd.Item_Name = d.Item and Prd.Item_Name = R.Item_Name

Its just giving me Refunded items with wrong total,

khan201021-Oct-09 5:00

21-Oct-09 5:00

I Tried this also but not getting right total.
'''''''''''''''''

SELECT Prd.Item_Name, Sum(P.Piece_No) AS PStock, Sum(D.Itm_Qty) AS SoldStock, Sum(R.Itm_Qty) AS RefStock, Sum(P.Piece_No)-Sum(D.Itm_Qty)-Sum(R.Itm_Qty) AS Avl_Stock
FROM Refund_Detail AS R, (Product AS Prd INNER JOIN Purchase_Detail AS P ON Prd.Item_Name = P.Item) INNER JOIN Detail_Description AS D ON Prd.Item_Name = D.Item
WHERE (((Prd.Item_Name)=[P].[Item] And (Prd.Item_Name)=[d].[Item]))
GROUP BY Prd.Item_Name;

khan201021-Oct-09 5:02

21-Oct-09 5:02

Hey.. Where are you Mr. J4amieC..
If you are capable answer this ...
Else....You know what...

i.j.russell21-Oct-09 5:10

i.j.russell

21-Oct-09 5:10

Is there any need for that? I fully agree with his comments. I only helped you because you tried to resolve it yourself and got part of the way there. If this is your attitude, I will refrain from assisting you any further and hope that others do the same.

khan201021-Oct-09 5:37

21-Oct-09 5:37

tell me broo why we are here, why peoples are posting his problem here...?
for to hear this " Go to your boss and tell him you are not qualified developer " ??

Before i posted this problem i already tried above query

and thats why i asked question here ?

I am also replying answer from other forums also..
i know how to answer and how to reply.
I am telling anyone who is asking so stupid question like this. But i am replying them.

i think you understand what i mean to say.

dan!sh 21-Oct-09 5:50

dan!sh

21-Oct-09 5:50

Let me break this down for you. See your original post. It just tells the problem followed by code demand. It shows as if either:
1. You are not capable of doing that and completely clueless, or
2. Lazy enough to do your work.
Although it does not proves you are any of these, but the impression is like that. As you mentioned, you had already tried something, you should have mentioned that. If you would have structured your post as:

1. Your task.
2. What have you tried.
3. Where did you got stuck while solving the problem.

This would give a better impression and better responses from others. Smile | :)

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

khan201021-Oct-09 7:18

21-Oct-09 7:18

Danish your way to discuss is very good.
you understand the fault of my first post.
and you mentioned it on very nice way,
i understand and thanks to explain me.

But i am sorry Danish i just dont like the way of other helps.

dan!sh 21-Oct-09 7:39

dan!sh

21-Oct-09 7:39

khan786 wrote:
i just dont like the way of other helps

Well, learn from the mistakes and get over it.

It's not necessary to be so stupid, either, but people manage it. - Christian Graus, 2009 AD

J4amieC21-Oct-09 5:32

Re: What Query i have to used [modified]

21-Oct-09 5:32

khan786 wrote:
If you are capable answer this ..

Absolutely.

khan786 wrote:
Else....You know what...

Er...no... what?

J4amieC21-Oct-09 6:02

21-Oct-09 6:02

Just to get rid of another useless, no-brainer of a post... here you go:

// Perfectly workable solution removed, as the OP is an ungrateful gimp.

Gives the exact output you needed, with the test code you provided. No warranties or guarantees. Change the names to your actual table names yourself.

edit: After reading the above comments, this is the very last time you'll get help from me.

edit2: And after reading the below comment, you'll not even get the benefit of this answer.

modified on Wednesday, October 21, 2009 2:21 PM

khan201021-Oct-09 7:20