| View previous topic :: View next topic |
| Author |
Message |
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
 Posted: Wed Jun 14, 2006 5:02 am Post subject: sql query needed based on count(*) |
 |
|
Hai All,
I have a table with some rows and there are some rows for which some column values are blanks.I want to do certain kind of operation:
Check the count of total records with the count of total records which have blanks in one of the columns and based on the condition i want to execute / not execute some sql statment.
My code:
| Code: |
SELECT CASE
WHEN SELECT COUNT(*) FROM TABLENAME =
SELECT COUNT(*) FROM TABLENAME WHERE COUMNNAME = ' '
THEN EXECUTE SOME SQL QUERY
WHEN SELECT COUNT(*) FROM TABLENAME <>
SELECT COUNT(*) FROM TABLENAME WHERE COLUMNAME = ' '
THEN EXECUTE SOME SQL QUERY
ELSE 0
END) FROM TABLENAME;
|
Can anybody help me out as i am getting SQL error -104 ?
[code:1:eaefb55a57]
DSNT408I SQLCODE = -104, ERROR: ILLEGAL SYMBOL \"COUNT\". SOME SYMBOLS THAT
MIGHT BE LEGAL ARE: < > = <> <= !< !> != >= |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 14, 2006 7:23 am Post subject: |
|
|
mfuser,
The Count Scalar function will never return a Blanks. It may return a Null value but never blanks. I am not sure as to what you are trying to do. Post a sample input and explain us the conditions and the actions to be done if the conditions are satisfied.
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
| Posted: Wed Jun 14, 2006 8:04 am Post subject: |
|
|
Kolusu,
Say for example i have a table with 5 records:
| Code: |
EMPNO FIRSTNAME MIDINIT LASTNAME
---------+---------+---------+---------+
100001 A Z MFUSER
100002 B Z MFUSER
100003 C Z
100004 D Z MFUSER
100005 E Z
SELECT COUNT(*) FROM TABLENAME will give me 5 records.
SELECT COUNT(*) FROM TABLENAME WHERE LASTNAME = ' ' will give me 2 records.
|
What exactly i want is to check both the counts ,
if they are equal , execute a update query which should update all 5 records with LASTNAME = 'MAINFRAME'
if they are not equal , execute a update query which should update all 5 records with LASTNAME = 'DB2'
There could be many ways to do so , but i want to achieve the results using CASE expression by the query posted by me . |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 14, 2006 8:59 am Post subject: |
|
|
mfuser,
try this sql
| Code: |
UPDATE TABLE
SET LASTNAME = (SELECT CASE WHEN SUM(CASE WHEN LASTNAME = ' '
THEN INT(1) ELSE INT(0) END) =
SUM(CASE WHEN LASTNAME > ' '
THEN INT(1) ELSE INT(0) END)
THEN CHAR('MAINFRAME')
ELSE
CHAR('DB2')
END FROM TABLE)
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mainhead
Beginner
Joined: 07 Feb 2006
Posts: 26
Topics: 11
|
| Posted: Wed Jun 14, 2006 1:09 pm Post subject: |
|
|
| Great Answer !!! |
|
| Back to top |
|
|
mfuser
Banned
Joined: 01 Mar 2005
Posts: 105
Topics: 58
|
| Posted: Wed Jun 14, 2006 1:27 pm Post subject: |
|
|
Kolusu,
You are simply great and i am surprised by seeing your query that it can be written so nicley .Can i have the some more information about the query coded by you as i am unable to properly understand it ?
BEFORE
| Code: |
---------+---------+---------+---------+
EMPNO FIRSTNAME MIDINIT LASTNAME
---------+---------+---------+---------+
100001 A Z MFUSER
100002 B Z MFUSER
100003 C Z
100004 D Z MFUSER
100005 E Z
|
AFTER
| Code: |
---------+---------+---------+---------+
EMPNO FIRSTNAME MIDINIT LASTNAME
---------+---------+---------+---------+
100001 A Z DB2
100002 B Z DB2
100003 C Z DB2
100004 D Z DB2
100005 E Z DB2
|
|
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Jun 14, 2006 3:16 pm Post subject: |
|
|
Mfuser,
The query is quite simple. we are creating 2 temp columns using the case statement. look at the case statement on lastname.
The first column will have a value of 1 when the last name is spaces and 0 when it is not.
The second column will have a value of 1 when the last name is NOT spaces and 0 when it is space.
ex:
| Code: |
LNAME COL1 COL2
======= ===== =====
MFUSER 0 1
MFUSER 0 1
1 0
MFUSER 0 1
1 0
|
Now we use SUM function on COL1 and COL2 so that we have the count of no: of space records and count of non space records.
The sum will produce counts as follows
| Code: |
COL1 COL2
===== =====
2 3
|
Now we use another case statement to see if col1 = col2 . If they are equal then we set the update value to 'mainframe' and if they are not equal then we set the update value to 'DB2'
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
shekar123
Advanced
Joined: 22 Jul 2005
Posts: 528
Topics: 90
Location: Bangalore India
|
| Posted: Wed Jun 14, 2006 3:27 pm Post subject: |
|
|
Oh God, Kolusu what kind of query is this ,amazing to think of a programming logic in a query and the logic used is unbelievable.
| Code: |
YOU ARE SIMPLY THE BEST MAINFRAMER.
|
_________________
Shekar
Grow Technically |
|
| Back to top |
|
|
mvenkatesha
Beginner
Joined: 10 Oct 2006
Posts: 16
Topics: 6
Location: Bangalore
|
| Posted: Thu Dec 14, 2006 4:44 am Post subject: |
|
|
kolusu
I am trying each topic posted on MVS forms. On this topic I have found some errors. Can you please find the query below and please let me know why I am getting SQLCODE = -118.
My Table Defination->
| Code: |
CREATE TABLE RUSH_TEST1
(
EMPNO INTEGER NOT NULL
, EMPNAME CHAR(5)
, DEPT CHAR(5)
, PRIMARY KEY(EMPNO)
);
|
Values in the table
| Code: |
SELECT * FROM RUSH_TEST1 ;
---------+---------+-------
EMPNO EMPNAME DEPT
---------+---------+-------
1 NAME1 MFRAM
2 NAME2 MFRAM
3 NAME3 MFRAM
4 NAME4 MFRAM
5 NAME5 DB2DB
6 NAME6 DB2DB
7 NAME7 DB2DB
8 NAME8 DB2DB
|
Update Query Format 1.
| Code: |
UPDATE RUSH_TEST1
SET DEPT =
( SELECT CASE WHEN SUM ( CASE WHEN DEPT = 'MFRAM'
THEN INT(1)
ELSE INT(0)
END
) =
SUM ( CASE WHEN DEPT = 'DB2DB'
THEN INT(1)
ELSE INT(0)
END
)
THEN CHAR('MURTY')
ELSE CHAR('RASHM')
END
FROM RUSH_TEST1
);
---------+---------+---------+---------+---------+---------+---------+----
DSNT408I SQLCODE = -118, ERROR: THE OBJECT TABLE OR VIEW OF THE DELETE OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE
|
Update Query Format 2.
| Code: |
UPDATE RUSH_TEST1
SET DEPT =
( SELECT CASE WHEN SUM ( CASE WHEN A.DEPT = 'MFRAM'
THEN INT(1)
ELSE INT(0)
END
) =
SUM ( CASE WHEN A.DEPT = 'DB2DB'
THEN INT(1)
ELSE INT(0)
END
)
THEN CHAR('MURTY')
ELSE CHAR('RASHM')
END
FROM RUSH_TEST1 A
);
---------+---------+---------+---------+---------+---------+---------+----
DSNT408I SQLCODE = -118, ERROR: THE OBJECT TABLE OR VIEW OF THE DELETE OR UPDATE STATEMENT IS ALSO IDENTIFIED IN A FROM CLAUSE
|
_________________
murthy |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Dec 14, 2006 8:05 am Post subject: |
|
|
mvenkatesha,
what is your DB2 version ? The older version of DB2 (< version 7) does not support and update and select statement on the same object within a single query.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mvenkatesha
Beginner
Joined: 10 Oct 2006
Posts: 16
Topics: 6
Location: Bangalore
|
| Posted: Thu Dec 14, 2006 10:05 am Post subject: |
|
|
| kolusu wrote: | mvenkatesha,
what is your DB2 version ? The older version of DB2 (< version 7) does not support and update and select statement on the same object within a single query.
Hope this helps...
Cheers
Kolusu |
Kolusu,
I am using DB2V7.
| Code: |
Enter LOGON parameters below: RACF LOGON parameters:
Userid ===> E02V74
Password ===> New Password ===>
Procedure ===> IKJDB2V7 Group Ident ===>
Acct Nmbr ===> ACCT#
Size ===>
Perform ===>
Command ===>
|
_________________
murthy |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Thu Dec 14, 2006 10:49 am Post subject: |
|
|
The query runs fine on my system and we are running DB2 version 7. Run the following query in spufi
| Code: |
SELECT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
ORDER BY NAME;
|
From the results of the above query, see if you can find of the following table.
| Code: |
SYSIBM.SYSCOLDIST_HIST
|
If you find it then you are running DB2 V7 and higher or else you are running an older version.
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
mvenkatesha
Beginner
Joined: 10 Oct 2006
Posts: 16
Topics: 6
Location: Bangalore
|
| Posted: Mon Dec 18, 2006 6:45 am Post subject: |
|
|
| kolusu wrote: | The query runs fine on my system and we are running DB2 version 7. Run the following query in spufi
| Code: |
SELECT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
ORDER BY NAME;
|
From the results of the above query, see if you can find of the following table.
| Code: |
SYSIBM.SYSCOLDIST_HIST
|
If you find it then you are running DB2 V7 and higher or else you are running an older version.
Hope this helps...
Cheers
Kolusu |
Kolusu,
Thanks for the Information. You are right. I am not find the table. Below are the only tables starting with SYSCOL.
| Code: |
SELECT NAME
FROM SYSIBM.SYSTABLES
WHERE CREATOR = 'SYSIBM'
ORDER BY NAME; A
---------+---------+-------
NAME
---------+---------+-------
SYSCOLAUTH
SYSCOLDIST
SYSCOLDISTSTATS
SYSCOLSTATS
SYSCOLUMNS |
_________________
murthy |
|
| Back to top |
|
|
mvenkatesha
Beginner
Joined: 10 Oct 2006
Posts: 16
Topics: 6
Location: Bangalore
|
| Posted: Mon Dec 18, 2006 7:30 am Post subject: |
|
|
Kolusu,
I went to my friend's project where DB2 Version 7 was used. I was able to get the result for the above query and it worked perfetly. Thanks a lot for your detailed explination.
_________________
murthy |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Mon Dec 25, 2006 4:47 pm Post subject: |
|
|
Dear Kolusu:
can you let us knkow where can we get these kind of programming constructs information in DB2.
I think my next module needs a lot of programming on table. Which other wise involve lots of queries between cobol and db2.
_________________
Thanks |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
