Is there a way to assigned constant value instead of entering prompt in SQL ?

[Ken]

Hi,

Want to have a constant value stored in a variable or field and
then use it in every different queries to avoid prompts ?

e.g.
1) Created a table with constant value 
   Example: Table: tblItem and 
            field: fldItemNbr with a value item1001

2) Below query gives error.
    [COLOR=#CC0000]Syntax Error. in query express (select....).[/color]

    [b]select (select max(fldItemNbr) from tblItem) as ItemNbr, 
           SubItem, ItemName, sum(Qty)
    from   TableMtrl
    GROUP BY ItemNbr,SubItem, ItemName
    [/b]


This Works:
SELECT O.CustomerID, O.OrderDate
FROM   Orders O
WHERE (((O.OrderDate)=(select max(fldDate) from tbl01)));

Help/suggestion way to get a constant value without typing on every prompt ?
Thx,

Ken

 

[Ken]

Hi,

Found a solution, but query runs slow to give result.
Looking for other query option to run faster

select       t.ItemNbr, 
             SubItem, ItemName, sum(Qty)
    from     TableMtrl, 
            (select max(fldItemNbr) as ItemNbr from tblItem) as t
    GROUP BY t.ItemNbr,SubItem, ItemName

 

[SkipVought]

Hi,

Something like this?

‘
    select ItemNbr, SubItem, ItemName, sum(Qty)
    from   TableMtrl
    Where ItemNbr = (select max(fldItemNbr) from tblItem)
    GROUP BY ItemNbr, SubItem, ItemName

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

You can use tempvars or a function also. However, these will not persist once the db is closed.

Public SomeVariable as String ' or other datatype

Public Function GetSomeVariable() as string
  GetSomeVariable = SomeVariable
end function

Select GetSomeVariable as SomeVariable, otherFields from someTable.