topic load from select return different results in QlikView

load from select return different results

shayraber — Thu, 16 Jun 2016 12:27:53 GMT

hi,

I'm working on a script which I didn't wrote.

the script part that saves data tend, lately, to return only few records from time to time (original source does not change)

the script is written in a way I'm not used to and i don't know if it might be the reason.

this is the script:

SQL SELECT * FROM system.dbo.MITX_USER_PROJ;
STORE MITX_USER_PROJ into $(Path_Full_QVD)\MITX_USER_PROJ (qvd); DROP Table MITX_USER_PROJ;

instead of writing it:

MITX_USER_PROJ:
SQL SELECT * FROM system.dbo.MITX_USER_PROJ;
STORE * From MITX_USER_PROJ into $(Path_Full_QVD)\MITX_USER_PROJ.qvd (qvd); DROP Table MITX_USER_PROJ;

(sometimes, instead of ~300Kb the result returns as a ~70Kb file)

can someone please assist?

happened to someone?

does the script change will help (not familiar with that file type)?

Re: load from select return different results

sunny_talwar — Thu, 16 Jun 2016 12:47:48 GMT

I think the more information in the script is always better. I would put the new script that your proposed

MITX_USER_PROJ:
SQL SELECT * FROM system.dbo.MITX_USER_PROJ;
STORE * From MITX_USER_PROJ into $(Path_Full_QVD)\MITX_USER_PROJ.qvd (qvd); DROP TableMITX_USER_PROJ;

Re: load from select return different results

marcus_sommer — Thu, 16 Jun 2016 12:52:07 GMT

I think that the syntax-adjustments won't be effect the number of records respectively the resulting file-size and I believe the reason for this behaviour is that the database returned a different number of records - maybe because there are some records blocked from other read- or write-handles in the database.

- Marcus

Re: load from select return different results

shayraber — Thu, 16 Jun 2016 12:52:09 GMT

thanks, that's somthing i intend to do.

BUT

any idea why the result changes?

the script was scheduled to 2:30pm and the file stored was a file with size of 60Kb.

after sawing that, i've reloded the document manually at 3:00pm (half an hour later) and the result was (as it should have been from the begining) a file with size of 320Kb.

any thoughts?

Re: load from select return different results

shayraber — Thu, 16 Jun 2016 12:55:54 GMT

thanks!

but

the syntax change was my idea since i heard (long time ago, when i learned QV) that the current syntax might cause problems.

second, the document was loaded twice in less than 30 minutes and the result came out different.

ONLY Source DB could be the issue?

(DBA says nothing is wrong with the user and its privileges)

Re: load from select return different results

sunny_talwar — Thu, 16 Jun 2016 13:00:12 GMT

Try this script:

SQL SELECT *

FROM system.dbo.MITX_USER_PROJ;
STORE MITX_USER_PROJ into $(Path_Full_QVD)\MITX_USER_PROJ_OLD (qvd);

DROP Table MITX_USER_PROJ;

MITX_USER_PROJ_NEW:
SQL SELECT *

FROM system.dbo.MITX_USER_PROJ;
STORE * From MITX_USER_PROJ_NEW into $(Path_Full_QVD)\MITX_USER_PROJ_NEW.qvd (qvd);

DROP Table MITX_USER_PROJ_NEW;

And check if you still see any difference between MITX_USER_PROJ_NEW.qvd and MITX_USER_PROJ_OLD.qvd

Re: load from select return different results

marcus_sommer — Thu, 16 Jun 2016 13:01:55 GMT

I didn't meant something with the user or their privileges. I meant that if anyone is accessing the database the database could lock those records to avoid conflictings between multiple accesses at the same time - in general this is the default-beaviour from most of the database-types (and which could be sometimes to some degree customized).

- Marcus

Re: load from select return different results

shayraber — Thu, 16 Jun 2016 13:09:27 GMT

thanks

already did that

there was no different between the two options

what I've suspected is that the current script might be "unstable"

but no. the two ways resulted the same,

but on the other hand- something made the script to return a small file at first and larger file less than 30 minutes afterwards

Re: load from select return different results

sunny_talwar — Thu, 16 Jun 2016 13:15:02 GMT

Number of rows are the same? or did that change as well? Check the logfile to see if something is not right in one or the other?

Re: load from select return different results

shayraber — Thu, 16 Jun 2016 14:00:40 GMT

that's probably the reason

was able to contact the guy who wrote the script

he said this is a customized table which sync with others and it happened before. it's a DB sync issue