New to DB2: Need help in forming a query - Page 2

ab_yunus · #16 (**permalink**) 08-17-11, 15:38

Ok..

Here's my exact query Andy.

Code:

select UNIT_ID, count (distinct PS_SGN_UID) as total_num_records, count (distinct (nullif(PRT_TS, '0')) as PRINTED_NUM from PS_BATCH_SIGNS where SGN_ST_DT ='2011-08-16' group by UNIT_ID with ur;

and the error thrown is

Code:

<eb1>SQL0199N  The use of the reserved word "FROM" following "" is not valid.  Expected tokens may include:  ") ,".  SQLSTATE=42601

State:42601,Native:-199,Origin:[IBM][CLI Driver][DB2]</eb1>

ARWinner · #17 (**permalink**) 08-17-11, 15:41

Read the error message closely, then pair up you parenthesis.

Andy

tonkuma · #18 (**permalink**) 08-17-11, 15:54

Quote:

from your query, it throws me the below error?

Code:

<eb1>SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

State:22007,Native:-180,Origin:[IBM][CLI Driver][DB2]</eb1>

What were the exact query you executed and exact data you used?

I used exactly your supplied data(copied and added some special characters to conform DB2 SQL syntax) and added a column batch_date.
And I showed(copy and paste) my used data, executed query, and the result.

ab_yunus · #19 (**permalink**) 08-17-11, 15:57

I dont know how to find out db2 version. the commands i found on google are throwing errors again.

From the query tool, i find that the db is db2 and the version is V9.1

I am on Windows XP and the Query tool i use is "Query Tool using ODBC" (MBCS Build)

ab_yunus · #20 (**permalink**) 08-17-11, 16:00

Ok.

Here's the query that I ran now, after checking for the parenthesis.

Code:

select UNIT_ID, 
count (distinct PS_SGN_UID) as total_num_records, 
count (distinct (nullif(PRT_TS, '0'))) as PRINTED_NUM from PS_BATCH_SIGNS
where SGN_ST_DT ='2011-08-16' group by UNIT_ID with ur;

and the error I obtained now is

Code:

<eb1>SQL0180N  The syntax of the string representation of a datetime value is incorrect.  SQLSTATE=22007

State:22007,Native:-180,Origin:[IBM][CLI Driver][DB2]</eb1>

tonkuma · #21 (**permalink**) 08-17-11, 16:36

The values of printed_time you showed were like
09:11.6
or
0

They are not string representations of time datatype.
So, I couldn't make the data type of printed_time used in my test as TIME.

If your actual data was using TIME datatype for printed_time,
please show the sample data exactly conform to TIME datatype of DB2, like
09:11:06 or 00:09:11 instead of 09:11.6
and
00:00:00 instead of 0

ab_yunus · #22 (**permalink**) 08-17-11, 16:52

Hi,

I even dont know how to check the datatype of the column. Cos, DESC, DESCRIBE dont work. I believe it wont be 'zero' when it is not printed, but it would be a null value. See below:
7032 532974
7032 532975
7032 532976
7032 532977
7032 532978
7032 532979
7032 532980
7032 532981
7032 532997
7032 532998
7032 532988
7032 532989
7032 532990
7032 532991
7032 532992
7032 532999
7032 533000
7032 533001

the third column is null for the branch 7032. See another example which has some records printed and some aren't.

7065 607482 13:42.9
7065 607483 13:42.9
7065 607515 58:27.7
7065 607516 02:21.8
7065 607517
7065 607485 03:16.5
7065 607518
7065 607519 58:27.7
7065 607520
7065 607488 13:42.9
7065 607489 13:42.9

So. That's how my data is.

tonkuma · #23 (**permalink**) 08-17-11, 17:14

1)
What client are you using?
13:42.9 is not a string representation of TIME datatype.(See following example result was SQL0180N error.)
So, if 13:42.9 was the exact representation of printed_time,
client program might did its own conversion to string from TIME data.

Example.

Code:

------------------------------ Commands Entered ------------------------------
SELECT TIME('13:42.9') FROM sysibm.sysdummy1;
------------------------------------------------------------------------------

1       
--------
SQL0180N  The syntax of the string representation of a datetime value is 
incorrect.  SQLSTATE=22007

2) If the data was not printed, it was null.
Then, NULLIF wouldn't be necessary, like...

Code:

SELECT unit_id
     , COUNT(*) AS number_of_rows
     , COUNT(printed_time) AS count_printed_time
 FROM  your_data
 WHERE batch_date = '2011-08-16'
 GROUP BY
       unit_id
 ORDER BY
       unit_id
;

ab_yunus · #24 (**permalink**) 08-17-11, 17:29

Wow... You guys Rock.

Thank you. Thanks a lot. That query worked. Here are the results.

7053 16 16
7055 31 31
7056 16 16
7057 14 14
7058 20 20
7061 22 22
7062 29 29
7063 28 28
7064 25 25
7065 50 50
7067 23 23
7073 19 19
7074 23 23
7075 17 17
7076 29 29
7077 31 31
7078 23 0
7079 30 30
7080 18 18
7081 16 16
7082 30 30
7084 31 31

Wow... But,

Can you explain the query for me in simple terms? Please?

tonkuma · #25 (**permalink**) 08-17-11, 18:07

Although I don't know what would be simple terms for you.
I'll try...

COUNT(*) counts number of rows satisfying conditions,
even if all columns of a row were null, the row would be counted.

COUNT(expression) counts non-null values of the expression. Each duplicated values also counted repeatedly.
A column name is a (kind of) expression.

COUNT(DISTINCT expression) counts non-null values of the expression. Each duplicated values counted once.
In other words, it counts non-null unique values of the expression.

See also
COUNT - IBM DB2 9.7 for Linux, UNIX, and Windows

ab_yunus · #26 (**permalink**) 08-17-11, 19:37

hi Tonkuma,

thank you. It helped me in understanding the query. I will read the article too. Thanks for your efforts.

Internet Services

Web Development

Digital Marketing

Consumer Tech