[SQL] Using Control Flow Functions in a SELECT Statement

[Ashish Ahlawat]

Hi Team

I am unable to fetch data using following simple query it prompts following
error

*ORA: 00907: Missing right parenthesis*

Query :-

SELECT Name AS Title, StatID AS Status, RatingID AS Rating,

IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify

FROM DVDs



*Table Structure :-*

CREATE TABLE DVDs (

Name VARCHAR(60) NOT NULL,

NumDisks INT NOT NULL ,

RatingID VARCHAR(4) NOT NULL,

StatID CHAR(3) NOT NULL

)

rows has been inserted with different numdisks numbers.  pls help

warm R's

Re: [SQL] Using Control Flow Functions in a SELECT Statement

[Ashish Ahlawat]

Hi Team

Thanks FOR your prompt responseBut USING CASE issue still NOT resolved
Oracle prompts same error.

Team its a simple query but really instresting one...

Following data has been updated IN TABLE DVDs

INSERT INTO DVDs (Name, NumDisks, RatingID, StatID)

VALUES('Mash', 2, 'R', 's2'), ('View', 1, 'NR', 's1'), ('Show', 2, 'NR',
's2'), ('Amadeus', 1, 'PG', 's2') , ('Falcon', 1, 'NR', 's2'),

('Africa', 1, 'PG', 's1'), ('Doc', 1, 'G', 's2') , ('Christmas', 1, 'NR',
's1');


On 12/4/06, Bricklen Anderson <[EMAIL PROTECTED]> wrote:


Ashish Ahlawat wrote:
>
> Hi Team
>
> I am unable to fetch data using following simple query it prompts
> following error
>
> *ORA: 00907: Missing right parenthesis*
>
> Query :-
>
> SELECT
>
> Name AS Title, StatID AS Status, RatingID AS Rating,
>
> IF(NumDisks>1, 'Check for extra disks!', 'Only 1 disk.') AS Verify
>

i. That's an Oracle error message.
ii. IF does not belong in an SQL query. Use CASE.

Re: [SQL] join and sort on 'best match'

[Ashish Ahlawat]

hi pls tell me 

if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need
join ??*

*Ashish*



On 12/13/06, Ragnar <[EMAIL PROTECTED]> wrote:


On mið, 2006-12-13 at 10:26 +0100, Dirk Griffioen wrote:

> I have been breaking my head on the following problem: how to join 2
> tables and sort the results on the best match.
^
> - there are 3 tables, items, tags and items_tags. The items_tags table
> links items to tags.
> - I have one item which has certain tags, and I want to look up all
> the other items that have those tags as well

looks to me like you want to join:
items->item_tags->tags->item_tags->items

so the basic select is:
SELECT *
FROM  items AS i1
  JOIN items_tags AS it1 ON (it1.item_id = i1.id)
  JOIN tags AS t ON (t.tag_id = it1.tag_id)
  JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id)
  JOIN items AS i2 ON (i2.id = it2.item_id)
WHERE i1.id=?

> - results should be sorted and presented by 'best match': first all
> the items that have 3 tags in common, then 2 and last 1

this would be:

SELECT i1.id,i2.id,COUNT(*) as quantity
FROM  items AS i1
  JOIN items_tags AS it1 ON (it1.item_id = i1.id)
  JOIN tags AS t ON (t.tag_id = it1.tag_id)
  JOIN items_tags AS it2 ON (it2.tag_id = t.tag_id)
  JOIN items AS i2 ON (i2.id = it2.item_id)
WHERE i1.id=?
GROUP by i1.id,i2.id
ORDER BY quantity DESC

> I thought I had found the solution (my test cases worked), but I now
> find cases that should be found by the query but are not.

if this does not work, please provide us with a counter example.

gnari



---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

Re: [SQL] join and sort on 'best match'

[Ashish Ahlawat]

Hi team

I was just asking that If table ITEM3 has all the columns then why we need
to have a join ?? even we put a sorting very easily.


*** Any way  I have a very intersting question to all of you. I want to
fetch more that 70,000 BLOB from different customer servers. the issue is
there are some BOLB files with common names on all servers. So I want merge
these files into a single BLOB during  fetching data. I am able to fetch
the  BLOB data from all cust servers but unfortunatelly it overwrite
previous file.

So pls provide any simple query format for the same, assuming two table tab1
& tab 2.

Ashish  . INDIA



On 12/14/06, Markus Schaber <[EMAIL PROTECTED]> wrote:


Hi, Ashish,

"Ashish Ahlawat" <[EMAIL PROTECTED]> wrote:

> hi pls tell me 
>
> if table *Item 3 : news, nature, greenpeace, whale has all clmn y v need
> join ??*

Please try to write in English, so we can understand and answer your
questions.



Regards,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in Europe! www.ffii.org
www.nosoftwarepatents.org

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

[SQL] fetching & Merging BLOB

[Ashish Ahlawat]

Hi team

I have a very intersting question to all of you. I want to fetch more that
70,000 BLOB from different customer servers. the issue is there are some
BOLB files with common names on all servers. So I want merge these files
into a single BLOB during  fetching data. Although I am able to fetch the
BLOB data from all cust servers but unfortunatelly it overwrite previous
same name file instead of merging.

So pls provide any simple query format for the same, assuming two table tab1
& tab 2.

Ashish  . INDIA

[SQL] Fetching BLOBs

[Ashish Ahlawat]

Hi team

I have a very intersting question to all of you. Pls help me to build this
query, I want to fetch more that 70,000 BLOB from different customer
servers. the issue is there are some BOLB files with common names on all
servers. So I want merge these files into a single BLOB during  fetching
data. I am able to fetch the  BLOB data from all cust servers but
unfortunatelly it overwrite previous file.

So pls provide any simple query format for the same, assuming two table tab1
& tab 2.

Ashish  . INDIA