COUNT Problem

[snassimr]

Hi !

I need to run COUNT filename command without " in file name

COUNT HASH1

not

COUNT 'HASH1'

How to remove ' from command before I do DSExecute ?

[ray.wurlod]

Don't put it in there in the first place, and you won't need to remove it.

Code: Select all

Call DSExecute("UV", "COUNT hashedfile", Output, SystemReturnCode)

or

Code: Select all

HashedFileName = "hashedfile"
Call DSExecute("UV", "COUNT " : HashedFileName, Output, SystemReturnCode)

The fourth argument contains the number of records counted (or a negative value if there was an error), so you don't even have to parse the third argument. But you can if you want to.

Code: Select all

RecordCount = Oconv(Output,"MCN")

[snassimr]

I dont want to hardcode the hashfile name . I want to pass it to routine

So your option not suitable for me

Any ideas ?

[chulett]

So, go ahead and pass it. Nothing about what Ray posted makes that "not suitable" for you.

[ray.wurlod]

Read through my post again, particularly the second example.

[snassimr]

I dont understand what happen but still the name is quoted

I get error with two your options:

Result = Retrieve: syntax error. Unexpected sentence without filename. Token was "".
Scanned command was COUNT 'H_DW_DIM_FARMACY'

I need to get COUNT H_DW_DIM_FARMACY

Have a nice day

[ray.wurlod]

Code: Select all

FUNCTION CountHashedFile(HashedFileName)

Command = "COUNT " : HashedFileName

Call DSExecute("UV", Command, Output, SystemReturnCode)

Ans = SystemReturnCode

RETURN(Ans)

Sheesh!

[snassimr]

Arg1 = H_DW_DIM_FARMACY

Test completed.


Result = Retrieve: syntax error. Unexpected sentence without filename. Token was "".
Scanned command was COUNT 'H_DW_DIM_FARMACY'

How to remove ' from Command . What is his char code ?

[ray.wurlod]

Code: Select all

Command = "COUNT " : Arg1
Call DSExecute("UV", Command, Output, Code)
Call DSLogInfo("Command = ": Command, "CountHashedFile")
Call DSLogInfo("Output = " : Quote(Output) : @FM : "Code = " : Code, "CountHashedFile")
Ans = Code

TEST #1
*******

Arg1 = VOC

Test completed.

DSLogInfo called from : CountHashedFile
Message to be logged is...
> Command = COUNT VOC
DSLogInfo called from : CountHashedFile
Message to be logged is...
> Output = "
> 4746 records counted.
> "
> Code = 4746

Result = 4746

DataStage version 7.5, server HP-UX 11.0

I think it must have something to do with the way the command is being passed through Windows, since this is the only difference apparent in our two scenarios. I won't have access to a Windows server this week; maybe someone else can replicate the test.

[ray.wurlod]

Found it!

The problem is the mixed case table name. UniVerse (DataStage) puts the quotes on when there's a non-all-upper-case name. This also occurs through the Command window of the Administrator client.

Can't find a solution using COUNT verb, but you can use quoted identifiers in SQL. For example

Code: Select all

SELECT COUNT(*) FROM "hashedfile";

Code: Select all

Command = "SELECT COUNT(*) FROM " : DQuote(HashedFileName) : ";"
Call DSExecute("UV", Command, Output, SystemReturnCode)

[snassimr]

I get


Result = DataStage/SQL: Table "H_DW_DIM_FARMACY" does not exist.

THe hashed file not written as the UV table .

How to create UV table ?

[snassimr]

Sorry , it working !

the both your codes .

But can you explane how to create UV table in Project Directory ?Or where to read about it ?

[ray.wurlod]

It doesn't have to be in the project directory. Search the forum for SETFILE and VOC.